| SQL Query with many joins [message #139721] |
Thu, 29 September 2005 05:34  |
sebastianR
Messages: 33
Registered: August 2005
|
Member |
|
|
Hi,
I'd like to know which possibilities exist to optimize a SQL-Query where there are many INNER JOINS?
The query looks like this:
============================
SELECT count(*) FROM
(SELECT MAINTABLE.KEY_ID AS OID,
CONCAT (CONCAT (CLASSTAB.NAME, '_'),MAINTABLE.KEY_ID) AS LONGNAME,
MAINTABLE.KEY_ID AS SHORTNAME,
'testing' as TestVal,
MAINTABLE_COMPO_MAIN.MATURITY_DATE AS MATURITYDATE,
MAINTABLE.ISSUE_PRICE AS FACEVALUE,
PRICETAB.quot_val AS PRICE ,
To_Date('10.02.2005','dd.mm.yyyy') AS ENDTIME,
MAINTABLE_COMPO_INTR.FLAT_RATE AS COUPONRATE,
MARKET.domi_ID AS COUNTRY,
PRICETAB.mkt_ID AS MARKET,
MAINTABLE.ISSUE_DATE AS DATEOFISSU,
MAINTABLE.ISSUE_PRICE AS ISSUEPRICE,
MAINTABLE.OPN_DATE AS DATEOFCREATION,
MAINTABLE_COMPO_MAIN.LAST_STATUS_CHANGE_DATE AS DATEOFMODIFICATION,
PRICETAB.price_curry_id AS CURRENCY,
MAINTABLE_COMPO_INTR.FREQ AS COUPONINTERVAL,
(SELECT name FROM MAPTAB WHERE CLASS_ID = MAINTABLE_compo_main.DFLT_PRICE_QTYPE_CLASS_ID) AS BondQuotModeName,
(SELECT name FROM MAPTAB WHERE CLASS_ID = MAINTABLE_compo_intr.INTR_CALC_METHOD_CLASS_ID) AS DayCountConvention
FROM
MAINTABLE
INNER JOIN CLASSINST ON MAINTABLE.KEY_ID =CLASSINST.OBJ_ID
Inner Join PRICETAB on MAINTABLE.key_id=PRICETAB.asset_id
INNER JOIN CLASSTAB ON CLASSINST.CLASS_ID =CLASSTAB.KEY_ID
INNER JOIN CLASSTABIF ON CLASSTAB.CLASSIF_ID = CLASSTABIF.KEY_ID
INNER JOIN MAINTABLE_COMPO_INTR ON MAINTABLE.KEY_ID =MAINTABLE_compo_intr.asset_id
INNER JOIN MAINTABLE_mkt ON MAINTABLE.key_id = MAINTABLE_MKT.asset_ID
INNER JOIN MARKET ON MAINTABLE_mkt.mkt_id = MARKET.key_id
INNER JOIN MAINTABLE_compo_main ON MAINTABLE.key_id =MAINTABLE_COMPO_MAIN.ASSET_ID
WHERE CLASSTABIF.INTL_ID = LOWER ('test1')
AND (CLASSTAB.INTL_ID LIKE LOWER ('arg1')
OR CLASSTAB.INTL_ID LIKE LOWER ('arg2'))
AND MAINTABLE_MKT.IS_MOST_LIQUID = 1
AND MAINTABLE_MKT.PRIO = 1
AND MAINTABLE_mkt.End_Date is Null
and MARKET.IS_TRADE_PLACE = 1
AND MAINTABLE_COMPO_MAIN.END_DATE IS NULL
AND MAINTABLE_COMPO_INTR.END_DATE IS NULL
and PRICETAB.price_class_id = 1)
WHERE Currency In (SELECT CURR_ID FROM TABLE1)
and MaturityDate>=EndTime
============================
As you can see, there are 8 (!) inner joins which have to be made. They have pretty different costs, so I guess the join order is important. Here is the respective explain plan to the above query:
============================
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 43
SORT AGGREGATE 1 129
HASH JOIN SEMI 1 129 43
NESTED LOOPS 1 116 40
NESTED LOOPS 1 106 38
NESTED LOOPS 1 96 36
NESTED LOOPS 1 88 35
NESTED LOOPS 2 144 31
NESTED LOOPS 14 784 3
MERGE JOIN CARTESIAN 14 714 3
TABLE ACCESS BY INDEX ROWID PKG.CLASSTAB 1 19 2
NESTED LOOPS 1 39 3
INDEX FULL SCAN PKG.IX_CLASSTABIF_2 1 20 1
INLIST ITERATOR
INDEX RANGE SCAN PKG.IX_CLASSTAB_1 1 1
BUFFER SORT 1 12 1
INDEX FULL SCAN PKG.UQ_PRICE_1 1 12
INDEX UNIQUE SCAN PKG.PK_MAINTABLE 1 5
TABLE ACCESS BY INDEX ROWID PKG.MARKET 1 16 2
INDEX RANGE SCAN PKG.IX_MAINTABLE_MKT_1 5 1
TABLE ACCESS BY INDEX ROWID PKG.MAINTABLE_COMPO_MAIN 1 16 2
INDEX RANGE SCAN PKG.IX_MAINTABLE_COMPO_MAIN_2 1 1
TABLE ACCESS BY INDEX ROWID PKG.MARKET 1 8 1
INDEX UNIQUE SCAN PKG.PK_MARKET 2
TABLE ACCESS BY INDEX ROWID PKG.MAINTABLE_COMPO_INTR 20 K 201 K 2
INDEX RANGE SCAN PKG.IX_MAINTABLE_COMPO_INTR_1 1 1
INDEX RANGE SCAN PKG.IX_CLASSINST_1 1 10 2
TABLE ACCESS FULL PKG.TABLE1 409 5 K 2
This query takes between 45 seconds to 3 minutes. The most expensive joins happen between MAINTABLE - CLASSINST and CLASSINST - CLASSTAB.
At least I think they are the most expensive because the joined tables return about 2 500 000 rows. Without these two joins the whole query takes only about 15 to 20 seconds.
Is there a way to make this faster? Does it make sense to split the sql-query in more than one statement?
Every help is appreciated, thank you,
sebastian
|
|
|
|
| Re: SQL Query with many joins [message #139760 is a reply to message #139721] |
Thu, 29 September 2005 08:50  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Do you have statistics on all of these tables? It doesn't look like it, because every line is showing only 1 row, but I'm just taking a wild guess. And what is your version? And is that a cartesian product you are doing? Are you forgetting a join condition? Also you want the optimizer mode for at least this query to be all rows.
I would write a query like this in a totally different style, which isn't necessarily better or worse just how I "see" it. But, often if you have one or two large tables slowing things down you can put them into inline views and apply any restrictive where conditions on them inside the view, so that the result that gets combined with the outer query has far far fewer rows to join.
In general you will be worse off by separating it into multiple queries, typically if you can do it in a single sql operation you should.
|
|
|
|
|
|
| Re: SQL Query with many joins [message #139805 is a reply to message #139721] |
Thu, 29 September 2005 12:06 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
It is all about the statistics. The whole point of the optimizer is to make your statement go as fast as it can. The only way for it to do its jobis for it to have info about the data.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
