Re: Delete based on rowid
- From: DBA Deepak <oracle.tutorials@xxxxxxxxx>
- To: Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx>
- Date: Mon, 5 Oct 2009 16:17:49 +0530
Thanks for the quick response.
Yes the trace file shows that an index full scan is done for the said DELETE
statement.
Ideally Oracle should not have done full index scan considering the
steps/logic provided by you.
Another question comes to my mind after going through your response is:
Which method is faster/efficient?
1> Deletion based on rowid
or
2> deletion based on index values
My application can provide the index values for the deletion as well.
After examining the explain plan I found that the 2nd method above has less
cost associated with it.
I had the impression till now that DML operations based on the rowids are
the fastest.
On Mon, Oct 5, 2009 at 4:10 PM, Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx
> wrote:
> Am asking this because in my case Oracle is doing a full index scan
>>
>
> Can you elaborate on that? Is the explain plan (or better tracefile) for
> that delete statement showing a full index scan?
>
> I would expect the following to happen:
> 1) Oracle fetches the row directly using the rowid supplied in the delete
> statement.
> Then,
> 2) Oracle reads all indexed column values of the fetched row, and uses
> these to delete the entries in the corresponding indexes.
> 3) Oracle deletes the row from the table.
>
>
> On Mon, Oct 5, 2009 at 12:34 PM, DBA Deepak <oracle.tutorials@xxxxxxxxx>wrote:
>
>> Have a simple question.
>>
>> When we delete a row based on rowid from an indexed table, how the index
>> entry gets deleted?
>>
>> DELETE t WHERE rowid='<some rowid>';
>>
>> Am asking this because in my case Oracle is doing a full index scan. Can
>> we say deleting an indexed table based on rowid may not be the fastest way
>> of deleting a row?
>>
>> Please comment...
>>
>
>
>
> --
> Toon Koppelaars
> RuleGen BV
> +31-615907269
> Toon.Koppelaars@xxxxxxxxxxx
> www.RuleGen.com <http://www.rulegen.com/>
> TheHelsinkiDeclaration.blogspot.com<http://thehelsinkideclaration.blogspot.com/>
>
> (co)Author: "Applied Mathematics for Database Professionals"
> www.RuleGen.com/pls/apex/f?p=14265:13<http://www.rulegen.com/pls/apex/f?p=14265:13>
>
>
--
Regards,
Deepak
Oracle DBA
Other related posts:
