Re: Why FULL TABLE SCAN ?

"André Hartmann" <andrehartmann_at_hotmail.com> wrote:

>
>"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?!
>

No, actually using an index in some situations can be slower ; ( remember each use of an index involves at least 2 reads, one from the index, one from the data itself - unless it is an IOT [ index organized table] )..
That is why the CBO checks to see how many rows are involved ( and other factors ) and makes its decision...

As to the better performance with RBO, too many variables are involved to determine why.

Others can probably give better reasons why... Received on Fri Jun 11 2004 - 11:27:13 CDT