Interesting case of the restriction of global hints

One of my colleagues sent following test case, of which he couldn’t control the join order with hints.

1. Create objects – table t1, t2 and t3

SQL> create table t1(c1 number, c2 number);

Table created.

SQL> create table t2(c1 number, c2 number);

Table created.

SQL> create table t3(c1 number, c2 number);

Table created.

2. Now Let’s set the join order as T1->T2->T3, using global hints convention. But it seems that Oracle does not work as expected.

SQL> explain plan for
  2  select * from
  3  (
  4  select
  5  	     /*+ leading(v.t1 v.t2 t3) */
  6  	     v.c1 as v_c1,
  7  	     v.c2 as v_c2,
  8  	     t3.c2 as t3_c2
  9  from
 10  	     (select
 11  		     t1.c1,
 12  		     t2.c2
 13  	     from
 14  		     t1, t2
 15  	     where
 16  		     t1.c1 = t2.c1) v,
 17  	     t3
 18  where
 19  	     v.c1 = t3.c1
 20  ) x
 21  ;

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN            |      |     1 |    65 |     7  (15)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     1 |    52 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |      |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | T3   |     1 |    26 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | T1   |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

It really seems that the optimizer does not understand the global hints when it is used with non global hint convention(t3 here). Yes, you can use hints inside the inline view, but what if you want to control the join order only with global hint convention?

3. The cure he tried was using Oracle’s internal global hint convention. You can get how Oracle’s internal hint convention looks using ‘ADVANCED’ format option with DBMS_XPLAN.DISPLAY function.

select * from table(dbms_xplan.display(null, null, 'advanced'));
...

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5C160134
   3 - SEL$5C160134 / T1@SEL$3
   4 - SEL$5C160134 / T2@SEL$3
   5 - SEL$5C160134 / T3@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$5C160134" "T3"@"SEL$2")
      USE_HASH(@"SEL$5C160134" "T2"@"SEL$3")
      LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2")
      FULL(@"SEL$5C160134" "T3"@"SEL$2")
      FULL(@"SEL$5C160134" "T2"@"SEL$3")
      FULL(@"SEL$5C160134" "T1"@"SEL$3")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$3")
      OUTLINE(@"SEL$335DD26A")
      OUTLINE(@"SEL$1")
      MERGE(@"SEL$335DD26A")
      OUTLINE_LEAF(@"SEL$5C160134")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

4. With this ugly global hint convention, the join order is now fully controllable.

SQL> explain plan for
  2  select * from
  3  (
  4  select
  5  	     /*+ LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2" ) */
  6  	     v.c1 as v_c1,
  7  	     v.c2 as v_c2,
  8  	     t3.c2 as t3_c2
  9  from
 10  	     (select
 11  		     t1.c1,
 12  		     t2.c2
 13  	     from
 14  		     t1, t2
 15  	     where
 16  		     t1.c1 = t2.c1) v,
 17  	     t3
 18  where
 19  	     v.c1 = t3.c1
 20  ) x
 21  ;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |    39 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | T3   |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

5. The better approach would be QB_NAME hint, which provides better readability and flexibility.

SQL> explain plan for
  2  select * from
  3  (
  4  select
  5  	     /*+ leading(t1@inline t2@inline t3) */
  6  	     v.c1 as v_c1,
  7  	     v.c2 as v_c2,
  8  	     t3.c2 as t3_c2
  9  from
 10  	     (select /*+ qb_name(inline) */
 11  		     t1.c1,
 12  		     t2.c2
 13  	     from
 14  		     t1, t2
 15  	     where
 16  		     t1.c1 = t2.c1) v,
 17  	     t3
 18  where
 19  	     v.c1 = t3.c1
 20  ) x
 21  ;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |    39 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | T3   |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Let me give my thanks to my colleague, who sent this interesting test case.

PS) Note that QB_NAME hint has also some restrictions, in that case, Oracle’s internal global hint convention would be a good alternative.