分页查询，分页列最好非空且创建索引。
11:34:48 SQL> set autot off
11:34:51 SQL> set autot traceonly
11:34:56 SQL> drop table t;
表已删除。
已用时间: 00: 00: 01.03
11:35:22 SQL> purge recyclebin;
回收站已清空。
已用时间: 00: 00: 00.54
11:35:36 SQL> set time on timing on
11:36:05 SQL> create table t as select * from dba_objects;
表已创建。
已用时间: 00: 00: 00.49
11:36:21 SQL> insert into t select * from t;
已创建 86747 行。
已用时间: 00: 00: 01.02
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 330K| 65M| 695 (1)| 00:00:09 |
| 1 | LOAD TABLE CONVENTIONAL | T | | | | |
| 2 | TABLE ACCESS FULL | T | 330K| 65M| 695 (1)| 00:00:09 |
---------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
33 recursive calls
11221 db block gets
2784 consistent gets
1239 physical reads
10091044 redo size
851 bytes sent via SQL*Net to client
786 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
86747 rows processed
11:37:53 SQL> set autot off;
11:38:08 SQL> insert into t select * from t;
已创建 173494 行。
已用时间: 00: 00: 00.23
11:38:12 SQL> /
已创建 346988 行。
已用时间: 00: 00: 02.68
11:38:30 SQL> commit;
提交完成。
已用时间: 00: 00: 00.06
11:38:34 SQL> insert into t select * from t;
已创建 693976 行。
已用时间: 00: 00: 04.27
11:38:43 SQL> commit;
提交完成。
已用时间: 00: 00: 00.01
11:38:47 SQL> create index idx_t_object_name on t(object_name);
索引已创建。
已用时间: 00: 00: 06.78
11:39:02 SQL> desc t;
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------- --------
----------------------------------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
11:39:16 SQL> execute dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'T',estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true);
PL/SQL 过程已成功完成。
已用时间: 00: 00: 07.62
11:39:35 SQL> set autot traceonly
11:39:38 SQL> SELECT *
11:39:44 2 FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM T ORDER BY OBJECT_NAME DESC) A WHERE ROWNUM < 16)
11:39:44 3 WHERE RN >=1;
已选择15行。
已用时间: 00: 00: 00.49
执行计划
----------------------------------------------------------
Plan hash value: 882605040
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 3300 | | 36391 (1)| 00:07:17 |
|* 1 | VIEW | | 15 | 3300 | | 36391 (1)| 00:07:17 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 1387K| 273M| | 36391 (1)| 00:07:17 |
|* 4 | SORT ORDER BY STOPKEY| | 1387K| 129M| 177M| 36391 (1)| 00:07:17 |
| 5 | TABLE ACCESS FULL | T | 1387K| 129M| | 5423 (1)| 00:01:06 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<16)
4 - filter(ROWNUM<16)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
19924 consistent gets
0 physical reads
0 redo size
1970 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15 rows processed
11:40:15 SQL> alter table t modify object_name not null;
表已更改。
已用时间: 00: 00: 00.10
11:40:22 SQL> desc t;
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------- --------
----------------------------------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
11:40:28 SQL> SELECT *
11:40:36 2 FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM T ORDER BY OBJECT_NAME DESC) A WHERE ROWNUM < 16)
11:40:36 3 WHERE RN >=1;
已选择15行。
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 3229612337
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 3300 | 14 (0)| 00:00:01 |
|* 1 | VIEW | | 15 | 3300 | 14 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 15 | 3105 | 14 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1387K| 129M| 14 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN DESCENDING| IDX_T_OBJECT_NAME | 15 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<16)
统计信息
----------------------------------------------------------
56 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
1970 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
15 rows processed
11:40:36 SQL> SELECT *
11:40:46 2 FROM (SELECT A.*, ROWNUM RN
11:40:46 3 FROM (SELECT * FROM T ORDER BY OBJECT_NAME DESC) A
11:40:46 4 WHERE ROWNUM < 30000)
11:40:46 5 WHERE RN >= 30000 - 15;
已选择15行。
已用时间: 00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 3229612337
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29999 | 6445K| 20611 (1)| 00:04:08 |
|* 1 | VIEW | | 29999 | 6445K| 20611 (1)| 00:04:08 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 29999 | 6064K| 20611 (1)| 00:04:08 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1387K| 129M| 20611 (1)| 00:04:08 |
| 5 | INDEX FULL SCAN DESCENDING| IDX_T_OBJECT_NAME | 29999 | | 152 (0)| 00:00:02 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=29985)
2 - filter(ROWNUM<30000)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
15681 consistent gets
0 physical reads
0 redo size
2266 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed
11:40:47 SQL> SELECT T.*
11:41:12 2 FROM T
11:41:12 3 WHERE ROWID IN (SELECT RID
11:41:12 4 FROM (SELECT A.RID, ROWNUM RN
11:41:12 5 FROM (SELECT ROWID RID FROM T ORDER BY OBJECT_NAME DESC) A
11:41:12 6 WHERE ROWNUM < 30000)
11:41:12 7 WHERE RN >= 30000 - 15);
已选择15行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 695960827
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 110 | 155 (2)| 00:00:02 |
| 1 | NESTED LOOPS | | 1 | 110 | 155 (2)| 00:00:02 |
| 2 | VIEW | VW_NSO_1 | 29999 | 351K| 152 (0)| 00:00:02 |
| 3 | HASH UNIQUE | | 1 | 732K| | |
|* 4 | VIEW | | 29999 | 732K| 152 (0)| 00:00:02 |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | VIEW | | 29999 | 351K| 152 (0)| 00:00:02 |
| 7 | INDEX FULL SCAN DESCENDING| IDX_T_OBJECT_NAME | 1387K| 48M| 152 (0)| 00:00:02 |
| 8 | TABLE ACCESS BY USER ROWID | T | 1 | 98 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("RN">=29985)
5 - filter(ROWNUM<30000)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
175 consistent gets
0 physical reads
0 redo size
1927 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed
11:41:13 SQL> spool off;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/89196/viewspace-1457042/ ，如需转载，请注明出处，否则将追究法律责任。
