Description

If the optimizer_index_caching and optimizer_index_cost_adj parameters are 
set to non-default values the optimizer may select a  merge join cartesian
even if hints are specified for other joins. This can occur as CBO
costs the MERGE JOIN as if it was a nested loops without any keys and so
the parameters incorrectly affect the merge join costing.
 
Eg:
    select /*+ use_hash (a) use_hash (b) use_hash (c) */
      a.id
    from look_up a
    inner join look_up b on (a.id = b.id)
    inner join look_up c on (b.id = c.id)
    where a.text = 'aa'
    and b.text = 'bb'
    and c.text = 'cc'
    ^
 
    | 0   | SELECT STATEMENT       |           |       |       |     6 |      
    | 1   |  HASH JOIN             |           |     2 |    36 |     6 |  
    | 2   |   INDEX RANGE SCAN     | LOOK_UP_UK|   100 |   600 |     1 |  
    | 3   |   MERGE JOIN CARTESIAN |           |   20K |  234K |     4 |  
    | 4   |    INDEX RANGE SCAN    | LOOK_UP_UK|     2 |    12 |     1 |  
    | 5   |    BUFFER SORT         |           |   10K |   59K |     3 |  
    | 6   |     INDEX RANGE SCAN   | LOOK_UP_UK|   10K |   59K |     1 |  
 
    Predicate Information:
    ----------------------
    1 - access("B"."ID"="C"."ID" AND "A"."ID"="B"."ID")
    2 - access("B"."TEXT"='bb')
    4 - access("A"."TEXT"='aa')
    6 - access("C"."TEXT"='cc')
 
Workaround
  Set "_optimizer_mjc_enabled"=false;