[转]Oracle ROWNUM用法和分页查询总结

本文转自:http://blog.csdn.net/fw0124/article/details/42737671

**********************************************************************************************************

[转载]

Oracle的分页查询语句基本上可以依照本文为出的格式来进行套用。

Oracle分页查询格式(一):http://yangtingkun.itpub.net/post/468/100278

Oracle分页查询格式(二):http://yangtingkun.itpub.net/post/468/101703

Oracle分页查询格式(三):http://yangtingkun.itpub.net/post/468/104595

Oracle分页查询格式(四):http://yangtingkun.itpub.net/post/468/104867

Oracle分页查询格式(五):http://yangtingkun.itpub.net/post/468/107934

Oracle分页查询格式(六):http://yangtingkun.itpub.net/post/468/108677

Oracle分页查询格式(七):http://yangtingkun.itpub.net/post/468/109834

Oracle分页查询格式(八):http://yangtingkun.itpub.net/post/468/224557

Oracle分页查询格式(九):http://yangtingkun.itpub.net/post/468/224409

Oracle分页查询格式(十):http://yangtingkun.itpub.net/post/468/224823

Oracle分页查询的排序问题:http://yangtingkun.itpub.net/post/468/112274 

Oracle官网连接查询优化的认证:http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#i36235
NESTED LOOP/HASH JOIN/SORT MERGE
JOIN的区别:http://jewfinkl.blog.163.com/blog/static/14076982012431052316/

**********************************************************************************************************

因上述文章进行了如下的下结论。

ROWNUM

可能还清楚ROWNUM只适用于小于或低于等于,如果展开等于判断,那么只能等1,不克展开超越的较。
ROWNUM是oracle系统顺序分配也于询问返回的施行的编号,返回的率先实践分配的是1,第二履是2,依此类推。
ROWNUM总是打1方始,不管当前的笔录是否满足查询结果,ROWNUM返回的价都是1,如果这长长的记下的价值最终满足所有的准,那么ROWNUM会递加,下一致久记下的ROWNUM会返回2,否则下同样长达记下的ROWNUM仍然返回1。 理解了这或多或少,就知为什么一般的ROWNUM大于某个值或顶有不也1底值是心有余而力不足回到结果的,因此对此各条记下的ROWNUM都是1,而ROWNUM为1休饱查询的结果,所以下同样长达记下的ROWNUM不会见递增,仍然是1,因此有着的记录还不满足条件。

分页查询格式1
在询问的无比外层控制分页的无限小值和极端老价值。查询语句如下:

[sql] view
plain
copy
print?图片 1图片 2

  1. SELECT * FROM   
  2. (  
  3. SELECT A.*, ROWNUM RN   
  4. FROM (SELECT * FROM TABLE_NAME) A   
  5. )  
  6. WHERE RN BETWEEN 21 AND 40  

图片 3

SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
)
WHERE RN BETWEEN 21 AND 40

分页查询格式2

[sql] view
plain
copy
print?图片 4图片 5

  1. SELECT * FROM   
  2. (  
  3. SELECT A.*, ROWNUM RN   
  4. FROM (SELECT * FROM TABLE_NAME) A   
  5. WHERE ROWNUM <= 40  
  6. )  
  7. WHERE RN >= 21  

图片 6

SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
WHERE ROWNUM <= 40
)
WHERE RN >= 21

分页查询格式3
考虑到多表联合之状况,如果非在意当系统受到运用HINT的言辞,可以将分页的查询语句改写为:

[sql] view
plain
copy
print?图片 7图片 8

  1. SELECT /*+ FIRST_ROWS */ * FROM   
  2. (  
  3. SELECT A.*, ROWNUM RN   
  4. FROM (SELECT * FROM TABLE_NAME) A   
  5. WHERE ROWNUM <= 40  
  6. )  
  7. WHERE RN >= 21  

图片 9

SELECT /*+ FIRST_ROWS */ * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
WHERE ROWNUM <= 40
)
WHERE RN >= 21

频率问题
对比就有限种植写法,绝大多数之动静下,第2个查询的效率比较第1单强得多。
这是由于CBO优化模式下,Oracle可以拿外层的询问条件推到内层查询中,以增进内层查询的执行效率。对于第2独查询语句,第二重叠的询问条件WHERE
ROWNUM <=
40就是足以吃Oracle推入到内层查询中,这样Oracle查询的结果如果超过了ROWNUM限制标准,就告一段落查询将结果回到了。
而第1单查询语句,由于查询条件BETWEEN 21 AND
40凡是在为查询的老三层,而Oracle无法将第三重合的询问条件促进至极致内层(即使推到最内层也并未意思,因为极度内层查询不知情RN代表什么)。因此,对于第1独查询语句,Oracle最内层返回给中间层的凡颇具满足条件的数额,而中级层返回给最好外层的为是有数据。数据的过滤在太外层完成,显然是频率要比第一只查询没有得多。
上面分析的查询不仅仅是指向单表的略询问,对于极端内层查询是错综复杂的多表联合查询或极内层查询包含排序的情状一样中。
观地方格式1暨格式2二者的施行计划得以窥见,两只实施计划唯一的别就是是格式2的询问在COUNT这步用了STOPKEY,也就是说,Oracle将ROWNUM
<=
20推入到查询内层,当副查询的规格的记录上STOPKEY的值,则Oracle结束查询。因此,可以预见,采用第二种方法,在翻页的始发有些查询速度很快,越到尾,效率进一步没有,当翻译至终极一页,效率应和率先栽方法接近。
分页查询语句之所以可很快的返结果,是坐它们的目标是极其抢的回第一长条结果。如果每页有20长条记下,目前翻至第5页,那么单纯待回到前100漫长记下还足以满足查询的要求了,也许还有几万修记下为符合查询的标准,但是出于分页的范围,在眼前底查询中得忽略这些数量,而止待尽快的回来前100长条数。这也是为什么在正规分页查询语句被时常会面使FIRST_ROWS提示的案由。
对于行操作,可以于获得结果的又用结果一直回到给上一样重合调用。但是对于结果集操作,Oracle必须得到结果集中具有的多寡,因此分页查询中所带动的ROWNUM信息不由左右。如果尽内层的子查询中富含了下面这些操作着之一个以上,则分页查询语句无法体现出其它的习性优势:UNION、UNION
ALL、MINUS、INTERSECT、GROUP
BY、DISTINCT、UNIQUE以及聚集函数而MAX、MIN和剖析函数等。
Oracle10g的初效能GROUP BY STOPKEY,使得Oracle10g缓解了GROUP
BY操作分页效率低之题材。在10g以前,Oracle的GROUP BY操作必须完全执行完毕,才会用结果回到给用户。但是Oracle10g增加了GROUP BY
STOPKEY执行路径,使得用户以尽GROUP
BY操作时,可以因STOPKEY随时刹车正在运转的操作。这使得业内分页函数对于GROUP BY操作更表达了意向。
除开这些操作以外,分页查询还有一个格外明朗的性状,就是拍卖的页数越聊,效率就越强,越到末端,查询速度越慢。
分页查询用来增长返回速度的方法还是对准数据量较小之前N漫长记下而言。无论是搜索引围观,NESTED
LOOP连接,还是ORDER BY
STOPKEY,这些方式带来性能提升的前提都是数据量比较小,一旦分页到了最终几乎页,会发觉这些主意不仅没有主意带性能的升迁,而且性能比寻常查询还要小得多。这一点,在用分页查询的时节,一定要心里有数。
分页查询一般情况下,很少会翻至最后一篇,如果只是有时遇到这种气象,对网性能不会见出酷怪的熏陶,但是一旦经常遇上这种情形,在规划分页查询时应该给足够的考虑。
多表联合
下面简单讨论一下多表联合之情。对于极端常见的等值表连接查询,CBO一般可能会见以简单栽连接方式NESTED
LOOP和HASH JOIN(MERGE JOIN效率比HASH JOIN效率低,一般CBO不会考虑)。
一般对于大表查询情况下,HASH JOIN的效率要比较NESTED
LOOP高多,所以CBO一般默认会选择HASH JOIN.
然要分页查询的内层是这种连接查询的话,使用NESTED
LOOP可以又快的获得前N漫漫记下。
在此地,由于用了分页,因此指定了一个返回的极度充分记录数,NESTED
LOOP在回到记录数超过最要命值时可以立刻停止并以结果回到给中间层,而HASH
JOIN必须处理完毕所有结果集(MERGE
JOIN也是)。那么在大部分的景况下,对于分页查询选择NESTED
LOOP作为查询的总是方式有比高的频率(分页查询的时段绝大部分之气象是询问前几乎页的数目,越靠后面的页数访问几率领进一步小)。
HASH
JOIN中率先步也不怕是第一张表的全表扫描是无能为力以STOPKEY的,这便是NESTED
LOOP比HASH JOIN优势的地方。
但是,如果刚好第一摆放表很有点,对立即张表的咸扫描的代价不过低,会显得HASH
JOIN效率更强。
如果简单摆表底大大小小相近,或者Oracle错误的选择了优先扫描大表,则动用HASH
JOIN的效率就会没有得多。
所以对此表连接来说,在描写分页查询的下,可以考虑多FIRST_ROWS提示,它会招致CBO选择NESTED
LOOP,有助于重新快的以查询结果回到。
其实,不光是说明连接,对于有的分页查询都得添加FIRST_ROWS提示。
不过需要注意的常常,分页查询的靶子是抢的归来前N长达记下,因此,无论是ROWNUM还是FIRST_ROWS机制都是增进前几乎页的查询速度,
对于分页查询的尾声几页,采用HASH JOIN的道,执行效率几乎没有其它改变,而使NESTED
LOOP方式,则效率严重下滑,而且老远小于HASH JOIN的艺术。
散序列不唯所带来的问题
如果用来排序的列不唯一,也便是有价值相当的履行,可能会见促成第一糟当面前10久回来记录面临,某行数据出现了,而第二潮在11顶第20长长的记下被,某行数据以起了。一长条数再次出现个别不行,就势必意味着来多少以少不善查询中还无见面面世。
其实造成这个题材之由很简短,是由排序列不唯造成的。Oracle这里运用的排序算法不享安定,也就是说,对于键值相等的数码,这种算法就排序后,不包这些键值相等的多少保持排序前之各个。
解决之题目实际上为老粗略。有个别栽方式可以设想。
1)在应用不唯的字段排序时,后面与一个唯一的字段。 一般在排序字段后面与一个主键就好了,如果表不设有主键,跟ROWID也可。这种方法极其简便,且对性能的影响无与伦比小。
2)另一样种方式就是使前为来过频的BETWEEN
AND的道。
这种方式由下表数据的全排序,每次仅获得全败序中之之一平局部数据,因此不见面产出上面提到的复数据问题。
但是幸亏出于采用了都排序,而且ROWNUM信息无法推至查询内部,导致这种写法的履效率很没有
测试结果
下面做片测试,按照如下步骤准备数据:

[sql] view
plain
copy
print?图片 10图片 11

  1. CREATE TABLE T AS SELECT * FROM DBA_USERS;  
  2. CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE;  
  3. ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (USERNAME);  
  4. ALTER TABLE T1 ADD CONSTRAINT FK_T1_OWNER FOREIGN KEY (OWNER) REFERENCES T(USERNAME);  
  5. CREATE INDEX IND_T1_OWNER ON T1(OWNER);  
  6. EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T’)  
  7. EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T1’)  
  8. set autotrace traceonly  
  9. set timing on  

图片 12

CREATE TABLE T AS SELECT * FROM DBA_USERS;
CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE;
ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (USERNAME);
ALTER TABLE T1 ADD CONSTRAINT FK_T1_OWNER FOREIGN KEY (OWNER) REFERENCES T(USERNAME);
CREATE INDEX IND_T1_OWNER ON T1(OWNER);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1')
set autotrace traceonly
set timing on

今天表格T中有37推行数据,表格T1受到来623K行数据。
于格式1和格式2的查询计划

[sql] view
plain
copy
print?图片 13图片 14

  1. –查询语句子1  
  2. SELECT * FROM     
  3. (    
  4. SELECT A.*, ROWNUM RN     
  5. FROM (SELECT * FROM T1) A     
  6. )    
  7. WHERE RN BETWEEN 21 AND 40;  
  8. –查询语句子2  
  9. SELECT * FROM     
  10. (    
  11. SELECT A.*, ROWNUM RN     
  12. FROM (SELECT * FROM T1) A     
  13. WHERE ROWNUM <= 40    
  14. )    
  15. WHERE RN >= 21;  

图片 15

--查询语句1
SELECT * FROM   
(  
SELECT A.*, ROWNUM RN   
FROM (SELECT * FROM T1) A   
)  
WHERE RN BETWEEN 21 AND 40;
--查询语句2
SELECT * FROM   
(  
SELECT A.*, ROWNUM RN   
FROM (SELECT * FROM T1) A   
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21;

 

  执行计划 执行时间 统计信息
查询语句1

———————————————————- Plan hash value: 3921461035

—————————————————————————- | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | —————————————————————————- |   0 | SELECT STATEMENT    |      |   623K|  1231M|  2879   (1)| 00:00:35 | |*  1 |  VIEW               |      |   623K|  1231M|  2879   (1)| 00:00:35 | |   2 |   COUNT             |      |       |       |            |          | |   3 |    TABLE ACCESS FULL| T1   |   623K|    59M|  2879   (1)| 00:00:35 | —————————————————————————-

Predicate Information (identified by operation id): —————————————————

   1 – filter("RN"<=40 AND "RN">=21)

00: 00: 02.40 1  recursive calls 0  db block gets 10441  consistent gets 10435  physical reads 0  redo size 1720  bytes sent via SQL*Net to client 431  bytes received via SQL*Net from client 3  SQL*Net roundtrips to/from client 0  sorts (memory) 0  sorts (disk) 20  rows processed
查询语句2

—————————————————————————- | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | —————————————————————————- |   0 | SELECT STATEMENT    |      |    40 | 82800 |     2   (0)| 00:00:01 | |*  1 |  VIEW               |      |    40 | 82800 |     2   (0)| 00:00:01 | |*  2 |   COUNT STOPKEY     |      |       |       |            |          | |   3 |    TABLE ACCESS FULL| T1   |    40 |  4000 |     2   (0)| 00:00:01 | —————————————————————————-

Predicate Information (identified by operation id): —————————————————

   1 – filter("RN">=21)    2 – filter(ROWNUM<=40)

00: 00: 00.03 0  recursive calls 0  db block gets 6  consistent gets 20  physical reads 0  redo size 1720  bytes sent via SQL*Net to client 431  bytes received via SQL*Net from client 3  SQL*Net roundtrips to/from client 0  sorts (memory) 0  sorts (disk) 20  rows processed

干查询

[sql] view
plain
copy
print?图片 16图片 17

  1. –查询语词1  
  2. SELECT * FROM     
  3. (    
  4. SELECT A.*, ROWNUM RN     
  5. FROM (SELECT * FROM T, T1 WHERE T.USERNAME = T1.OWNER) A     
  6. WHERE ROWNUM <= 40    
  7. )    
  8. WHERE RN >= 21;  
  9. –查询语句2  
  10. SELECT /*+ FIRST_ROWS */ * FROM     
  11. (    
  12. SELECT A.*, ROWNUM RN     
  13. FROM (SELECT * FROM T, T1 WHERE T.USERNAME = T1.OWNER) A     
  14. WHERE ROWNUM <= 40    
  15. )    
  16. WHERE RN >= 21;  
  17. –或者  
  18. SELECT * FROM     
  19. (    
  20. SELECT A.*, ROWNUM RN     
  21. FROM (SELECT /*+ USE_NL(T T1) */ * FROM T, T1 WHERE T.USERNAME = T1.OWNER) A     
  22. WHERE ROWNUM <= 40    
  23. )    
  24. WHERE RN >= 21;  

图片 18

--查询语句1
SELECT * FROM   
(  
SELECT A.*, ROWNUM RN   
FROM (SELECT * FROM T, T1 WHERE T.USERNAME = T1.OWNER) A   
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21;
--查询语句2
SELECT /*+ FIRST_ROWS */ * FROM   
(  
SELECT A.*, ROWNUM RN   
FROM (SELECT * FROM T, T1 WHERE T.USERNAME = T1.OWNER) A   
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21;
--或者
SELECT * FROM   
(  
SELECT A.*, ROWNUM RN   
FROM (SELECT /*+ USE_NL(T T1) */ * FROM T, T1 WHERE T.USERNAME = T1.OWNER) A   
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21;

足见见默认是行使hash join,改用nested loop
join方式如同效率并没有明显提高,但是这是出于表T比较小但出34执行,所以hash
join的率先步就是对T进行全表扫描而望洋兴叹以stopkey,效率呢要命高。

  执行计划 执行时间 统计信息
查询语句1

—————————————————————————– | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | —————————————————————————– |   0 | SELECT STATEMENT     |      |    40 |   165K|     6  (17)| 00:00:01 | |*  1 |  VIEW                |      |    40 |   165K|     6  (17)| 00:00:01 | |*  2 |   COUNT STOPKEY      |      |       |       |            |          | |*  3 |    HASH JOIN         |      |    40 | 12400 |     6  (17)| 00:00:01 | |   4 |     TABLE ACCESS FULL| T    |    34 |  3740 |     3   (0)| 00:00:01 | |   5 |     TABLE ACCESS FULL| T1   |    40 |  4000 |     2   (0)| 00:00:01 | —————————————————————————–

Predicate Information (identified by operation id): —————————————————

   1 – filter("RN">=21)    2 – filter(ROWNUM<=40)    3 – access("T"."USERNAME"="T1"."OWNER")

00: 00: 00.04 0 recursive calls 0 db block gets 9 consistent gets 20 physical reads 0 redo size 2927 bytes sent via SQL*Net to client 431 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed
查询语句2

———————————————————————————————– | Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     | ———————————————————————————————– |   0 | SELECT STATEMENT               |              |    40 |   165K| 13627   (1)| 00:02:44 | |*  1 |  VIEW                          |              |    40 |   165K| 13627   (1)| 00:02:44 | |*  2 |   COUNT STOPKEY                |              |       |       |            |          | |   3 |    NESTED LOOPS                |              |       |       |            |          | |   4 |     NESTED LOOPS               |              |   623K|   124M| 13627   (1)| 00:02:44 | |   5 |      TABLE ACCESS FULL         | T            |    34 |  3740 |     3   (0)| 00:00:01 | |*  6 |      INDEX RANGE SCAN          | IND_T1_OWNER | 36684 |       |    91   (0)| 00:00:02 | |   7 |     TABLE ACCESS BY INDEX ROWID| T1           | 18342 |  1791K|   710   (1)| 00:00:09 | ———————————————————————————————–

Predicate Information (identified by operation id): —————————————————

   1 – filter("RN">=21)    2 – filter(ROWNUM<=40)    6 – access("T"."USERNAME"="T1"."OWNER")

00: 00: 00.01 1 recursive calls 0 db block gets 14 consistent gets 0 physical reads 0 redo size 2927 bytes sent via SQL*Net to client 431 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed

现行增大表T,

[sql] view
plain
copy
print?图片 19图片 20

  1. ALTER TABLE T MODIFY(USER_ID NULL, ACCOUNT_STATUS NULL, DEFAULT_TABLESPACE NULL,  
  2. TEMPORARY_TABLESPACE NULL, CREATED NULL, PROFILE NULL);  
  3. INSERT INTO T(USERNAME) SELECT (‘USER’ || LEVEL) FROM DUAL CONNECT BY LEVEL < 100000;  
  4. COMMIT;  
  5. EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T’)  

图片 21

ALTER TABLE T MODIFY(USER_ID NULL, ACCOUNT_STATUS NULL, DEFAULT_TABLESPACE NULL,
TEMPORARY_TABLESPACE NULL, CREATED NULL, PROFILE NULL);
INSERT INTO T(USERNAME) SELECT ('USER' || LEVEL) FROM DUAL CONNECT BY LEVEL < 100000;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

然后再度测试语句1,会发觉本oracle已经改变成为用nested loop join了。
因此现在语句1和语句2的功力相当跟了。可以动用 USE_HASH(T T1)
HINT强制行使hash join,结果召开下对比,会发现hash join的效率低于nested
loop join,读数据来的IO(consistent gets+physical reads)大大增加了.
可以见见CBO是一定智能了。
包含排序的查询
含排序操作的分页查询。可以概括的以查询分为两种植不同情形,第一种消序列就是找引列,这种可采取索引读取,第二种植消序列没有索引。
第一种情形又足以细分为:完全摸引围观以及透过查找引围观定位及说明记录点滴种情景。无论是那种情景,都可通过索引的咸扫描来避免排序的出。
第二种状况下,排序不可避免,但是用吃出分页格式,Oracle不会见针对负有数据开展排序,而是只排序前N漫长记下。

[sql] view
plain
copy
print?图片 22图片 23

  1. –查询语句1,排序列就是索引列.注意这里要加上OWNER IS NOT NULL,否则由OWNER列不是NOT NULL,会造成索引无法运用。  
  2. SELECT * FROM  
  3. (  
  4. SELECT A.*, ROWNUM RN  
  5. FROM (SELECT * FROM T1 WHERE OWNER IS NOT NULL ORDER BY OWNER) A  
  6. WHERE ROWNUM <= 40  
  7. )  
  8. WHERE RN >= 21;  
  9.   
  10. –查询语句2,排序列没有索引  
  11. SELECT * FROM  
  12. (  
  13. SELECT A.*, ROWNUM RN  
  14. FROM (SELECT * FROM T1 ORDER BY NAME) A  
  15. WHERE ROWNUM <= 40  
  16. )  
  17. WHERE RN >= 21;  
  18.   
  19.   
  20. –查询语句3,排序列没有索引  
  21. SELECT * FROM  
  22. (  
  23. SELECT A.*, ROWNUM RN  
  24. FROM (SELECT * FROM T1 ORDER BY NAME) A  
  25. )  
  26. WHERE RN BETWEEN 21 AND 40;  

图片 24

--查询语句1,排序列就是索引列.注意这里需要加上OWNER IS NOT NULL,否则由于OWNER列不是NOT NULL,会导致索引无法使用。
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T1 WHERE OWNER IS NOT NULL ORDER BY OWNER) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21;

--查询语句2,排序列没有索引
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T1 ORDER BY NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21;


--查询语句3,排序列没有索引
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T1 ORDER BY NAME) A
)
WHERE RN BETWEEN 21 AND 40;

 

  执行计划 执行时间 统计信息
查询语句1

———————————————————————————————– | Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     | ———————————————————————————————– |   0 | SELECT STATEMENT               |              |    40 | 82800 |     4   (0)| 00:00:01 | |*  1 |  VIEW                          |              |    40 | 82800 |     4   (0)| 00:00:01 | |*  2 |   COUNT STOPKEY                |              |       |       |            |          | |   3 |    VIEW                        |              |    40 | 82280 |     4   (0)| 00:00:01 | |   4 |     TABLE ACCESS BY INDEX ROWID| T1           |   646K|    62M|     4   (0)| 00:00:01 | |*  5 |      INDEX FULL SCAN           | IND_T1_OWNER |    40 |       |     3   (0)| 00:00:01 | ———————————————————————————————–

Predicate Information (identified by operation id): —————————————————

   1 – filter("RN">=21)    2 – filter(ROWNUM<=40)    5 – filter("OWNER" IS NOT NULL)

*排序列就是索引列,可以看到通过索引的全扫描来避免了排序的产生。

00: 00: 00.01 1 recursive calls 0 db block gets 8 consistent gets 1 physical reads 0 redo size 1682 bytes sent via SQL*Net to client 427 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed
查询语句2

—————————————————————————————– | Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | —————————————————————————————– |   0 | SELECT STATEMENT         |      |    40 | 82800 |       | 18077   (1)| 00:03:37 | |*  1 |  VIEW                    |      |    40 | 82800 |       | 18077   (1)| 00:03:37 | |*  2 |   COUNT STOPKEY          |      |       |       |       |            |          | |   3 |    VIEW                  |      |   646K|  1268M|       | 18077   (1)| 00:03:37 | |*  4 |     SORT ORDER BY STOPKEY|      |   646K|    62M|    72M| 18077   (1)| 00:03:37 | |   5 |      TABLE ACCESS FULL   | T1   |   646K|    62M|       |  3023   (1)| 00:00:37 | —————————————————————————————–

Predicate Information (identified by operation id): —————————————————

   1 – filter("RN">=21)    2 – filter(ROWNUM<=40)    4 – filter(ROWNUM<=40)

*排序列没有索引,排序不可避免。带STOPKEY的ORDER BY,排序操作放到了内存中, 在大数据量需要排序的情况下,要比不带STOPKEY排序的效率高得多。

00: 00: 01.32 1 recursive calls 0 db block gets 10973 consistent gets 10969 physical reads 0 redo size 2529 bytes sent via SQL*Net to client 427 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 20 rows processed
查询语句3

————————————————————————————– | Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | ————————————————————————————– |   0 | SELECT STATEMENT      |      |   646K|  1276M|       | 18077   (1)| 00:03:37 | |*  1 |  VIEW                 |      |   646K|  1276M|       | 18077   (1)| 00:03:37 | |   2 |   COUNT               |      |       |       |       |            |          | |   3 |    VIEW               |      |   646K|  1268M|       | 18077   (1)| 00:03:37 | |   4 |     SORT ORDER BY     |      |   646K|    62M|    72M| 18077   (1)| 00:03:37 | |   5 |      TABLE ACCESS FULL| T1   |   646K|    62M|       |  3023   (1)| 00:00:37 | ————————————————————————————–

Predicate Information (identified by operation id): —————————————————

   1 – filter("RN"<=40 AND "RN">=21)

*排序列没有索引,排序不可避免,不带STOPKEY, 进行的数据的全排序,排序数据量大,排序操作不得不在磁盘上完成,因此耗时比较多。

00: 00: 05.31 72 recursive calls 26 db block gets 10973 consistent gets 19933 physical reads 0 redo size 6489 bytes sent via SQL*Net to client 427 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 20 rows processed

消除序列不唯所带动的题目

[sql] view
plain
copy
print?图片 25图片 26

  1. tony@ORCL1> CREATE TABLE TEST AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;  
  2. Table created.  
  3.   
  4. tony@ORCL1> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘TEST’);  
  5. PL/SQL procedure successfully completed.  
  6.   
  7. tony@ORCL1> COLUMN OBJECT_NAME FORMAT A30  
  8. tony@ORCL1> SELECT * FROM  
  9.   2  (  
  10.   3  SELECT A.*, ROWNUM RN  
  11.   4  FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER) A  
  12.   5  WHERE ROWNUM <= 10  
  13.   6  )  
  14.   7  WHERE RN >= 1;  
  15.   
  16.         ID OWNER                          OBJECT_NAME                            RN  
  17. ———- —————————— —————————— ———-  
  18.      69170 APEX_030200                    WWV_FLOW_INIT_HTP_BUFFER                1  
  19.      69179 APEX_030200                    WWV_HTF                                 2  
  20.      69178 APEX_030200                    WWV_FLOW_LANG                           3  
  21.      69177 APEX_030200                    WWV_FLOW_UTILITIES                      4  
  22.      69176 APEX_030200                    VC4000ARRAY                             5  
  23.      69175 APEX_030200                    WWV_FLOW_SECURITY                       6  
  24.      69174 APEX_030200                    WWV_FLOW                                7  
  25.      69173 APEX_030200                    HTMLDB_ITEM                             8  
  26.      69172 APEX_030200                    WWV_FLOW_GLOBAL                         9  
  27.      69171 APEX_030200                    WWV_FLOW_IMAGE_PREFIX                  10  
  28.   
  29. 10 rows selected.  
  30.   
  31. tony@ORCL1> SELECT * FROM  
  32.   2  (  
  33.   3  SELECT A.*, ROWNUM RN  
  34.   4  FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER) A  
  35.   5  WHERE ROWNUM <= 20  
  36.   6  )  
  37.   7  WHERE RN >= 11;  
  38.   
  39.         ID OWNER                          OBJECT_NAME                            RN  
  40. ———- —————————— —————————— ———-  
  41.      69180 APEX_030200                    WWV_HTP                                11  
  42.      69179 APEX_030200                    WWV_HTF                                12  
  43.      69178 APEX_030200                    WWV_FLOW_LANG                          13  
  44.      69177 APEX_030200                    WWV_FLOW_UTILITIES                     14  
  45.      69176 APEX_030200                    VC4000ARRAY                            15  
  46.      69175 APEX_030200                    WWV_FLOW_SECURITY                      16  
  47.      69174 APEX_030200                    WWV_FLOW                               17  
  48.      69173 APEX_030200                    HTMLDB_ITEM                            18  
  49.      69172 APEX_030200                    WWV_FLOW_GLOBAL                        19  
  50.      69171 APEX_030200                    WWV_FLOW_IMAGE_PREFIX                  20  
  51.   
  52. 10 rows selected.  
  53. –可以看,有差不多只ID在少数不好询问中还冒出了。  
  54. –通过添加ID作为消除序列解决这个题材。  
  55.   
  56. tony@ORCL1> SELECT * FROM  
  57.   2  (  
  58.   3  SELECT A.*, ROWNUM RN  
  59.   4  FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER, ID) A  
  60.   5  WHERE ROWNUM <= 10  
  61.   6  )  
  62.   7  WHERE RN >= 1;  
  63.   
  64.         ID OWNER                          OBJECT_NAME                            RN  
  65. ———- —————————— —————————— ———-  
  66.      69170 APEX_030200                    WWV_FLOW_INIT_HTP_BUFFER                1  
  67.      69171 APEX_030200                    WWV_FLOW_IMAGE_PREFIX                   2  
  68.      69172 APEX_030200                    WWV_FLOW_GLOBAL                         3  
  69.      69173 APEX_030200                    HTMLDB_ITEM                             4  
  70.      69174 APEX_030200                    WWV_FLOW                                5  
  71.      69175 APEX_030200                    WWV_FLOW_SECURITY                       6  
  72.      69176 APEX_030200                    VC4000ARRAY                             7  
  73.      69177 APEX_030200                    WWV_FLOW_UTILITIES                      8  
  74.      69178 APEX_030200                    WWV_FLOW_LANG                           9  
  75.      69179 APEX_030200                    WWV_HTF                                10  
  76.   
  77. 10 rows selected.  
  78.   
  79. tony@ORCL1> SELECT * FROM  
  80.   2  (  
  81.   3  SELECT A.*, ROWNUM RN  
  82.   4  FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER, ID) A  
  83.   5  WHERE ROWNUM <= 20  
  84.   6  )  
  85.   7  WHERE RN >= 11;  
  86.   
  87.         ID OWNER                          OBJECT_NAME                            RN  
  88. ———- —————————— —————————— ———-  
  89.      69180 APEX_030200                    WWV_HTP                                11  
  90.      69181 APEX_030200                    ESCAPE_SC                              12  
  91.      69182 APEX_030200                    WWV_FLOW_META_DATA                     13  
  92.      69183 APEX_030200                    WWV_FLOW_TEMPLATES_UTIL                14  
  93.      69184 APEX_030200                    WWV_RENDER_CALENDAR2                   15  
  94.      69185 APEX_030200                    WWV_RENDER_CHART2                      16  
  95.      69186 APEX_030200                    WWV_FLOW_CHECK                         17  
  96.      69187 APEX_030200                    WWV_RENDER_REPORT3                     18  
  97.      69188 APEX_030200                    WWV_FLOW_PAGE_CACHE_API                19  
  98.      69189 APEX_030200                    WWV_FLOW_RENDER_QUERY                  20  
  99.   
  100. 10 rows selected.  

图片 27

tony@ORCL1> CREATE TABLE TEST AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
Table created.

tony@ORCL1> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST');
PL/SQL procedure successfully completed.

tony@ORCL1> COLUMN OBJECT_NAME FORMAT A30
tony@ORCL1> SELECT * FROM
  2  (
  3  SELECT A.*, ROWNUM RN
  4  FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER) A
  5  WHERE ROWNUM <= 10
  6  )
  7  WHERE RN >= 1;

        ID OWNER                          OBJECT_NAME                            RN
---------- ------------------------------ ------------------------------ ----------
     69170 APEX_030200                    WWV_FLOW_INIT_HTP_BUFFER                1
     69179 APEX_030200                    WWV_HTF                                 2
     69178 APEX_030200                    WWV_FLOW_LANG                           3
     69177 APEX_030200                    WWV_FLOW_UTILITIES                      4
     69176 APEX_030200                    VC4000ARRAY                             5
     69175 APEX_030200                    WWV_FLOW_SECURITY                       6
     69174 APEX_030200                    WWV_FLOW                                7
     69173 APEX_030200                    HTMLDB_ITEM                             8
     69172 APEX_030200                    WWV_FLOW_GLOBAL                         9
     69171 APEX_030200                    WWV_FLOW_IMAGE_PREFIX                  10

10 rows selected.

tony@ORCL1> SELECT * FROM
  2  (
  3  SELECT A.*, ROWNUM RN
  4  FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER) A
  5  WHERE ROWNUM <= 20
  6  )
  7  WHERE RN >= 11;

        ID OWNER                          OBJECT_NAME                            RN
---------- ------------------------------ ------------------------------ ----------
     69180 APEX_030200                    WWV_HTP                                11
     69179 APEX_030200                    WWV_HTF                                12
     69178 APEX_030200                    WWV_FLOW_LANG                          13
     69177 APEX_030200                    WWV_FLOW_UTILITIES                     14
     69176 APEX_030200                    VC4000ARRAY                            15
     69175 APEX_030200                    WWV_FLOW_SECURITY                      16
     69174 APEX_030200                    WWV_FLOW                               17
     69173 APEX_030200                    HTMLDB_ITEM                            18
     69172 APEX_030200                    WWV_FLOW_GLOBAL                        19
     69171 APEX_030200                    WWV_FLOW_IMAGE_PREFIX                  20

10 rows selected.
--可以看到,有多个ID在两次查询中都出现了。
--通过加上ID作为排序列解决这个问题。

tony@ORCL1> SELECT * FROM
  2  (
  3  SELECT A.*, ROWNUM RN
  4  FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER, ID) A
  5  WHERE ROWNUM <= 10
  6  )
  7  WHERE RN >= 1;

        ID OWNER                          OBJECT_NAME                            RN
---------- ------------------------------ ------------------------------ ----------
     69170 APEX_030200                    WWV_FLOW_INIT_HTP_BUFFER                1
     69171 APEX_030200                    WWV_FLOW_IMAGE_PREFIX                   2
     69172 APEX_030200                    WWV_FLOW_GLOBAL                         3
     69173 APEX_030200                    HTMLDB_ITEM                             4
     69174 APEX_030200                    WWV_FLOW                                5
     69175 APEX_030200                    WWV_FLOW_SECURITY                       6
     69176 APEX_030200                    VC4000ARRAY                             7
     69177 APEX_030200                    WWV_FLOW_UTILITIES                      8
     69178 APEX_030200                    WWV_FLOW_LANG                           9
     69179 APEX_030200                    WWV_HTF                                10

10 rows selected.

tony@ORCL1> SELECT * FROM
  2  (
  3  SELECT A.*, ROWNUM RN
  4  FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER, ID) A
  5  WHERE ROWNUM <= 20
  6  )
  7  WHERE RN >= 11;

        ID OWNER                          OBJECT_NAME                            RN
---------- ------------------------------ ------------------------------ ----------
     69180 APEX_030200                    WWV_HTP                                11
     69181 APEX_030200                    ESCAPE_SC                              12
     69182 APEX_030200                    WWV_FLOW_META_DATA                     13
     69183 APEX_030200                    WWV_FLOW_TEMPLATES_UTIL                14
     69184 APEX_030200                    WWV_RENDER_CALENDAR2                   15
     69185 APEX_030200                    WWV_RENDER_CHART2                      16
     69186 APEX_030200                    WWV_FLOW_CHECK                         17
     69187 APEX_030200                    WWV_RENDER_REPORT3                     18
     69188 APEX_030200                    WWV_FLOW_PAGE_CACHE_API                19
     69189 APEX_030200                    WWV_FLOW_RENDER_QUERY                  20

10 rows selected.

 

 

相关文章