SQL> select * from dba_indexes where table_name='T100' and owner='SYSTEM';SQL> 未选定行SQL> select * from (select rownum as rn, a.* from (select * from t100 a where object_id > 1500 and owner = 'SYSTEM' order by object_id desc) a where rownum <= 40) a where rn >= 1;SQL> select count(*) from t100 where owner='SYSTEM'; COUNT(*)---------- 79232SQL> select count(*) from t100 where owner='SYSTEM' and object_id>1500; COUNT(*)---------- 71552---高级执行计划:11G：set linesize 200;set pagesize 200;alter session set statistics_level=all; ---再运行SQLSQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID89y7dnv0suzyt, child number 0-------------------------------------select * from (select rownum as rn, a.* from (select * from t100 a where object_id > 1500 and owner = 'SYSTEM' order by object_iddesc) a where rownum <= 40) awhere rn >= 1Plan hash value: 3078193190-------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time| Buffers | Reads | OMem | 1Mem | Used-Mem |-------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT || 1 | |40 |00:00:00.73 | 171K| 171K| | | ||* 1 | VIEW || 1 | 40 |40 |00:00:00.73 | 171K| 171K| | | ||* 2 | COUNT STOPKEY || 1 | |40 |00:00:00.73 | 171K| 171K| | | || 3 | VIEW || 1 | 425K|40 |00:00:00.73 | 171K| 171K| | | ||* 4 | SORT ORDER BY STOPKEY|| 1 | 425K|40 |00:00:00.73 | 171K| 171K| 61440 | 61440 |55296 (0)||* 5 | TABLE ACCESS FULL | T100 | 1 | 425K| 71552 |00:00:00.70 | 171K| 171K| | | |-------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=40) 4 - filter(ROWNUM<=40) 5 - filter(("OWNER"='SYSTEM' AND "OBJECT_ID">1500))已选择28行。在object_id列上创建索引；SQL> create index t100_idx1 on t100(object_id);索引已创建。返回只有40条,但是这个SQL需要访问71552 条记录 显然不够优化创建索引:SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID89y7dnv0suzyt, child number 0-------------------------------------select * from (select rownum as rn, a.* from (select * from t100 a where object_id > 1500 and owner = 'SYSTEM' order by object_iddesc) a where rownum <= 40) awhere rn >= 1Plan hash value: 2240177993----------------------------------------------------------------------------------------------------------------| Id | Operation| Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT| | 1 | | 40 |00:00:00.01 | 46 | 2 ||* 1 | VIEW| | 1 | 40 | 40 |00:00:00.01 | 46 | 2 ||* 2 | COUNT STOPKEY | | 1 | | 40 |00:00:00.01 | 46 | 2 || 3 | VIEW | | 1 | 41 | 40 |00:00:00.01 | 46 | 2 ||* 4 | TABLE ACCESS BY INDEX ROWID | T100 | 1 | 425K| 40 |00:00:00.01 | 46 | 2 ||* 5 | INDEX RANGE SCAN DESCENDING| T100_IDX1 | 1 | 1149 | 40 |00:00:00.01 | 6 | 2 |----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=40) 4 - filter("OWNER"='SYSTEM') 5 - access("OBJECT_ID">1500)已选择28行。这时候就只访问了40条.继续优化:SQL> create index t100_idx2 on t100(owner,object_id);索引已创建。SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID89y7dnv0suzyt, child number 0-------------------------------------select * from (select rownum as rn, a.* from (select * from t100 a where object_id > 1500 and owner = 'SYSTEM' order by object_iddesc) a where rownum <= 40) awhere rn >= 1Plan hash value: 2326092562----------------------------------------------------------------------------------------------------------------| Id | Operation| Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT| | 1 | | 40 |00:00:00.01 | 46 | 2 ||* 1 | VIEW| | 1 | 40 | 40 |00:00:00.01 | 46 | 2 ||* 2 | COUNT STOPKEY | | 1 | | 40 |00:00:00.01 | 46 | 2 || 3 | VIEW | | 1 | 41 | 40 |00:00:00.01 | 46 | 2 || 4 | TABLE ACCESS BY INDEX ROWID | T100 | 1 | 425K| 40 |00:00:00.01 | 46 | 2 ||* 5 | INDEX RANGE SCAN DESCENDING| T100_IDX2 | 1 | 41 | 40 |00:00:00.01 | 6 | 2 |----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=40) 5 - access("OWNER"='SYSTEM' AND "OBJECT_ID" IS NOT NULL AND "OBJECT_ID">1500)已选择27行。继续测试:SQL> select count(*) from t100 where owner='SYSTEM'; COUNT(*)---------- 79232SQL> create index t100_idx2 on t100(owner,object_id);select count(*) from t100 a where owner = 'SYSTEM' and object_name like '%LOG%'select * from (select rownum as rn, a.* from (select * from t100 a where owner = 'SYSTEM' and object_name like '%LOG%' order by object_id desc) a where rownum <= 40) a where rn >= 1SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID5yc23rv5p3151, child number 1-------------------------------------select * from (select rownum as rn, a.* from (select * from t100 a whereowner = 'SYSTEM' and object_name like '%LOG%' order by object_id desc) a where rownum <= 40) awhere rn >= 1Plan hash value: 2326092562----------------------------------------------------------------------------------------------------------------| Id | Operation| Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT| | 1 | | 40 |00:00:00.01 | 1715 | 3 ||* 1 | VIEW| | 1 | 40 | 40 |00:00:00.01 | 1715 | 3 ||* 2 | COUNT STOPKEY | | 1 | | 40 |00:00:00.01 | 1715 | 3 || 3 | VIEW | | 1 | 40 | 40 |00:00:00.01 | 1715 | 3 ||* 4 | TABLE ACCESS BY INDEX ROWID | T100 | 1 |21578 | 40 |00:00:00.01 | 1715 | 3 ||* 5 | INDEX RANGE SCAN DESCENDING| T100_IDX2 | 1 | 800 | 1704 |00:00:00.01 | 11 | 0 |----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=40) 4 - filter(("OBJECT_NAME" LIKE '%LOG%' AND "OBJECT_NAME" IS NOT NULL)) 5 - access("OWNER"='SYSTEM')已选择29行。创建索引:SQL> create index t100_idx3 on t100(owner,object_name,object_id);SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID5jj00atgztb53, child number 0-------------------------------------select * from (select rownum as rn, a.* from (select * from t100 a whereowner = 'SYSTEM' and object_name like '%LOG%' order by object_id desc) a where rownum <= 40) awhere rn >= 1Plan hash value: 3429328390-------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation| Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |-------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT| | 1 | | 40 |00:00:00.13 |39633 | 918 | | | ||* 1 | VIEW| | 1 | 40 | 40 |00:00:00.13 |39633 | 918 | | | ||* 2 | COUNT STOPKEY | | 1 | | 40 |00:00:00.13 |39633 | 918 | | | || 3 | VIEW | | 1 |21578 | 40 |00:00:00.13 |39633 | 918 | | | ||* 4 | SORT ORDER BY STOPKEY| | 1 |21578 | 40 |00:00:00.13 |39633 | 918 | 24576 | 24576 |22528 (0)|| 5 | TABLE ACCESS BY INDEX ROWID| T100 | 1 |21578 | 39168 |00:00:00.11 |39633 | 918 | | | ||* 6 | INDEX RANGE SCAN| T100_IDX3 | 1 |21578 | 39168 |00:00:00.04 | 465 | 464 | | | |-------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=40) 4 - filter(ROWNUM<=40) 6 - access("OWNER"='SYSTEM') filter(("OBJECT_NAME" LIKE '%LOG%' AND "OBJECT_NAME" IS NOT NULL))已选择31行。创建索引:SQL> create index t100_idx4 on t100(object_id,owner,object_name);索引已创建。SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID5jj00atgztb53, child number 0-------------------------------------select * from (select rownum as rn, a.* from (select * from t100 a whereowner = 'SYSTEM' and object_name like '%LOG%' order by object_id desc) a where rownum <= 40) awhere rn >= 1Plan hash value: 1439634448---------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |---------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 40 |00:00:01.75 | 68683 |68639 ||* 1 | VIEW | | 1 | 40 | 40 |00:00:01.75 | 68683 |68639 ||* 2 | COUNT STOPKEY | | 1 | | 40 |00:00:01.75 | 68683 |68639 || 3 | VIEW | | 1 | 40 | 40 |00:00:01.75 | 68683 |68639 || 4 | TABLE ACCESS BY INDEX ROWID| T100 | 1 | 21578 | 40 |00:00:01.75 | 68683 |68639 ||* 5 | INDEX FULL SCAN DESCENDING| T100_IDX4 | 1 | 40 | 40 |00:00:01.75 | 68643 |68639 |---------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=40) 5 - access("OWNER"='SYSTEM') filter(("OWNER"='SYSTEM' AND "OBJECT_NAME" LIKE '%LOG%' AND "OBJECT_NAME" IS NOT NULL))已选择29行。对比:走 T100_IDX4 索引, create index t100_idx4 on t100(object_id,owner,object_name);执行计划----------------------------------------------------------Plan hash value: 1439634448--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | |40 | 8800 | 189 (0)| 00:00:03 ||* 1 | VIEW | |40 | 8800 | 189 (0)| 00:00:03 ||* 2 | COUNT STOPKEY | | | || || 3 | VIEW | |40 | 8280 | 189 (0)| 00:00:03 || 4 | TABLE ACCESS BY INDEX ROWID| T100 | 21578 | 2044K| 189 (0)| 00:00:03 ||* 5 | INDEX FULL SCAN DESCENDING| T100_IDX4 |40 | | 148 (0)| 00:00:02 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=40) 5 - access("OWNER"='SYSTEM') filter("OWNER"='SYSTEM' AND "OBJECT_NAME" LIKE '%LOG%' AND "OBJECT_NAME" IS NOT NULL)统计信息---------------------------------------------------------- 1 recursive calls 0 db block gets 68683 consistent gets 0 physical reads 0 redo size 2543 bytes sent via SQL*Net to client541 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 40 rows processed此时逻辑读为 68683走 index t100_idx2 SQL> create index t100_idx2 on t100(owner,object_id)SQL> select * from (select rownum as rn, a.* from (select * from t100 a where owner = 'SYSTEM' and object_name like '%LOG%' order by object_id desc) a where rownum <= 40) a where rn >= 1 2 3 4 5 6 7 8 9 10 ;已选择40行。执行计划----------------------------------------------------------Plan hash value: 2326092562---------------------------------------------------------------------------------------------| Id | Operation| Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT| | 40 | 8800 |806 (0)| 00:00:10 ||* 1 | VIEW| | 40 | 8800 |806 (0)| 00:00:10 ||* 2 | COUNT STOPKEY | | | | | || 3 | VIEW | | 40 | 8280 |806 (0)| 00:00:10 ||* 4 | TABLE ACCESS BY INDEX ROWID | T100 | 21578 | 2044K|806 (0)| 00:00:10 ||* 5 | INDEX RANGE SCAN DESCENDING| T100_IDX2 |800 | | 5 (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=40) 4 - filter("OBJECT_NAME" LIKE '%LOG%' AND "OBJECT_NAME" IS NOT NULL) 5 - access("OWNER"='SYSTEM')统计信息---------------------------------------------------------- 43 recursive calls 0 db block gets 1752 consistent gets 0 physical reads 0 redo size 2543 bytes sent via SQL*Net to client541 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 40 rows processed此时逻辑读为1752SQL> create index t100_idx4 on t100(owner,object_name,object_id);SQL> select count(*) from t100; COUNT(*)---------- 12083584SQL> select count(*) from t100 where owner='SYSTEM'; COUNT(*)---------- 79232SQL> select count(*) from t100 where owner='SYSTEM' and object_name like '%LOG%'; COUNT(*)---------- 39168SQL> create index t100_idx4 on t100(owner,object_name,object_id);执行计划----------------------------------------------------------Plan hash value: 3541489740-----------------------------------------------------------------------------------------------------| Id | Operation| Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT| | 40 | 8800 | | 24854 (1)| 00:04:59 ||* 1 | VIEW| | 40 | 8800 | | 24854 (1)| 00:04:59 ||* 2 | COUNT STOPKEY | | | | | | || 3 | VIEW | | 21578 | 4361K| | 24854 (1)| 00:04:59 ||* 4 | SORT ORDER BY STOPKEY| | 21578 | 2044K| 2840K| 24854 (1)| 00:04:59 || 5 | TABLE ACCESS BY INDEX ROWID| T100 | 21578 | 2044K| | 24372 (1)| 00:04:53 ||* 6 | INDEX RANGE SCAN| T100_IDX4 | 21578 | | | 2790 (1)| 00:00:34 |-----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=40) 4 - filter(ROWNUM<=40) 6 - access("OWNER"='SYSTEM') filter("OBJECT_NAME" LIKE '%LOG%' AND "OBJECT_NAME" IS NOT NULL)统计信息---------------------------------------------------------- 31 recursive calls 0 db block gets 39665 consistent gets124 physical reads 0 redo size 2543 bytes sent via SQL*Net to client541 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 40 rows processed分页创建索引,where条件列加order by列 where 条件列为能过滤掉大量数据的列
0 0
