Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why FULL TABLE SCAN ?

Re: Why FULL TABLE SCAN ?

From: Turkbear <john.g_at_dot.spamfree.com>
Date: Fri, 11 Jun 2004 10:38:00 -0500
Message-ID: <v9kjc0pfag6ansjac3oenc0iv19e4jdvbl@4ax.com>


"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" )
>

Assuming the stats for the tables involved are up to date ( recently analyzed ) - the CBO has determined that a full table scan is most efficient..Perhaps the % of all rows returned sufficient to make this so. Received on Fri Jun 11 2004 - 10:38:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US