一, 索引的概念
在使用oracle的过程中,我们就不能不考虑性能和SQL优化, 而正确的使用索引在优化过程中是很关键的.
索引是建立在表的一列或多列上的辅助对象, 它有助于快速访问该表中的数据. 索引由于其内在的结构, 具有某些内在的开销, 这些开销依赖于为了检索由索引中ROWID指定的行所访问的表中的块数, 需要特别注意的是: 这个开销可能会超过进行顺序全表扫描的成本.
Oracle使用B*树存储索引(包括位图索引). 索引的顶点称为根节点, 第二级节点称为分支节点, 最低级的节点是叶节点. 上级索引块(分支节点)包含了指向下级索引块的索引数据. 最低级索引块(叶节点)包含每个值的索引数据和一个相对应的用来确定该实际行位置的ROWID. 叶节点本身使用双向链表连接, 允许叶节点双向切换.
二, 索引的文件存储
索引文件在存储器上分为两个区：索引区和数据区。索引区存放索引表，数据区存放主文件。建立索引文件的过程：
（1） 按输入记录的先后次序建立数据区和索引表。其中索引表中关键字是无序的
（2） 待全部记录输入完毕后对索引表进行排序，排序后的索引表和主文件一起就形成了索引文件。
【例】对于表10.2的数据文件，主关键字是职工号，排序前的索引表如表10.3所示，排序后的索引表见表10.4，表10.2和表10.4一起形成了一个索引文件。
drop table t_index_test;
create table T_index_test(
f1 integer,
f2 integer,
f3 varchar2(400)
);
insert into t_index_test
select rownum, mod(rownum,100),lpad(rownum,300,'-')
from dba_objects, dba_tab_cols
where rownum <= 10000;
commit;
create index ind_index_test_1 on t_index_test(f1);
analyze index ind_index_test_1 validate structure;
select * from index_stats where name = upper('ind_index_test_1');
| 字段名称 | 字段描述 | 字段内容 |
| HEIGHT | 索引树高度 | 2 |
| BLOCKS | 分配给索引的块数 | 32 |
| NAME |
| IND_INDEX_TEST_1 |
| PARTITION_NAME |
|
|
| LF_ROWS | 索引叶子节点个数 | 10000 |
| LF_BLKS | 叶子节点块数 | 21 |
| LF_ROWS_LEN | 叶子节点总长度 | 149801 |
| LF_BLK_LEN | 平均每个叶子块的大小 | 7980 |
| BR_ROWS | 根节点指针个数,就是说根节点中有20个指针指向叶子节点 | 20 |
| BR_BLKS | 根节点个数 | 1 |
| BR_ROWS_LEN | 根节点总长度 | 220 |
| BR_BLK_LEN |
| 8012 |
| DEL_LF_ROWS | 删除的叶子节点行数 | 0 |
| DEL_LF_ROWS_LEN |
| 0 |
| DISTINCT_KEYS | 不同值总数 | 10000 |
| MOST_REPEATED_KEY |
| 1 |
| BTREE_SPACE | 分配给索引的字节数 | 175592 |
| USED_SPACE | 索引已经使用的字节数 | 150021 |
| PCT_USED |
| 86 |
| ROWS_PER_KEY | 每个字段的平均个数 | 1 |
| BLKS_GETS_PER_ACCESS |
| 3 |
| PRE_ROWS |
| 0 |
| PRE_ROWS_LEN |
| 0 |
| OPT_CMPR_COUNT |
| 0 |
| OPT_CMPR_PCTSAVE |
| 0 |
可以看到，该所引高度为2，只有1个branch块，同时也是root根节点，同时有21个leaf块。
select extent_id,file_id,block_id,blocks from dba_extents where segment_name=upper('ind_index_test_1')
| EXTENT_ID | FILE_ID | BLOCK_ID | BLOCKS |
| 0 | 33 | 12073 | 8 |
| 1 | 33 | 12081 | 8 |
| 2 | 33 | 12089 | 8 |
| 3 | 33 | 12097 | 8 |
2.0 转储索引结构
ALTER SESSION SET EVENTS 'immediate trace name treedump level 92287'; --转储索引结构
/oracle/admin/edw/udump/edw_ora_4376.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /oracle/product/9201
System name: HP-UX
Node name: warehous
Release: B.11.11
Version: U
Machine: 9000/800
Instance name: edw
Redo thread mounted by this instance: 1
Oracle process number: 16
Unix process pid: 4376, image: oracle@warehous (TNS V1-V3)
*** SESSION ID:(17.13390) 2009-06-03 16:03:51.980
----- begin tree dump
branch: 0x8402f2c 138424108 (0: nrow: 21, level: 1)
leaf: 0x8402f2d 138424109 (-1: nrow: 484 rrow: 484)
leaf: 0x8402f2e 138424110 (0: nrow: 477 rrow: 477)
leaf: 0x8402f2f 138424111 (1: nrow: 477 rrow: 477)
leaf: 0x8402f30 138424112 (2: nrow: 477 rrow: 477)
leaf: 0x8402f31 138424113 (3: nrow: 477 rrow: 477)
leaf: 0x8402f32 138424114 (4: nrow: 477 rrow: 477)
leaf: 0x8402f33 138424115 (5: nrow: 477 rrow: 477)
leaf: 0x8402f34 138424116 (6: nrow: 477 rrow: 477)
leaf: 0x8402f35 138424117 (7: nrow: 477 rrow: 477)
leaf: 0x8402f36 138424118 (8: nrow: 477 rrow: 477)
leaf: 0x8402f37 138424119 (9: nrow: 477 rrow: 477)
leaf: 0x8402f38 138424120 (10: nrow: 477 rrow: 477)
leaf: 0x8402f3a 138424122 (11: nrow: 477 rrow: 477)
leaf: 0x8402f3b 138424123 (12: nrow: 477 rrow: 477)
leaf: 0x8402f3c 138424124 (13: nrow: 477 rrow: 477)
leaf: 0x8402f3d 138424125 (14: nrow: 477 rrow: 477)
leaf: 0x8402f3e 138424126 (15: nrow: 477 rrow: 477)
leaf: 0x8402f3f 138424127 (16: nrow: 477 rrow: 477)
leaf: 0x8402f40 138424128 (17: nrow: 477 rrow: 477)
leaf: 0x8402f41 138424129 (18: nrow: 477 rrow: 477)
leaf: 0x8402f42 138424130 (19: nrow: 453 rrow: 453)
----- end tree dump
2.1 索引头内容分析
Block 12073- 12075表示索引段头块
*** SESSION ID:(17.13181) 2009-06-03 08:54:24.055
Start dump data blocks tsn: 15 file#: 33 minblk 12074 maxblk 12074
buffer tsn: 15 rdba: 0x08402f2a (33/12074)
scn: 0x0001.8b6ef608 seq: 0x01 flg: 0x00 tail: 0xf6082101
frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK
Dump of Second Level Bitmap Block
number: 2 nfree: 2 ffree: 0 pdba: 0x08402f2b
opcode:0
xid:
L1 Ranges :
--------------------------------------------------------
0x08402f29 Free: 3 Inst: 1
0x08402f39 Free: 5 Inst: 1
--------------------------------------------------------
End dump data blocks tsn: 15 file#: 33 minblk 12074 maxblk 12074
*** 2009-06-03 08:58:52.865
Start dump data blocks tsn: 15 file#: 33 minblk 12075 maxblk 12075
buffer tsn: 15 rdba: 0x08402f2b (33/12075)
scn: 0x0001.8b6ef608 seq: 0x03 flg: 0x00 tail: 0xf6082303
frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 32
last map 0x00000000 #maps: 0 offset: 2720
Highwater:: 0x08402f43 ext#: 3 blk#: 2 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 26
mapblk 0x00000000 offset: 3
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x08402f43 ext#: 3 blk#: 2 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 26
mapblk 0x00000000 offset: 3
Level 1 BMB for High HWM block: 0x08402f39
Level 1 BMB for Low HWM block: 0x08402f39
--------------------------------------------------------
Segment Type: 2 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001438
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x08402f2a
Last Level 1 BMB: 0x08402f39
Last Level II BMB: 0x08402f2a
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 4 obj#: 92293 flag: 0x20000000
Extent Map
-----------------------------------------------------------------
0x08402f29 length: 8
0x08402f31 length: 8
0x08402f39 length: 8
0x08402f41 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x08402f29 Data dba: 0x08402f2c
Extent 1 : L1 dba: 0x08402f29 Data dba: 0x08402f31
Extent 2 : L1 dba: 0x08402f39 Data dba: 0x08402f3a
Extent 3 : L1 dba: 0x08402f39 Data dba: 0x08402f41
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x08402f2a
End dump data blocks tsn: 15 file#: 33 minblk 12075 maxblk 12075
2.2 索引root节点分析
从第一节的分析中我们看到root所在的块号为: 0x8402f2c 138424108 (0: nrow: 21, level: 1)
select dbms_utility.data_block_address_file(138424108 ) "file",
dbms_utility.data_block_address_block(138424108 ) "block"
from dual;
Block 12076 表示该索引的root 所在的块，dump该块,可以看到,branch 块中存在中指向下级叶块(有可能是叶块,也可能是分支块, 由于这里索引树只有两层,索引root块的指针直接指向叶块)的地址指针及叶块中的最小值，dump该块。
alter system dump datafile 33 block 12076; --转储root索引块
*** 2009-06-03 09:00:55.558
Start dump data blocks tsn: 15 file#: 33 minblk 12076 maxblk 12076
buffer tsn: 15 rdba: 0x08402f2c (33/12076)
scn: 0x0001.8b6ef608 seq: 0x01 flg: 0x00 tail: 0xf6080601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x08402f2c
Object id on Block? Y
seg/obj: 0x16885 csc: 0x01.8b6ef601 itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x8402f29 ver: 0x01
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0001.8b6ef601
Branch block dump --表示这个数据块中存储的是索引的根节点
=================
header address 9223372041151461452=0x80000001001a384c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 20 --表示根节点下有20个子节点
kdxcofbo 68=0x44
kdxcofeo 7860=0x1eb4
kdxcoavs 7792
kdxbrlmc 138424109=0x8402f2d 第一个叶块地址前2位补0,
即0100 0100 0000 0010 1111 0010 1101, file 33 block 12077，为第一个index leaf 的块地址
kdxbrsno 0
kdxbrbksz 8040
row#0[8031] dba: 138424110=0x8402f2e --第二个索引块地址
col 0; len 3; (3): c2 05 56 --第二个索引块启示内容
col 1; TERM
row#1[8022] dba: 138424111=0x8402f2f --第三个索引块地址
col 0; len 3; (3): c2 0a 3f --第三个索引块起始内容
col 1; TERM
…
row#19[7860] dba: 138424130=0x8402f42
col 0; len 3; (3): c2 60 31
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 15 file#: 33 minblk 12076 maxblk 12076
2.3 索引叶子节点分析
SQL> alter system dump datafile 33 block 12077;
*** 2009-06-03 09:01:11.310
Start dump data blocks tsn: 15 file#: 33 minblk 12077 maxblk 12077
buffer tsn: 15 rdba: 0x08402f2d (33/12077)
scn: 0x0001.8b6ef604 seq: 0x02 flg: 0x04 tail: 0xf6040602
frmt: 0x02 chkval: 0xebc2 type: 0x06=trans data
Block header dump: 0x08402f2d
Object id on Block? Y
seg/obj: 0x16885 dba_objects.obj_id csc: 0x01.8b6ef601 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x8402f29 ver: 0x01
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0001.8b6ef601
Leaf block dump
===============
header address 9223372041151461476=0x80000001001a3864
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 484 --该索引叶块所存储的记录数
kdxcofbo 1004=0x3ec
kdxcofeo 1827=0x723
kdxcoavs 823
kdxlespl 0
kdxlende 0
kdxlenxt 138424110=0x8402f2e --下一个叶块地址
kdxleprv 0=0x0 --上一个叶块地址, 这是第一个叶块, 所以这里为0
kdxledsz 0
kdxlebksz 8016
row#0[8004] flag: -----, lock: 0
col 0; len 2; (2): c1 02
col 1; len 6; (6): 08 40 2e ac 00 00 最后四个字节为相对顺序号.转换为二进制后,前6位为数据表所在文件的fileid, 后面为数据所在块的blockid. file 33, block
拿前八个字节08402eac转换为10进制后
select dbms_utility.data_block_address_file(138423980) "file",
dbms_utility.data_block_address_block(138423980) "block"
from dual;
row#1[7992] flag: -----, lock: 0
col 0; len 2; (2): c1 03
col 1; len 6; (6): 08 40 2e ac 00 01
----- end of leaf block dump -----
End dump data blocks tsn: 15 file#: 33 minblk 12077 maxblk 12077
理解col 0 :
完整数据由三部分组成, 最高位表示位(c1 个位, c2 十位, c3 百位…
负数 3e 个位 , 3d 十位…),
如果数值为0 , 只有数值最高位表示位: 80
如果为正数, 包含两个部分, 数值最高位表示位,
一：Oracle的NUMBER类型最多由三个部分构成，这三个部分分别是最高位表示
位、数据部分、符号位。其中负数包含符号位，正数不会包括符号位。
二：数值0比较特殊，它只包含一个数值最高位表示位80，没有数据部分
三：正数的最高位表示位大于80，负数的最高位表示位小于80。其中一个正数的最高位是个位的话，则最高位表示位为C1，百位、万位依次为C2、C3，百分位、万分为依次为C0、BF。
四：一个负数的最高位为个位的话，最高位表示位为3E，百位、万位依次为3D、3C，百分位、万分位依次为3F、40。
五：数据部分每一位都表示2位数。这个两位数可能是从0到99，如果是数据本身是正数，则分别用二进制的1到64表示，如果数据本身是负数，则使用二进制65到2表示。
六：符号位用66表示。
七：负数和正数互为相反数，负数的最高位表示位和它对应的相反数的最高位相加的值是FF。1的最高位表示位是C1，-1的最高位表示位是3E。负数中1用64表示。负数中的数值和它相反数的数据相加是0x66，也就是符号位。正数1用0x02表示，负数1用0x64表示，二者相加是0x66。负数多个一个标识位，用0x66表示。由于正数的表示范围是0x01到0x64，负数的表示范围是0x65到0x02。因此，不会在表示数字时出现的0x66表示。
三, 如何建立最佳索引
何时使用索引
假定索引的唯一目的是减少IO操作, 如果一个查询使用索引时相对于全表扫描执行了更多的IO操作, 则使用索引的意义会明显降低.
例如, 假设有一个拥有1000000行的表存储在5000个块中, 某个给定的查询需要的结果分布在其中4000个数据块中, 这种情况下,建立和使用这一列上的索引肯定不是最佳的.
如果一个拥有1000行的表经历了大量的重复插入和删除操作后, 表的高水位标记线将升高, 因为delete操作不能收回已经使用的数据块. 如果高水位标记线为1000, 而实际记录存储在其中100个数据块中, 这时使用索引是有意义的. 因为被访问的数据块的数量和执行IO操作的数量明显少于执行全表扫描的数量.
什么是最佳索引
较好的索引(数据按照索引组织, 在索引中顺序的内容在表中也相邻存储.这样之需要读取较少的数据块就可以完成检索任务)
A---------7
A---------8
B---------8
B---------8
C----------8
C----------9
较差的索引(索引中相邻的数据在表中存储位置相隔较远, 导致每次读取了多余的重复数据块)
A---------1357
A---------2
B---------9878
B---------38
C----------1008
C----------9
最佳索引的参数(CF)
| 什么是Clustering Factor | |
|
问题和答案
1, 什么是Index clustering Facotr(CF).
Index CF是一个CBO的统计值, 这个值标示表中两行记录的距离与索引中两行记录的距离的比值.可以大致理解为( rowid(row1) – rowid(row2))/(rowed(index1) – rowed(index2)).
2, 为什么Index CF值越小越好.
根据上面的定义描述, 我们知道, 这个值越小, 索引中两个相邻值在表中存储的位置越接近, 这样oracle在根据根据索引范围得到存储记录的位置的范围越小. 所需要读取的数据块数就越少, 所以索引的性能就越高.
3, 使用exp/imp或者table/index move可以帮助减少Index CF值吗?
答案是否定的, 这两种方式都对index CF没有改变.
Ok, 那么我们就可以理解为, table/index move虽然可以收回没有记录的数据块, 但这个过程并不对数据记录排序后重新存储, 而只是简单地将几个相邻的空闲块中的内容写入新块中.
4, 怎么做才能减少index CF
只有对结果记录排序后重新reload到表中才能减少这个值.
5, 如果表中的索引不止一个, 怎么办?
如果表的索引不止一个, 我们不可能同时让所有的index CF值减少, 而只能通过排序reload减少某一个或者几个索引的index CF值.
6, 有没有什么办法可以避免产生高的index CF values?
可以将表放在keep pool中.
…
7, 减少index CF values的方式.
使用外部排序特性, 对表数据按照索引排序后重新读入.
或者使用create table as select from table order by 的方式.
有效使用索引的几个问题
以下问题的答案有助于建立最佳索引.
1, 与全表扫描相比, 索引扫描需要执行多少块IO操作.
如果知道这个问题的答案, 就会立即知道建立和使用一个索引是否具有性能意义.
2, 用于特定表中的数据访问的最常用列组合是什么?
研究应用程序代码, 如果程序代码不容易看懂, 则查看V$SQLAREA或V$SQLTEXT, 并分析最常用的SQL语句. 查找在V$SQLAREA中具有较高执行次数的语句, 并查找它们的where子句的成分.
3, 对打算在其上建立索引的一组给定的列, 其选择性是什么?
如果一些列始终有值并且相对唯一, 则他们应该是索引的前导列. 为建立索引, 按照可能具有唯一值的概率, 对这些列进行降序排列.
4, 在where子句中引用的所有列都需要进行索引吗?
具有很低的数据基数,或者可能具有空值的列不适合做索引列.
5, 索引所基于的表主要用于事务处理还是查询?
如果它是事务处理的表, 则需要确定由于给出附加索引对事务处理的潜在负面影响.
关于建立最佳索引的答案不是轻易得来的; 它们相当复杂, 但是, 上述的问题将指出正确的路径. 从性能的立场上, 应该了解并朝着它努力的一件事情是如何使使用索引的开销最小化, 使得索引真正对性能有帮助而不是阻碍性能.
四, 导致索引无效的情况
限制索引是一些没有经验的开发人员经常犯的错误之一. 在SQL中有很多陷阱使一些索引无法使用.
1, 使用不等于操作符(<>, !=)
下面的查询即使在cust_rating列有一个索引，查询语句仍然执行一次全表扫描。
select cust_Id,cust_name
from customers
where cust_rating <> 'aa';
把上面的语句改成如下的查询语句，这样，在采用基于规则的
优化器而不是基于代价的优化器（更智能）时，将会使用索引。
select cust_Id,cust_name
from customers
where cust_rating < 'aa' or cust_rating > 'aa';
特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。
2, 使用is null或者is not null
使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，关于位图索引在稍后在详细讨论）。
3, 比较不匹配的数据类型
比较不匹配的数据类型也是比较难于发现的性能问题之一。
注意下面查询的例子，account_number是一个VARCHAR2类型，
在account_number字段上有索引。下面的语句将执行全表扫描。
select bank_name,address,city,state,zip
from banks
where account_number = 990354;
Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：
select bank_name,address,city,state,zip
from banks
where account_number ='990354';
特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。
五, 创建索引选择合适的可选项
如为了大批量导入数据，我们往往会先取消索引其以提高插入的速度。然后等数据导入完毕后再重新创建索引。在这个过程中如果能够采用一些可选项，则可以缩短索引创建的时间。在Oracle数据库中提供了丰富的可选项。我们常用的可选项主要有以下这些。
可选项一：NOSORT，记录排序可选项。
默认情况下，在表中创建索引的时候，会对表中的记录进行排序，排序成功后再创建索引。但是当记录比较多的是，这个排序作业会占用比较多的时间，这也就增加了索引建立的时间(排序作业是在索引创建作业中完成)。有时候，我们导入数据的时候，如采用insert into 语句插入数据过程中同时采用Order by子句对索引字段进行了排序。此时如果在索引创建过程中再进行排序的话，就有点脱裤子放屁，多此一举了。为此在重新创建索引时，如果表中的数据已经排好序了(按索引字段排序)，那么在创建索引时就不需要为此重新排序。此时在创建索引时，数据库管理员就可以使用NOSORT可选项，告诉数据库系统不需要对表中当记录进行重新排序了。
采用了这个选项之后，如果表中的记录已经按顺序排列，那么在重新创建索引的时候，就不会重新排序，可以提高索引创建的时间，节省内存中的排序缓存空间。相反，如果表中的记录是不按索引关键字排序的话，那么此时采用NOSORT关键字的话，系统就会提示错误信息，并拒绝创建索引。所以在使用NOSORT可选项的时候，数据库管理员尽管放心大胆的使用。因为其实在不能够使用这个选项的时候，数据库也会明确的告知。为此其副作用就比较少，数据库管理员只需要把这个可选项去掉然后重新执行一次即可。不过这里需要注意的是，如果表中的记录比较少的话，那么使用NOSORT选项的效果并不是很明显。当采用insert into批量导入数据，并在这个过程中采用了Order by子句对索引关键字进行了排序的话，则此时采用NOSORT选项的话，往往能够起到比较好的效果。
可选项二：NOLOGGING，是否需要记录日志信息。
在创建索引的时候，系统会把相关的信息存储到日志信息中去。如果表中的记录比较多，则需要一一的把这些信息记录到日志文件中，这显然会让数据库增加很大的工作量。从而增加索引创建的时间。为此在创建索引的过程中，如果有必要时，我们可以采用NOLOGGING选项，让数据库在创建索引的过程中，不产生任何重做日志信息。此时当表中的记录比较多时，就可以明显提高速度。
但是默认情况下，数据库在在创建索引时，是不采用这个选项的，即会把相关的信息保存到重做日志中去。这虽然降低了索引创建的效率，但是如果遇到什么意外的话，却可以利用重做日志来进行恢复。所以，此时数据库管理员就比较难以抉择了。一方面是数据的安全，另一方面是索引创建的速度。根据笔者的经验，只要数据库服务器比较稳定，而数据库中约束机制又比较完善的话，那么在创建索引的过程中一般不会出现问题，可以放心大胆的使用这个可选项。
但是如果数据库已经使用了好几年了。后来因为某种原因需要重建索引。在这种情况下，由于数据库使用过程中很多因素数据库管理员无法控制。此时为这种类型的数据库创建索引时，为了保险起见还是不要采用这个选项好。因为此时遇到错误的几率相对来说会搞一点。为此此时牺牲一下索引创建的速率，而提高数据的安全性还是有必要的。万一遇到什么问题时，可以通过重做日志来及时的恢复数据，为企业用户减少损失。
可选项三：COMPUTE STATISTICS，是否生成统计信息。
如果管理员在创建索引时采用了这个选项，则数据库将在创建索引的过程中以非常小的代价直接生成关于索引的相关统计信息，然后把这些信息存储在数据字典中。这就可以避免以后对索引进行分析统计，而且优化器在优化SQL语句的时候可以随机使用这些统计信息，以确定是否生成使用该索引的执行计划。通常情况下，在生成索引的过程中统计索引的相关信息，其所花的代价是最小的。无论从时间上，还是从硬件资源的耗费上，都是非常小的。所以，在创建索引的过程中统计相关的索引信息是非常有用的。
但是默认情况下，数据库是不采用这个选项的。这主要是因为一些事物处理系统，索引的信息是经常需要发生变化的。如果在索引创建的过程中统计了相关信息。这些信息随着索引的调整等等原因会很快的过时。所以说，其在默认情况下没有采用这个选项。可见这个选项并不是在任何情况下都能够起到效果。但是如果这个数据库系统是一个决策支持系统。其数据、索引等等在一段时间内基本上是稳定不变的。此时在创建索引时可以使用这个选项。如此的话，在生成索引时可以以最小的代价生成这些统计信息，方便优化器使用。笔者在部署数据库应用的时候，对于事务型的数据库系统，一般不会启用这个选项。但是对于一些决策性的数据库系统或者数据仓库中，创建索引时则笔者喜欢采用这个选项。这有助于提高数据库的性能。因为优化器在生成执行计划时，可以直接采用这个统计信息。所以，数据库能够在最短的时间内确定需要采用的执行计划。而且在执行计划制定中参考了这个索引统计信息，为此所生成的执行计划在同等条件下可能更加的合理。
可选项四：ONLINE，DML操作与创建索引操作是否可以同时进行。
默认情况下，数据库系统是不允许DML操作与创建索引的操作同时进行的。也就是说，在创建索引的过程中，是不允许其他用户对其所涉及的表进行任何的DML操作。这主要是因为对基础表进行DML操作时，会对基础表进行加锁。所以在基础表上的DDL事务没有递交之前，即没有对基础表进行解锁之前，是无法对这基础表创建索引的。反之亦然。显然此时数据库没有采用这个ONLIE选项，继之DML操作与创建索引操作同时进行，主要是从创建索引的效率出发的。防止因为两个作业相互冲突，从而延长某个作业的运行时间。
但是有时会我们必须允许他们进行同时操作。如用户可能一刻都不能够离开数据库系统，需要时时刻刻对数据库基础表进行DML操作。而此时由于某些原因，数据库管理员又需要重新建立索引时，那么不得不在创建索引的语句中加入这个ONLINE选项。让他们同时运行。此时虽然可能会延长索引创建作业的时间，但是可以保障用户DML操作能够正常进行。有时候牺牲这个代价是值得的。用户是不能够等的，而我们数据库管理员则可以勉强的等一会儿。
当然，如果用户对于这个DML操作及时性没有这么高。如数据库管理员在晚上员工没有使用数据库时创建索引时，则可以不带这个选项。在限制用户对基础表进行DML操作的同时，提高数据库创建索引的效率。
可选项五：PARALLEL，多服务进程创建索引。
默认情况下，Oracle数据库系统不采用这个选项。这并不是说这个选项不可用，而是因为大多数情况下企业部署Oracle数据库时所采用的数据库服务器往往只有单个CPU。此时数据库系统是用一个服务进程来创建索引的。
如果企业的服务器有多个CPU的话，则可以在创建索引时采用这个选项。因为只要采用了这个选项，则数据库就会使用多个服务进程来并行的创建索引，以提高索引创建的速度。为此，在同等条件下，多服务并行创建进索引并单服务创建索引速度要快的多。所以如果服务器中有多个CPU，而且需要创建的索引比较多或者基础表中记录比较多的话，则采用这个选项能够大幅度的提高索引的创建效率。
故笔者建议，如果采用多CPU的服务器时，最好在创建索引时使用这个选项。不能够浪费了服务器的CPU呀。不然的话，多CPU服务器的优势就体现不出来了。为此采用这个选项，也是物尽其用。
六, 索引相关的hints
6.1 index
How to specify an 'Index' Hint
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The format for an index hint is:
select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
There are a number of rules that need to be applied to this hint:
o The TABLE_NAME is mandatory in the hint
o The table alias MUST be used if the table is aliased in the query
o If TABLE_NAME or alias is spelled incorrectly then the hint will not be used.
o The INDEX_NAME is optional.
o If an INDEX_NAME is entered without a TABLE_NAME then the hint will not be
applied.
o If a TABLE_NAME is supplied on its own then the optimizer will decide
which index to use based on statistics.
o If the INDEX_NAME is spelt incorrectly but the TABLE_NAME is spelled correctly
then the hint will not be applied even though the TABLE_NAME is correct.
o If there are multiple index hints to be applied, then the simplest way of
addressing this is to repeat the index hint syntax for each index e.g.:
select /*+ index(TABLE_NAME1 INDEX_NAME1) index(TABLE_NAME2 INDEX_NAME2) */ col1...
o Remember that the parser/optimizer may have transformed/rewritten the query
or may have chosen an access path which make the use of the index invalid and
this may result in the index not being used.
NB Please note that as long as the index() hint structure is correct
it will force the use of the Cost Based Optimizer (CBO). This will happen
even if the alias or table name is incorrect.
6.2 index_combine
index_combine最早是用在bitmap index上的,在9i开始oracle默认可以使用在btree索引上,这是由_b_tree_bitmap_plans参数来控制的.oracle将btree索引中获得的rowid信息通过BITMAP CONVERSION FROM ROWIDS的步骤转换成bitmap进行匹配,然后匹配完成后通过BITMAP CONVERSION TO ROWIDS再转换出rowid获得数据或者回表获得数据.
SELECT /*+ index_combine(test ind_test_owner ind_test_object_name)*/
OWNER, OBJECT_NAME
FROM TEST
WHERE OWNER = 'TEST'
AND OBJECT_NAME = 'TEST'
Execution Plan
———————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
———————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)|
| 1 | BITMAP CONVERSION TO ROWIDS | | 1 | 29 | 2 (0)|
| 2 | BITMAP AND | | | | |
| 3 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 4 | INDEX RANGE SCAN | IND_TEST_OWNER | | | 1 (0)|
| 5 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 6 | INDEX RANGE SCAN | IND_TEST_OBJECT_NAME | | | 1 (0)|
———————————————————————————————
回表取数据的情况
SELECT /*+ index_combine(test ind_test_owner ind_test_object_name)*/
OWNER, OBJECT_NAME,OBJECT_TYPE
FROM TEST
WHERE OWNER = 'TEST'
AND OBJECT_NAME = 'TEST'
Execution Plan
———————————————————-
———————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
———————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 40 | 2 (0)|
| 2 | BITMAP CONVERSION TO ROWIDS | | | | |
| 3 | BITMAP AND | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 5 | INDEX RANGE SCAN | IND_TEST_OWNER | | | 1 (0)|
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 7 | INDEX RANGE SCAN | IND_TEST_OBJECT_NAME | | | 1 (0)|
———————————————————————————————-
不带查询条件的情况
index combine将不被使用
SELECT /*+ index_combine(test ind_test_owner ind_test_object_name)*/
OWNER, OBJECT_NAME
FROM TEST
Execution Plan
———————————————————-
———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
———————————————————————————–
| 0 | SELECT STATEMENT | | 51984 | 1472K| 1480 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 51984 | 1472K| 1480 (1)|
| 2 | INDEX FULL SCAN | IND_TEST_OWNER | 51984 | | 123 (2)|
———————————————————————————–
index_combine会是and_equal的很好的替代者,随着and_equal的退出,index_combine将更多得被我们看到.
6.3 index_join:
index join顾名思义是对index进行关联,oracle通过hash index join的方式实现了避免对表的访问.所有的数据都从索引中直接获得.它不受查询条件影响,可以是唯一索引,也可以是多列索引.
SELECT /*+ index_join(test ind_test_owner ind_test_object_name)*/
OWNER, OBJECT_NAME
FROM TEST
WHERE OWNER = 'TEST'
AND OBJECT_NAME = 'TEST';
由对定义的描述我们可以知道,index_join这个hint的主要功能是通过对表索引的hash_join操作获得所需要的数据,从而避免回表执行查询.针对源表数据较大,而返回结果数据都可以在索引中满足的情况,这个hint比较有效.
Execution Plan
———————————————————-
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 29 | 3 (34)|
|* 1 | VIEW | index$_join$_001 | 1 | 29 | 3 (34)|
|* 2 | HASH JOIN | | | | |
|* 3 | INDEX RANGE SCAN| IND_TEST_OWNER | 1 | 29 | 1 (0)|
|* 4 | INDEX RANGE SCAN| IND_TEST_OBJECT_NAME | 1 | 29 | 1 (0)|
——————————————————————————–
可以不带查询条件,
我们甚至可以为不带查询条件的检索语句使用index_join的提示.只不过由index range scan变成了index fast full scan
SELECT /*+ index_join(test ind_test_owner ind_test_object_name)*/
OWNER, OBJECT_NAME
FROM TEST
Execution Plan
———————————————————-
————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
————————————————————————————
| 0 | SELECT STATEMENT | | 51984 | 1472K| 478 (2)|
| 1 | VIEW | index$_join$_001 | 51984 | 1472K| 478 (2)|
|* 2 | HASH JOIN | | | | |
| 3 | INDEX FAST FULL SCAN| IND_TEST_OWNER | 51984 | 1472K| 153 (2)|
| 4 | INDEX FAST FULL SCAN| IND_TEST_OBJECT_NAME | 51984 | 1472K| 322 (1)|
————————————————————————————
返回结果包括非索引数据列
如果不是所有数据都能从索引获得,那么将不会使用index join
SELECT /*+ index_join(test ind_test_owner ind_test_object_name)*/
OWNER, OBJECT_NAME,object_type
FROM TEST
WHERE OWNER = 'TEST'
AND OBJECT_NAME = 'TEST';
Execution Plan
———————————————————-
———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
———————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 40 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IND_TEST_OWNER | 1 | | 1 (0)|
———————————————————————————–
6.4 and_equal
这种方式需要查询条件里面包括所有索引列,然后取得每个索引中得到的rowid列表,然后对这些列表做merge join,过滤出相同的rowid后再去表中获取数据或者直接从索引中获得数据.
and_equal有一些限制,比如它只对单列索引有效,只对非唯一索引有效,使用到的索引不能超过5个,查询条件只能是”=”.在10g中,and_equal已经被废弃了,只能通过hint才能生效.
涉及索引满足and_equal的限制条件的情况
create table test as select * from dba_objects;
create index ind_test_owner on test(owner);
create index ind_test_object_name on test(object_name);
SELECT /*+ and_equal(test ind_test_owner ind_test_object_name)*/
OWNER, OBJECT_NAME
FROM TEST
WHERE OWNER = 'TEST'
AND OBJECT_NAME = 'TEST';
由于涉及到的索引都为单列索引,且所有索引都在查询中出现,谓词也都是使用=关系符号连接,所以我们可以看到add_equal索引生效.执行计划如下所示.
Execution Plan
———————————————————-
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
——————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)|
|* 1 | AND-EQUAL | | | | |
|* 2 | INDEX RANGE SCAN| IND_TEST_OWNER | 1 | | 1 (0)|
|* 3 | INDEX RANGE SCAN| IND_TEST_OBJECT_NAME | 2 | | 1 (0)|
——————————————————————————-
如果查询条件只包含owner
SELECT /*+ and_equal(test ind_test_owner ind_test_object_name)*/
OWNER, OBJECT_NAME
FROM TEST
WHERE OWNER = 'TEST'
本例中只使用到了hint中的一个索引关联的列owner,所以导致and_equal提示无效.执行计划中只使用了一个索引.
Execution Plan
———————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
———————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 29 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IND_TEST_OWNER | 1 | | 1 (0)|
———————————————————————————–
查询条件是”>”的情况
SELECT /*+ and_equal(test ind_test_owner ind_test_object_name)*/
OWNER, OBJECT_NAME
FROM TEST
WHERE OWNER = 'TEST'
AND OBJECT_NAME > 'TEST';
本例中由于查询谓词中对列object_name使用了>符号,所以也会导致and_equal提示无效.
Execution Plan
———————————————————-
—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
—————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 40 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IND_TEST_OBJECT_NAME | 2 | | 1 (0)|
—————————————————————————————–
查询条件是in的情况
SELECT /*+ and_equal(test ind_test_owner ind_test_object_name)*/
OWNER, OBJECT_NAME
FROM TEST
WHERE OWNER IN('TEST','zhanglei')
AND OBJECT_NAME IN('TEST')
In的情况有点特殊,如果in中的可选值有两个,那么将会导致add_equal提示失效.但如果in后面的可选值只有一个,oracle会将in当作=号来处理,同样可以使用and_equal提示.
Execution Plan
———————————————————-
—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
—————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 40 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IND_TEST_OBJECT_NAME | 2 | | 1 (0)|
————————
362
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
