Home » RDBMS Server » Performance Tuning » Long Running SQL - Oracle
Long Running SQL - Oracle [message #235902] Tue, 08 May 2007 06:24 Go to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Hi,

Wondering i have got below sql is running forever,takes long time, it has nearly 10 to 12 tables, some of them have 100,000 / 500,000 rows, some of them have few hundreds.

Platform - Unix , sun solaris
Oracle : 9.2.0.7

SQL is like this


INSERT INTO T VALUES(.....) 
SELECT (......) 
FROM
(SELECT /+* parallel() */.....
  FROM
  WHERE d.field = 'aaa'
        a.id = b.id(+)
        a.field2 = b.field2
        a.date >= c.date
)ip,
(select  /+* parallel() */.....
  FROM p, q, r, (select /+* parallel() */... from m where ....)s
  WHERE p.field = q.field
        r. id = p. id
        s.date > r.date
 )sp

where ip.id = sp.id(+)
      ip.date >= sp.date
      .....


Any suggestion how i can above query efficient.

Thanks
Sam
Re: Long Running SQL - Oracle [message #235920 is a reply to message #235902] Tue, 08 May 2007 07:25 Go to previous messageGo to next message
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
Member
Hi,

Try using the subquery factoring i.e the usage of with clause for all the inner selects.

Thanks
Re: Long Running SQL - Oracle [message #235921 is a reply to message #235920] Tue, 08 May 2007 07:27 Go to previous messageGo to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Thanks Sriram,

Can you please give me some example.

Thanks
Re: Long Running SQL - Oracle [message #235933 is a reply to message #235921] Tue, 08 May 2007 07:53 Go to previous message
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
Member
Hi

Read Oracle application developers Guide 9i.

Also to get a concise view on the same visit

http://jonathanlewis.wordpress.com/2006/11/06/filter-subqueries/#more-27
Previous Topic: insufficient privileges when generating explain plan for a synonym
Next Topic: Re-creating partitioned tablespaces from backup.
Goto Forum:
  


Current Time: Sun Dec 04 00:38:48 CST 2016

Total time taken to generate the page: 0.12433 seconds