August 14, 2006 at 9:28 am
TAB_B has primary key defintion of PK_TAB_B defined on columns: COL_B1, COL_B2, COL_B3
In the following SQL:
SELECT B.COL1
FROM TAB_A A inner join TAB_B B ON B.COL_B1 = A.COL_A1
inner join TAB_C C ON C.COL_C1 = B.COL_B2
will SQL's optimizer utilize COL_B2 index access for the 2nd join above? or should I create a 2nd index on TAB_B COL_B2 to ensure the Optimizer uses index access?
August 14, 2006 at 12:10 pm
Not enough info to know for sure. There are too many factors, relative sizes of the tables, availability of a clustered index, fragmentation, selectivity of any indexes, data distribution, datatypes of the joined columns, freshness of the statistics.
Why not just capture the execution plan and go from there ?
August 16, 2006 at 3:06 am
If you're asking will the query optimiser use the index on PK_TAB_B, the answer is no, not if the PK is defined with the columns in the order you specify. You would need a separate index on COL_B2. Then, as PW says, whether that index actually gets used depends on a lot of things.
John
August 16, 2006 at 8:17 am
Just use QA and show the execution plan. I have seen Index Scans occurr in situations like this. However, it might even choose an index or table scan in TAB_C instead, depending on if the optimizer chose to join A to B first or C to B.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply