Re: Why FULL TABLE SCAN ?

Read up on parameters

optimizer_index_caching              
optimizer_index_cost_adj             

default values might not be optimal for you.

On Fri, 11 Jun 2004 17:11:40 +0200, "André Hartmann" <andrehartmann_at_hotmail.com> 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" )
>
>

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Fri Jun 11 2004 - 13:51:24 CDT