NO_UNNEST hint

Unnest 我们称之为对子查询展开,就是别让子查询孤单地嵌套(nest)在里面。
所以NO_NNNEST双重否定代表肯定,即让子查询不展开,让他嵌套在(nest)里面。

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from dba_objects;

Table created.

SQL> insert into t2 select * From t1;

87014 rows created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> begin
  2     dbms_stats.gather_table_stats(
  3             user,
  4             't1',
  5             cascade => true,
  6             estimate_percent => null,
  7             method_opt => 'for all columns size 1'
  8     );
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> begin
  2     dbms_stats.gather_table_stats(
  3             user,
  4             't2',
  5             cascade => true,
  6             estimate_percent => null,
  7             method_opt => 'for all columns size 1'
  8     );
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL> select t1.object_id
  2    from t1
  3   where exists (select 1 from t2 where t1.object_id = t2.object_id * 20);

Execution Plan
----------------------------------------------------------
Plan hash value: 1713220790

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    10 |  1028   (1)| 00:00:13 |
|*  1 |  HASH JOIN SEMI    |      |     1 |    10 |  1028   (1)| 00:00:13 |
|   2 |   TABLE ACCESS FULL| T1   | 87014 |   424K|   343   (1)| 00:00:05 |
|   3 |   TABLE ACCESS FULL| T2   |   174K|   849K|   683   (1)| 00:00:09 |
---------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID"*20)

SQL> select t1.object_id
  2    from t1
  3   where exists (select /*+ no_unnest*/ 1 from t2 where t1.object_id = t2.object_id * 20);

Execution Plan
----------------------------------------------------------
Plan hash value: 895956251

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |   229K  (1)| 00:45:49 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 87014 |   424K|   343   (1)| 00:00:05 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |    10 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2" WHERE
              "T2"."OBJECT_ID"*20=:B1))
   3 - filter("T2"."OBJECT_ID"*20=:B1)

此条目发表在Hint, Uncategorized分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s