And if you collect adequate statistics , particularly the join column statistics (career) you might not need to hint your query in order to obtain your desired execution plan http://hourim.wordpress.com/2013/11/22/on-how-important-is-collecting-statistics-adequately/ BEGIN dbms_stats.gather_table_stats (ownname => user, tabname => 'EMP_APP_SEC', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => true, method_opt => 'FOR ALL COLUMNS SIZE 1' ); END; / Best regards Mohamed Houri www.hourim.wordpress.com 2013/12/3 Gaja Krishna Vaidyanatha <gajav@xxxxxxxxx> > 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 <http://www.dbperfman.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@xxxxxxxxxxxxxxx> > *To:* "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx> > *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 > -- > //www.freelists.org/webpage/oracle-l > > > > > -- Bien Respectueusement Mohamed Houri