push_subq－控制子查询比外部连接先运行

http://bitirainy.itpub.net/post/330/1886

 

先子查询再做表连接比较有利，也就是说子查询本身代价不大，并且过滤掉很多记录，产生一个很小的结果集再做表连接，比较有利。

oracle  document:

The PUSH_SUBQ hint causes non-merged subqueries to be evaluated 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 it improves performance to evaluate the subquery earlier.

反之就是 no_push_subq
The NO_PUSH_SUBQ hint causes non-merged subqueries to be evaluated as the last step in the execution plan. If the subquery is relatively expensive or does not reduce the number of rows significantly, then it improves performance to evaluate the subquery last.

我们通过查询的执行计划来看个例子

explain  plan  for  select   m.*
from  member m,offer  o
where  m.login_id = o.member_id
and exists (select 1 from  company where admin_member_id = m.login_id and id < 1000000)

SQL> @?/rdbms/admin/utlxplp;

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  FILTER              |             |       |       |       |
|   2 |   MERGE JOIN         |             |       |       |       |
|   3 |    SORT JOIN         |             |       |       |       |
|   4 |     TABLE ACCESS FULL| OFFER       |       |       |       |
|*  5 |    SORT JOIN         |             |       |       |       |
|   6 |     TABLE ACCESS FULL| MEMBER      |       |       |       |
|*  7 |   TABLE ACCESS FULL  | COMPANY     |       |       |       |
--------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM "COMPANY" "COMPANY" WHERE
              "COMPANY"."ID"<1000000 AND "COMPANY"."ADMIN_MEMBER_ID"=:B1))
   5 - access("SYS_ALIAS_1"."LOGIN_ID"="O"."MEMBER_ID")
       filter("SYS_ALIAS_1"."LOGIN_ID"="O"."MEMBER_ID")
   7 - filter("COMPANY"."ID"<1000000 AND
              "COMPANY"."ADMIN_MEMBER_ID"=:B1)

Note: rule based optimization

25 rows selected.

这里可以看出是 member 和offer 先做表连接然后再和company表连接

加上提示看看

explain  plan  for select  /*+ push_subq */ m.*
from  member m,offer  o
where  m.login_id = o.member_id
and exists (select 1 from  company where admin_member_id = m.login_id and id < 1000000)

SQL> @?/rdbms/admin/utlxplp;

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   592 |   325K|   555 |
|*  1 |  HASH JOIN           |             |   592 |   325K|   555 |
|*  2 |   HASH JOIN          |             |   521 |   280K|   348 |
|   3 |    SORT UNIQUE       |             |       |       |       |
|*  4 |     TABLE ACCESS FULL| COMPANY     |   521 | 13025 |     2 |
|   5 |    TABLE ACCESS FULL | MEMBER      |  3466K|  1738M|     2 |
|   6 |   TABLE ACCESS FULL  | OFFER       |  3941K|    45M|     2 |
--------------------------------------------------------------------

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

   1 - access("M"."LOGIN_ID"="O"."MEMBER_ID")
   2 - access("COMPANY"."ADMIN_MEMBER_ID"="M"."LOGIN_ID")
   4 - filter("COMPANY"."ID"<1000000)

Note: cpu costing is off

21 rows selected.

这里可以看出是member和company先连接，再和offer做表连接

再比如，我们假定以member为驱动表，和company先做nested  loop 比较有效，然后再和offer表连接，整个查询的效率最高，那我们可以控制执行计划按照我们的意愿进行。

explain plan  for  select  /*+ push_subq use_nl(m c) full(m) */ m.*
from  member m,offer  o
where  m.login_id = o.member_id
and exists (select 1 from  company c where c.admin_member_id = m.login_id and c.id < 1000000)

SQL> @?/rdbms/admin/utlxplp;

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   592 |   325K|  1262 |
|*  1 |  HASH JOIN           |             |   592 |   325K|  1262 |
|   2 |   NESTED LOOPS       |             |   521 |   280K|  1055 |
|   3 |    SORT UNIQUE       |             |       |       |       |
|*  4 |     TABLE ACCESS FULL| COMPANY     |   521 | 13025 |     2 |
|*  5 |    TABLE ACCESS FULL | MEMBER      |     1 |   526 |     2 |
|   6 |   TABLE ACCESS FULL  | OFFER       |  3941K|    45M|     2 |
--------------------------------------------------------------------

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

   1 - access("M"."LOGIN_ID"="O"."MEMBER_ID")
   4 - filter("C"."ID"<1000000)
   5 - filter("C"."ADMIN_MEMBER_ID"="M"."LOGIN_ID")

Note: cpu costing is off

21 rows selected.

push_subq 为我们提供了一种控制执行计划的手段，如果我们不希望子查询先进行而是最后进行，自然可以使用no_push_subq。

总的来说，sql执行计划的调整，一定是建立在对表的索引、数据量、各阶段满足条件的返回结果的量上进行考量的。如果我熟悉应用和数据的情况下，我一般是通过hints来控制达到我所期望的执行计划，而很少去反复试验看哪种执行计划好。

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

转载于:http://blog.itpub.net/8102208/viewspace-667562/