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

Details of the primary key index as fully scanned

     UNIQUE Index CROSS__0

     Column Name                         #Distinct

     TYPE                                          14
     NAME                                       7,141
     PROG                                         118
     INCLUDE                                   24,926

     Last statistics date                  19.07.1999
     Analyze Method                      Estimate 10%
     Levels of B-Tree                               2
     Number of leaf blocks                      1,849
     Number of distinct keys                  344,130
     Average leaf blocks per key                    1
     Average data blocks per key                    1
     Clustering factor                         91,883

the column_name1 is in fact the TYPE column above and column_name2 is in fact the INCLUDE column metioned above. I would think that the number of rows to be delete should be less than one percent.

Regards

--
Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich England)

                        <KevinALewis_at_HotMail.com>

The views expressed herein by the author of this document are not necessarily those of BOCM PAULS Ltd. Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message news:932383511.7332.0.nnrp-07.9e984b29_at_news.demon.co.uk...
>
> 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 - 10:28:50 CDT