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

Ok, thanks I will give it a go

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:7n2lih$76p$1_at_nnrp1.deja.com...
> 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 Wed Jul 21 1999 - 05:45:15 CDT