arjan wrote:
> Now that sounds all easy then, AnaCDent. I am sure explain plan will help as well.
Not in all cases. E.g. when You use bind variables, the explain plan
command may give You false output (the used explain plan may be other
than that showed by explain plan command).
>
> AnaCDent <anacedent_at_hotmail.com> wrote in message news:<%_Hcc.39$Wo6.28_at_fed1read03>...
>
>>arjan wrote:
>>
>>>Hi there,
>>>
>>>I have a delete sql that runs too slow (around 15 mins). I would like
>>>to improve it.
>>>
>>>It looks like this:
>>>
>>> delete from table_a a
>>> where exists (select *
>>> from table_b b
>>> where b.columnX = a.columnX
>>> and b.columnY = a.columnY)
>>>
>>>There are two indexes. IndexA on table_a(columnX,columnY) and IndexB
>>>on table_b(columnX,columnY).
>>>
>>>Looking at the statement in explain plan, it shows that it's doing
>>>full scans on both tables. It's not picking up an index hint like
>>>this:
>>>
>>> delete /*+ INDEX(a IndexA) */ from table_a a
>>> where exists (select *
>>> from table_b b
>>> where b.columnX = a.columnX
>>> and b.columnY = a.columnY)
>>>
>>
>>Stop throwing darts in the dark.
>>Get facts before proceeding.
>>Otherwise all you are doing is....
>>
>>Ready, Fire, Aim!
>>
>>ALTER SESSION SET SQL_TRACE=TRUE
>>DELETE....
>>
>>then go run TKPROF & actually see where Oracle is spending all the time.
Received on Wed Apr 07 2004 - 05:52:29 CDT
