Re: Why doesn't my hint work?

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Mon, 2 Dec 2013 10:33:51 -0800
Message-ID: <CAA2Dszzx9Aq0dddMs+-yo3edJE8T29Lgi08gaTHXppkQBZj3CQ_at_mail.gmail.com>

Paul
  You need to specify table alias in your hint, as hinted below:

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

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/>

Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/> , Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert
RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices>

<http://tinyurl.com/book-expert-plsql-practices>

On Mon, Dec 2, 2013 at 10:25 AM, Paul Houghton < Paul.Houghton_at_admin.cam.ac.uk> wrote:

> 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

Received on Mon Dec 02 2013 - 19:33:51 CET