所获，不止Oracle之B-Tree目录

所获，不止Oracle之B-Tree索引
/*索引索引由三部分组成，根块(root),Branch(茎块)，Leaf(叶子块)。其中Leaf块主要存储了key column value(索引列具体值)，以及能具体定位到数据块位置的rowid。索引的创建过程：1.要建索引先排序，将索引列的值顺序取出，及该行的rowid放入到内存中。2.依次将内存中的顺序存放的值和rowid存放入索引块中。3.当填满两个索引块后，oracle会产生一个块，用于管理同级的叶子块。这个块记录了叶子块的信息，并不记录索引列的键值，所以使用的空间比较少。4.当管理叶子块的块被填满后，oracle又会产生一个上一级管理块，依次循环。同级两块需要管理。索引结构的三大重要特点1.索引的高度比较低2.索引存储列值3.索引本身是有序的*/----------------------------------------------索引高度较低验证-------------------------------------------索引的大小和高度是巨大差别的，可能大小差好多倍，但高度却一样。--构造T1-T7表，记录从5到500WCREATE TABLE T1 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=5;CREATE TABLE T2 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=50;CREATE TABLE T3 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=500;CREATE TABLE T4 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=5000;CREATE TABLE T5 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=50000;CREATE TABLE T6 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=500000;CREATE TABLE T7 AS SELECT ROWNUM AS ID,ROWNUM+1 AS ID2 FROM DUAL CONNECT BY LEVEL<=5000000;--创建索引CREATE INDEX IDX_ID_T1 ON T1(ID);CREATE INDEX IDX_ID_T2 ON T2(ID);CREATE INDEX IDX_ID_T3 ON T3(ID);CREATE INDEX IDX_ID_T4 ON T4(ID);CREATE INDEX IDX_ID_T5 ON T5(ID);CREATE INDEX IDX_ID_T6 ON T6(ID);CREATE INDEX IDX_ID_T7 ON T7(ID);--查看索引大小SELECT SEGMENT_NAME,BYTES/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ( 'IDX_ID_T1', 'IDX_ID_T2', 'IDX_ID_T3', 'IDX_ID_T4', 'IDX_ID_T5', 'IDX_ID_T6', 'IDX_ID_T7');--查看索引高度SELECT INDEX_NAME, BLEVEL,--索引高度,BLEVEL=0表示1层,BLEVEL=1表示2层 LEAF_BLOCKS,--Number of leaf blocks in the index NUM_ROWS, DISTINCT_KEYS, CLUSTERING_FACTOR FROM USER_IND_STATISTICS WHERE TABLE_NAME IN ('T1', 'T2', 'T3', 'T4', 'T5', 'T6', 'T7');INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR----------------------- ----------- ---------- ------------- -----------------IDX_ID_T1 0 1 5 5 1IDX_ID_T2 0 1 50 50 1IDX_ID_T3 1 2 500 500 1IDX_ID_T4 1 11 5000 5000 9IDX_ID_T5 1 110 50000 50000 101IDX_ID_T6 2 1113 500000 500000 1035IDX_ID_T7 2 12023 5134040 4994100 22527--相比T6表与T7表的查询效率，使用索引的查询效率一致，因为索引的高度低--以下内容已多次执行消除物理读和递归admin@ORCL> select * from T6 where id = 10;已用时间: 00: 00: 00.07执行计划----------------------Plan hash value: 1902844584-------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time-------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 9 | 4 (0)| 00:00:| 1 | TABLE ACCESS BY INDEX ROWID| T6 | 1 | 9 | 4 (0)| 00:00:|* 2 | INDEX RANGE SCAN | IDX_ID_T6 | 1 | | 3 (0)| 00:00:-------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("ID"=10)统计信息---------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 462 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed admin@ORCL> select * from T7 where id = 10;已用时间: 00: 00: 00.01执行计划----------------------Plan hash value: 1124755243-----------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 11 | 4 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T7 | 1 | 11 | 4 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_ID_T7 | 1 | | 3 (0)| 00:00:01 |-----------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("ID"=10)统计信息---------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 462 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed --DUMP索引结构，一探究竟admin@ORCL> column object_name for a20admin@ORCL> SELECT OBJECT_ID, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_NAME IN ('IDX_ID_T1'); OBJECT_ID OBJECT_NAME---------- -------------------- 65143 IDX_ID_T1--DUMP后存放的trace文件后缀 SELECT SPID FROM V$PROCESS WHERE ADDR = (SELECT PADDR FROM V$SESSION WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT));SPID------------6704--DUMP索引"结构"alter system set events 'immediate trace name treedump level 65143';--存放在以22408结尾的trace文件中SELECT SPID FROM V$PROCESS WHERE ADDR = (SELECT PADDR FROM V$SESSION WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT));--索引结构如下：--只有一个叶子节点 0x代表16进制数，16798212是前面16进制转化为10进制的数--以下内容记录了leaf对应的文件号和块号----- begin tree dumpleaf: 0x1005204 16798212 (0: nrow: 5 rrow: 5)----- end tree dump--将16进制为10进制的数admin@ORCL> select to_number('1005204','xxxxxxx') from dual;TO_NUMBER('1005204','XXXXXXX')------------------------------ 16798212--获取数据块的文件编号,及块编号admin@ORCL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16798212) FROM DUAL;DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16798212)---------------------------------------------- 4 admin@ORCL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16798212) FROM DUAL;DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16798212)----------------------------------------------- 20996--从以下结果可以看出leaf块确实在分配的空间中admin@ORCL> SELECT SEGMENT_NAME,BLOCK_ID,BLOCKS,RELATIVE_FNO FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'IDX_ID_T1';SEGMENT_NAME BLOCK_ID BLOCKS RELATIVE_FNO-------------------- ---------- ---------- ------------IDX_ID_T1 20993 8 4--DUMP 数据块ALTER SYSTEM DUMP DATAFILE 4 BLOCK 20996; --DUMP内容如下row#0[8024] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 02 --十六进制col 1; len 6; (6): 01 00 03 cc 00 00row#1[8012] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 03col 1; len 6; (6): 01 00 03 cc 00 01row#2[8000] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 04col 1; len 6; (6): 01 00 03 cc 00 02row#3[7988] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 05col 1; len 6; (6): 01 00 03 cc 00 03row#4[7976] flag: ------, lock: 0, len=12col 0; len 2; (2): c1 06col 1; len 6; (6): 01 00 03 cc 00 04----- end of leaf block dump -----End dump data blocks tsn: 4 file#: 4 minblk 20996 maxblk 20996 --查看1转化为16进制的值为 c1,2admin@ORCL> select dump(1,16) from dual;DUMP(1,16)-----------------Typ=2 Len=2: c1,2--4位二进制数表示16进制数 a在16进制中表示 10,b 代表 11,c为12 d为1301 00 03 cc 00 000000 00010000 0000 0000 00111100 11000000 00000000 0000--上面转化为二进制后，前十位为文件编号，后22个二进制数据为块编号，后面的数字为第几行--从结果可以看出，索引内容中包含了索引列的值，及该条记录的rowid 后3部分内容。表段的OBJECT_ID是没有必要的0000 0001 00: 400 0000 0000 0011 1100 1100 : 9720000 0000 0000 0000: 0SELECT ROWID, T1.* FROM T1;admin@ORCL> SELECT DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_ID, 2 DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE_ID, 3 DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_ID, 4 DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) NUM 5 FROM T1; OBJECT_ID FILE_ID BLOCK_ID NUM---------- ---------- ---------- ---------- 65136 4 972 0 65136 4 972 1 65136 4 972 2 65136 4 972 3 65136 4 972 4--分区索引与全局索引的总结--若建立的非前缀的索引，且无法有效的应用到分区条件，则最好建为全局索引。--若考虑业务的查询只会集中在一个或少数几个分区，则最好建成分区索引----------------------------------------------巧用索引存储列植-----------------------------------------/*总结： sum/avg在是否为空都不会影响结果值，但Oracle必须列为非空才可以用到索引max/min在列在非空或空，均可以用到索引count(*)必须索引列不为空才可以，因为count(*)会统计空值.count(column)时，不论column是否为空，都会用到此列的索引列。因为count(column)不统计空。*/--count--什么时候索引扫描比全表扫描高效很多？--当在大表上一个非空而且长度很短的列创建索引，这时索引的体积相对表来说特别小，那效率就高很多了。DROP TABLE T PURGE;CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;--没有应用到索引，因为object_id 可能为空admin@ORCL> select count(*) from t;执行计划----------------------Plan hash value: 2966233522-------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------| 0 | SELECT STATEMENT | | 1 | 161 (2)| 00:00:02 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| T | 57075 | 161 (2)| 00:00:02 |---------------------------------限制后为非空后，Oracle会使用到索引，因为索引不存储空值admin@ORCL> SELECT COUNT(*) FROM T WHERE OBJECT_ID IS NOT NULL;执行计划----------------------Plan hash value: 1131838604---------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 30 (4)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | ||* 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 57075 | 724K| 30 (4)| 00:00:01 |-----------------------------------------------------若是只统计object_id，Oracle会走索引，这点有点小奇怪，但经过测试，Oracle count单列的时候是不统计空值的，但统计count(*)时，--哪怕整行都是空值，Oracle仍会记录此条记录。admin@ORCL> SELECT COUNT(OBJECT_ID) FROM T;执行计划----------------------Plan hash value: 1131838604---------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 30 (4)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 57075 | 724K| 30 (4)| 00:00:01 |-----------------------------------------------------修改字段OBJECT_ID 为非空，Oracle则会自动应用OBJECT_ID上面的索引ALTER TABLE T MODIFY OBJECT_ID NOT NULL;admin@ORCL> SELECT COUNT(*) FROM T;执行计划----------------------Plan hash value: 1131838604-------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------| 0 | SELECT STATEMENT | | 1 | 30 (4)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 57075 | 30 (4)| 00:00:01 |---------------------------------------------SUM/AVG优化DROP TABLE T PURGE;CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;CREATE INDEX IDX_T_OBJECT_ID ON T(OBJECT_ID);--SUM与AVG均没有用到索引，这个蛮奇怪，因为SUM与AVG的计算中均没有NULL记录的运算admin@ORCL> SELECT SUM(OBJECT_ID) FROM T;执行计划----------------------Plan hash value: 2966233522---------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 162 (2)| 00:00:02 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | TABLE ACCESS FULL| T | 57075 | 724K| 162 (2)| 00:00:02 |---------------------------------------admin@ORCL> SELECT AVG(OBJECT_ID) FROM T;执行计划----------------------Plan hash value: 2966233522---------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 162 (2)| 00:00:02 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | TABLE ACCESS FULL| T | 57075 | 724K| 162 (2)| 00:00:02 |-----------------------------------------将OBJECT_ID设置为非空后，就可以用到索引。这点也蛮奇怪，可以sum是不包含空值的，但Oracle却不走索引。这点记住就好了ALTER TABLE T MODIFY OBJECT_ID NOT NULL;admin@ORCL> SELECT SUM(OBJECT_ID) FROM T;执行计划----------------------Plan hash value: 1572773910-----------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 30 (4)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 57075 | 724K| 30 (4)| 00:00:01 |-----------------------------------------------------admin@ORCL> SELECT AVG(OBJECT_ID) FROM T;执行计划----------------------Plan hash value: 1572773910-----------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 30 (4)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 57075 | 724K| 30 (4)| 00:00:01 |-----------------------------------------------------Note----- - dynamic sampling used for this statement统计信息---------------------- 0 recursive calls 0 db block gets 119 consistent gets 0 physical reads 0 redo size 433 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed--执行计划可以看出，count,sum,avg连续三个聚合语句写在一块使用的资源与单个avg是一致的。这是因为一次扫描索引块，就可以同时解决三个问题。admin@ORCL> SELECT SUM(OBJECT_ID),AVG(OBJECT_ID),COUNT(*) FROM T;执行计划----------------------Plan hash value: 1572773910-----------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 30 (4)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 57075 | 724K| 30 (4)| 00:00:01 |-----------------------------------------------------Note----- - dynamic sampling used for this statement统计信息---------------------- 0 recursive calls 0 db block gets 119 consistent gets 0 physical reads 0 redo size 567 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed--测试MAX/MIN函数的优化,这时哪怕字段可以为NULL,仍可以用到索引--INDEX FULL SCAN (MIN/MAX)：无论多大的索引，此操作都会非常快，因为索引是有序的，它只要去索引头块和索引尾块，即可查询到MIN/MAX值ALTER TABLE T MODIFY OBJECT_ID NULL;admin@ORCL> SELECT MAX(OBJECT_ID) FROM T;执行计划----------------------Plan hash value: 1152835269----------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 162 (2)| 00:00:02 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | INDEX FULL SCAN (MIN/MAX)| IDX_T_OBJECT_ID | 57075 | 724K| | |----------------------------------------------------------admin@ORCL> SELECT MIN(OBJECT_ID) FROM T;执行计划----------------------Plan hash value: 1152835269----------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 162 (2)| 00:00:02 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | INDEX FULL SCAN (MIN/MAX)| IDX_T_OBJECT_ID | 57075 | 724K| | |------------------------------------------------------------此处没有用到索引,看到Oracle的优化器也不是太智能。admin@ORCL> select max(object_id),min(object_id) from t;执行计划----------------------Plan hash value: 2966233522---------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------| 0 | SELECT STATEMENT | | 1 | 5 | 162 (2)| 00:00:02 || 1 | SORT AGGREGATE | | 1 | 5 | | || 2 | TABLE ACCESS FULL| T | 50826 | 248K| 162 (2)| 00:00:02 |-----------------------------------------换种写法，就可以走索引了SELECT MAX,MIN FROM (SELECT MAX(OBJECT_ID) MAX FROM T) A,(SELECT MIN(OBJECT_ID) MIN FROM T) B;admin@ORCL> SELECT MAX,MIN FROM (SELECT MAX(OBJECT_ID) MAX FROM T) A,(SELECT MIN(OBJECT_ID) MIN FROM T) B执行计划----------------------Plan hash value: 3758149690------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 26 | 323 (2)| 00:00:04 || 1 | NESTED LOOPS | | 1 | 26 | 323 (2)| 00:00:04 || 2 | VIEW | | 1 | 13 | 162 (2)| 00:00:02 || 3 | SORT AGGREGATE | | 1 | 5 | | || 4 | INDEX FULL SCAN (MIN/MAX)| IDX_T_OBJECT_ID | 50826 | 248K| | || 5 | VIEW | | 1 | 13 | 162 (2)| 00:00:02 || 6 | SORT AGGREGATE | | 1 | 5 | | || 7 | INDEX FULL SCAN (MIN/MAX)| IDX_T_OBJECT_ID | 50826 | 248K| | |------------------------------------------------------------统计信息---------------------- 1 recursive calls 0 db block gets 4 consistent gets--索引回表--TABLE ACCESS BY INDEX ROWID，实际上是查询返回的值不在索引里面，索引需要根据rowid返回表，去查询得出。--若查询的列可以在索引中查询到，则可以避免回表--若需要查询多列，可以考虑建立复合索引，但要注意平衡，如果联合索引太多，必然导致索引过大--虽然减少了回表动作，但增加了索引的大小，意味着查询就要遍历更多的索引块--建议不要超过3个admin@ORCL> select * from t where object_id = 20;执行计划----------------------Plan hash value: 4285561625-----------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 93 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 93 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_T_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |-----------------------------------------------------------admin@ORCL> select object_id from t where object_id = 20;执行计划----------------------Plan hash value: 2498590897------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| IDX_T_OBJECT_ID | 1 | 5 | 1 (0)| 00:00:01 |--------------------------------------------------Order by 之排序优化--看来按字段排序，考虑用到索引，也会考虑到null值SELECT * FROM T ORDER BY OBJECT_IDadmin@ORCL> SELECT * FROM T;已选择50826行。执行计划----------------------Plan hash value: 1601196873--------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------| 0 | SELECT STATEMENT | | 50826 | 4616K| 163 (3)| 00:00:02 || 1 | TABLE ACCESS FULL| T | 50826 | 4616K| 163 (3)| 00:00:02 |--------------------------------------统计信息---------------------- 0 recursive calls 0 db block gets 4058 consistent gets 0 physical reads 0 redo size 5460695 bytes sent via SQL*Net to client 37653 bytes received via SQL*Net from client 3390 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50826 rows processed --来看下没有排序，访问T表的资源admin@ORCL> SELECT * FROM T ORDER BY OBJECT_ID;已选择50826行。执行计划----------------------Plan hash value: 961378228-----------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-----------------------------------------------| 0 | SELECT STATEMENT | | 50826 | 4616K| | 1259 (2)| 00:00:16 || 1 | SORT ORDER BY | | 50826 | 4616K| 12M| 1259 (2)| 00:00:16 || 2 | TABLE ACCESS FULL| T | 50826 | 4616K| | 163 (3)| 00:00:02 |-----------------------------------------------统计信息---------------------- 0 recursive calls 0 db block gets 704 consistent gets 0 physical reads 0 redo size 2470336 bytes sent via SQL*Net to client 37653 bytes received via SQL*Net from client 3390 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 50826 rows processed--查看段大小，发现只有6M，而排序段用了12Madmin@ORCL> SELECT OWNER, SEGMENT_NAME, BYTES / 1024 / 1024 2 FROM DBA_SEGMENTS 3 WHERE SEGMENT_NAME = 'T' 4 AND OWNER = 'ADMIN';OWNER SEGMENT_NAME BYTES/1024/1024------------------------------ -------------------- ---------------ADMIN T 6--来看下没有排序的资源消耗，cost为163,排序使用了1259,逻辑读为4058，而排序只使用了704.这点蛮奇怪，但--真正决定性能的是cost的高低和真实完成的时间(cost右边的时间)，Oracle执行计划也是根据cost来决定执行路径admin@ORCL> SELECT * FROM T;已选择50826行。执行计划----------------------Plan hash value: 1601196873--------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------| 0 | SELECT STATEMENT | | 50826 | 4616K| 163 (3)| 00:00:02 || 1 | TABLE ACCESS FULL| T | 50826 | 4616K| 163 (3)| 00:00:02 |--------------------------------------统计信息---------------------- 0 recursive calls 0 db block gets 4058 consistent gets 0 physical reads 0 redo size 5460695 bytes sent via SQL*Net to client 37653 bytes received via SQL*Net from client 3390 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50826 rows processed--看看索引的costALTER TABLE T MODIFY OBJECT_ID NOT NULL;--发现oracle第一次走索引扫描，也会有排序的动作admin@ORCL> SELECT * FROM T ORDER BY OBJECT_ID;已选择50826行。执行计划----------------------Plan hash value: 2718353294-----------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------| 0 | SELECT STATEMENT | | 50826 | 4616K| 946 (1)| 00:00:12 || 1 | TABLE ACCESS BY INDEX ROWID| T | 50826 | 4616K| 946 (1)| 00:00:12 || 2 | INDEX FULL SCAN | IDX_T_OBJECT_ID | 50826 | | 115 (2)| 00:00:02 |-----------------------------------------------------------统计信息---------------------- 543 recursive calls 0 db block gets 7749 consistent gets 864 physical reads 0 redo size 5460695 bytes sent via SQL*Net to client 37653 bytes received via SQL*Net from client 3390 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 50826 rows processed--第二次执行就没有排序动作咯，这点也是有点奇怪噻(5 sorts (memory))。但是走全表扫描排序时，会走SORT ORDER BY 步骤，此步骤会使用--大约表大小的2倍去排序。此点比较消耗时间--另外发现使用索引时，逻辑读会大很多，这个是因为走索引，只能通过单个块查询，而全表扫描可以读多个块。 admin@ORCL> SELECT * FROM T ORDER BY OBJECT_ID;已选择50826行。执行计划----------------------Plan hash value: 2718353294-----------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------| 0 | SELECT STATEMENT | | 50826 | 4616K| 946 (1)| 00:00:12 || 1 | TABLE ACCESS BY INDEX ROWID| T | 50826 | 4616K| 946 (1)| 00:00:12 || 2 | INDEX FULL SCAN | IDX_T_OBJECT_ID | 50826 | | 115 (2)| 00:00:02 |-----------------------------------------------------------统计信息---------------------- 0 recursive calls 0 db block gets 7666 consistent gets 0 physical reads 0 redo size 5460695 bytes sent via SQL*Net to client 37653 bytes received via SQL*Net from client 3390 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50826 rows processed--DISTINCT 排重优化--大多数情况，使用索引消除重复，收效不是太明显。最好从业务上考虑，不保留重复。UPDATE T SET OBJECT_ID =3 WHERE ROWNUM <=10000;COMMIT;--可以看出,虽然执行计划中，sorts为0.但HASH UNIQUE仍然使用了1M多的空间去重复。admin@ORCL> SELECT DISTINCT OBJECT_ID FROM T;已选择40827行。执行计划----------------------Plan hash value: 1793979440-----------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-----------------------------------------------| 0 | SELECT STATEMENT | | 50826 | 248K| | 325 (4)| 00:00:04 || 1 | HASH UNIQUE | | 50826 | 248K| 1208K| 325 (4)| 00:00:04 || 2 | TABLE ACCESS FULL| T | 50826 | 248K| | 162 (2)| 00:00:02 |-----------------------------------------------统计信息---------------------- 0 recursive calls 0 db block gets 704 consistent gets 0 physical reads 0 redo size 598769 bytes sent via SQL*Net to client 30316 bytes received via SQL*Net from client 2723 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 40827 rows processed--修改T为非空ALTER TABLE T MODIFY OBJECT_ID NULL;--table access full变为index fast full scan,存在hash unique.--cost提高不少。但执行计划没有去除排序--index fast full scan 会一次性读取多个索引块，而index full scan扫描一次只能读取一个块.--所以 index fast full scan无法排重,而index full scan是可以排重的。--count(*)和sum无须用到排序，所以一般走 index fast null scanadmin@ORCL> SELECT DISTINCT OBJECT_ID FROM T;已选择40827行。执行计划----------------------Plan hash value: 2729247865-------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-------------------------------------------------------------| 0 | SELECT STATEMENT | | 50826 | 248K| | 191 (5)| 00:00:03 || 1 | HASH UNIQUE | | 50826 | 248K| 1208K| 191 (5)| 00:00:03 || 2 | INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 50826 | 248K| | 27 (4)| 00:00:01 |-------------------------------------------------------------统计信息---------------------- 0 recursive calls 0 db block gets 186 consistent gets 0 physical reads 0 redo size 598769 bytes sent via SQL*Net to client 30316 bytes received via SQL*Net from client 2723 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 40827 rows processed--强制走索引，效率不如上面admin@ORCL> SELECT /*+ index(t) */DISTINCT OBJECT_ID FROM T;已选择40827行。执行计划----------------------Plan hash value: 503711260--------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------| 0 | SELECT STATEMENT | | 50826 | 248K| 278 (4)| 00:00:04 || 1 | SORT UNIQUE NOSORT| | 50826 | 248K| 278 (4)| 00:00:04 || 2 | INDEX FULL SCAN | IDX_T_OBJECT_ID | 50826 | 248K| 115 (2)| 00:00:02 |--------------------------------------------------统计信息---------------------- 0 recursive calls 0 db block gets 2864 consistent gets 0 physical reads 0 redo size 598769 bytes sent via SQL*Net to client 30316 bytes received via SQL*Net from client 2723 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 40827 rows processed/*几个索引的扫描方式：INDEX FULLL SCAN：扫描一次只读取一个索引块INDEX FAST FULL SCAN：一次性会读取多个索引块，读取多个数据块不容易保证有序。因此COUNT(*),SUM等不需要排序动作的操作会走INDEX FAST FULL SCANINDEX FULL SCAN(MIN/MAX)：索引扫描最大值和最小值*/--UNION优化--看出来UNION会用到排序的步骤 SORT UNIQUEadmin@ORCL> SELECT OBJECT_ID FROM T 2 UNION 3 SELECT OBJECT_ID FROM T1;已选择50890行。执行计划----------------------Plan hash value: 631167089---------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |---------------------------------------------------------------| 0 | SELECT STATEMENT | | 101K| 894K| | 471 (62)| 00:00:06 || 1 | SORT UNIQUE | | 101K| 894K| 3216K| 471 (62)| 00:00:06 || 2 | UNION-ALL | | | | | | || 3 | INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 50826 | 248K| | 27 (4)| 00:00:01 || 4 | INDEX FAST FULL SCAN| IDX_T1_OBJECT_ID | 50898 | 646K| | 30 (4)| 00:00:01 |---------------------------------------------------------------Note----- - dynamic sampling used for this statement统计信息---------------------- 0 recursive calls 0 db block gets 305 consistent gets 0 physical reads 0 redo size 736599 bytes sent via SQL*Net to client 37697 bytes received via SQL*Net from client 3394 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 50890 rows processed--union all不会用到排序动作，对比下cost 471，而UNION　ALL 的COST为57 admin@ORCL> SELECT OBJECT_ID FROM T1 2 UNION ALL 3 SELECT OBJECT_ID FROM T;已选择101617行。执行计划----------------------Plan hash value: 1727178076------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------| 0 | SELECT STATEMENT | | 101K| 894K| 57 (50)| 00:00:01 || 1 | UNION-ALL | | | | | || 2 | INDEX FAST FULL SCAN| IDX_T1_OBJECT_ID | 50898 | 646K| 30 (4)| 00:00:01 || 3 | INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 50826 | 248K| 27 (4)| 00:00:01 |------------------------------------------------------Note----- - dynamic sampling used for this statement统计信息---------------------- 0 recursive calls 0 db block gets 7061 consistent gets 0 physical reads 0 redo size 1460206 bytes sent via SQL*Net to client 74899 bytes received via SQL*Net from client 6776 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 101617 rows processed--尝试使用HINT去消除UNION 尝试消除排序段.发现并没有消除掉--这是会因为两个结果集的筛选，各自的索引当然无法奏效。admin@ORCL> SELECT /*+ INDEX (T) */OBJECT_ID FROM T 2 UNION 3 SELECT /*+ INDEX (T1) */OBJECT_ID FROM T1;已选择50890行。执行计划----------------------Plan hash value: 2084608915----------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |----------------------------------------------------------| 0 | SELECT STATEMENT | | 101K| 894K| | 654 (59)| 00:00:08 || 1 | SORT UNIQUE | | 101K| 894K| 3216K| 654 (59)| 00:00:08 || 2 | UNION-ALL | | | | | | || 3 | INDEX FULL SCAN| IDX_T_OBJECT_ID | 50826 | 248K| | 115 (2)| 00:00:02 || 4 | INDEX FULL SCAN| IDX_T1_OBJECT_ID | 50898 | 646K| | 126 (2)| 00:00:02 |----------------------------------------------------------Note----- - dynamic sampling used for this statement统计信息---------------------- 0 recursive calls 0 db block gets 261 consistent gets 0 physical reads 0 redo size 736599 bytes sent via SQL*Net to client 37697 bytes received via SQL*Net from client 3394 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 50890 rows processed --索引之主外键设计CREATE TABLE T_P(ID NUMBER,NAME VARCHAR2(30));--创建主键ALTER TABLE T_P ADD CONSTRAINT T_P_ID_PK PRIMARY KEY(ID);CREATE TABLE T_C(ID NUMBER,FID NUMBER,NAME VARCHAR2(30));--创建外键约束ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY(FID) REFERENCES T_P(ID);--INSERT 数据INSERT INTO T_P SELECT ROWNUM,TABLE_NAME FROM ALL_TABLES;INSERT INTO T_C SELECT ROWNUM,MOD(ROWNUM,1000)+1,OBJECT_NAME FROM ALL_OBJECTS;--看下两表join的执行计划admin@ORCL> SELECT A.ID,A.NAME,B.NAME FROM T_P A,T_C B WHERE A.ID = B.FID AND A.ID = 880;已选择50行。执行计划----------------------Plan hash value: 727955870------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------| 0 | SELECT STATEMENT | | 39 | 2340 | 72 (3)| 00:00:01 || 1 | NESTED LOOPS | | 39 | 2340 | 72 (3)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 1 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 1 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL | T_C | 39 | 1170 | 71 (3)| 00:00:01 |--------------------------------------------------------在T_C表上创建索引，再来看下执行计划--CREATE INDEX IDX_T_C_FID ON T_C(FID);admin@ORCL> SELECT A.ID,A.NAME,B.NAME FROM T_P A,T_C B WHERE A.ID = B.FID AND A.ID = 880;已选择50行。执行计划----------------------Plan hash value: 4290308465--------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------| 0 | SELECT STATEMENT | | 50 | 2900 | 54 (0)| 00:00:01 || 1 | NESTED LOOPS | | 50 | 2900 | 54 (0)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| T_P | 1 | 30 | 2 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 1 (0)| 00:00:01 || 4 | TABLE ACCESS BY INDEX ROWID| T_C | 50 | 1400 | 52 (0)| 00:00:01 ||* 5 | INDEX RANGE SCAN | IDX_T_C_FID | 50 | | 1 (0)| 00:00:01 |----------------------------------------------------------分析下外键约束的危害--若没有在外键上创建索引，则在做DML操作外键所在的表时，会锁住整个主键表.--删除索引DROP INDEX IDX_T_C_FID;--删除外键所在的表一条记录，会造成主键所在的表全表锁住。admin@ORCL> DELETE T_C WHERE ID = 2;已删除 1 行。--执行任何DML都会锁住admin@ORCL> DELETE T_P WHERE ID =2000;--创建索引后，试试看.--这样后，就不会锁住主键所在的表。CREATE INDEX IDX_T_C_FID ON T_C(FID);admin@ORCL> DELETE T_C WHERE ID = 2;已删除 1 行。admin@ORCL> DELETE T_P WHERE ID =2000;已删除0行。--尝试删除主键的表的记录admin@ORCL> DELETE T_P WHERE ID =2;DELETE T_P WHERE ID =2*第 1 行出现错误:ORA-02292: 违反完整约束条件 (ADMIN.FK_T_C) - 已找到子记录--指定ON DELETE CASCADEALTER TABLE T_C DROP CONSTRAINT FK_T_C;ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY(FID) REFERENCES T_P(ID) ON DELETE CASCADE;admin@ORCL> DELETE T_P WHERE ID =2;已删除 1 行。--再看组合索引/*1.适合的场合能避免回表2.组合列返回越少越高效（过多的字段建立组合索引往往是不可取的，这样索引也必然过大，不宜超过三个）3.组合索引，对于性能来将，谁放在前面都一样。4.当时范围查询与等值查询结合时，等值查询列在前，范围查询列在后，这样的组合索引才高效5.当只是范围查询时，肯定是范围查询的列在前时，查询效率高。6.建立组合索引时，要考虑单列查询的情况，要把经常查询的列放在组合索引的第一列*/--测试下第三点DROP TABLE T PURGE;CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;CREATE INDEX IDX1_OBJECT_ID ON T(OBJECT_ID,OBJECT_TYPE);CREATE INDEX IDX2_OBJECT_ID ON T(OBJECT_TYPE,OBJECT_ID);admin@ORCL> SELECT * FROM T WHERE OBJECT_ID = 20 AND OBJECT_TYPE = 'TABLE';执行计划----------------------Plan hash value: 1913591113----------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 177 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 177 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX2_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID"=20)统计信息---------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1198 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed--利用hint，测试第二个索引admin@ORCL> SELECT /*+INDEX(T,IDX1_OBJECT_ID)*/* FROM T WHERE OBJECT_ID = 20 AND OBJECT_TYPE = 'TABLE';执行计划----------------------Plan hash value: 2486998213----------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 885 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 5 | 885 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX1_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |----------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("OBJECT_ID"=20 AND "OBJECT_TYPE"='TABLE')Note----- - dynamic sampling used for this statement统计信息---------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1198 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed--看看组合索引对单列查询的影响DROP INDEX IDX2_OBJECT_ID;--会用到索引，当查询列在组合索引的前列时admin@ORCL> SELECT * FROM T WHERE OBJECT_ID = 12;执行计划----------------------Plan hash value: 2486998213----------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 177 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 177 | 3 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX1_OBJECT_ID | 1 | | 2 (0)| 00:00:01 |----------------------------------------------------------DROP INDEX IDX1_OBJECT_ID;--创建非前缀索引看看,发现并不会走索引CREATE INDEX IDX2_OBJECT_ID ON T(OBJECT_TYPE,OBJECT_ID);admin@ORCL> SELECT * FROM T WHERE OBJECT_ID = 12;执行计划----------------------Plan hash value: 1601196873--------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------| 0 | SELECT STATEMENT | | 8 | 1416 | 162 (2)| 00:00:02 ||* 1 | TABLE ACCESS FULL| T | 8 | 1416 | 162 (2)| 00:00:02 |--------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("OBJECT_ID"=12)统计信息---------------------- 0 recursive calls 0 db block gets 704 consistent gets 0 physical reads 0 redo size 1193 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed--强制走下索引，对比下效率。发现并不如全表扫描的效率，因为全表扫描会有多块读。admin@ORCL> SELECT /*+index(t,IDX2_OBJECT_ID)*/* FROM T WHERE OBJECT_ID = 12;执行计划----------------------Plan hash value: 961656401----------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------| 0 | SELECT STATEMENT | | 8 | 1416 | 187 (2)| 00:00:03 || 1 | TABLE ACCESS BY INDEX ROWID| T | 8 | 1416 | 187 (2)| 00:00:03 ||* 2 | INDEX FULL SCAN | IDX2_OBJECT_ID | 190 | | 177 (2)| 00:00:03 |----------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("OBJECT_ID"=12) filter("OBJECT_ID"=12)Note----- - dynamic sampling used for this statement统计信息-------------------------------------- 0 recursive calls 0 db block gets 177 consistent gets 0 physical reads 0 redo size 1197 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed--有序插入与无序插入的执行时间--当记录有序插入时，索引块的扩展和批量重组是可以批量做的。而无序插入是无法使用批量的。--所以无序插入执行的速度比有序插入慢很多./*索引对DML语句的影响1.对INSERT影响最大，有百害而无一利，只要有索引，插入就慢，越多越慢。2.对DELETE语句来说，有好有坏。海量数据中定位删除少量记录时，这个条件列时索引列时必要的。但过多列有索引还是会影响明显。因为其他列的索引也要因此被更新。在经常需要删除大量记录的时候，危害加剧。3.对UPDATE语句危害最小，快速定位少量并更新的场景和DELETE类似。但具体修改某列时候，不会触发其他索引列的维护。另外在创建索引的过程中，会产生锁，并把整个表锁住。任何该表的DML操作都将会被阻止。这是因为建索引时，需要把索引列的列值全部取出来，加上锁是为了避免此时的列值被更新。*/--如何监控索引alter index index_name monitoring usage;--查看索引使用情况，进行跟踪select * from v$object_usage;--停止监控alter index index_name nomonitoring usage;

所获不止Oracle之B-Tree目录

热点推荐