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

Hi.

 I think that the additional cost of another index  is negligent compared to gain in retrieval :

  You have 344K entries in your index and all of them are scanned now.   With a new index the average number of scanned records will be :   344K / 25K( number of distinct values of INCLUDE field ) /

     14 (number of distinct values of TYPE field )= 1 row

  The overhead for additional index while deleting 1 row ...

  I would try it.

  Good luck. Michael.

In article <IQVk3.7191$F72.446_at_newreader.ukcore.bt.net>,   "Kevin A Lewis" <KevinALewis_at_Hotmail.com> wrote:
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jul 20 1999 - 15:18:40 CDT