oracle实训学习笔记

伪列（rowid、rownum）
一，什么是伪列RowID？
1,首先是一种数据类型，唯一标识一条记录物理位置的一个id，基于64位编码的18个字符显示。

2,未存储在表中，可以从表中查询，但不支持插入，更新，删除它们的值。

二，RowID的用途
1,在开发中使用频率应该是挺多的，特别在一些update语句中使用更加频繁。

所以oracle ERP 中大部份的视图都会加入rowid这个字段。

在一些cursor定义时也少不了加入rowid。

但往往我们在开发过程中，由于连接的表很多，再加上程序的复制，有时忽略了rowid对应的是那一个表中rowid，所以有时过程出错，往往发上很多时间去查错，最后查出来既然是update时带的rowid并非此表的rowid,所以在发现很多次的错误时，重视rowid起来了，开发中一定要注意rowid的匹配
2，能以做快的方式访问表中的一行。

3，能显示表的行是如何存储的。

4，作为表中唯一标识。

三，RowID的组成
rowid确定了每条记录是在Oracle中的哪一个数据对象，数据文件、块、行上。

ROWID 的格式如下：
数据对象编号文件编号块编号行编号
OOOOOO FFF BBBBBB RRR
由data_object_id# + rfile# + block# + row# 组成，占用10个bytes的空间，
32bit的data_object_id#,
10 bit 的rfile#,
22bit 的block#,
16 bit 的row#.
所以每个表空间不能超过1023个数据文件。

四，RowID的应用
1，查找和删除重复记录
当试图对库表中的某一列或几列创建唯一索引时，
系统提示ORA-01452 ：不能创建唯一索引，发现重复记录。

/*conn scott/tiger
Create table empa as select * from emp;
插入重复记录
insert into empa select * from emp where empno = 7369;
insert into empa select * from emp where empno = 7839;
insert into empa select * from emp where empno = 7934;
*/
查找重复记录的几种方法：
查找大量重复记录
select empno from empa group by empno having count(*) >1;
Select * From empa Where ROWID Not In(Select Min(ROWID) From empa Group By empno);
查找少量重复记录
select * from empa a where rowid<>(select max(rowid) from empa where empno=a.empno );
删除重复记录的几种方法：
(1).适用于有大量重复记录的情况(列上建有索引的时候，用以下语句效率会很高)：
Delete empa Where empno In (Select empno From empa Group By empno Having Count(*) > 1)
And ROWID Not In (Select Min(ROWID) From empa Group By empno Having Count(*) > 1);
Delete empa Where ROWID Not In(Select Min(ROWID) From empa Group By empno);
(2).适用于有少量重复记录的情况(注意，对于有大量重复记录的情况，用以下语句效率会很低)：
Delete empa a where rowid<>(select max(rowid) from empa where empno=a.empno );
整理ROWID二
搞oracle都会经常碰到rowid，本文是笔者根据网上各位的文章，加上自己学习中的体会，总结而成。

一.rowid简介
rowid就是唯一标志记录物理位置的一个id，在oracle 8版本以前，rowid由file#+block#+row#组成，占用6个bytes的空间，10 bit 的file# ，22bit 的block# ，16 bit 的row#。

从oracle 8开始rowid变成了extend rowid，由data_object_id#+rfile#+block#+row#组成，占用10个bytes的空间，32bit的data_object_id#,10 bit 的rfile#,22bit 的block#,16 bit 的row#.由于rowid的组成从file#变成了rfile#，所以数据文件数的限制也从整个库不能超过1023个变成了每个表空间不能超过1023个数据文件。

说了rowid的组成，那么我们再来看看rowid在索引里面占用的字节数又是什么样子的。

在oracle 8以前索引中存储的rowid占用字节数也是6bytes,在oracle8之后，虽然oracle 使用了extend rowid，但是在普通索引里面依然存储了bytes的rowid，只有在global index 中存储的是10bytes的extend rowid，而extend rowid也是global index出现的一个必要条件，下面我们会解释原因。

为什么golbal index需要把data_object_id#也包含在index rowid entry中呢？如果不包含会这么样？首先我们需要知道index的rowid entry的存在是为了能根据它找到表的这条
记录存在哪个具体的物理位置，我们需要知道它在哪个数据文件，在哪个block，在那一行，普通的索引oracle根据rfile#,block#,row#就可以知道了，但是partition table可以分布在多个表空间，也就是可以分布在多个数据文件，当我们建立local index时，index rowid entry 并不包含data_object_id#，因为oracle可以知道这个index对应的是哪一个table分区，并可以得到table分区的ts#(tablespace号)，那么oracle根据ts#和rfile#就可以找到具体的数据文件。

但是如果换成是golbal index，如果不包含data_object_id#，那么我们并不能知道这个索引对应着哪个表分区，也自然不能知道它的rfile#和file#的转换关系，所以它将找不到所对应的记录。

包含data_object_id#后,oracle可以根据data_object_id#实现rfile#和file#的转换然后找到记录对应的物理位置。

需要注意的是要理解以上概念我们还是需要了解file#和rfile#的区别。

二.比较file#和rfile#
oracle数据文件为什么存在file#和rfile#?
归根结底的原因是因为ROWID 的存储格式造成的，因为rowid 中文件编号标志只有10bit,最大数据容量1024，由于不存在0编号文件，所以实际上只允许1023个文件编号。

在oracle8 之前的版本的数据库中，rowid是受限的，只包括file# /block# /row# ,则数据库最多只允许1023个文件。

而oracle8开始rowid 包括data_object_id# / Rfile# /block# /rowid# 。

data object id 的引入，同时支持了表分区的概念，一个表可以拥有多个分区（segment）,而一个分区可以在不同的表空间中（由Rfile# 表示在segment对应的表空间中对应的相对文件编号）。

这样表的容量也增大了。

扩展的rowid使得oracle不再局限于数据文件只能有1023个的限制，而一个表可以分区，也使得表的容量不再局限于单个表空间中（1023个文件的限制）。

当然，你或许要问，为什么oracle不调整rowid中表示file# 的bit数量，这个应该是由于兼容性的引起的，在oracle7 的索引中存储的rowid就是file# + block# + row# ,，因为这样处理后关于索引的存储，oracle8和oracle7没有发生变化（在oracle8中一个索引（可能分区）segment肯定对应了一个表（可能分区）的segment，这个可以由数据字典关系得到，从而确立了索引中的rowid 对应哪个表空间中的数据文件），在升级的时候就不用关心索引的问题，而直接升级oracle软件以及运行相关的包，否则将会大动干戈解决索引的问题。

这就是oracle实现物理文件升级的基础。

当然，真正升级的时候，一些数据文件头的rfile# 需要发生变化，这也是有文件的一些存储的特性决定的，为了不和oracle8的格式发生冲突，才需要修改。

这个修改代价非常的小，所以oracle选择了这个方案。

详细的信息，大家可以去参考metalink相关内容，有详细的存储(byte 中字节位)的变化关系。

三.rowid举例
1.创建一临时表
create table test_rowid (id number, row_id rowid);
2.插入一行记录
insert into test_rowid values(1,null);
3.修改刚插入的记录
update test_rowid set row_id = rowid where id = 1;
4.查看rowid
select rowid,row_id from test_rowid;
返回结果为：
rowid row_id
AAAO0DAAJAAAAMYAAA AAAO0DAAJAAAAMYAAA
Oracle的物理扩展ROWID有18位，每位采用64位编码，分别用A~Z、a~z、0~9、+、/共64个字符表示。

A表示0，B表示1，……Z表示25，a表示26，……z表示51，0表示52，……，9表示61，+表示62，/表示63。

ROWID具体划分可以分为4部分。

(1).OOOOOO：前6位表示DATA OBJECT NUMBER，将起转化位数字后匹配DBA_OBJECTS中的DATA_OBJECT_ID，可以确定表信息。

如上面例子中的DATA OBJECT NUMBER是AAAO0D，转化位数字是14×64×64 ＋52×64 ＋3。

输入以下查询：
select owner, object_name from dba_objects where data_object_id = 14*64*64 + 52*64 + 3;
返回：
OWNER OBJECT_NAME
WG TEST_ROWID
(2)FFF：第7到9位表示相对表空间的数据文件号。

上面的例子中是AAJ，表示数据文件9。

输入以下查询：
(3).BBBBBB：第10到15位表示这条记录在数据文件中的第几个BLOCK中。

上面的例子是AAAAMY，转化位数字是12×64＋24，表示这条记录在数据文件中的第792个BLOCK。

(4).RRR：最后3位表示这条记录是BLOCK中的第几条记录。

上面的例子是AAA，表示第0条记录（总是从0开始计数）。

四.参考资料
1.oracle rowid
2.Oracle基本数据类型存储格式浅析（四）——ROWID类型（一）
3.oracle数据文件为什么存在Rfile# and file#
---------------------------------------------------------------------------------------------------------------------------------
------------------
注意：rownum从1开始；
rownum按照记录插入时的顺序给记录排序，所以有order by的子句时一定要注意啊！
使用时rownum，order by字段是否为主键有什么影响？
子查询中rownum rn，而rn用到外查询中到底是怎样的序列？
若id主键是按照从小到大的顺序插入的，select语句没有group by 和order by 的子句时，rownum的顺序和id顺序基本一致。

对于Oracle 的rownum 问题，很多资料都说不支持>,>=,=,between...and，只能用以上符号(<、<=、!=)，并非说用>,>=,=,between..and 时会提示SQL语法错误，而是经常是查不出一条记录来，还会出现似乎是莫名其妙的结果来，其实您只要理解好了这个rownum 伪列的意义就不应该感到惊奇，同样是伪列，rownum 与rowid 可有些不一样，下面以例子说明
假设某个表t1(c1) 有20 条记录
如果用select rownum,c1 from t1 where rownum < 10, 只要是用小于号，查出来的结果很容易地与一般理解在概念上能达成一致，应该不会有任何疑问的。

可如果用select rownum,c1 from t1 where rownum > 10 (如果写下这样的查询语句，这时候在您的头脑中应该是想得到表中后面10条记录)，你就会发现，显示出来的结果要让您失望了，也许您还会怀疑是不谁删了一些记录，然后查看记录数，仍然是20 条啊？那问题是出在哪呢？
先好好理解rownum 的意义吧。

因为ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列(强调：先要有结果集)。

简单的说rownum 是对符合条件结果的序列号。

它总是从1开始排起的。

所以你选出的结果不可能没有1，而有其他大于1的值。

所以您没办法期望得到下面的结果集：
11 aaaaaaaa
12 bbbbbbb
13 ccccccc
.................
rownum >10 没有记录，因为第一条不满足去掉的话，第二条的ROWNUM又成了1，所以永远没有满足条件的记录。

或者可以这样理解：
ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。

它取得第一条记录则rownum值为1，第二条为2，依次类推。

如果你用>,>=,=,between...and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除，接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。

有了以上从不同方面建立起来的对rownum 的概念，那我们可以来认识使用rownum 的几种现像
1. select rownum,c1 from t1 where rownum != 10 为何是返回前9条数据呢？它与select rownum,c1 from tablename where rownum < 10 返回的结果集是一样的呢？
因为是在查询到结果集后，显示完第9 条记录后，之后的记录也都是!= 10,或者>=10,所以只显示前面9条记录。

也可以这样理解，rownum 为9后的记录的rownum为10，因条件为!=10，所以去掉，其后记录补上，rownum又是10，也去掉，如果下去也就只会显示前面9条记录了。

2. 为什么rownum >1 时查不到一条记录，而rownum >0 或rownum >=1 却总显示所有的记录？
因为rownum 是在查询到的结果集后加上去的，它总是从1开始。

3. 为什么between 1 and 10 或者between 0 and 10 能查到结果，而用between 2 and 10 却得不到结果？
原因同上一样，因为rownum 总是从 1 开始。

从上可以看出，任何时候想把rownum = 1 这条记录抛弃是不对的，它在结果集中是不可或缺的，少了rownum=1 就像空中楼阁一般不能存在，所以你的rownum 条件要包含到1 。

但如果就是想要用rownum > 10 这种条件的话话就要用嵌套语句,把rownum 先生成，然后对他进行查询。

select *
from (selet rownum as rn，t1.* from a where ...)
where rn >10
一般代码中对结果集进行分页就是这么干的。

另外：rowid 与rownum 虽都被称为伪列，但它们的存在方式是不一样的，rowid 可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中唯一。

只要记录没被搬动过，rowid是不变的。

rowid 相对于表来说又像表中的一般列，所以以rowid 为条件就不会有rownum那些情况发生。

另外还要注意：rownum不能以任何基表的名称作为前缀。

对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的
是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。

(1) rownum 对于等于某值的查询条件
如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。

但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。

因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，所以无法查到rownum = n（n>1的自然数）。

SQL> select rownum,id,name from student where rownum=1;（可以用在限制返回记录条数的地方，保证不出错，如：隐式游标）
SQL> select rownum,id,name from student where rownum =2;
ROWNUM ID NAME17:20 2014-6-30
（2）rownum对于大于某值的查询条件
如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立，所以查不到记录。

查找到第二行以后的记录可使用以下的子查询方法来解决。

注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。

SQL>select * from(select rownum no ,id,name from student) where no>2;
NO ID NAME
---------- ------ ---------------------------------------------------
3 200003 李三
4 200004 赵四
（3）rownum对于小于某值的查询条件
rownum对于rownum<n（(n>1的自然数）的条件认为是成立的，所以可以找到记录。

SQL> select rownum,id,name from student where rownum <3;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200001 张一
2 200002 王二
查询rownum在某区间的数据，必须使用子查询。

例如要查询rownum在第二行到第三行之间的数据，包括第二行和第三行数据，那么我们只能写以下语句，先让它返回小于等于三的记录行，然后在主查询中判断新的rownum的别名列大于等于二的记录行。

但是这样的操作会在大数据集中影响速度。

SQL> select * from (select rownum no,id,name from student where rownum<=3 ) where no >=2;
NO ID NAME
---------- ------ ---------------------------------------------------
2 200002 王二
3 200003 李三
（4）rownum和排序
Oracle中的rownum的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的rowmun行数据就必须注意了。

SQL> select rownum ,id,name from student order by name;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
3 200003 李三
2 200002 王二
1 200001 张一
4 200004 赵四
可以看出，rownum并不是按照name列来生成的序号。

系统是按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。

为了解决这个问题，必须使用子查询；
SQL> select rownum ,id,name from (select * from student order by name);
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200003 李三
2 200002 王二
3 200001 张一
4 200004 赵四
这样就成了按name排序，并且用rownum标出正确序号（有小到大）
笔者在工作中有一上百万条记录的表，在jsp页面中需对该表进行分页显示，便考虑用rownum来作，下面是具体方法(每页显示20条)：
“select * from tabname where rownum<20 order by name" 但却发现oracle却不能按自己的意愿来执行，而是先随便取20条记录，然后再order by，后经咨询oracle,说rownum确实就这样，想用的话，只能用子查询来实现先排序，后rownum，方法如下：
"select * from (select * from tabname order by name) where rownum<20",但这样一来，效率会低很多。

后经笔者试验，只需在order by 的字段上加主键或索引即可让oracle先按该字段排序，然后再rownum；方法不变：“select * from tabname where rownum<20 order by name"
取得某列中第N大的行
select column_name from
(select table_name.*,dense_rank() over (order by column desc) rank from table_name)
where rank = &N；
假如要返回前5条记录：
select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6)
假如要返回第5-9条记录：
select * from tablename
where …
and rownum<10
minus
select * from tablename
where …
and rownum<5
order by name
选出结果后用name排序显示结果。

(先选再排序)
注意：只能用以上符号(<、<=、!=)。

select * from tablename where rownum != 10;返回的是前９条记录。

不能用：>,>=,=,Between...and。

由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件不成立。

另外，这个方法更快：
select * from (
select rownum r,a from yourtable
where rownum <= 20
order by name )
where r > 10
这样取出第11-20条记录!(先选再排序再选)
要先排序再选则须用select嵌套：内层排序外层选。

rownum是随着结果集生成的，一旦生成，就不会变化了；同时,生成的结果是依次递加的，没有1就永远不会有2!
rownum 是在查询集合产生的过程中产生的伪列，并且如果where条件中存在rownum 条件的话，则:
1：假如判定条件是常量，则：
只能rownum = 1, <= 大于1 的自然数，= 大于1 的数是没有结果的；大于一个数也是没有结果的
即当出现一个rownum 不满足条件的时候则查询结束this is stop key（一个不满足，系统将该记录过滤掉，则下一条记录的rownum还是这个，所以后面的就不再有满足记录，this is stop key）；
2：假如判定值不是常量，则：
若条件是= var , 则只有当var 为1 的时候才满足条件，这个时候不存在stop key ,必须进行full scan ,对每个满足其他where条件的数据进行判定，选出一行后才能去选rownum=2的行……
---------------------------------------------------------------------------
以下摘自《中国IT实验室》
1.在oracle中实现select top n
由于oracle不支持select top语句，所以在oracle中经常是用order by跟rownum的组合来实现select top n的查询。

简单地说，实现方法如下所示：
select列名１．．．列名ｎfrom
(select列名１．．．列名ｎfrom 表名order by 列名１．．．列名ｎ)
where rownum<=n（抽出记录数）
order by rownum asc
下面举个例子简单说明一下。

顾客表customer(id,name)有如下数据：
ID NAME
01 first
02 Second
03 third
04 forth
05 fifth
06 sixth
07 seventh
08 eighth
09 ninth
10 last
则按NAME的字母顺抽出前三个顾客的SQL语句如下所示：
select * from
(select * from customer order by name)
where rownum<=3
order by rownum asc
输出结果为：
ID NAME
08 eighth
05 fifth
01 first
-------------------------------------------------
ROWID伪列
每个表在oracle内部都有一个ROWID伪列,它在所有sql中无法显示，不占存储空间;它用于从表中查询行的地址或者在where中进行参照,一个例子如下
先对rowid有个感官认识：
SQL> select ROWID from Bruce_test where rownum<2;
ROWID
------------------ ----------
AAABnlAAFAAAAAPAAA
ROWID的格式如下：
数据对象编号文件编号块编号行编号
OOOOOO FFF BBBBBB RRR
我们可以看出，从上面的rowid可以得知：
AAABnl 是数据对象编号
AAF是相关文件编号
AAAAAP是块编号
AAA 是行编号
怎么依据这些编号得到具体的十进制的编码值呢，这是经常遇到的问题。

这里需要明白rowid的是基于64位编码的18个字符显示（数据对象编号(6) +文件编号(3) +块编号(6)+ 行编号(3)=18位），其中
A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
#根据rowid抽块对像编号
SQL> select dbms_rowid.rowid_object('AAAAASAABAAAGiRAAG') obj# from dual;
OBJ#
----------
18
#根据rowid抽取表空间相对文件号
SQL> select dbms_rowid.rowid_relative_fno('AAAAASAABAAAGiRAAG') rfile# from dual;
RFILE#
----------
1
#根据rowid抽取块号
SQL> select dbms_rowid.ROWID_BLOCK_NUMBER('AAAAASAABAAAGiRAAG') block# from dual;
BLOCK#
----------
26769
#根据rowid抽取行号
SQL> select dbms_rowid.rowid_row_number('AAAAASAABAAAGiRAAG') row# from dual;
ROW#
----------
6
#将extended rowid转换成为restricted rowid
SQL> select dbms_rowid.rowid_to_restricted('AAAAASAABAAAGiRAAG',0) restricted_rowid from dual;
RESTRICTED_ROWID
------------------
00006891.0006.0001
除了用dbms_rowid来抽取rowid的不同部分外，也可以用substr
#extended rowid
SQL> SELECT ROWID,
2007-02-01 15:19:28
2 SUBSTR(ROWID,1,6) "OBJECT",
3 SUBSTR(ROWID,7,3) "FIL",
4 SUBSTR(ROWID,10,6) "BLOCK",
5 SUBSTR(ROWID,16,3) "ROW"
6 from obj$ where rownum<=5;
ROWID OBJECT FIL BLOCK ROW
------------------ ------------ ------ ------------ ------
AAAAASAABAAAAB6AAa AAAAAS AAB AAAAB6 AAa AAAAASAABAAAAB6AAu AAAAAS AAB AAAAB6 AAu AAAAASAABAAAAB6AAF AAAAAS AAB AAAAB6 AAF AAAAASAABAAAAB6AAv AAAAAS AAB AAAAB6 AAv AAAAASAABAAAAB6AAZ AAAAAS AAB AAAAB6 AAZ
#restricted rowid
SQL> SELECT ROWID,
2 SUBSTR(ROWID,15,4) "FILE",
3 SUBSTR(ROWID,1,8) "BLOCK",
4 SUBSTR(ROWID,10,4) "ROW"
5 from obj$ where rownum<=5;
ROWID FILE BLOCK ROW
------------------ -------- ---------------- --------
AAAAASAABAAAAB6AAa 6AAa AAAAASAA AAAA
AAAAASAABAAAAB6AAu 6AAu AAAAASAA AAAA
AAAAASAABAAAAB6AAF 6AAF AAAAASAA AAAA
AAAAASAABAAAAB6AAv 6AAv AAAAASAA AAAA
AAAAASAABAAAAB6AAZ 6AAZ AAAAASAA AAAA
请注意extented rowid与restricted rowid的编码方式不一样，大家不能拿两种不同编码方式的组件作比较,比如AAAAASAABAAAAB6AAa 这行的File#在两种方式下是有不同的值，表示不同的意义,没有可比性.
索引（单列、多列）
在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

对于数据库来说，索引是一个必选项，但对于现在的各种大型数据库来说，索引可以大大提高数据库的性能，以至于它变成了数据库不可缺少的一部分。

索引分类：
逻辑分类
single column or concatenated 对一列或多列建所引
unique or nonunique 唯一的和非唯一的所引，也就是对某一列或几列的键值（key）是否是唯一的。

Function-based 基于某些函数索引，当执行某些函数时需要对其进行计算，可以将某些函数的计算结果事先保存并加以索引，提高效率。

Doman 索引数据库以外的数据，使用相对较少
物理分类
B-Tree ：normal or reverse key B-Tree索引也是我们传统上常见所理解的索引，它又可以分为正常所引和倒序索引。

Bitmap ：位图所引，后面会细讲
B-Tree 索引
B-Tree index 也是我们传统上常见所理解的索引。

B-tree （balance tree）即平衡树，左右两个分支相对平衡。

B-Tree index
Root为根节点，branch 为分支节点，leaf 到最下面一层称为叶子节点。

每个节点表示一层，当查找某一数据时先读根节点，再读支节点，最后找到叶子节点。

叶子节点会存放index entry （索引入口），每个索引入口对应一条记录。

Index entry 的组成部分：
Indexentry entry header 存放一些控制信息。

Key column length 某一key的长度
Key column value 某一个key 的值
ROWID 指针，具体指向于某一个数据
创建索引：
复制代码
用户登录：
SQL> conn as1/as1
Connected.
创建表：
SQL> create table dex (id int,sex char(1),name char(10));
Table created.
向表中插入1000条数据
SQL> begin
2 for i in 1..1000
3 loop
4 insert into dex values(i,'M','chongshi');
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
查看表记录
SQL> select * from dex;
ID SE NAME
---------- -- --------------------
... . .....
991 M chongshi
992 M chongshi
993 M chongshi
994 M chongshi
995 M chongshi
996 M chongshi
997 M chongshi
998 M chongshi
999 M chongshi
1000 M chongshi
1000 rows selected.
创建索引：
SQL> create index dex_idx1 on dex(id);
Index created.
注：对表的第一列（id）创建索引。

查看创建的表与索引
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
DEX TABLE
DEX_IDX1 INDEX
复制代码
索引分离于表，作为一个单独的个体存在，除了可以根据单个字段创建索引，也可以根据多列创建索引。

Oracle要求创建索引最多不可超过32列。

复制代码
SQL> create index dex_index2 on dex(sex,name);
Index created.
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
DEX TABLE
DEX_IDX1 INDEX
DEX_INDEX2 INDEX
复制代码
这里需要理解：
编写一本书，只有章节页面定好之后再设置目录；数据库索引也是一样，只有先插入好数据，再建立索引。

那么我们后续对数据库的内容进行插入、删除，索引也需要随之变化。

但索引的修改是由oracle自动完成的。

上面这张图能更加清晰的描述索引的结构。

跟节点记录0至50条数据的位置，分支节点进行拆分记录0至10.......42至50，叶子节点记录每第数据的长度和值，并由指针指向具体的数据。

最后一层的叶子节是双向链接，它们是被有序的链接起来，这样才能快速锁定一个数据范围。

如：
复制代码
SQL> select * from dex where id>23 and id<32;
ID SE NAME
---------- -- --------------------
24 M chongshi
25 M chongshi
26 M chongshi
27 M chongshi
28 M chongshi
29 M chongshi
30 M chongshi
31 M chongshi
8 rows selected.
复制代码
如上面查找的列子，通过索引的方式先找到第23条数据，再找到第32条数据，这样就能快速的锁定一个查找的范围，如果每条数据都要从根节点开始查找的话，那么效率就会非常低下。

位图索引
位图索引主要针对大量相同值的列而创建。

拿全国居民登录一第表来说，假设有四个字段：姓名、性别、年龄、和身份证号，年龄和性别两个字段会产生许多相同的值，性别只有男女两种值，年龄，1到120（假设最大年龄120岁）个值。

那么不管一张表有几亿条记录，但根据性别字段来区分的话，只有两种取值（男、女）。

那么位图索引就是根据字段的这个特性所建立的一种索引。

Bitmap Index
从上图，我们可以看出，一个叶子节点（用不同颜色标识）代表一个key , start rowid 和end rowid规定这种类型的检索范围，一个叶子节点标记一个唯一的bitmap值。

因为一个数值类型对应一个节点，当时行查询时，位图索引通过不同位图取值直接的位运算（与或），来获取到结果集合向量（计算出的结果）。

举例讲解：
假设存在数据表T，有两个数据列A和B，取值如下，我们看到A和B列中存在相同的数据。

对两个数据列A、B分别建立位图索引：idx_t_bita和idx_t_bitb。

两个索引对应的存储逻辑结构如下：
Idx_t_bita索引结构，对应的是叶子节点：
Idx_t_bitb索引结构，对应的是叶子节点：
对查询“select * from t where b=1 and (a=’L’or a=’M’)”
分析：位图索引使用方面，和B*索引有很大的不同。

B*索引的使用，通常是从根节点开始，经过不断的分支节点比较到最近的符合条件叶子节点。

通过叶子节点上的不断Scan操作，“扫描”出结果集合rowid。

而位图索引的工作方式截然不同。

通过不同位图取值直接的位运算（与或），来获取到结果集合向量（计算出的结果）。

针对实例SQL，可以拆分成如下的操作：
1、a=’L’or a=’M’
a=L：向量：1010
a=M：向量：0001
or操作的结果，就是两个向量的或操作：结果为1011。

2、结合b=1的向量
中间结果向量：1011
B=1：向量：1001
and操作的结果，1001。

翻译过来就是第一和第四行是查询结果。

3、获取到结果rowid
目前知道了起始rowid和终止rowid，以及第一行和第四行为操作结果。

可以通过试算的方法获取到结果集合rowid。

位图索引的特点：
1.Bitmap索引的存储空间节省
2.Bitmap索引创建的速度快
3.Bitmap索引允许键值为空
4.Bitmap索引对表记录的高效访问
创建位图索引：
复制代码
查看表记录
SQL> select * from dex; ...................
ID SEX NAME
---------- -- --------------------
991 M chongshi
992 M chongshi
993 G chongshi
994 G chongshi
995 G chongshi
996 M chongshi
997 G chongshi
998 G chongshi
999 G chongshi
1000 M chongshi
1000 rows selected.
对于上面表来说sex（性别）只有两种值，最适合用来创建位图所引创建索引：
SQL> create bitmap index my_bit_idx on dex(sex);
Index created.
查看创建的所引
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
MY_BIT_IDX INDEX
复制代码
创建索引的一些规则
1、权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。

这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也要跟着修改。

这里需要权衡我们的操作是查询多还是修改多。

2、把索引与对应的表放在不同的表空间。

当读取一个表时表与索引是同时进行的。

如果表与索引和在一个表空间里就会产生资源竞争，放在两个表这空就可并行执行。

3、最好使用一样大小是块。

Oracle默认五块，读一次I/O，如果你定义6个块或10个块都需要读取两次I/O。

最好是5的整数倍更能提高效率。

4、如果一个表很大，建立索引的时间很长，因为建立索引也会产生大量的redo信息，所以在创建索引时可以设置不产生或少产生redo信息。

只要表数据存在，索引失败了大不了再建，所以可以不需要产生redo信息。

5、建索引的时候应该根据具体的业务SQL来创建，特别是where条件，还有where条件的顺序，尽量将过滤大范围的放在后面，因为SQL执行是从后往前的。

（小李飛菜刀）
索引常见操作
改变索引：。