Re: Explain Plan question

It's possible that the statistics for this table indicate that the rows with src_dbname = 'ADVTCM' comprise a significant percentage of the table's rows (say, 20% or more). In that case, a full table scan will be faster than index scans and ROWID lookups into the table.

The reason the SELECT COUNT(*) is using the index is because it doesn't really need any _data_ from the rows, just a count of the number of rows, so the index is always the fast way to fill that query.

Cheng-Jih Chen wrote in message <6smpfp$g6j$1_at_interport.net>...
>
>Hi, quick question on explain plan results.
>
>I have this largish table against which the following delete is
>executed:
>
> delete from top_line_link where src_dbname = 'ADVTCM';
>
>The explain plan for this gives:
>
> DELETE STATEMENT
> TABLE ACCESS FULL TOP_LINE_LINK
>
>There is, however, an index on this table that has src_dbname at the
>beginning:
>
>Table INDEX_NAME COLUMN_NAME
>--------------- ------------------------------ ----------------------------
--
>TOP_LINE_LINK IX1TLL SRC_DBNAME
>TOP_LINE_LINK IX1TLL SRC_ATOM_ID
>
>Also, explain plan for
>
> select count(*) from top_line_link where src_dbname = 'ADVTCM';
>
>gives:
>
> SELECT STATEMENT
> SORT AGGREGATE
> INDEX RANGE SCAN IX1TLL
>
>so I'm sure Oracle knows about the index.
>
>This is Oracle 7.3.3.3 using the cost-based optimizer.
>
>Why is the DELETE doing a full table scan? Thanks.
>
Received on Thu Sep 03 1998 - 14:56:08 CDT