Is there a limit on the number of joins in a query?

[rwozniak71]

Hi Folks,

I'm working with a client that uses a third party tool (Hyperion EIS) to generate "drill-through" queries against a very large Oracle table (approx. 150,000,000 rows). We have a partition and an index defined on the table to help query performance.

For the most part, performance is very good-our queries typically come back in a matter of seconds. Except we noticed that if there are more than 13 tables joined in the query the optimizer does not use the drill-through table's index and insead does a full table scan. Consequently, response time goes up to 15-20 min. per query.

We cannot alter the SQL generated by the third party tool. Our schema is defined in a snowflake and that cannot be altered. What we need to know is if there is a hard limit on the number of joins in Oracle so we can take this information to the client and present workarounds.

Insight appreciated,

Rob

 

[SantaMufasa]

Rob,

There is virtually no limit to the number of tables you can join. When this question arose in an earlier thread, I successfully joined several thousand tables.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 01:04 (14Jan05) UTC (aka "GMT" and "Zulu"),
@ 18:04 (13Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[rwozniak71]

Thanks Santa,

I guess it's back to the drawing board, then! I'll try playing with the order of the columns in the where clause. Anything else you can think of?

R

 

[sem]

Try to play with optimizer_mode=first_rows

Regards, Dima

http://seminihin.narod.ru

 

[rwozniak71]

Thanks sem, but what does this mean? Is this a parameter to set on the optimizer? My query looks like this:

SELECT DISTINCT ac."ACCT_MEMBER",
aa."DEPARTMENT",
aa."CATEGORY",
ai."LOC_MEMBER",
aa."POSTED_AMT",
ae."TIME_MEMBER",
al."STYL_MEMBER",
al."STYL_MEMBER_DESC",
am."VNDR_MEMBER",
am."VNDR_MEMBER_DESC",
aa."POSTED_DATE",
aa."DIVISION",
aa."SL_FOOD_NONFOOD",
ac."ACCT_MEMBER_DESC",
ae."TIME_MEMBER_DESC",
ag."BOOK_MEMBER",
ai."LOC_MEMBER_DESC",
ak."DEPT_MEMBER_DESC",
aa."FACT_DETAIL_KEY",
aa."LAST_REBUILD_DATE"
FROM SLDM.VW_SL_FACT_DETAIL aa,
SLDM.VW_ACCOUNTS_DIM ab,
SLDM.VW_ACCOUNT_MEMBERS ac,
SLDM.VW_TIME_DIM ad,
SLDM.VW_TIME_MEMBERS ae,
SLDM.VW_BOOKS_DIM af,
SLDM.VW_BOOK_MEMBERS ag,
SLDM.VW_LOCATIONS_DIM ah,
SLDM.VW_LOCATION_MEMBERS ai,
SLDM.VW_DEPARTMENTS_DIM aj,
SLDM.VW_DEPARTMENT_MEMBERS ak,
SLDM.VW_STYLE_MEMBERS al,
SLDM.VW_VENDOR_MEMBERS am
WHERE aa."ACCT_MEMBER_KEY" = ac."ACCT_MEMBER_KEY"
AND aa."TIME_MEMBER_KEY" = ae."TIME_MEMBER_KEY"
AND aa."BOOK_MEMBER_KEY" = ag."BOOK_MEMBER_KEY"
AND aa."LOC_MEMBER_KEY" = ai."LOC_MEMBER_KEY"
AND aa."DEPT_MEMBER_KEY" = ak."DEPT_MEMBER_KEY"
AND ac."ACCT_MEMBER_KEY" = ab."ACCT_CHILD_MEMBER_KEY"
AND ae."TIME_MEMBER_KEY" = ad."TIME_CHILD_MEMBER_KEY"
AND ag."BOOK_MEMBER_KEY" = af."BOOK_CHILD_MEMBER_KEY"
AND ai."LOC_MEMBER_KEY" = ah."LOC_CHILD_MEMBER_KEY"
AND ak."DEPT_MEMBER_KEY" = aj."DEPT_CHILD_MEMBER_KEY"
AND aa."VNDR_MEMBER_KEY" = am."VNDR_MEMBER_KEY"
AND aa."STYL_MEMBER_KEY" = al."STYL_MEMBER_KEY"
AND ((ab."ACCT_CHILD_MEMBER" IN ( 'A210751')))
AND ((ad."TIME_CHILD_MEMBER" IN ( 'PC09')))
AND ((af."BOOK_CHILD_MEMBER" IN ( 'DOLLARS')))
AND ((ah."LOC_CHILD_MEMBER" IN ( 'LOC0001')))
AND ((aj."DEPT_CHILD_MEMBER" IN ( 'DEPT009XXX')))
ORDER BY 1 ASC, 2 ASC, 3 ASC, 4 ASC

(The views are a SELECT * of the underlying tables).

If I take out a couple of the joins I can get the optimizer to use the underlying index. Otherwise it always wants to do a full table scan, even if you include a hint.

Any suggestions appreciated.

 

[sem]

That parameter may be set on database, session or statement level, but as I understand the second option is not suitable for your case.

Regards, Dima

http://seminihin.narod.ru