SQL>
select *
from (select rownum as rn, a.*
from (select *
from t100 a
where object_id > 1500
and owner = 'SYS'
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='SYS';
COUNT(*)
----------
4838784
SQL> select count(*)
from t100 a
where
owner = 'SYS'
and object_name like '%LOG%' 2 3 4 5 ;
COUNT(*)
----------
37632
创建索引1：
CREATE INDEX "SYSTEM"."T100_IDX1" ON "SYSTEM"."T100" ("OWNER", "OBJECT_ID")
SQL> select *
from (select rownum as rn, a.*
from (select /*+ index(a t100_idx1)*/ *
from t100 a
where
owner = 'SYS'
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: 3995274525
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1540 | 432K (1)| 01:26:36 |
|* 1 | VIEW | | 7 | 1540 | 432K (1)| 01:26:36 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 7 | 1449 | 432K (1)| 01:26:36 |
|* 4 | SORT ORDER BY STOPKEY | | 7 | 679 | 432K (1)| 01:26:36 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T100 | 7 | 679 | 432K (1)| 01:26:36 |
|* 6 | INDEX RANGE SCAN | T100_IDX1 | 431K| | 1318 (1)| 00:00:16 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=40)
4 - filter(ROWNUM<=40)
5 - filter("OBJECT_NAME" LIKE 'LOG%')
6 - access("OWNER"='SYS')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4852145 consistent gets
116880 physical reads
0 redo size
2537 bytes sent via SQL*Net to client
542 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40 rows processed
逻辑读 4852145
创建索引2：
CREATE INDEX "SYSTEM"."T100_IDX2" ON "SYSTEM"."T100" ("OWNER", "OBJECT_NAME", "OBJECT_ID")
执行计划
----------------------------------------------------------
Plan hash value: 3889701471
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1540 | 13 (8)| 00:00:01 |
|* 1 | VIEW | | 7 | 1540 | 13 (8)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 7 | 1449 | 13 (8)| 00:00:01 |
|* 4 | SORT ORDER BY STOPKEY | | 7 | 679 | 13 (8)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T100 | 7 | 679 | 12 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T100_IDX2 | 7 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=40)
4 - filter(ROWNUM<=40)
6 - access("OWNER"='SYS' AND "OBJECT_NAME" LIKE 'LOG%')
filter("OBJECT_NAME" LIKE 'LOG%')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
10685 consistent gets
0 physical reads
0 redo size
2537 bytes sent via SQL*Net to client
542 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40 rows processed
逻辑读 10685
创建索引3：
CREATE INDEX "SYSTEM"."T100_IDX3" ON "SYSTEM"."T100" ("OBJECT_ID", "OWNER")
执行计划
----------------------------------------------------------
Plan hash value: 1239817574
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1540 | 468K (1)| 01:33:43 |
|* 1 | VIEW | | 7 | 1540 | 468K (1)| 01:33:43 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 7 | 1449 | 468K (1)| 01:33:43 |
|* 4 | SORT ORDER BY STOPKEY | | 7 | 679 | 468K (1)| 01:33:43 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T100 | 7 | 679 | 468K (1)| 01:33:43 |
|* 6 | INDEX FULL SCAN | T100_IDX3 | 431K| | 36886 (1)| 00:07:23 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=40)
4 - filter(ROWNUM<=40)
5 - filter("OBJECT_NAME" LIKE 'LOG%')
6 - access("OWNER"='SYS')
filter("OWNER"='SYS')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4875771 consistent gets
153771 physical reads
0 redo size
2537 bytes sent via SQL*Net to client
542 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40 rows processed
逻辑读 4875771
创建索引4：
CREATE INDEX "SYSTEM"."T100_IDX4" ON "SYSTEM"."T100" ("OBJECT_ID", "OWNER", "OBJECT_NAME")
执行计划
----------------------------------------------------------
Plan hash value: 1079028630
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1540 | 77998 (1)| 00:15:36 |
|* 1 | VIEW | | 7 | 1540 | 77998 (1)| 00:15:36 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 7 | 1449 | 77998 (1)| 00:15:36 |
|* 4 | SORT ORDER BY STOPKEY | | 7 | 679 | 77998 (1)| 00:15:36 |
| 5 | TABLE ACCESS BY INDEX ROWID| T100 | 7 | 679 | 77997 (1)| 00:15:36 |
|* 6 | INDEX FULL SCAN | T100_IDX4 | 7 | | 77989 (1)| 00:15:36 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=40)
4 - filter(ROWNUM<=40)
6 - access("OWNER"='SYS' AND "OBJECT_NAME" LIKE 'LOG%')
filter("OWNER"='SYS' AND "OBJECT_NAME" LIKE 'LOG%')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
89401 consistent gets
2935 physical reads
0 redo size
2537 bytes sent via SQL*Net to client
542 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40 rows processed
逻辑读: 89401
创建索引5：
CREATE INDEX "SYSTEM"."T100_IDX5" ON "SYSTEM"."T100" ("OBJECT_NAME", "OWNER", "OBJECT_ID")
执行计划
----------------------------------------------------------
Plan hash value: 3702588553
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1540 | 14 (8)| 00:00:01 |
|* 1 | VIEW | | 7 | 1540 | 14 (8)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 7 | 1449 | 14 (8)| 00:00:01 |
|* 4 | SORT ORDER BY STOPKEY | | 7 | 679 | 14 (8)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T100 | 7 | 679 | 13 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T100_IDX5 | 7 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=40)
4 - filter(ROWNUM<=40)
6 - access("OBJECT_NAME" LIKE 'LOG%' AND "OWNER"='SYS')
filter("OWNER"='SYS' AND "OBJECT_NAME" LIKE 'LOG%')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
10898 consistent gets
0 physical reads
0 redo size
2537 bytes sent via SQL*Net to client
542 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40 rows processed