Re: Why FULL TABLE SCAN ?

André Hartmann wrote:

> Hi folks,
>
> i have a problem with the way Oracle executes one of my SQL statements on
> a partitioned table. It uses a full table access even though in my point of
> view the CBO could perfectly well use an index to accelerate things. But it
> doesnt. So I am going to post my statement and the schema information about
> the table and index here, hoping that someone may find the time to go
> through it and tell my why my index is not being used and how I can speed
> things up.
>
> As a preview: Basically, what I have, is a partitioned table
> SG_TracedNodes_p (for the moment just 1 partition) and a local index
> SG_TracedNodes_p_I_MSN on a particular column (MSN, which appears in the
> WHERE clause of my DELETE statement and which happens to be also a foreign
> key to another table called SG_MSN where it points to a primary key
> there.)... so I want the statement to use my index
>
> Thanks for that in advance.
> AH
>
> 1. SQL Statement:
> ***************
> DELETE FROM "SG_TracedNodes_p" PARTITION (p0) WHERE "Partition" = 0 AND msn
> = :1
> (PS: I tried alternative statements in which I omitted the "Partition"=0
> and/or the PARTITION (p0) clause... no change! still full table scan!)
>
> 2. Execution Plan created by Oracle:
> ****************************
> COST ALL ROWS (Optimizer: CHOOSE)
> total cost:923
> 1. HARTMANN_SCHEMGEN_ARCHIVERTEST.SG_TracedNodes_p TABLE ACCESS [FULL]
> (estimated cost: 923, estimated rows returned: 151.172, estimated KB
> returned: 3.247,836
> 2. DELETE
> 3. DELETE STATEMENT (estimated cost: 923, estimated rows returned: 151.172,
> estimated KB returned: 3.247,836
>
> 3. Definition of table SG_TracedNodes_p:
> *********************************
> CREATE TABLE "SG_TracedNodes_p"
> ( "MSN" NUMBER(*,0) NOT NULL ENABLE,
> "NodeIdentifier" VARCHAR2(1000),
> "BIP_Identifier" NUMBER(*,0),
> "ConnectedComponent" NUMBER(*,0),
> "Reason" NUMBER(*,0),
> "Plug" CHAR(4),
> "Pin" CHAR(6),
> "ShuntCode" CHAR(7),
> "Trimmed" VARCHAR2(4000) NOT NULL ENABLE,
> "Partition" NUMBER(1,0) NOT NULL ENABLE,
> CONSTRAINT "SG_TracedNodes_p_FK_MSN" FOREIGN KEY ("MSN")
> REFERENCES "SG_MSN" ("Id") ON DELETE CASCADE ENABLE
> ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
> STORAGE(
> BUFFER_POOL DEFAULT)
> TABLESPACE "SCHEMGEN_Tbl"
> PARTITION BY RANGE ("Partition")
> (PARTITION "P0" VALUES LESS THAN (1) PCTFREE 10 PCTUSED 40 INITRANS 1
> MAXTRANS 255
> STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
> TABLESPACE "SCHEMGEN_Tbl" NOCOMPRESS )
>
> 3. Definition of index SG_TracedNodes_p_I_MSN:
> ****************************************
> CREATE INDEX "SG_TracedNodes_p_I_MSN" ON "SG_TracedNodes_p" ("MSN")
> PCTFREE 10 INITRANS 2 MAXTRANS 255
> STORAGE(BUFFER_POOL DEFAULT)LOCAL (PARTITION "P0" PCTFREE 10 INITRANS 2
> MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
> 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
> TABLESPACE "SCHEMGEN_Idx" )
>
>

It might have to do with the use of the :bind variable and your version of the DB.
IIRC, only V9.2 does something which I think is called bind peak ahead; or something like that. Also if the datatype the bind variable does not match the datatype of the column, the index won't be used. Received on Fri Jun 11 2004 - 20:23:51 CDT