| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Six or More Outer Joins Cause Improper Query Execution
Has anyone had experience/problems with more than five nested loop
joins in 9i?
Problem: I have a query like the following in 9i Enterprise Edition:
SELECT DIM_TABLE.ID, A.CITY, B.CITY, C.CITY, D.CITY, E.CITY FROM DIM_TABLE
LEFT OUTER JOIN FACT_TABLE A ON DIM_TABLE.ID = A.ID
AND A.STATE = 'TX'
LEFT OUTER JOIN FACT_TABLE B ON DIM_TABLE.ID = B.ID
AND B.STATE = 'OK'
LEFT OUTER JOIN FACT_TABLE C ON DIM_TABLE.ID = C.ID
AND C.STATE = 'AR'
LEFT OUTER JOIN FACT_TABLE D ON DIM_TABLE.ID = D.ID
AND D.STATE = 'LA'
LEFT OUTER JOIN FACT_TABLE E ON DIM_TABLE.ID = E.ID
AND E.STATE = 'CA'
WHERE DIM_TABLE.ID2=123 AND DIM_TABLE.ID3=456;
The fact table is large (~200M rows) and the dimension table is about
200K rows. Assume there are bitmap indexes on all the fields in the
DIM_TABLE and bitmap indexes on the ID and STATE fields in the
FACT_TABLE.
This query runs very quickly and EXPLAIN PLAN shows the following:
OPERATION OPTIONS OBJECT_NAME --------------------------- ------------- -------------------SELECT STATEMENT
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
TABLE ACCESS BY INDEX ROWID DIM_TABLE
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE DIM_TABLE_ID2_IX
BITMAP INDEX SINGLE VALUE DIM_TABLE_ID3_IX
TABLE ACCESS BY INDEX ROWID FACT_TABLE
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE FACT_TABLE_ID_IX
BITMAP INDEX SINGLE VALUE FACT_TABLE_STATE_IX
TABLE ACCESS BY INDEX ROWID FACT_TABLE
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE FACT_TABLE_ID_IX
BITMAP INDEX SINGLE VALUE FACT_TABLE_STATE_IX
TABLE ACCESS BY INDEX ROWID FACT_TABLE
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE FACT_TABLE_ID_IX
BITMAP INDEX SINGLE VALUE FACT_TABLE_STATE_IX
TABLE ACCESS BY INDEX ROWID FACT_TABLE
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE FACT_TABLE_ID_IX
BITMAP INDEX SINGLE VALUE FACT_TABLE_STATE_IX
TABLE ACCESS BY INDEX ROWID FACT_TABLE
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE FACT_TABLE_ID_IX
BITMAP INDEX SINGLE VALUE FACT_TABLE_STATE_IX
*****************************************************************
* Things look very orderly and symmetric and the query performs *
* great. However, if six (or more) outer joins are used, the *
* symmetry gets lost and the performance goes down the tubes. *
* Here's an example: *
*****************************************************************
SELECT DIM_TABLE.ID, A.CITY, B.CITY, C.CITY, D.CITY, E.CITY, F.CITY
FROM DIM_TABLE
LEFT OUTER JOIN FACT_TABLE A ON DIM_TABLE.ID = A.ID AND
A.STATE='TX'
LEFT OUTER JOIN FACT_TABLE B ON DIM_TABLE.ID = B.ID AND
B.STATE='OK'
LEFT OUTER JOIN FACT_TABLE C ON DIM_TABLE.ID = C.ID AND
C.STATE='AR'
LEFT OUTER JOIN FACT_TABLE D ON DIM_TABLE.ID = D.ID AND
D.STATE='LA'
LEFT OUTER JOIN FACT_TABLE E ON DIM_TABLE.ID = E.ID AND
E.STATE='CA'
LEFT OUTER JOIN FACT_TABLE F ON DIM_TABLE.ID = E.ID AND
E.STATE='WA'
WHERE DIM_TABLE.ID2=123 AND DIM_TABLE.ID3=456;
This query runs very slowly and EXPLAIN PLAN shows the following:
OPERATION OPTIONS OBJECT_NAME --------------------------------- ------------- -------------------SELECT STATEMENT
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
TABLE ACCESS BY INDEX ROWID DIM_TABLE
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE DIM_TABLE_ID2_IX
BITMAP INDEX SINGLE VALUE DIM_TABLE_ID3_IX
TABLE ACCESS BY INDEX ROWID FACT_TABLE
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE FACT_TABLE_ID_IX
BITMAP INDEX SINGLE VALUE FACT_TABLE_STATE_IX
TABLE ACCESS BY INDEX ROWID FACT_TABLE
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE FACT_TABLE_ID_IX
BITMAP INDEX SINGLE VALUE FACT_TABLE_STATE_IX
TABLE ACCESS BY INDEX ROWID FACT_TABLE
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE FACT_TABLE_ID_IX
BITMAP INDEX SINGLE VALUE FACT_TABLE_STATE_IX
TABLE ACCESS BY INDEX ROWID FACT_TABLE
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE FACT_TABLE_ID_IX
BITMAP INDEX SINGLE VALUE FACT_TABLE_STATE_IX
TABLE ACCESS BY INDEX ROWID FACT_TABLE
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE FACT_TABLE_ID_IX
BITMAP INDEX SINGLE VALUE FACT_TABLE_STATE_IX
TABLE ACCESS BY INDEX ROWID FACT_TABLE
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE FACT_TABLE_ID_IX
Note that the last table access does not have BITMAP AND. Adding more outer joins to the FACT_TABLE results in more of these at the bottom of the EXPLAIN PLAN. Also, there never seem to be more than 7 TABLE ACCESS statements. Instead, additional joins to the FACT_TABLE get crammed under the DIM_TABLE ACCESS statement, instead of getting their own TABLE ACCESS lines.
Is there a hint that needs to be used to get the optimizer to behave with 6 or more joins like it does with 1 to 5 joins? Is there a limit to TABLE ACCESS or NESTED LOOPS in Oracle I'm coming up against?
Any insight would be greatly appreciated.
Thanks,
Wendell Bielefeld Received on Mon May 12 2003 - 18:16:19 CDT
 |
 |