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

Hi.

 Is it possible for you to create an additional index

  CREATE INDEX CROSS_X ON CROSS ( "INCLUDE", "TYPE" );  Michael.

In article <vpHk3.12250$b21.2020_at_newreader.ukcore.bt.net>,   "Kevin A Lewis" <KevinALewis_at_Hotmail.com> wrote:
> 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.
> >
> >
> >
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jul 19 1999 - 14:48:49 CDT