所获,不止Oracle之索引篇(1) 博客分类: Oracle 优化
程序员文章站
2024-03-22 12:51:04
...
/* 索引 索引由三部分组成,根块(root),Branch(茎块),Leaf(叶子块)。其中Leaf块主要存储了key column value(索引列具体值),以及能具体 定位到数据块位置的rowid。 索引的创建过程: 1.要建索引先排序,将索引列的值顺序取出,及该行的rowid放入到内存中。 2.依次将内存中的顺序存放的值和rowid存放入索引块中。 3.当填满两个索引块后,oracle会产生一个块,用于管理同级的叶子块。这个块记录了叶子块的信息,并不记录索引列的键值,所以使用的空间比较少。 4.当管理叶子块的块被填满后,oracle又会产生一个上一级管理块,依次循环。同级两块需要管理。 索引结构的三大重要特点 1.索引的高度比较低 2.索引存储列值 3.索引本身是有序的*/ ----------------------------------------------索引高度较低验证----------------------------------------- --索引的大小和高度是巨大差别的,可能大小差好多倍,但高度却一样。 --构造T1-T7表,记录从5到500W CREATE 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 1 IDX_ID_T2 0 1 50 50 1 IDX_ID_T3 1 2 500 500 1 IDX_ID_T4 1 11 5000 5000 9 IDX_ID_T5 1 110 50000 50000 101 IDX_ID_T6 2 1113 500000 500000 1035 IDX_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 a20 admin@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 dump leaf: 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=12 col 0; len 2; (2): c1 02 --十六进制 col 1; len 6; (6): 01 00 03 cc 00 00 row#1[8012] flag: ------, lock: 0, len=12 col 0; len 2; (2): c1 03 col 1; len 6; (6): 01 00 03 cc 00 01 row#2[8000] flag: ------, lock: 0, len=12 col 0; len 2; (2): c1 04 col 1; len 6; (6): 01 00 03 cc 00 02 row#3[7988] flag: ------, lock: 0, len=12 col 0; len 2; (2): c1 05 col 1; len 6; (6): 01 00 03 cc 00 03 row#4[7976] flag: ------, lock: 0, len=12 col 0; len 2; (2): c1 06 col 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,2 admin@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为13 01 00 03 cc 00 00 0000 0001 0000 0000 0000 0011 1100 1100 0000 0000 0000 0000 --上面转化为二进制后,前十位为文件编号,后22个二进制数据为块编号,后面的数字为第几行 --从结果可以看出,索引内容中包含了索引列的值,及该条记录的rowid 后3部分内容。表段的OBJECT_ID是没有必要的 0000 0001 00: 4 00 0000 0000 0011 1100 1100 : 972 0000 0000 0000 0000: 0 SELECT 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; ------------------------------------------------------------------- | 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; --------------------------------------------------------------------------------------- | 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; --------------------------------------------------------------------------------------- | 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; ------------------------------------------------------------------------------- | 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; --------------------------------------------------------------------------- | 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; --------------------------------------------------------------------------- | 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; ----------------------------------------------------------------------------------------- | 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; ----------------------------------------------------------------------------------------- | 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 | ----------------------------------------------------------------------------------------- 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 | ----------------------------------------------------------------------------------------- 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_ID 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 --来看下没有排序,访问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,而排序段用了12M admin@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 --看看索引的cost ALTER 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 scan admin@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