As well as your hint, I'd be intrigued to see the result of using the 8i
syntax (+) instead of the new ANSI syntax.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Wendell B" <wendellnsherri_at_hotmail.com> wrote in message
news:c2b207fd.0305121516.1f52bd92_at_posting.google.com...
> 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 Wed May 14 2003 - 15:23:42 CDT
