Home » SQL & PL/SQL » SQL & PL/SQL » Optimize the query (9.0.4.0)
Optimize the query [message #336166] Fri, 25 July 2008 01:34 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I wrote the following query. It's taking more than 20 mins for execution. So please optimize the following query. The query contains Insert and delete statements. If I execute Delete first it is executed in 2 mins.If I execute the delete statement after Insert It's taking 20 mins. But I need to execute the delete statement after Insert only.

   delete from multi_tier2_item amti2
    where exists (
                   select item
                     from (
                            select *
                              from (
                                   select * 
                                     from (
                                            select nco.commodity_id,cd.commodity_code item,substr(sp.stocking_point_name,2,4) loc,                                       sp.tier,nco.timestamp,nco.stocking_point_id
from n_contents_order nco,commodity cd,stocking_point sp,MULTI_TIER_WH mtw
where nco.commodity_id=cd.commodity_id                                and nco.status<>'C'
and nco.order_type='P'
and nco.stocking_point_id  = sp.stocking_point_id   
and substr(sp.stocking_point_name,2,4) = mtw.TIER1_VIRTUAL_WH
union
select nco.commodity_id,cd.commodity_code item,substr(sp.stocking_point_name,2,4) loc,
                                          sp.tier,nco.timestamp,nco.stocking_point_id
from n_contents_order nco,commodity cd,stocking_point sp,MULTI_TIER_WH mtw
where nco.commodity_id=cd.commodity_id
and nco.status<>'C'
and nco.order_type='P'
and nco.stocking_point_id  = sp.stocking_point_id   
and substr(sp.stocking_point_name,2,4) = mtw.TIER2_VIRTUAL_WH
) nco_outer1
where  nco_outer1.timestamp=(
select max(nco1.timestamp)
from n_contents_order nco1
where nco1.commodity_id=nco_outer1.commodity_id
and nco1.status<>'C'
and nco1.order_type='P'
and nco1.stocking_point_id = nco_outer1.stocking_point_id
)
) nco_outer2
where nco_outer2.tier=1
and  exists (
select item 
from multi_tier2_item amti1
where nco_outer2.item =amti1.item
)
) nco_outer3
where nco_outer3.item =amti2.item
);


Thank you.
Re: Optimize the query [message #336178 is a reply to message #336166] Fri, 25 July 2008 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Almost 300 posts and you still don't know how to post.

Regards
Michel
Re: Optimize the query [message #336181 is a reply to message #336178] Fri, 25 July 2008 02:19 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,

Have posted in proper manner right? But bit spaces in starting...
Re: Optimize the query [message #336190 is a reply to message #336181] Fri, 25 July 2008 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Have posted in proper manner right?

No. Read guide, read performances forum and you will know what you have to post.

Regards
Michel
Re: Optimize the query [message #336191 is a reply to message #336190] Fri, 25 July 2008 02:35 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,

Please give me any idea for this..because it's my P1 Issue.I will go through the guide after completing this.I know this is not ru\ight way to ask in this way.. But please look into this..

Thank you
Re: Optimize the query [message #336200 is a reply to message #336191] Fri, 25 July 2008 03:12 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Even if your life was in danger and you give million dollars noone can answer without information and these information you have to give you should now know them by heart with the number of posts you made.

Regards
Michel

Previous Topic: BULK COLLECT VS CURSORS
Next Topic: PL/SQL table VS Nested Table
Goto Forum:
  


Current Time: Sun Dec 11 02:33:35 CST 2016

Total time taken to generate the page: 0.14290 seconds