Explain Plan question

From: Cheng-Jih Chen <cjc_at_interport.net>
Date: 3 Sep 1998 15:08:41 -0400
Message-ID: <6smpfp$g6j$1@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:08:41 CDT