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;
COUNT(*)
----------
12083584
SQL> select count(*) from t100 where owner='SYSTEM';
COUNT(*)
----------
79232
SQL> select count(*)
from t100 a
where
owner = 'SYSTEM'
and object_name like '%LOG%' 2 3 4 5 ;
COUNT(*)
----------
39168
---特殊执行计划:
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'));
创建索引1:
SQL> select count(*) from t100 where owner='SYSTEM';
COUNT(*)
----------
79232
SQL> create index t100_idx1 on t100(owner,object_id);
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: 2240177993
---------------------------------------------------------------------------------------------
| Id | Operation| Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT| | 40 | 8800 | 45 (0)| 00:00:01 |
|* 1 | VIEW| | 40 | 8800 | 45 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 41 | 8487 | 45 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | T100 |425K| 39M| 45 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN DESCENDING| T100_IDX1 | 41 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=40)
5 - access("OWNER"='SYSTEM' AND "OBJECT_ID">1500 AND "OBJECT_ID" IS NOT NULL)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
46 consistent gets
0 physical reads
0 redo size
2529 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
创建索引2:
SQL> create index t100_idx2 on t100(owner,object_name,object_id);
执行计划
----------------------------------------------------------
Plan hash value: 3889701471
-----------------------------------------------------------------------------------------------------
| 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_IDX2 | 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)
统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
39661 consistent gets
464 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
创建索引3:
SQL> create index t100_idx3 on t100(object_id,owner);
索引已创建。
执行计划
----------------------------------------------------------
Plan hash value: 1672976351
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |40 | 8800 | 872 (0)| 00:00:11 |
|* 1 | VIEW | |40 | 8800 | 872 (0)| 00:00:11 |
|* 2 | COUNT STOPKEY | | | || |
| 3 | VIEW | |40 | 8280 | 872 (0)| 00:00:11 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T100 | 21578 | 2044K| 872 (0)| 00:00:11 |
|* 5 | INDEX FULL SCAN DESCENDING| T100_IDX3 | 800 | |71 (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')
filter("OWNER"='SYSTEM')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
33730 consistent gets
32022 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
创建索引4:
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> 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
68639 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
结论: 分页SQL 创建索引 where 列+ order by列
