Re: Why doesn't my hint work?

Hi Paul,

At first glance, the issue seems to be that you are using an alias for the table and the INDEX hint has reference to the actual table name. When you alias something in the query, the hints should also use the same alias. I would modify the hint to the following and see whether it fixes the issue:

select /*+ LEADING (b a)
          INDEX (b 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';

 
Cheers,

Gaja

Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC
http://www.dbperfman.com
http://www.dbcloudman.com

Phone - +1 (650) 743-6060
LinkedIn - http://www.linkedin.com/in/gajakrishnavaidyanatha

Co-author: Oracle Insights:Tales of the Oak Table - http://www.apress.com/9781590593875
Primary Author: Oracle Performance Tuning 101 - http://www.amzn.com/0072131454
Enabling Exadata, Big Data and Cloud Deployment & Management for Oracle


________________________________
 From: Paul Houghton <Paul.Houghton_at_admin.cam.ac.uk>
To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> 
Sent: Monday, December 2, 2013 10:25 AM
Subject: Why doesn't my hint work?
 

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

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