如果说no_unnest是为了让子查询不展开，独立的完成，那么push_subq就是为了让子查询最先进行join。
所以，这个hint其实是控制的join的顺序。

SQL> create table t1 tablespace cbo as select * from all_objects;

Table created.

SQL> create table t2 tablespace cbo as select * from all_objects;

Table created.

SQL> create table t3 tablespace cbo as select * from all_objects;

Table created.

SQL> create table t4 tablespace cbo as select * from all_objects;

Table created.

SQL> create index idx_t3 on t3(object_id) tablespace idx_2k;

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t3',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t4',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set timing on
SQL> select t1.object_name from
  2  t1,t2,t4
  3  where t1.object_name like '%a%'
  4  and t1.object_id+t2.object_id>50
  5  and t4.object_type=t1.object_type
  6  and 11 in
  7  (select t3.object_id from t3 where t1.object_id=t3.object_id);

no rows selected

Elapsed: 00:00:13.24

Execution Plan
----------------------------------------------------------
Plan hash value: 588163567

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |  8557 |   359K|   207K  (1)| 00:41:28 |
|*  1 |  FILTER              |        |       |       |            |          |
|*  2 |   HASH JOIN          |        |    93M|  3839M| 22310   (5)| 00:04:28 |
|   3 |    TABLE ACCESS FULL | T4     | 10946 | 76622 |    40   (0)| 00:00:01 |
|   4 |    NESTED LOOPS      |        |   299K|    10M| 21520   (1)| 00:04:19 |
|*  5 |     TABLE ACCESS FULL| T1     |   547 | 16957 |    40   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL| T2     |   547 |  2735 |    39   (0)| 00:00:01 |
|*  7 |   FILTER             |        |       |       |            |          |
|*  8 |    INDEX RANGE SCAN  | IDX_T3 |     1 |     5 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T3" "T3" WHERE 11=:B1 AND
              "T3"."OBJECT_ID"=11))
   2 - access("T4"."OBJECT_TYPE"="T1"."OBJECT_TYPE")
   5 - filter("T1"."OBJECT_NAME" LIKE '%a%')
   6 - filter("T1"."OBJECT_ID"+"T2"."OBJECT_ID">50)
   7 - filter(11=:B1)
   8 - access("T3"."OBJECT_ID"=11)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        564  consistent gets
          0  physical reads
          0  redo size
        280  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select /*+push_subq(@tmp)*/ t1.object_name from
  2  t1,t2,t4
  3  where t1.object_name like '%a%'
  4  and t1.object_id+t2.object_id>50
  5  and t4.object_type=t1.object_type
  6  and 11 in
  7  (select /*+QB_Name(tmp)*/ t3.object_id from t3 where t1.object_id=t3.object_id);

no rows selected

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1295769906

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |  4681K|   191M|  1181   (4)| 00:00:15 |
|*  1 |  HASH JOIN           |        |  4681K|   191M|  1180   (4)| 00:00:15 |
|   2 |   TABLE ACCESS FULL  | T4     | 10946 | 76622 |    40   (0)| 00:00:01 |
|   3 |   NESTED LOOPS       |        | 14970 |   526K|  1102   (1)| 00:00:14 |
|*  4 |    TABLE ACCESS FULL | T1     |    27 |   837 |    40   (0)| 00:00:01 |
|*  5 |     FILTER           |        |       |       |            |          |
|*  6 |      INDEX RANGE SCAN| IDX_T3 |     1 |     5 |     1   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS FULL | T2     |   547 |  2735 |    39   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - access("T4"."OBJECT_TYPE"="T1"."OBJECT_TYPE")
   4 - filter("T1"."OBJECT_NAME" LIKE '%a%' AND  EXISTS (SELECT /*+
              PUSH_SUBQ QB_NAME ("TMP") */ 0 FROM "T3" "T3" WHERE 11=:B1 AND
              "T3"."OBJECT_ID"=11))
   5 - filter(11=:B1)
   6 - access("T3"."OBJECT_ID"=11)
   7 - filter("T1"."OBJECT_ID"+"T2"."OBJECT_ID">50)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        282  consistent gets
          0  physical reads
          0  redo size
        280  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

不加提示时，耗时达到13秒，而加了提示后，耗时居然只有0.02秒！