Home » SQL & PL/SQL » SQL & PL/SQL » Optimisation problem
Optimisation problem [message #195381] Thu, 28 September 2006 05:50 Go to next message
Pri123
Messages: 5
Registered: September 2006
Junior Member
Hi all, i have a statmt which does a delete and is taking too much time...
is their any way i can optimise this piece of code?

delete /*+ PARALLEL(tabx,4) */
from tabx
where f_no in (select f_no from tab_y)
and l_seq in (select l_seq from tab_y )

tab_x has 400,000 rows
the second tab in the subquery has 130,000 rows

here is the xplain plan

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | TQ |IN-OUT| PQ Distrib |
| 0 | DELETE STATEMENT | | 92025 | 1617K| | 2267 | | | |
| 1 | DELETE | TAB_X | | | | | | | |
| 2 | HASH JOIN SEMI | | 92025 | 1617K| 896K| 2267 | 07,04 | P->S | QC (RAND) |
| 3 | HASH JOIN SEMI | | 140K| 1917K| 1832K| 1524 | 07,03 | P->P | HASH |
| 4 | TABLE ACCESS FULL| TAB_X | 356K| 3135K| | 756 | 07,02 | P->P | HASH |
| 5 | TABLE ACCESS FULL| TAB_Y | 112K| 549K| | 710 | 07,00 | S->P | HASH |
| 6 | TABLE ACCESS FULL | TAB_Y | 103K| 404K| | 710 | 07,01 | S->P | HASH |

[Updated on: Thu, 28 September 2006 06:03]

Report message to a moderator

Re: Optimisation problem [message #195437 is a reply to message #195381] Thu, 28 September 2006 10:50 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
It could be the IO releated to cleaning up all the indexes on the table where the delets occur. Try this (if it's really what you really mean - they aren't equivalent statements!)

where (f_no, l_seq) in (select f_no, l_seq from tab_y)

Re: Optimisation problem [message #195463 is a reply to message #195437] Thu, 28 September 2006 18:17 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Of course you've already made sure:
- No dependent Foreign Key constraints
- No BEFORE/AFTER DELETE triggers
- Not being locked out by another user.


There's two parts to this:
- Finding the rows to delete
- Deleting them

If you run the query:

SELECT max(some non-indexed column)
from tabx
where f_no in (select f_no from tab_y)
and l_seq in (select l_seq from tab_y )


That will give you a rough idea of how long it takes to FIND the rows. I would think it should take no longer than 1 minute (probably more like 10-20sec) based on the sizes you provided. Now you can tell how long the DELETE is taking by subtracting this time from the total DELETE time.

How slow is it at the moment? If the SELECT above is acceptably fast, then there is not much YOU can do to speed it up. You would need to take it up with your DBA to see whether the Database Writer and Log Writer are performing acceptably.

Ross Leishman
Re: Optimisation problem [message #195619 is a reply to message #195463] Fri, 29 September 2006 13:25 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
how about:
delete /*+ PARALLEL(x,4) */
from tabx x
where exists (select null from tab_y y where y.f_no = x.f_no)
and exists (select null from tab_y y where y.l_seq = x.l_seq )
Re: Optimisation problem [message #195758 is a reply to message #195619] Mon, 02 October 2006 03:51 Go to previous messageGo to next message
Pri123
Messages: 5
Registered: September 2006
Junior Member
where (f_no, l_seq) in (select f_no, l_seq from tab_y)
this one does not give the same result
but this one does...

delete /*+ PARALLEL(x,4) */
from tabx x
where exists (select null from tab_y y where y.f_no = x.f_no)
and exists (select null from tab_y y where y.l_seq = x.l_seq )

but are they logically the same?
delete /*+ PARALLEL(x,4) */
from tabx x
where exists (select null from tab_y y where y.f_no = x.f_no)
and exists (select null from tab_y y where y.l_seq = x.l_seq )

delete /*+ PARALLEL(x,4) */
from tabx x
where f_no in (select f_no from tab_y )
and l_no in (select l_no from tab_y )

thnks
Re: Optimisation problem [message #195764 is a reply to message #195758] Mon, 02 October 2006 04:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:

where (f_no, l_seq) in (select f_no, l_seq from tab_y)
this one does not give the same result

Obviously won't give the same result as
Quote:

where f_no in (select f_no from tab_y)
and l_seq in (select l_seq from tab_y )

The first requires that there be a single row in TAB_Y with the value of f_no and l_seq on it, whereas the second only requires that the values f_no and l_seq each exist on seperate rows in TAB_Y.

Re: Optimisation problem [message #195798 is a reply to message #195758] Mon, 02 October 2006 08:51 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
yes, the IN and NOT EXISTS statements are logically the same.
Re: Optimisation problem [message #195801 is a reply to message #195798] Mon, 02 October 2006 09:08 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
IN and EXISTS surely....
Previous Topic: pattern matching of table names
Next Topic: How to convert the data?
Goto Forum:
  


Current Time: Sat Dec 03 05:48:32 CST 2016

Total time taken to generate the page: 0.11657 seconds