Re: Question

"Tim" <df_at_sd.net> wrote in message
news:5djg1vk6n61a19pu2m2rgacqd4snfdvsck_at_4ax.com...
> On Sat, 4 Jan 2003 14:20:33 +0100, "Pier Gaetano Novara"
> <pgaetanoNoSpam_at_csc.com> wrote:
>
> >I have this statement:
> >DELETE FROM
> > TMP_TABLE_MERGE
> >WHERE EXISTS
> > (SELECT 1 FROM
> > TMP_TABLE_1 T1
> > WHERE
> > TMP_TABLE_MERGE.OBJECT_ID = T1.OBJECT_ID)
> >
> >In both the table TMP_TABLE_MERGE and TMP_TABLE_1 the field OBJECT_ID is
the
> >primary key but in the Explain Plan the table TMP_TABLE_MERGE is acessed
in
> >full scan mode.
> >Do someone knows how can I force the use of the primary keys?
> >Thanks
> >Pier
> >
>
> What percentage of the rows in TMP_TABLE_MERGE match the WHERE
> condition? If it's greater than 15% or so, it may be more efficient
> to do a table scan than to use the index. Make sure you run analyze
> statistics if using CBO also.

Hi Tim/Pier,

Couple of little points.

Firstly, the percentage of rows that need to be accessed from the TMP_MERGE_TABLE is going to be somewhat more than 15%. It's going to be 100%. The sub-select is a correlated subquery and as there are no additional predicates after it, Oracle has no choice (as the statement is currently written) but to read all rows in the outer query in order for it to determine if a corresponding row matches the inner query.

Secondly, I have a little shudder when someone mentions a percentage of *rows* when discussing what the CBO might or might not do. Oracle may decide to use a FTS if it requires 0.01% of rows, it might decide to use an index if it requires 99.9% of rows. The percentage of rows has nothing to do per se with how the optimizer decides to strut it's stuff. The percentage of blocks that need to be accessed is a touch more accurate but even such analysis is mis-leading. It's a little general I know but it really is the *cost* (hence the optimizer's name) of the various possible access paths that is the determining factor and it's simple issues such as the number of rows per block, the fragmentation of the segments, the perceive efficiencies of sorts, multiblock reads, index caching, parallelism etc. that makes the percentage of rows rule(s) of thumb such a dangerous one to go by.

What are these costs ? This is a whole whopper of a discussion but CPU, I/O, network overheads are some components of the mix and for those lucky enough to go to Jonathon Lewis's IOUG-A class, I'm sure will have all the juicy details revealed (note this plug attracts a 10% commission ;)

Cheers

Richard Received on Mon Jan 06 2003 - 06:21:40 CST