push_subq hint

The PUSH_SUBQ hint instructs the optimizer to evaluate nonmerged subqueries at the earliest possible step in the execution plan. Generally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then evaluating the subquery earlier can improve performance.

This hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join.

env 11.2.0.4
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
create table t3 as select * from dba_objects;
create table t4 as select * from dba_objects;

begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => ‘t1′,
cascade => true,
estimate_percent => null,
method_opt =>’for all columns size 1’
);
end;
/

begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => ‘t2′,
cascade => true,
estimate_percent => null,
method_opt =>’for all columns size 1’
);
end;
/

begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => ‘t3′,
cascade => true,
estimate_percent => null,
method_opt =>’for all columns size 1’
);
end;
/

begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => ‘t4′,
cascade => true,
estimate_percent => null,
method_opt =>’for all columns size 1’
);
end;
/
— no hint

SQL> select t1.object_name
  2    from t1, t2, t4
  3   where t4.object_type = t1.object_type
  4     and 100 in
  5         (select t3.object_id from t3 where t1.object_id = t3.object_id);

Execution Plan
----------------------------------------------------------
Plan hash value: 1203306920

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |   168M|  8023M|  4390T  (1)|999:59:59 |
|*  1 |  FILTER                |      |       |       |            |          |
|*  2 |   HASH JOIN            |      |    14T|   665T|   119M (76)|397:51:52 |
|   3 |    TABLE ACCESS FULL   | T4   | 87017 |   849K|   343   (1)| 00:00:05 |
|   4 |    MERGE JOIN CARTESIAN|      |  7571M|   282G|    29M  (1)| 98:49:13 |
|   5 |     TABLE ACCESS FULL  | T1   | 87014 |  3398K|   343   (1)| 00:00:05 |
|   6 |     BUFFER SORT        |      | 87015 |       |    29M  (1)| 98:49:09 |
|   7 |      TABLE ACCESS FULL | T2   | 87015 |       |   341   (1)| 00:00:05 |
|*  8 |   FILTER               |      |       |       |            |          |
|*  9 |    TABLE ACCESS FULL   | T3   |     1 |     5 |   343   (1)| 00:00:05 |
-------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM "T3" "T3" WHERE :B1=100 AND
              "T3"."OBJECT_ID"=100))
   2 - access("T4"."OBJECT_TYPE"="T1"."OBJECT_TYPE")
   8 - filter(:B1=100)
   9 - filter("T3"."OBJECT_ID"=100)

–add hint push_subq

SQL> select /*+ push_subq(@v1)*/ t1.object_name
  2    from t1, t2, t4
  3   where t4.object_type = t1.object_type
  4     and 100 in
  5         (select /*+ qb_name(v1)*/t3.object_id from t3 where t1.object_id = t3.object_id);

Execution Plan
----------------------------------------------------------
Plan hash value: 1902668207

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |   732G|    33T|  5969K (76)| 19:53:49 |
|*  1 |  HASH JOIN            |      |   732G|    33T|  5968K (76)| 19:53:45 |
|   2 |   TABLE ACCESS FULL   | T4   | 87017 |   849K|   343   (1)| 00:00:05 |
|   3 |   MERGE JOIN CARTESIAN|      |   378M|    14G|  1482K  (1)| 04:56:33 |
|*  4 |    TABLE ACCESS FULL  | T1   |  4351 |   169K|   343   (1)| 00:00:05 |
|*  5 |     FILTER            |      |       |       |            |          |
|*  6 |      TABLE ACCESS FULL| T3   |     1 |     5 |   343   (1)| 00:00:05 |
|   7 |    BUFFER SORT        |      | 87015 |       |  1482K  (1)| 04:56:29 |
|   8 |     TABLE ACCESS FULL | T2   | 87015 |       |   341   (1)| 00:00:05 |
------------------------------------------------------------------------------

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

   1 - access("T4"."OBJECT_TYPE"="T1"."OBJECT_TYPE")
   4 - filter( EXISTS (SELECT /*+ PUSH_SUBQ QB_NAME ("V1") */ 0 FROM
              "T3" "T3" WHERE :B1=100 AND "T3"."OBJECT_ID"=100))
   5 - filter(:B1=100)
   6 - filter("T3"."OBJECT_ID"=100)
此条目发表在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