Re: Delete on FULL INDEX SCAN - is it inefficient ??

Are column_name1 and column_name2 the leading columns of the primary key, and is it possibly for Oracle to get a reasonable estimate of the fraction of the table to be deleted by this SQL ?

The path is NOTIONALLY a reasonable one for a given fraction of the data being deleted and a given scattering of that data across the table.

Oracle will have an (unexpected) bias to using the index because the index is presumably larger than the table, so it can easily make sense to scan the entire index, then access the table by rowid to delete the row rather than scanning the table then searching into the index to find the index entry for each row to be deleted.

I think you will find that Oracle is assuming that a reasonably large percentage of the table is being deleted, and that the data scatter is high.

You could try hinting a different path and seeing the difference in performance - you may find that logical I/O goes up and physical I/O drops.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Kevin A Lewis wrote in message
<4uDk3.4378$b21.968_at_newreader.ukcore.bt.net>...
>I have a system (SAPr3) with a table CROSS which is regularly issued with a
>statement
>
>DELETE CROSS where column_name1 = 'abcd' and column_name2 = '1234';
>
>The table has 4 columns total which are all part of the primary key and it
>has an index on one column as well.
>
>This delete performs like a dog and is explained as being accessed by a
FULL
>INDEX SCAN.
>
>What I wondered was if there were performance issues with doing a delete
>when the access is be FULL INDEX SCAN. Surely the data has to be accessed
>for a delete so such a scan is inherently inefficient.
Received on Mon Jul 19 1999 - 06:12:47 CDT