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

I could

But surely that would make the deletion activity itself less efficient ! I can see what you are getting at, improving the selectivity of the access to improve the route to the data before the delete.

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. <michael_bialik_at_my-deja.com> wrote in message news:7mvver$64d$1_at_nnrp1.deja.com...
> 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 Tue Jul 20 1999 - 02:53:39 CDT