ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 当subquery遭遇rownum

当subquery遭遇rownum

原创 Linux操作系统 作者:jcq0 时间:2009-10-11 01:05:47 0 删除 编辑


一般情况下,含有子查询的语句都会在完全执行完子查询后在继续执行外层的查询

JCQ0> explain plan for select * from ( select * from test_jcq0 where object_id<=10) where object_name='xxx';

已解释。

JCQ0> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3487203024

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    22 |  3894 |   154   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| test_jcq0 |    22 |  3894 |   154   (1)| 00:00:02 |
------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("test_jcq0"."OBJECT_NAME"='xxx' AND "OBJECT_ID"<=10)

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

已选择17行。

按道理,应该执行子查询,选择出object_id<=10的记录,然后执行外层查询,从子查询的结果集中选择出object_name='xxx'的记录,
那么整个语句的cost=对test_jcq0进行全表扫描的cost+对子结果集全表扫描的cost
由于该查询较简单,执行时进行谓词合并("test_jcq0"."OBJECT_NAME"='xxx' AND "OBJECT_ID"<=10)做为一个and执行,其最终
的cost变成了对test_jcq0进行全表扫描的cost 154.

是否所有类似这种查询的cost都一定会满足:
cost>=对test_jcq0进行全表扫描的cost

JCQ0> explain plan for select * from ( select * from test_jcq0 where object_id<=10) where rownum=1;

已解释。

JCQ0> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 770253427

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   177 |    22   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |          |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| test_jcq0 |     8 |  1416 |    22   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   1 - filter(ROWNUM=1)
   2 - filter("OBJECT_ID"<=10)

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

已选择19行。

在以上的例子中,rownum为1,当对test_jcq0全表扫描找到第一条符合条件的记录时,就不在继续查询而返回结果,也就是执行计划中的STOPKEY.

当然,如果对object_id建立索引后,执行的成本又会大大降低。

JCQ0> create index test_jcq0_idx on test_jcq0(object_id);

索引已创建。

JCQ0> explain plan for select * from ( select * from test_jcq0 where object_id<=10) where rownum=1;

已解释。
JCQ0> set linesize 100
JCQ0> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3942985762

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |   177 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |              |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| test_jcq0     |     9 |  1593 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | test_jcq0_IDX |     9 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------


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

   1 - filter(ROWNUM=1)
   3 - access("OBJECT_ID"<=10)

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

已选择20行。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14130873/viewspace-616249/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-25

  • 博文量
    93
  • 访问量
    342904