ORACLE-SQL性能优化

   作者:张小飞 iteye账号:z_xiaofei168

SQL语句优化是提高性能的重要环节

n  开发人员不能只注重功能的实现，不管性能如何

n  开发人员不能把Oracle当成一个黑盒子，必须了解其结构、处理SQL和数据的方法

n  必需遵守既定的开发规范

访问数据表的方式

①全表扫描

全表扫描就是顺序地访问表中每条记录。Oracle采用一次读入多个数据块(database block)的方式优化全表扫描。

②通过ROWID访问表

ROWID包含了表中记录的物理位置信息。可以采用基于ROWID的访问方式情况提高访问表的效率。Oracle采用索引实现了数据和存放数据的物理位置(ROWID)之间的联系通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能的提高。

SQL Tunning 的重点

n  SQL: insert, update, delete, select;

n  主要关注的是select

n  关注的是：如何用最小的硬件资源消耗、最少的响应时间定位数据位置

SQL Tunning Tips

sql 语句的编写原则和优化

随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于大量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。

sql 语句的编写原则和优化

n  在编写SQL语句时我们应清楚优化器根据何种原则来使用索引，这有助于写出高性能的SQL语句。

n  SQL语句的编写原则和SQL语句的优化，请跟我一起学习以下几方面：

1.不要让Oracle做得太多；

2.给优化器更明确的命令；

3.减少访问次数；

4.细节上的影响；

一、不要让Oracle做得太多

1、避免复杂的多表关联，如：

select …from user_files uf, df_money_files dm, cw_charge_record cc

where uf.user_no = dm.user_no

and dm.user_no = cc.user_no

and ……

and not exists(select …)

2、避免使用“*”

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘*’是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将‘*’依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意

味着将耗费更多的时间；

3、避免使用耗费资源的操作

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.例如,一个UNION查询,其中每个查询都带有GROUP BY子句, GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个查询需要执行一次排序, 然后在执行UNION时, 又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深度会大大影响查询的效率.通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写.

4、用EXISTS替换DISTINCT，例如:

低效:

    SELECT DISTINCT DEPT_NO,DEPT_NAME

    FROM DEPT D,EMP E

    WHERE D.DEPT_NO = E.DEPT_NO

高效:

    SELECT DEPT_NO,DEPT_NAME

    FROM DEPT D

    WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

用UNION-ALL 替换UNION ( if possible)

当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.

低效：

　　 SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

        UNION

        SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

高效:

        SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

        UNION ALL

        SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

二、给优化器更明确的命令

                   1、避免在索引列上使用计算函数

         WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．举例:

低效：

SELECT … FROM DEPT WHERE SAL * 12 > 25000;

高效:

SELECT … FROM DEPT WHERE SAL > 25000/12;

2、避免使用前置通配符

WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用.

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO LIKE '%109204421';

                 在这种情况下，ORACLE将使用全表扫描.

         在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：

select * from employee where last_name like 'c%';

                   3、避免在索引列上使用NOT

通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE遇到NOT,他就会停止使用索引转而执行全表扫描.举例:

低效: (这里,不使用索引)

   SELECT … FROM DEPT WHERE DEPT_CODE NOT = 0;

高效: (这里,使用了索引)

   SELECT … FROM DEPT WHERE DEPT_CODE > 0;

                   4、避免在索引列上使用 IS NULL和IS NOT NULL

         避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中．如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

三、减少访问次数

         当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.类比，工程实施。

四、细节上的影响

         1、WHERE子句中的连接顺序

           ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理, 当在WHERE子句中有多个表联接时，WHERE子句中排在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在WHERE子句中的最后。

n  如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：

select * from emp e,dept d where d.deptno >10 and e.deptno =30 ;

n  如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。

select * from emp e,dept d where e.deptno =30 and d.deptno >10 ;

2、WHERE子句 ——函数、表达式使用

最好不要在WHERE子句中使用函或表达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引

3、Order by语句

n  ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

n  仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

4、用>= 替代 >

如果DEPTNO上有一个索引。

         高效:

          SELECT * ROM EMP HERE DEPTNO >=4

         低效:

        SELECT * ROM EMP HERE DEPTNO >3

5、通过使用>=、<=等，避免使用NOT命令

例子：select * from employee where salary <> 3000;

对这个查询，可以改写为不使用NOT：

select * from employee where salary<3000 or salary>3000;

         虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。

 

评论

13 楼 bitray 2013-08-05  

你说是把一些处理放在数据库端好,还是放在程序中处理好呢

12 楼 留下的祝福 2013-08-05  

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

Oracle分页查询语句（一）

分页查询格式：
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。
上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。
选择第21 到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40
对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。
这是由于CBO 优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。
而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对 于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率 要比第一个查询低得多。
上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。
这里就不对包含排序的查询进行说明了，下一篇文章会通过例子来详细说明。下面简单讨论一下多表联合的情况。对于最常见的等值表连接查询，CBO 一般可能会采用两种连接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）。
因此，如果不介意在系统中使用HINT的话，可以将分页的查询语句改写为：
SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

Oracle分页查询语句（二）

这篇文章用几个例子来说明分页查询的效率。首先构造一个比较大的表作为测试表：
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS, DBA_SEQUENCES;
表已创建。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
457992
首先比较两种分页方法的区别：
SQL> SET AUTOT ON
SQL> COL OBJECT_NAME FORMAT A30
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL 过程已成功完成。
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T
8 )
9 )
10 WHERE RN BETWEEN 11 AND 20;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
5807 ALL_APPLY_PROGRESS
1769 ALL_ARGUMENTS
2085 ALL_ASSOCIATIONS
4997 ALL_AUDIT_POLICIES
4005 ALL_BASE_TABLE_MVIEWS
5753 ALL_CAPTURE
5757 ALL_CAPTURE_PARAMETERS
5761 ALL_CAPTURE_PREPARED_DATABASE
5765 ALL_CAPTURE_PREPARED_SCHEMAS
5769 ALL_CAPTURE_PREPARED_TABLES
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=457992 Bytes=42135264)
1 0 VIEW (Cost=864 Card=457992 Bytes=42135264)
2 1 COUNT
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8979 consistent gets
7422 physical reads
0 redo size
758 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
5807 ALL_APPLY_PROGRESS
1769 ALL_ARGUMENTS
2085 ALL_ASSOCIATIONS
4997 ALL_AUDIT_POLICIES
4005 ALL_BASE_TABLE_MVIEWS
5753 ALL_CAPTURE
5757 ALL_CAPTURE_PARAMETERS
5761 ALL_CAPTURE_PREPARED_DATABASE
5765 ALL_CAPTURE_PREPARED_SCHEMAS
5769 ALL_CAPTURE_PREPARED_TABLES
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=20 Bytes=1840)
1 0 VIEW (Cost=864 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
758 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
二者执行效率相差很大，一个需要8000 多逻辑读，而另一个只需要5个逻辑读。观察二者的执行计划可以发现，两个执行计划唯一的区别就是第二个查询在COUNT这步使用了STOPKEY，也就是 说，Oracle将ROWNUM <= 20推入到查询内层，当符合查询的条件的记录达到STOPKEY的值，则Oracle结束查询。
因此，可以预见，采用第二种方式，在翻页的开始部分查询速度很快，越到后面，效率越低，当翻到最后一页，效率应该和第一种方式接近。
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T
8 )
9 WHERE ROWNUM <= 457990
10 )
11 WHERE RN >= 457980;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
7128 XCF_I_HANDLE_STATUS
7126 XCF_P
7127 XCF_U1
7142 XDF
7145 XDF_I_DF_KEY
7146 XDF_I_HANDLE_STATUS
7143 XDF_P
7144 XDF_U1
TEST.YANGTINGKUN
TEST4.YANGTINGKUN
YANGTK.YANGTINGKUN
已选择11行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=457990 Bytes=42135080)
1 0 VIEW (Cost=864 Card=457990 Bytes=42135080)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8979 consistent gets
7423 physical reads
0 redo size
680 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed

Oracle分页查询语句（三）

继续看查询的第二种情况，包含表连接的情况：
SQL> CREATE TABLE T AS SELECT * FROM DBA_USERS;
表已创建。
SQL> CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE;
表已创建。
SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (USERNAME);
表已更改。
SQL> ALTER TABLE T1 ADD CONSTRAINT FK_T1_OWNER FOREIGN KEY (OWNER)
2 REFERENCES T(USERNAME);
表已更改。
SQL> CREATE INDEX IND_T1_OWNER ON T1(NAME);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL 过程已成功完成。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1')
PL/SQL 过程已成功完成。
创建了T表和T1表，默认情况下，HASH JOIN的效率要比NESTED LOOP高很多：
SQL> SET AUTOT TRACE
SQL> SELECT * FROM T, T1 WHERE T.USERNAME = T1.OWNER;
已选择96985行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=844 Card=96985 Bytes=46164860)
1 0 HASH JOIN (Cost=844 Card=96985 Bytes=46164860)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=1044)
3 1 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=37727165)
Statistics
----------------------------------------------------------
39 recursive calls
0 db block gets
14475 consistent gets
7279 physical reads
0 redo size
37565579 bytes sent via SQL*Net to client
71618 bytes received via SQL*Net from client
6467 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96985 rows processed
SQL> SELECT /*+ FIRST_ROWS */ * FROM T, T1 WHERE T.USERNAME = T1.OWNER;
已选择96985行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96985 Bytes=46164860)
1 0 NESTED LOOPS (Cost=97811 Card=96985 Bytes=46164860)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=37727165)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=87)
4 3 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
117917 consistent gets
7268 physical reads
0 redo size
37565579 bytes sent via SQL*Net to client
71618 bytes received via SQL*Net from client
6467 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96985 rows processed
但是如果分页查询的内层是这种连接查询的话，使用NESTED LOOP可以更快的得到前N条记录。
下面看一下这种情况下的分页查询情况：
SQL> SELECT USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=20 Bytes=1200)
1 0 VIEW (Cost=830 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 HASH JOIN (Cost=830 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=132)
5 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
7 physical reads
0 redo size
574 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT /*+ FIRST_ROWS */ USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=20 Bytes=1200)
1 0 VIEW (Cost=97811 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
574 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
看上去似乎HASH JOIN效率更高，难道上面说错了。
其实这个现象是由于这个例子的特殊性造成的。T 表是根据DBA_USERS创建，这张表很小。HASH JOIN中第一步也就是第一张表的全表扫描是无法应用STOPKEY的，这就是上面提到的NESTED LOOP比HASH JOIN优势的地方。但是，这个例子中，恰好第一张表很小，对这张表的全扫描的代价极低，因此，显得HASH JOIN效率更高。但是，这不具备共性，如果两张表的大小相近，或者Oracle错误的选择了先扫描大表，则使用HASH JOIN的效率就会低得多。
SQL> SELECT USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT /*+ ORDERED */ T.USER_ID, T.USERNAME, T1.NAME
8 FROM T1, T
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=951 Card=20 Bytes=1200)
1 0 VIEW (Cost=951 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 HASH JOIN (Cost=951 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=132)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8585 consistent gets
7310 physical reads
0 redo size
601 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
通过HINT提示，让Oracle先扫描大表，这回结果就很明显了。NESTED LOOP的效果要比HASH JOIN好得多。
下面，继续比较一下两个分页操作的写法，为了使结果更具有代表性，这里都采用了FIRST_ROWS提示，让Oracle采用NESTED LOOP的方式来进行表连接：
SQL> SELECT /*+ FIRST_ROWS */ USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=20 Bytes=1200)
1 0 VIEW (Cost=97811 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
574 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT /*+ FIRST_ROWS */ USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 )
12 WHERE RN BETWEEN 11 AND 20;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96985 Bytes=5819100)
1 0 VIEW (Cost=97811 Card=96985 Bytes=5819100)
2 1 COUNT
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
105571 consistent gets
7299 physical reads
0 redo size
574 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
两种写法的效率差别极大。关键仍然是是否能将STOPKEY应用到最内层查询中。
对于表连接来说，在写分页查询的时候，可以考虑增加FIRST_ROWS提示，它有助于更快的将查询结果返回。
其实，不光是表连接，对于所有的分页查询都可以加上FIRST_ROWS 提示。不过需要注意的时，分页查询的目标是尽快的返回前N条记录，因此，无论是ROWNUM还是FIRST_ROWS机制都是提高前几页的查询速度，对于 分页查询的最后几页，采用这些机制不但无法提高查询速度，反而会明显降低查询效率，对于这一点使用者应该做到心中有数。

Oracle分页查询语句（四）

最后的例子说明内部循环包含排序的情况：
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
表已创建。
SQL> CREATE INDEX IND_T_OBJECT_NAME ON T (OBJECT_NAME);
索引已创建。
SQL> ALTER TABLE T MODIFY OBJECT_NAME NOT NULL;
表已更改。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL 过程已成功完成。
下面进行测试包含排序操作的分页查询。可以简单的将查询分为两种不同情况，第一种排序列就是索引列，这种可以利用索引读取，第二种排序列没有索引。
第一种情况又可以细分为：完全索引扫描和通过索引扫描定位到表记录两种情况。
无论是那种情况，都可以通过索引的全扫描来避免排序的产生。看下面的例子：
SQL> SET AUTOT TRACE
SQL> SELECT OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_NAME FROM T ORDER BY OBJECT_NAME
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=20 Bytes=1580)
1 0 VIEW (Cost=26 Card=20 Bytes=1580)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=26 Card=6361 Bytes=419826)
4 3 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361 Bytes=108137)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
576 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
这种情况下，通过索引可以完全得到查询的结果，因此可以避免表扫描的产生，而且，由于索引已经是排序过的，因此通过索引的全扫描，连排序操作都省略了。
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=20 Bytes=1840)
1 0 VIEW (Cost=43 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=43 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY STOPKEY) (Cost=43 Card=6361 Bytes=133581)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
673 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
由于不能仅仅通过索引扫描得到查询结果，这里Oracle选择了表扫描。这是由于初始化参数设置决定的。因此，建议在分页的时候使用FIRST_ROWS提示。
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=20 Bytes=1840)
1 0 VIEW (Cost=826 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=826 Card=6361 Bytes=502519)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)
5 4 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
673 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
使用了FIRST_ROWS提示后，Oracle不需要扫描全表，而且避免了排序操作。
下面讨论最后一种情况，排序列不是索引列。这个时候排序不可避免，但是利用给出分页格式，Oracle不会对所有数据进行排序，而是只排序前N条记录。
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
8 )
9 )
10 WHERE RN BETWEEN 11 AND 20;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=6361 Bytes=585212)
1 0 VIEW (Cost=64 Card=6361 Bytes=585212)
2 1 COUNT
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=260801)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
690 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=20 Bytes=1840)
1 0 VIEW (Cost=64 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY STOPKEY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=260801)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
690 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
观察两种不同写法的ORDER BY步骤，一个是带STOPKEY的ORDER BY，另一个不带。在大数据量需要排序的情况下，带STOPKEY的效率要比不带STOPKEY排序的效率高得多。
SQL> INSERT INTO T SELECT T.* FROM T, USER_OBJECTS;
已创建407104行。
SQL> COMMIT;
提交完成。
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME FROM
5 (
6 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
7 )
8 WHERE ROWNUM <= 20
9 )
10 WHERE RN >= 11;
已选择10行。
已用时间: 00: 00: 03.78

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=20 Bytes=1840)
1 0 VIEW (Cost=64 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY STOPKEY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=260801)

Statistics
----------------------------------------------------------
268 recursive calls
0 db block gets
6215 consistent gets
6013 physical reads
0 redo size
740 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME FROM
5 (
6 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
7 )
8 )
9 WHERE RN BETWEEN 11 AND 20;
已选择10行。
已用时间: 00: 00: 11.86

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=6361 Bytes=585212)
1 0 VIEW (Cost=64 Card=6361 Bytes=585212)
2 1 COUNT
3 2 VIEW (Cost=64 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY) (Cost=64 Card=6361 Bytes=260801)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=260801)

Statistics
----------------------------------------------------------
26 recursive calls
12 db block gets
6175 consistent gets
9219 physical reads
0 redo size
737 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
10 rows processed
观察两个查询语句的执行时间，以及统计信息中的排序信息。对于第一个查询语句，Oracle利用了ORDER BY STOPKEY方式进行排序，排序操作只排序需要的TOP N的数据，因此排序操作放到了内存中，而对于第二个查询语句来说，进行的数据的全排序，排序数据量大，排序操作不得不在磁盘上完成，因此耗时比较多。
通过上面的例子可以看出给出的标准分页查询格式，对于包含排序的操作仍然可以在很大程度上提高分页查询性能。

Oracle分页查询语句（五）

前面的各种例子已经说明了分页查询语句的标准写法所带来的性能提升。
这里简单总结一下，并简单的说明分页查询语句在何时无法带来性能提升。
分页查询语句之所以可以很快的返回结果，是因为它的目标是最快的返回第一条结果。如果每页有20 条记录，目前翻到第5页，那么只需要返回前100条记录都可以满足查询的要求了，也许还有几万条记录也符合查询的条件，但是由于分页的限制，在当前的查询 中可以忽略这些数据，而只需尽快的返回前100条数据。这也是为什么在标准分页查询语句中经常会使用FIRST_ROWS提示的原因。
对于行操作，可以在得到结果的同时将结果直接返回给上一层调用。但是对于结果集操作，Oracle 必须得到结果集中所有的数据，因此分页查询中所带的ROWNUM信息不起左右。如果最内层的子查询中包含了下面这些操作中的一个以上，则分页查询语句无法 体现出任何的性能优势：UNION、UNION ALL、MINUS、INTERSECT、GROUP BY、DISTINCT、UNIQUE以及聚集函数如MAX、MIN和分析函数等。
除了这些操作以外，分页查询还有一个很明显的特点，就是处理的页数越小，效率就越高，越到后面，查询速度越慢。
分页查询用来提高返回速度的方法都是针对数据量较小的前N 条记录而言。无论是索引扫描，NESTED LOOP连接，还是ORDER BY STOPKEY，这些方法带来性能提升的前提都是数据量比较小，一旦分页到了最后几页，会发现这些方法不但没有办法带来性能的提升，而且性能比普通查询还 要低得多。这一点，在使用分页查询的时候，一定要心里有数。
最后看几个例子：
首先看看UNION ALL、GROUP BY以及分析函数使外层的ROWNUM限制对内层查询无效。
SQL> SET AUTOT TRACE
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=20 Bytes=1840)
1 0 VIEW (Cost=826 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=826 Card=6361 Bytes=502519)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)
5 4 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
这是分页查询ROWNUM起作用的情况，下面看看如果内层查询包括了集操作时的情况：
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T
8 UNION ALL
9 SELECT OBJECT_ID, OBJECT_NAME FROM T
10 ORDER BY OBJECT_NAME
11 )
12 WHERE ROWNUM <= 20
13 )
14 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=85 Card=20 Bytes=1840)
1 0 VIEW (Cost=85 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=85 Card=12722 Bytes=1005038)
4 3 SORT (ORDER BY STOPKEY) (Cost=18 Card=12722 Bytes=267162)
5 4 UNION-ALL
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)
7 5 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
322 consistent gets
0 physical reads
0 redo size
546 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT /*+ INDEX(T) */ OBJECT_ID, OBJECT_NAME FROM T
8 UNION ALL
9 SELECT /*+ INDEX(T) */ OBJECT_ID, OBJECT_NAME FROM T
10 ORDER BY OBJECT_NAME
11 )
12 WHERE ROWNUM <= 20
13 )
14 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1719 Card=20 Bytes=1840)
1 0 VIEW (Cost=1719 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=1719 Card=12722 Bytes=1005038)
4 3 SORT (ORDER BY STOPKEY) (Cost=1652 Card=12722 Bytes=267162)
5 4 UNION-ALL
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)
7 6 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)
8 5 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)
9 8 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24004 consistent gets
0 physical reads
0 redo size
546 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
如果说全表扫描的情况下，ROWNUM不起作用效果也不是很明显的话，通过使用HINT，让Oracle使用索引扫描，这时ROWNUM不起作用的效果就相当惊人了。
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T
8 GROUP BY OBJECT_ID, OBJECT_NAME
9 ORDER BY OBJECT_NAME
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=43 Card=20 Bytes=1840)
1 0 VIEW (Cost=43 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=43 Card=6361 Bytes=502519)
4 3 SORT (GROUP BY STOPKEY) (Cost=43 Card=6361 Bytes=133581)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
161 consistent gets
0 physical reads
0 redo size
673 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME, ROW_NUMBER() OVER(ORDER BY OBJECT_NAME)
8 FROM T
9 ORDER BY OBJECT_NAME
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=20 Bytes=1840)
1 0 VIEW (Cost=826 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=826 Card=6361 Bytes=502519)
4 3 WINDOW (BUFFER) (Cost=826 Card=6361 Bytes=133581)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)
6 5 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12002 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
上面的例子说明了分页查询的优化作用对于哪些种查询不起作用，下面看看，分页查询在翻页到最后的时候的性能。
SQL> SET AUTOT OFF
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
12722
SQL> SET AUTOT TRACE
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME
8 FROM T
9 ORDER BY OBJECT_NAME
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=20 Bytes=1840)
1 0 VIEW (Cost=826 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=826 Card=6361 Bytes=502519)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)
5 4 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME
8 FROM T
9 ORDER BY OBJECT_NAME
10 )
11 )
12 WHERE RN BETWEEN 11 AND 20;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=6361 Bytes=585212)
1 0 VIEW (Cost=43 Card=6361 Bytes=585212)
2 1 COUNT
3 2 VIEW (Cost=43 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY) (Cost=43 Card=6361 Bytes=133581)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
161 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
首先看一下，分页查询的性能比不使用ROWNUM的情况要强很多，但是，如果将分页的范围设置到12710和12720之间，这时候再来对比一下两种查询的效率。
SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME
8 FROM T
9 ORDER BY OBJECT_NAME
10 )
11 WHERE ROWNUM <= 12720
12 )
13 WHERE RN >= 12711;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=6361 Bytes=585212)
1 0 VIEW (Cost=826 Card=6361 Bytes=585212)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=826 Card=6361 Bytes=502519)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=6361 Bytes=133581)
5 4 INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=6361)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12001 consistent gets
0 physical reads
0 redo size
612 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME
8 FROM T
9 ORDER BY OBJECT_NAME
10 )
11 )
12 WHERE RN BETWEEN 12711 AND 12720;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=6361 Bytes=585212)
1 0 VIEW (Cost=43 Card=6361 Bytes=585212)
2 1 COUNT
3 2 VIEW (Cost=43 Card=6361 Bytes=502519)
4 3 SORT (ORDER BY) (Cost=43 Card=6361 Bytes=133581)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=6361 Bytes=133581)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
161 consistent gets
0 physical reads
0 redo size
612 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
不难发现，对于第二个查询，效率和第一次执行完全一样，但是分页查询的效率则大大的下降，而且此时的效率远远低于没有使用ROWNUM的查询。

Oracle分页查询语句（六）

这篇文章通过例子说明分页查询使用的NESTED LOOP操作，在分页查询翻到最后几页时的性能问题：
SQL> CREATE TABLE T AS SELECT * FROM DBA_USERS;
表已创建。
SQL> CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE;
表已创建。
SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (USERNAME);
表已更改。
SQL> ALTER TABLE T1 ADD CONSTRAINT FK_T1_OWNER FOREIGN KEY (OWNER)
2 REFERENCES T(USERNAME);
表已更改。
SQL> CREATE INDEX IND_T1_OWNER ON T1(NAME);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL 过程已成功完成。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1')
PL/SQL 过程已成功完成。
SQL> SELECT /*+ FIRST_ROWS */ USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 20
12 )
13 WHERE RN >= 11;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=20 Bytes=1200)
1 0 VIEW (Cost=97811 Card=20 Bytes=1200)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
574 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 )
12 WHERE RN BETWEEN 11 AND 20;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=96985 Bytes=5819100)
1 0 VIEW (Cost=830 Card=96985 Bytes=5819100)
2 1 COUNT
3 2 HASH JOIN (Cost=830 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=132)
5 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8586 consistent gets
8052 physical reads
0 redo size
574 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
在分页查询的前几页，NESTED LOOP操作比HASH JOIN操作效率高得多。
SQL> SET AUTOT OFF
SQL> SELECT COUNT(*) FROM T, T1 WHERE USERNAME = OWNER;
COUNT(*)
----------
96985
SQL> SET AUTOT TRACE
SQL> SELECT USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 )
12 WHERE RN BETWEEN 96971 AND 96980;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=830 Card=96985 Bytes=5819100)
1 0 VIEW (Cost=830 Card=96985 Bytes=5819100)
2 1 COUNT
3 2 HASH JOIN (Cost=830 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=12 Bytes=132)
5 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8586 consistent gets
8068 physical reads
0 redo size
571 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
对于最后几页，采用HASH JOIN的方式，执行效率几乎没有任何改变，而采用NESTED LOOP方式，则效率严重下降，而且远远低于HASH JOIN的方式。
SQL> SELECT /*+ FIRST_ROWS */ USER_ID, USERNAME, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, USER_ID, USERNAME, NAME
5 FROM
6 (
7 SELECT T.USER_ID, T.USERNAME, T1.NAME
8 FROM T, T1
9 WHERE T.USERNAME = T1.OWNER
10 )
11 WHERE ROWNUM <= 96980
12 )
13 WHERE RN >= 96971;
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=97811 Card=96980 Bytes=5818800)
1 0 VIEW (Cost=97811 Card=96980 Bytes=5818800)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS (Cost=97811 Card=96985 Bytes=2909550)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=826 Card=96985 Bytes=1842715)
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF 'PK_T' (UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
105566 consistent gets
8068 physical reads
0 redo size
571 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
分页查询一般情况下，很少会翻到最后一篇，如果只是偶尔碰到这种情况，对系统性能不会有很大的影响，但是如果经常碰到这种情况，在设计分页查询时应该给予足够的考虑。

Oracle分页查询语句（七）

在文章的最后看一下ORDER BY STOPKEY和ORDER BY在翻页查询的最后几页的性能差异：
SQL> CREATE TABLE T AS SELECT A.* FROM DBA_OBJECTS A, DBA_USERS B, TAB;
表已创建。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
458064
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL 过程已成功完成。
SQL> SET AUTOT TRACE
SQL> SET TIMING ON
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;
已选择10行。
已用时间: 00: 00: 00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=20 Bytes=1840)
1 0 VIEW (Cost=13888 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5579 consistent gets
0 physical reads
0 redo size
694 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
8 )
9 )
10 WHERE RN BETWEEN 11 AND 20;
已选择10行。
已用时间: 00: 00: 09.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)
1 0 VIEW (Cost=13888 Card=458064 Bytes=42141888)
2 1 COUNT
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
0 recursive calls
41 db block gets
5579 consistent gets
7935 physical reads
0 redo size
689 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
10 rows processed
对于翻页查询的前几页，采用ORDER BY STOPKEY的方式比ORDER BY性能上有很大的优势，那么对于分页查询的最后几页，ORDER BY STOPKEY是否和其他分页查询技术一样，性能比普通方式还要低很多：
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
8 )
9 WHERE ROWNUM <= 458060
10 )
11 WHERE RN >= 458051;
已选择10行。
已用时间: 00: 00: 09.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458060 Bytes=42141520)
1 0 VIEW (Cost=13888 Card=458060 Bytes=42141520)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
0 recursive calls
41 db block gets
5579 consistent gets
7933 physical reads
0 redo size
667 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
10 rows processed
SQL> SELECT OBJECT_ID, OBJECT_NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
5 FROM
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T ORDER BY TIMESTAMP
8 )
9 )
10 WHERE RN BETWEEN 458051 AND 458060;
已选择10行。
已用时间: 00: 00: 10.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)
1 0 VIEW (Cost=13888 Card=458064 Bytes=42141888)
2 1 COUNT
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics
----------------------------------------------------------
0 recursive calls
41 db block gets
5579 consistent gets
7935 physical reads
0 redo size
649 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
10 rows processed
出乎意料的是，虽然ORDER BY STOPKEY的方式在分页查询的最后几页性能也有明显的下降，但是在和普通的ORDER BY相比，无论从逻辑读、物理读还是从执行时间上看，二者都属于一个数量级上的。
看来ORDER BY STOPKEY排序方式，在STOPKEY接近排序总量的时候也不会有明显的性能下降。

Oracle分页查询语句（八）

前面提到了对于集操作和聚集操作，使用标准的分页函数没有太大的意义，下面通过几篇文章专门讨论集操作和聚集操作的情况。这里首先讨论集操作的情况。
当查询需要分页时，大多数情况都会包含排序操作，因为如果缺少排序操作，很难保证分页返回的数据是连续不重复的。
因此这里只考虑包含排序的情况：
SQL> CREATE TABLE T AS SELECT * FROM DBA_TABLES;
表已创建。
SQL> CREATE TABLE T1 AS SELECT * FROM DBA_INDEXES;
表已创建。
SQL> CREATE INDEX IND_T_OWNER ON T(OWNER);
索引已创建。
SQL> CREATE INDEX IND_T1_OWNER ON T1(OWNER);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL 过程已成功完成。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1')
PL/SQL 过程已成功完成。
SQL> SET AUTOT ON
SQL> SELECT /*+ FIRST_ROWS */ OWNER, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OWNER, NAME
5 FROM
6 (
7 SELECT OWNER, TABLE_NAME NAME
8 FROM T
9 UNION ALL
10 SELECT OWNER, INDEX_NAME NAME
11 FROM T1
12 ORDER BY OWNER
13 )
14 WHERE ROWNUM <= 20
15 )
16 WHERE RN > 10;
OWNER NAME
------------------------------ ------------------------------
CTXSYS DR$SECTION_GROUP
CTXSYS DR$THS_BT
CTXSYS DR$THS_FPHRASE
CTXSYS DR$THS_PHRASE
CTXSYS DR$THS
CTXSYS DR$SQE
CTXSYS SYS_IOT_OVER_26472
CTXSYS DR$INDEX_OBJECT
CTXSYS DR$POLICY_TAB
CTXSYS DR$INDEX_PARTITION
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=20 Bytes=940)
1 0 VIEW (Cost=17 Card=20 Bytes=940)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=17 Card=2877 Bytes=97818)
4 3 SORT (ORDER BY STOPKEY) (Cost=7 Card=2877 Bytes=76522)
5 4 UNION-ALL
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=3 Card=1157 Bytes=30082)
7 5 TABLE ACCESS (FULL) OF 'T1' (Cost=4 Card=1720 Bytes=46440)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
639 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT /*+ FIRST_ROWS */ OWNER, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OWNER, NAME
5 FROM
6 (
7 SELECT OWNER, TABLE_NAME NAME
8 FROM T
9 UNION ALL
10 SELECT OWNER, INDEX_NAME NAME
11 FROM T1
12 ORDER BY OWNER
13 )
14 )
15 WHERE RN > 10 AND RN <=20 ;
OWNER NAME
------------------------------ ------------------------------
CTXSYS DR$THS_BT
CTXSYS DR$THS_FPHRASE
CTXSYS DR$THS_PHRASE
CTXSYS DR$THS
CTXSYS DR$SQE
CTXSYS SYS_IOT_OVER_26472
CTXSYS DR$INDEX_OBJECT
CTXSYS DR$POLICY_TAB
CTXSYS DR$PART_STATS
CTXSYS DR$STATS
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=2877 Bytes=135219)
1 0 VIEW (Cost=17 Card=2877 Bytes=135219)
2 1 COUNT
3 2 VIEW (Cost=17 Card=2877 Bytes=97818)
4 3 SORT (ORDER BY) (Cost=7 Card=2877 Bytes=76522)
5 4 UNION-ALL
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=3 Card=1157 Bytes=30082)
7 5 TABLE ACCESS (FULL) OF 'T1' (Cost=4 Card=1720 Bytes=46440)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
626 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
从执行SQL的统计中看到，由于集操作的存在，导致了Oracle没有办法将ROWNUM信息推入到查询内部，导致标准分页方式的效率和其他分页方式效率差别不大。
当存在排序操作，且集操作为UNION ALL操作时，可以改写SQL语句为：
SQL> SELECT /*+ FIRST_ROWS */ OWNER, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OWNER, NAME
5 FROM
6 (
7 SELECT OWNER, NAME
8 FROM
9 (
10 SELECT OWNER, TABLE_NAME NAME
11 FROM T
12 ORDER BY OWNER
13 )
14 WHERE ROWNUM <= 20
15 UNION ALL
16 SELECT *
17 FROM
18 (
19 SELECT OWNER, TABLE_NAME NAME
20 FROM T1
21 ORDER BY OWNER
22 )
23 WHERE ROWNUM <= 20
24 ORDER BY OWNER
25 )
26 WHERE ROWNUM <= 20
27 )
28 WHERE RN > 10;
OWNER NAME
------------------------------ ------------------------------
CTXSYS DR$POLICY_TAB
CTXSYS DR$INDEX_OBJECT
CTXSYS SYS_IOT_OVER_26472
CTXSYS DR$SQE
CTXSYS DR$THS
CTXSYS DR$THS_PHRASE
CTXSYS DR$THS_FPHRASE
CTXSYS DR$THS_BT
CTXSYS DR$SECTION_GROUP
CTXSYS DR$SECTION
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1654 Card=20 Bytes=940)
1 0 VIEW (Cost=1654 Card=20 Bytes=940)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=1654 Card=40 Bytes=1360)
4 3 SORT (ORDER BY STOPKEY) (Cost=1652 Card=40 Bytes=1360)
5 4 UNION-ALL
6 5 COUNT (STOPKEY)
7 6 VIEW (Cost=826 Card=1157 Bytes=39338)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=1157 Bytes=30082)
9 8 INDEX (FULL SCAN) OF 'IND_T_OWNER' (NON-UNIQUE) (Cost=26 Card=1157)
10 5 COUNT (STOPKEY)
11 10 VIEW (Cost=826 Card=1720 Bytes=58480)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=826 Card=1720 Bytes=39560)
13 12 INDEX (FULL SCAN) OF 'IND_T1_OWNER' (NON-UNIQUE) (Cost=26 Card=1720)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
2 physical reads
0 redo size
631 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
进行了上面的等价改写，查询的逻辑读大大的减少。其实，这里使用的方法就是人为的将ROWNUM推入到UNION ALL操作的每个子查询中，使用这种方法保证查询结果正确的同时，提高了查询的效率。
不过上面给出的改写方法只对包含排序的UNION ALL操作有效。而其他集操作不能使用这种方法，比如UNION操作使用这种方法可能导致查询结果的数量小于查询开始限定的数量。而对于另外两种集操作，这种方法可能会导致错误的查询结果。

Oracle分页查询语句（九）

本文简单讨论一下包含GROUP BY、DISTINCT、UNIQUE等操作的分页查询语句。
由于包含上述的集操作，Oracle必须处理完所有的数据才会将结果全部的提交给用户。即使包含了ROWNUM，Oracle也只是在处理完所有的数据之后，将需要的数据进行返回。
不过并不是说前面提到的标准写法在这里没有意义，而是最耗时的部分已经处理完毕。通过ROWNUM来限制意义不大。
虽然标准分页的写法对于GROUP BY之类的操作意义不大，但是如果在执行分页之前需要计算总数的话，那么可以对分页的查询语句稍做修改，将计算总数和查询第一页的SQL结合起来，避免一次计算总数的操作。
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
会话已更改。
SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> ALTER TABLE T ADD PRIMARY KEY (ID);
表已更改。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL 过程已成功完成。
SQL> SET AUTOT ON
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT OBJECT_TYPE, CREATED, COUNT(*)
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM < 20
13 )
14 WHERE RN >= 10;
RN OBJECT_TYPE CREATED COUNT(*)
---------- ------------------ ------------------- ----------
10 TABLE 2003-11-13 01:41:01 16
11 CLUSTER 2003-11-13 01:41:02 3
12 INDEX 2003-11-13 01:41:02 31
13 LOB 2003-11-13 01:41:02 2
14 SEQUENCE 2003-11-13 01:41:02 4
15 TABLE 2003-11-13 01:41:02 20
16 INDEX 2003-11-13 01:41:03 16
17 LOB 2003-11-13 01:41:03 6
18 SEQUENCE 2003-11-13 01:41:03 2
19 SYNONYM 2003-11-13 01:41:03 1
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=19 Bytes=874)
1 0 VIEW (Cost=97 Card=19 Bytes=874)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=97 Card=32185 Bytes=1062105)
4 3 SORT (GROUP BY STOPKEY) (Cost=97 Card=32185 Bytes=547145)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
759 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT OBJECT_TYPE, CREATED, COUNT(*)
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 )
13 WHERE RN >= 10 AND RN < 20;
RN OBJECT_TYPE CREATED COUNT(*)
---------- ------------------ ------------------- ----------
10 TABLE 2003-11-13 01:41:01 16
11 CLUSTER 2003-11-13 01:41:02 3
12 INDEX 2003-11-13 01:41:02 31
13 LOB 2003-11-13 01:41:02 2
14 SEQUENCE 2003-11-13 01:41:02 4
15 TABLE 2003-11-13 01:41:02 20
16 INDEX 2003-11-13 01:41:03 16
17 LOB 2003-11-13 01:41:03 6
18 SEQUENCE 2003-11-13 01:41:03 2
19 SYNONYM 2003-11-13 01:41:03 1
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=32185 Bytes=1480510)
1 0 VIEW (Cost=97 Card=32185 Bytes=1480510)
2 1 COUNT
3 2 VIEW (Cost=97 Card=32185 Bytes=1062105)
4 3 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
759 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
由于查询操作中GROUP BY操作是耗时最大的SQL，因此标准分页方式在这里所带来的性能提升十分有限。但是，如果在执行分页查询前需要执行COUNT(*)的话，那么可以考虑将COUNT(*)的结果和分页结果一起返回，从而减少了一次计算总数所需的时间。
SQL> SELECT COUNT(*)
2 FROM
3 (
4 SELECT OBJECT_TYPE, CREATED, COUNT(*)
5 FROM T
6 GROUP BY OBJECT_TYPE, CREATED
7 ORDER BY CREATED
8 );
COUNT(*)
----------
3570

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=97 Card=32185)
3 2 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
377 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT OBJECT_TYPE, CREATED, COUNT(*)
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM < 20
13 )
14 WHERE RN >= 10;
RN OBJECT_TYPE CREATED COUNT(*)
---------- ------------------ ------------------- ----------
10 TABLE 2003-11-13 01:41:01 16
11 CLUSTER 2003-11-13 01:41:02 3
12 INDEX 2003-11-13 01:41:02 31
13 LOB 2003-11-13 01:41:02 2
14 SEQUENCE 2003-11-13 01:41:02 4
15 TABLE 2003-11-13 01:41:02 20
16 INDEX 2003-11-13 01:41:03 16
17 LOB 2003-11-13 01:41:03 6
18 SEQUENCE 2003-11-13 01:41:03 2
19 SYNONYM 2003-11-13 01:41:03 1
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=19 Bytes=874)
1 0 VIEW (Cost=97 Card=19 Bytes=874)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=97 Card=32185 Bytes=1062105)
4 3 SORT (GROUP BY STOPKEY) (Cost=97 Card=32185 Bytes=547145)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
759 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
与上面的两个查询相比，下面的两种方法都可以通过一个SQL语句来实现：
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT COUNT(*) OVER() CNT, OBJECT_TYPE, CREATED, COUNT(*) CN
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM < 20
13 )
14 WHERE RN >= 10;
RN CNT OBJECT_TYPE CREATED CN
---------- ---------- ------------------ ------------------- ----------
10 3570 TABLE 2003-11-13 01:41:01 16
11 3570 CLUSTER 2003-11-13 01:41:02 3
12 3570 INDEX 2003-11-13 01:41:02 31
13 3570 LOB 2003-11-13 01:41:02 2
14 3570 SEQUENCE 2003-11-13 01:41:02 4
15 3570 TABLE 2003-11-13 01:41:02 20
16 3570 INDEX 2003-11-13 01:41:03 16
17 3570 LOB 2003-11-13 01:41:03 6
18 3570 SEQUENCE 2003-11-13 01:41:03 2
19 3570 SYNONYM 2003-11-13 01:41:03 1
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=19 Bytes=1121)
1 0 VIEW (Cost=97 Card=19 Bytes=1121)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=97 Card=32185 Bytes=1480510)
4 3 WINDOW (BUFFER) (Cost=97 Card=32185 Bytes=547145)
5 4 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
808 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> SELECT *
2 FROM
3 (
4 SELECT COUNT(*) OVER() CNT, ROWNUM RN, A.*
5 FROM
6 (
7 SELECT OBJECT_TYPE, CREATED, COUNT(*) CN
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 )
13 WHERE RN >= 10 AND RN < 20;
CNT RN OBJECT_TYPE CREATED CN
---------- ---------- ------------------ ------------------- ----------
3570 10 TABLE 2003-11-13 01:41:01 16
3570 11 CLUSTER 2003-11-13 01:41:02 3
3570 12 INDEX 2003-11-13 01:41:02 31
3570 13 LOB 2003-11-13 01:41:02 2
3570 14 SEQUENCE 2003-11-13 01:41:02 4
3570 15 TABLE 2003-11-13 01:41:02 20
3570 16 INDEX 2003-11-13 01:41:03 16
3570 17 LOB 2003-11-13 01:41:03 6
3570 18 SEQUENCE 2003-11-13 01:41:03 2
3570 19 SYNONYM 2003-11-13 01:41:03 1
已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=32185 Bytes=1898915)
1 0 VIEW (Cost=97 Card=32185 Bytes=1898915)
2 1 WINDOW (BUFFER) (Cost=97 Card=32185 Bytes=1062105)
3 2 COUNT
4 3 VIEW (Cost=97 Card=32185 Bytes=1062105)
5 4 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
808 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10 rows processed
第一种方法采用了标准分页方式，效率相对更高一些，但是第一种方法需要对原始SQL进行修改，而第二种方式不需要修改原始SQL，直接在原始SQL外面添加一些代码就可以实现。

Oracle分页查询语句（十）

Oracle10g的新功能GROUP BY STOPKEY，使得Oracle10g解决了上一篇文章中提到的GROUP BY操作无法分页的问题。
在10g以前，Oracle的GROUP BY操作必须完全执行完，才能将结果返回给用户。但是Oracle10g增加了GROUP BY STOPKEY执行路径，使得用户在执行GROUP BY操作时，可以根据STOPKEY随时中止正在运行的操作。
这使得标准分页函数对于GROUP BY操作重新发挥了作用。
SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> CREATE INDEX IND_T_CREATED ON T (CREATED);
索引已创建。
SQL> ALTER TABLE T MODIFY CREATED NOT NULL;
表已更改。
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
会话已更改。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL 过程已成功完成。
SQL> SET AUTOT ON
SQL> SET TIMING ON
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT CREATED, COUNT(*)
8 FROM T
9 GROUP BY CREATED
10 ) A
11 WHERE ROWNUM < 20
12 )
13 WHERE RN >= 10;
RN CREATED COUNT(*)
---------- ------------------- ----------
10 2005-12-19 17:07:57 50
11 2005-12-19 17:07:58 36
12 2005-12-19 17:08:24 10
13 2005-12-19 17:08:25 49
14 2005-12-19 17:08:26 66
15 2005-12-19 17:08:27 62
16 2005-12-19 17:08:28 81
17 2005-12-19 17:08:29 82
18 2005-12-19 17:08:33 1
19 2005-12-19 17:08:35 3
已选择10行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 3639065582
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 665 | 1 (0)|
|* 1 | VIEW | | 19 | 665 | 1 (0)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | | 973 | 21406 | 1 (0)|
|* 4 | SORT GROUP BY STOPKEY| | 973 | 7784 | 1 (0)|
| 5 | INDEX FULL SCAN | IND_T_CREATED | 984 | 7872 | 1 (0)|
---

11 楼 留下的祝福 2013-08-05  

邮箱发给我，我有比较完整的总结，直接发给你们！这里有些图片没显示出来!

一、SQL 编写注意事项（标准）
在多在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于大量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对
于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。
在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一
般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来使
用索引，这有助于写出高性能的SQL语句。

改成下面：

二、SQL语句的编写原则：
●选择一个好的表联接顺序（这是一个比较重要的原则）
当在WHERE子句中有多个表联接时，WHERE子句中排在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在WHERE子句中的最后。
如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：
select * from emp e,dept d where d.deptno >10 and e.deptno =30; 如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。
select * from emp e,dept d where e.deptno =30 and d.deptno >10;
●最好不要在WHERE子句中使用函数或表达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引。
SELECT *  FROM T1 WHERE  F1*2 = 100
改成
SELECT *  FROM T1 WHERE  F1 = 100/2
● 使用WHERE （NOT）EXISTS 来代替（NOT）IN子句，使用NOT EXISTS 子句可以有效地利用索引。
尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（但NOT IN不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。
例子1： 
SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp);
例子2：
SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);
明显的，2要比1的执行性能好很多。
因为1中对emp进行了全表扫描,这是很浪费时间的操作。而且1中没有用到emp的索引， 因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。
● 通过使用>=、<=等，避免使用NOT命令
如这个例子：
select * from employee where salary<>3000;
对这个查询，可以改写为不使用NOT：
select * from employee where salary<3000 or salary>3000;
虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。
● 外部联接 + 的用法
外部联接+按其在=的左边或右边分左联接和右联接。若不带+运算符的表中的一个行不直接匹配于带+预算符的表中的任何行，则前者的行与后者中的一个空行相匹配并被返回。利用外部联接+，可以替代效率十分低下的 not in 运算，大大提高运行速度。例如，下面这条命令执行起来很慢：
select a.empno from emp a where a.empno not in
(select empno from emp1 where job=‘SALE’);
索引倘若利用外部联接，改写命令如下: 
select a.empno from emp a left join emp1 b
on a.empno=b.empno
where b.empno is null
and b.job=‘SALE’;
例如表少，但情况复杂的时候应该写如下语句：
select a.empno AS empno,
(select emp2.address from emp2 where a.id=emp2.id) AS address,
(select emp3.address1 from emp3 where b.id=emp3.id) AS address1
from emp a left join emp1 b on a.empno=b.empno
where b.empno is null and b.job=‘SALE’
这样运行速度明显提高.
●在查询时尽量少用格式转换
如用 WHERE a.order_no = b.order_no
而不用
WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, ’.’)-1)
= TO_NUMBER (substr(a.order_no, instr(b.order_no, .’) - 1)
●Order by语句
索引ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。
如必须使用排序操作，请遵循如下规则： 如结果集不需唯一，使用union all代替union。
●IS NULL 与 IS NOT NULL
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
       ●SELECT子句中避免使用 ‘ *‘
            SELECT * FROM　EMP
            应改为：
            SELECT COLUMN FROM　EMP
       ●当在SQL语句中连接多个表时, 最好使用表别名并把别名加在每个列上
       ●其他
使用count(*)而不要使用count(column_name)。
避免困难的正规表达式：LIKE通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT ＊ FROM customer WHERE zipcode LIKE “98_ _ _”
即使在zipcode字段上建立了索引，也还是采用顺序扫描的方式。如果把语句改为
SELECT ＊ FROM customer WHERE zipcode >98000
在执行查询时就会利用索引来查询，显然会大大提高速度。
另外，以下语句也不会使用索引：
SELECT ＊ FROM customer WHERE zipcode LIKE '[C-P]arsen'
SELECT ＊ FROM customer WHERE zipcode LIKE 'parsen [^L]%'
SELECT ＊ FROM customer WHERE zipcode LIKE '%parsen'
        








三、其他经验性规则
　 1、用多表连接代替EXISTS子句。如例（1）
　  2、少用DISTINCT，用EXISTS代替 如例（2），where 条件满足后，可以一次　
性返回结果集
3、如果有两张表联接利用COUNT的时候最好将急纪录少的表放放在后后面
　表 TAB1 16,384 条记录
　　 　 表 TAB2 5000 条记录
select count(tab2 .*) from tab1,tab2 where tab2.id=tab1.id这个
速度要快些。
select count(tab2 .*) from tab2,tab1 where tab2.id=tab1.id
如果还有tab3表，而且tab3表是tab1和tab2表的联协表，也叫交集表，
此时，将tab3放在最后效率要高很多。
select count(tab2 .*) from tab2,tab1,tab3 where tab2. name=tab1. name and tab3.sex=tab1.sex and tab3.age=tab2.age
示例：


例1：
   X   SELECT * FROM emp WHERE EXISTS ( SELECT 'X' FROM dept
    WHERE dept_no=e.dept_no AND dept_cat='A');
   O   SELECT * FROM emp e,dept d WHERE e.dept_no=d.dept_no
    AND dept_cat='A';
例2：
   X   SELECT DISTINCT d.dept_code,d.dept_name FROM dept d ,emp e
    WHERE e.dept_code=d.dept_code;
   O   SELECT dept_code,dept_name FROM dept d
    WHERE EXISTS ( SELECT 'X' FROM emp e
    WHERE e.dept_code=d.dept_code);








SQL 性能调整原则
以下就某些SQL语句的where子句编写及性能优化中需要注意的问题作详细介绍。
在这些where子句中，虽然某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。
1. IS NULL 与 IS NOT NULL
  在应不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
例：
drop index temp_zhao.aaaindex
select *  from temp_zhao where  aulname is not null
create clustered index aaaindex on temp_zhao(aulname)
select *  from temp_zhao where aulname is not null
2. 联接列
在应对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。
我们一起来看一个例子，假定有一个职工表（employee），对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME），现在要查询一个叫比尔.克林顿（Bill Cliton）的职工。
下面是一个采用联接查询的SQL语句：
   select * from employss
   where
   first_name||''||last_name ='Beill Cliton';
上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优化器对基于last_name创建的索引没有使用。应当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。
   Select * from employee
   where
   first_name ='Beill' and last_name ='Cliton';
         例：
drop index temp_zhao.aaaindex
select * from temp_zhao where aulname+' '+au_fname = 'White Johnson'
select * from temp_zhao where aulname = 'White' and  au_fname='Johnson'
create clustered index aaaindex on temp_zhao(au_fname)
select * from temp_zhao where aulname+' '+au_fname = 'White Johnson'
select * from temp_zhao where aulname = 'White' and  au_fname='Johnson'
3. 带通配符（%）的like语句
同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包含
cliton的人。可以采用如下的查询SQL语句：
select * from employee where last_name like '%cliton%';
这里由于通配符（%）在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：
    select * from employee where last_name like 'cliton%';
例：
drop index temp_zhao.aaaindex
select * from temp_zhao where aulname like 'G%' or aulname like 'W%'or aulname like 'b%'
create clustered index aaaindex on temp_zhao(aulname)
select *  from temp_zhao where  aulname like 'G%' or aulname like 'W%' or aulname like 'b%'
4. Order by语句
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列
没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索
引项或者有计算表达式都将降低查询速度。仔细检查order by语句以找出非索引项或者表达式，
它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列
建立另外一个索引，同时应绝对避免在order by子句中使用表达式。
例：
    Order by 语句
drop index temp_zhao.aaaindex
select  * from temp_zhao order by  au_id
create clustered index aaaindex on temp_zhao(au_id)
select  * from temp_zhao order by  au_id

Group by 语句
drop index temp_zhao.aaaindex
select  aulname from temp_zhao group by  aulname
create clustered index aaaindex on temp_zhao(aulname)
select  aulname from temp_zhao group by  aulname



5. NOT
我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，
也可以使用and（与）、or（或）以及not（非）。NOT可用来对任何逻辑运算符号取反。下面是一
个NOT子句的例子：
... where not (status ='VALID')
如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算
符包含在另外一个逻辑运算符中，这就是不等于（<>）运算符。换句话说，即使不在查询where
子句中显式地加入NOT词，NOT仍在运算符中，见下例：
... where status <>　'INVALID';
再看下面这个例子：
      select * from employee where  salary<>3000;
对这个查询，可以改写为不使用NOT：
      select * from employee where  salary<3000 or salary>3000;
例：
drop index temp_zhao.aaaindex
select *  from temp_zhao where  zip<>94609
create clustered index aaaindex on temp_zhao(zip)
select *  from temp_zhao where  zip<>94609
select *  from temp_zhao where  zip>94609 or zip<94609
虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。
6. IN和EXISTS
有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where
子句中可以使用两种格式的子查询。
第一种格式是使用IN操作符：
    ... where column in(select * from ... where ...);
第二种格式是使用EXIST操作符：
    ... where exists (select 'X' from ...where ...);
    相信绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。
在第二种格式中，子查询以‘select 'X'开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在where语句中使用的列存在索引）。相对于IN子句来说，EXISTS使用相连子查询，构造起来要比IN子查询困难一些。通过使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。
例：
create clustered index aaaindex on temp_zhao(au_id)
select * from temp_zhao where temp_zhao.au_id  in (select au_id from temp_zhao1)
select * from temp_zhao where exists (select * from temp_zhao1 where temp_zhao.au_id=temp_zhao1.au_id)
7、其他
1、SELECT子句中避免使用 ‘ *‘
  select zip from temp_zhao
  select * from temp_zhao
2、使用count(*)而不要使用count(column_name)。
select count(zip) from temp_zhao
select count(*) from temp_zhao





















培训注意点总结
1、语句中出现的所有表名、字段名全部小写，系统保留字、内置函数名、SQL保留字大写。
2、连接符or、in、and、以及＝、<=、>=等前后加上一个空格。
3、对较为复杂的sql语句加上注释，说明算法、功能。
4、查询多个列时，尽量使每列查询占一行，且加上注释来标明该列名称，以方便其他人以后的修改。
5、where子句书写时，每个条件占一行，语句令起一行时，以保留字或者连接符开始，连接符右对齐。
6、多表连接时，使用表的别名来引用列。
7、变量命名不能超出ORACLE的限制（30个字符），命名要规范，要用英文命名。
8、查找数据库表或视图时，只取需要字段，不要使用*来代替所有列名。
9、当在WHERE子句中有多个表联接时，WHERE子句中排在最后的表应当是返回行数可能最少的表。
10、过滤条件最有效的子句应放在WHERE子句中的最后。
11、最好不要在WHERE子句中使用函数或表达式。
12、尽可能使用NOT EXISTS来代替NOT IN，使用EXISTS来代替IN。
13、通过使用>... OR <...来避免使用<>...。
14、在查询时尽量少用格式转换。
15、在不必要的情况下尽量避免使用ORDER BY和GROUP BY（必要时注意对索引列排序、分组）。
16、尽量避免使用IS NULL 和 IS NOT NULL（尤其对具有索引的列使用）。
17、在使用count时，尽量使用count(*)而不要使用count(column_name)。
18、在不必要的情况下尽量避免使用DISTINCT。
19、尽量避免使用‘%...‘。
20、尽可能多地使用主键列（默认的会在主键上加上索引）作为条件。
21、尽量避免使用UNION ALL，而用UNION代替。

10 楼 z_xiaofei168 2013-08-02  

pppqqqzxj 写道

好东西。。

好东西就应该一起分享

9 楼 z_xiaofei168 2013-08-02  

求求你帮帮我 写道

   博主，应该保证所写内容的正确性，要不然或误导很多刚入行的菜鸟。菜鸟在工作中的唯一希望就是上网查找，并且坚信网上是正确的，甚至会那网上的东西去跟经理理论。我刚入行的时候就是这样的，经理问我为什么这样写，我说在网上看的。
    虽然，博主无私奉献的精神可喜可贺，但至少要保证所写内容是正确的，如果不确定是否正确要给出提示，让读者自己判断实验。

你是刚接触SQL吗？这也是简单的常识，是很实用的，你也可以参考其他相关的知识。

8 楼 求求你帮帮我 2013-08-02  

   博主，应该保证所写内容的正确性，要不然或误导很多刚入行的菜鸟。菜鸟在工作中的唯一希望就是上网查找，并且坚信网上是正确的，甚至会那网上的东西去跟经理理论。我刚入行的时候就是这样的，经理问我为什么这样写，我说在网上看的。
    虽然，博主无私奉献的精神可喜可贺，但至少要保证所写内容是正确的，如果不确定是否正确要给出提示，让读者自己判断实验。

7 楼 pppqqqzxj 2013-08-02  

好东西。。

6 楼 z_xiaofei168 2013-08-01  

freezingsky 写道

以上性能优化，应该说适用于目前主流的数据库吧。不止是oracle.

嗯，是的，当时看的是关于oracle优化的所有信息

5 楼 freezingsky 2013-08-01  

以上性能优化，应该说适用于目前主流的数据库吧。不止是oracle.

4 楼 z_xiaofei168 2013-08-01  

BUYAOZAIBEIDAOLE 写道

不错哦 出了union all 替换uninon那个有点不对

拿来分享，互相学习吧

3 楼 z_xiaofei168 2013-08-01  

伤心眼泪 写道

用UNION-ALL 替换UNION ( if possible)
看到这一句我就不想看了。
你举得例子并没有可比性，因为一般用union是为了去重复，union all是不去重复的，而你的例子里union all并没有去重复，所以例子没可比性。

看博客就是吸收有用的，摒弃无用的，你说呢？

2 楼 BUYAOZAIBEIDAOLE 2013-08-01  

不错哦 出了union all 替换uninon那个有点不对

1 楼 伤心眼泪 2013-08-01  

用UNION-ALL 替换UNION ( if possible)
看到这一句我就不想看了。
你举得例子并没有可比性，因为一般用union是为了去重复，union all是不去重复的，而你的例子里union all并没有去重复，所以例子没可比性。