| optimisation prob [message #195387] |
Thu, 28 September 2006 06:17  |
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  |
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 |
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)
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
