Re: Why FULL TABLE SCAN ?

"Turkbear" <john.g_at_dot.spamfree.com> schrieb im Newsbeitrag news:v9kjc0pfag6ansjac3oenc0iv19e4jdvbl_at_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.
>

Yes, stats are up to date. How can a full table scan be better than using the index ? Isnt using an index ALWAYS more efficient if it is over the column being selected (here: MSN) ?

Also, in a previous version of my data model the partitioned table was not partitioned, I used RBO then and of course the statement was always using the index then... do I have to mention that it was much faster then... with exactly the same data in it?! Received on Fri Jun 11 2004 - 10:47:04 CDT