Oracle的分页查询

最新推荐文章于 2023-11-08 16:09:23 发布

selley

最新推荐文章于 2023-11-08 16:09:23 发布

阅读量726

点赞数

分类专栏： Oracle 文章标签： oracle nested join table access sql

Oracle 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

分页查询的基本格式如下：

SELECT * FROM

(

SELECT A. * , ROWNUM RN

FROM ( 真正的查询 ) A

WHERE ROWNUM < = 40

)

WHERE RN > = 21

其中最内层的真正的查询表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。

选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。查询语句如下：

SELECT * FROM

(

SELECT A. * , ROWNUM RN

FROM ( 真正的查询 ) 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 ( 真正的查询 ) A

WHERE ROWNUM <= 40

)

WHERE RN >= 21

说明（自己查的资料）：关于Nested Loop、HashJoin

NESTED LOOP

    对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是 nested loops。
一般在nested loop中，驱动表满足条件结果集不大，被驱动表的连接字段要有索引，这样就走nstedloop。如果驱动表返回记录太多，就不适合nested loops了。如果连接字段没有索引，则适合走hash join，因为不需要索引。
可用ordered提示来改变CBO默认的驱动表，可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。

HASH JOIN

   hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表（或数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。
当小表可以全部放入内存中，其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。临时段中的分区都需要换进内存做hash join。这时候成本接近于全表扫描小表+分区数*全表扫描大表的代价和。
    至于两个表都进行分区，其好处是可以使用parallel query，就是多个进程同时对不同的分区进行join，然后再合并。但是复杂。
使用hash join时，HASH_AREA_SIZE初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET即可。
以下条件下hash join可能有优势：
两个巨大的表之间的连接。
在一个巨大的表和一个小表之间的连接。
可用ordered提示来改变CBO默认的驱动表，可用USE_HASH(table_name1 table_name2)提示来强制使用hash join。

nested loop join和hash join测试过程

SQL> create table t1 as select * from user_tables;

Table created.

SQL> create table t2 as select * from user_indexes;

Table created.

SQL> select count(*) from t1
2 ;

COUNT(*)
----------
704

SQL> select count(*) from t2;

COUNT(*)
----------
810

SQL> set autot on exp
SQL> select count(*) from t1,t2 where t1.table_name=t2.table_name;

COUNT(*)
----------
787

Execution Plan
----------------------------------------------------------
Plan hash value: 446739472

----------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    34 |    14   (8)| 00:00:01 |
|   1 | SORT AGGREGATE     |      |     1 |    34 |            |          |
|* 2 |   HASH JOIN         |      |   810 | 27540 |    14   (8)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |   704 | 11968 |     6   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |   810 | 13770 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

2 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")

Note
-----
- dynamic sampling used for this statement

SQL> create index index_tn_t1 on t1(table_name);

Index created.

SQL> select count(*) from t1,t2 where t1.table_name=t2.table_name;

COUNT(*)
----------
787

Execution Plan
----------------------------------------------------------
Plan hash value: 962536480

--------------------------------------------------------------------------------
------

--------------------------------------------------------------------------------
------

| 0 | SELECT STATEMENT | | 1 | 34 | 11 (10)| 00:0
0:01 |

| 1 | SORT AGGREGATE | | 1 | 34 | |
|

|* 2 | HASH JOIN | | 810 | 27540 | 11 (10)| 00:0
0:01 |

| 3 | INDEX FAST FULL SCAN| INDEX_TN_T1 | 704 | 11968 | 3 (0)| 00:0
0:01 |

| 4 | TABLE ACCESS FULL | T2 | 810 | 13770 | 7 (0)| 00:0
0:01 |

--------------------------------------------------------------------------------
------

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

2 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")

Note
-----
- dynamic sampling used for this statement

SQL> create index index_tn_t2 on t2(table_name);

Index created.

SQL> select count(*) from t1,t2 where t1.table_name=t2.table_name;

COUNT(*)
----------
787

Execution Plan
----------------------------------------------------------
Plan hash value: 1205552978

--------------------------------------------------------------------------------
------

--------------------------------------------------------------------------------
------

| 0 | SELECT STATEMENT | | 1 | 34 | 7 (15)| 00:0
0:01 |

| 1 | SORT AGGREGATE | | 1 | 34 | |
|

|* 2 | HASH JOIN | | 810 | 27540 | 7 (15)| 00:0
0:01 |

| 3 | INDEX FAST FULL SCAN| INDEX_TN_T1 | 704 | 11968 | 3 (0)| 00:0
0:01 |

| 4 | INDEX FAST FULL SCAN| INDEX_TN_T2 | 810 | 13770 | 3 (0)| 00:0
0:01 |

--------------------------------------------------------------------------------
------

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

2 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")

Note
-----
- dynamic sampling used for this statement

SQL> drop index index_tn_t1;

Index dropped.

SQL> select count(*) from t1,t2 where t1.table_name=t2.table_name;

COUNT(*)
----------
787

Execution Plan
----------------------------------------------------------
Plan hash value: 1730698804

--------------------------------------------------------------------------------
------

--------------------------------------------------------------------------------
------

| 0 | SELECT STATEMENT | | 1 | 34 | 10 (10)| 00:0
0:01 |

| 1 | SORT AGGREGATE | | 1 | 34 | |
|

|* 2 | HASH JOIN | | 810 | 27540 | 10 (10)| 00:0
0:01 |

| 3 | TABLE ACCESS FULL | T1 | 704 | 11968 | 6 (0)| 00:0
0:01 |

| 4 | INDEX FAST FULL SCAN| INDEX_TN_T2 | 810 | 13770 | 3 (0)| 00:0
0:01 |

--------------------------------------------------------------------------------
------

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

2 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")

Note
-----
- dynamic sampling used for this statement

SQL> alter table t1 add constraint pk_t1 primary key (table_name);

Table altered.

SQL> select count(*) from t1,t2 where t1.table_name=t2.table_name;

COUNT(*)
----------
787

Execution Plan
----------------------------------------------------------
Plan hash value: 1260911036

--------------------------------------------------------------------------------
------

--------------------------------------------------------------------------------
------

| 0 | SELECT STATEMENT | | 1 | 34 | 3 (0)| 00:0
0:01 |

| 1 | SORT AGGREGATE | | 1 | 34 | |
|

| 2 | NESTED LOOPS | | 810 | 27540 | 3 (0)| 00:0
0:01 |

| 3 | INDEX FAST FULL SCAN| INDEX_TN_T2 | 810 | 13770 | 3 (0)| 00:0
0:01 |

|* 4 | INDEX UNIQUE SCAN | PK_T1 | 1 | 17 | 0 (0)| 00:0
0:01 |

--------------------------------------------------------------------------------
------

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

4 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")

Note
-----
- dynamic sampling used for this statement

SQL>

结果: 因为开始是普通index所以可能存在重复的key吧，你看一个explain的rows是700多一个是800左右。返回记录是700多，那oracle 认为你返回的基本就是记录集里的大部分记录了所以走hash jion
最后个因为你建立了唯一索引，所以oracle知道nest loop join的话out loop来驱动inner loop
而out的row只可能在inner里找到唯一记录（inner是是唯一索引返回的记录集）
所以扫描唯一索引后的rows是1
虽然前面还是800左右并返回700多的记录
row=1让 oracle认为你返回的内容是整个rowset中的小部分所以用了nest loop join

selley

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle的分页查询

分页查询的基本格式如下：SELECT * FROM ( SELECT A. * , ROWNUM RN FROM ( 真正的查询 ) A WHERE ROWNUM = 40 ) WHERE RN >= 21 其中最内层的真正的查询表示不进行翻页的原始查询语句。ROWNUM = 21控制分页查询的每页的范围。上面给出的这个分页查询语句，在大多数
复制链接

扫一扫