Home » RDBMS Server » Performance Tuning » Optimize large SQL query with multiple joins
Optimize large SQL query with multiple joins [message #287485] Wed, 12 December 2007 06:28 Go to next message
ramya.pathak
Messages: 15
Registered: August 2007
Junior Member
Hi all,

Am using a big SQL query in order to retrieve my data in a procedure.
The query is like:

SELECT A.COL1,.....,K.COL100
FROM A,B,C,D,E,F,G,H,I,J,K
WHERE A.KEY = B.KEY (+)
AND A.KEY = C.KEY (+)
AND A.KEY = D.KEY (+)
AND A.KEY = E.KEY (+)
AND A.KEY = F.KEY (+)
AND A.KEY = G.KEY (+)
AND A.KEY = H.KEY (+)
AND A.KEY = I.KEY (+)
AND A.KEY = J.KEY (+)
AND A.KEY = K.KEY (+)


'A' is the main table. Parent table of all.
Table 'A' contains around 150,000 records.
The query is taking too long to run.
Any way I can optimize the query..??
Re: Optimize large SQL query with multiple joins [message #287493 is a reply to message #287485] Wed, 12 December 2007 06:38 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

1. Do have any index on joining tables ?
2. can you post here execution plan , autotrace output.
Re: Optimize large SQL query with multiple joins [message #287511 is a reply to message #287485] Wed, 12 December 2007 07:52 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
If (as I'm suspecting) you have 1:M relationship between A and all other tables, then for each line of A your "resultset"
will contain
M(B) * M(C) * M(D) * M(E) * M(F) * M(G) * M(H) * M(I) * M(J) * M(K) 

rows.

Michael

Re: Optimize large SQL query with multiple joins [message #287513 is a reply to message #287493] Wed, 12 December 2007 08:08 Go to previous messageGo to next message
ramya.pathak
Messages: 15
Registered: August 2007
Junior Member
Mohammad,

Yes the indexes are there. The execution plan is attached.

Micheal,
The relationship is mostly 1:1 except for a few places but some columns from the child tables are required.

[Updated on: Thu, 13 December 2007 00:46]

Report message to a moderator

Re: Optimize large SQL query with multiple joins [message #287516 is a reply to message #287513] Wed, 12 December 2007 08:25 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Statistics
----------------------------------------------------------
        154  recursive calls
         26  db block gets
    2486906  consistent gets
      58980  physical reads
          0  redo size
       4842  bytes sent via SQL*Net to client
       1428  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
          9  rows processed


1. According to me your execution plan is correct, but wait someone more exprience/expert member will advice you.

2. query makes lots of consistent gets and also sort in disk.
it seems your buffer cache and pga is small.
can you post here your sga configuration.

Re: Optimize large SQL query with multiple joins [message #287567 is a reply to message #287485] Wed, 12 December 2007 12:54 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I cannot see your query plan, the file must be missing.

However, if your query is truly like this, you are looking at all rows in all tables subject to the correct joins, then using indexes (and presumably nested loops) would likely be a real bad thing.

I suggest you enable hash joins (may require the setting of multiple parameters so do some research), compute statistics on all the tables involved (dbms_stats... cascade=>true), and then see if the query plan shows full table scans and hash joins. This would be much faster than nested loop via index lookups, especially if there is more than just a few rows in these tables. You should look for improvement somewhere between 2 and 10 times faster depending upon the number of rows being joined.

As always, your mileage may vary.

Good luck, Kevin
Re: Optimize large SQL query with multiple joins [message #287584 is a reply to message #287485] Wed, 12 December 2007 15:24 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1.
Quote:

except for a few places


may mean a lot of rows...

2. IMHO - Kevin is right. You need to exchange nested loop joins for hash joins.

Michael
Re: Optimize large SQL query with multiple joins [message #287597 is a reply to message #287584] Wed, 12 December 2007 20:27 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The plan contains a COUNT STOPKEY line, which means that query contains a ROWNUM clause somewhere. Your original query does not contain a ROWNUM clause, so this tells me the Explain Plan is for a different query.

Post the REAL explain plan.

Ross Leishman
Re: Optimize large SQL query with multiple joins [message #287651 is a reply to message #287597] Thu, 13 December 2007 01:12 Go to previous message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Consider about
Quote:


Statistics
----------------------------------------------------------
154 recursive calls
.....



Post your TKPROF or Trace(Explain or Only) here. I guess the executions << parse
Previous Topic: takes more time to open lov
Next Topic: This query make much recursive call
Goto Forum:
  


Current Time: Wed Dec 07 16:19:56 CST 2016

Total time taken to generate the page: 0.07987 seconds