Home » SQL & PL/SQL » SQL & PL/SQL » optimisation prob
optimisation prob [message #195387] Thu, 28 September 2006 06:17 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 |

Re: optimisation prob [message #195396 is a reply to message #195387] Thu, 28 September 2006 06:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If f_no and l_seq have relatively few values in tab_y, you could try creating indexes on them and rewrite the query as

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

Hopefully that would convert the full table scans on tab_y into index scans.

You could also try indexing f_no and l_seq on tab_x.
Re: optimisation prob [message #195401 is a reply to message #195396] Thu, 28 September 2006 07:23 Go to previous messageGo to next message
Pri123
Messages: 5
Registered: September 2006
Junior Member
Thnks

could i use exist?
something like


select count(*)
from tab_x fs
where exists (select f_no from tab_y where f_no = fs.f_no)
and exists (select l_seq from tab_y where l_seq = fs.l_seq)

Re: optimisation prob [message #195402 is a reply to message #195401] Thu, 28 September 2006 07:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How many rows are you anticipating being deleted from tab_x?
Re: optimisation prob [message #195759 is a reply to message #195402] Mon, 02 October 2006 03:54 Go to previous message
Pri123
Messages: 5
Registered: September 2006
Junior Member
more than half the table...
250 000 to 300 000
Previous Topic: TYPE VARRAY - Insert
Next Topic: Stored Procedure Performance ..
Goto Forum:
  


Current Time: Wed Dec 07 08:37:57 CST 2016

Total time taken to generate the page: 0.07284 seconds