Why doesn't my hint work?

From: Paul Houghton <Paul.Houghton_at_admin.cam.ac.uk>
Date: Mon, 2 Dec 2013 18:25:40 +0000
Message-ID: <FF5D5F2AD07EE2429C8AFD9BC0EE57FB4C8214675A_at_LUCIFERTWO.internal.admin.cam.ac.uk>

Hi

I am trying to influence the execution plan of a query using hints. Oracle enterprise edition 11.2.0.3. Tables are heap and indexes are all b-tree.

  select * from oprid_sec a, emp_app_sec b where a.oprid = :1

  AND a.career = b.career
  and a.org = b.org
  and b.latest_row = 'Y';

  ---------------------------------------------------
  | Id  | Operation          | Name        | Starts | 
  ---------------------------------------------------
  |   0 | SELECT STATEMENT   |             |      1 |


  |*  1 |  HASH JOIN         |             |      1 |

  |*  2 |   INDEX RANGE SCAN | OPRID_SEC   |      1 |

  |*  3 |   TABLE ACCESS FULL| EMP_APP_SEC |      1 |

  ---------------------------------------------------

  Predicate Information (identified by operation id):

---

   1 - access("A"."CAREER"="B"."CAREER" AND "A"."ORG"="B"."ORG")
   2 - access("OPRID"=:1)
   3 - filter("B"."LATEST_ROW"='Y')

The developer is asking me to make the access on EMP_APP_SEC use an index. I assume they want a plan something like:

---

  | Id  | Operation                     | Name          | Starts | 
  ----------------------------------------------------------------
  |   0 | SELECT STATEMENT              |               |      1 |


  |*  1 |  NESTED LOOPS                 |               |      1 |

  |*  2 |   TABLE ACCESS BY INDEX ROWID | EMP_APP_SEC   |      n |

  |   3 |    INDEX access of some sort  | EMP_APP_SEC_I |      n |


  |*  4 |   INDEX RANGE SCAN            | OPRID_SEC     |      1 |

  ----------------------------------------------------------------

i.e. use the OPRID_SEC table as the main loop and look up the index on EMP_APP_SEC for each row to get the row(s) to join. I thought that I could

create index emp_app_sec_i on emp_app_sec (career,org,latest_row);

select /*+ LEADING (b a)

           INDEX (EMP_APP_SEC EMP_APP_SEC_I) */ *   from oprid_sec a, emp_app_sec b where a.oprid = :1

  AND a.career = b.career
  and a.org = b.org
  and b.latest_row = 'Y';

But the hints are not having the effect I intended. The leading hint works to change the join order, but the index is not used.

---

  | Id | Operation | Name | Starts |

---

  |   0 | SELECT STATEMENT   |             |      1 |
  |   1 |  NESTED LOOPS      |             |      1 |


  |*  2 |   TABLE ACCESS FULL| EMP_APP_SEC |      1 |

|* 3 | INDEX UNIQUE SCAN| OPRID_SEC | 583K|

---

  Predicate Information (identified by operation id):

---

   2 - filter("B"."LATEST_ROW"='Y')
   3 - access("A"."OPRID"=:1 AND "A"." CAREER"="B"." CAREER" AND
              "A"." ORG"="B"." ORG")

What am I doing wrong? (I hope it is more interesting than a syntax error!)

I understand this is likely to be slower than the plan the optimiser chose, but I would like to be able to measure the difference.

Thanks

PaulH

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Dec 02 2013 - 19:25:40 CET