Reverse Engineer the Explain plan to get the HINTS??

[gsprince]

Hi,

I have an SQL stmt which has join on three tables ENTRY, JOURNAL and BATCH. I added certain condition (say AND RECORD# > = 0, which will be always true) to make use of one of my index.

Now I got the Exp Plan as follows.

I want to get the same result by using HINTS instead of having the extraneous AND RECORD# > = 0 condition.

Can some one, by looking at the following Exp Plan output tell me what would be the equivalent HINT ?


0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=98)
1 0 SORT (ORDER BY) (Cost=9 Card=1 Bytes=98)
2 1 NESTED LOOPS (Cost=7 Card=1 Bytes=98)
3 2 NESTED LOOPS (Cost=6 Card=1 Bytes=90)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'ENTRY' (Cost=4 C
ard=1 Bytes=49)

5 4 INDEX (RANGE SCAN) OF 'IX_ENTRY_ACCOUNT#' (NON-U
NIQUE) (Cost=3 Card=1)

6 3 TABLE ACCESS (BY INDEX ROWID) OF 'BATCH' (Cost=2 C
ard=155 Bytes=6355)

7 6 INDEX (UNIQUE SCAN) OF 'PK_BATCH' (UNIQUE) (Cost
=1 Card=155)

8 2 TABLE ACCESS (BY INDEX ROWID) OF 'JOURNAL' (Cost=1 C
ard=6 Bytes=48)

9 8 INDEX (UNIQUE SCAN) OF 'PK_JOURNAL' (UNIQUE)

Thanks,

[sagaran]

Hi,

You can use "use_nl(tablenames)" hint which gives you eqvivalent result and plan.


Sagaran

[chrisrlong]

Originally posted by sagaran
Hi,

You can use "use_nl(tablenames)" hint which gives you eqvivalent result and plan.


Sagaran

Unlikely. The issue at hand was how to enforce an index usage, not a join methodology. You want the INDEX hint. Unfortunately, you haven't said what index you are trying to enforce or on what table, so I can't tell for certain. My guess ould be that you want /*+ INDEX(ENTRY,'IX_ENTRY_ACCOUNT#) */


HTH,

- Chris

[gsprince]

Thank you both for your response.

Chris,

I want to use 'PK_JOURNAL' , 'PK_BATCH' and 'PK_BATCH' , NL on ENTRY and BATCH.

In short, I want to use the exact same indexes and Join method shown by the EXPLAIN PLAN.

I got this EXPLAIN PLAN by having an Extraneous AND condition im my qry, which I will remove if I can get the SAME ACCESS PATH using HINTS.

I tried giving the INDEX HINT, then USE_NL but, I guess my order of HINTS are not correct.

Please update the thread, if I have to be more specific.

Thanks,
Prince..

[chrisrlong]

Yes, you should fill in the details. What is the old plan, without the AND? What, exactly, were the hints you attempted and what was the plan that was generated? There are tons of variables that play into the generation of a plan, so we will definitely need more input.

Thanks,

- Chris