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(*)
----------
79232
SQL> 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; ---再运行SQL
SQL> 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_ID 89y7dnv0suzyt, 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_id
desc) a where rownum <= 40) a where rn >= 1
Plan 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_ID 89y7dnv0suzyt, 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_id
desc) a where rownum <= 40) a where rn >= 1
Plan 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_ID 89y7dnv0suzyt, 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_id
desc) a where rownum <= 40) a where rn >= 1
Plan 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(*)
----------
79232
SQL> 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 >= 1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
SQL_ID 5yc23rv5p3151, child number 1
-------------------------------------
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
Plan 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_ID 5jj00atgztb53, child number 0
-------------------------------------
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
Plan 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_ID 5jj00atgztb53, child number 0
-------------------------------------
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
Plan 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 client
541 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 client
541 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
40 rows processed
此时逻辑读为1752
SQL> create index t100_idx4 on t100(owner,object_name,object_id);
SQL> select count(*) from t100;
COUNT(*)
----------
12083584
SQL> select count(*) from t100 where owner='SYSTEM';
COUNT(*)
----------
79232
SQL> select count(*) from t100 where owner='SYSTEM' and object_name like '%LOG%';
COUNT(*)
----------
39168
SQL> 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 gets
124 physical reads
0 redo size
2543 bytes sent via SQL*Net to client
541 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 条件列为能过滤掉大量数据的列