Re: Why FULL TABLE SCAN ?

Tony Dare <tonydare_at_yahoo.com> wrote:

>Turkbear wrote:
>> "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...
>>
>>
>>
>>
>>
>>
>actually - the index is used to probe the table only if the query is not
>satisifed by the data in the index itself. If all the required data is
>in the index, the table is never used. However, that doesn't seem to
>apply in this case...
>
>Oh, wait - the stats on the table may be current, but did you compute
>statistics on the index? RBO uses indexes by design, CBO of course
>examines the stats on the tables and indexes and makes the best
>decision it can. If the statistics do not exist for the index, the
>optimizer may be "thinking" that a full table scan is best.
>
>
>TD

Right..I meant to imply that by citing the IOT, but I should have been more specific.

I had completely forgotten about analyzing the index ( and even, if much has been added since built, rebuilding it)

Thanks for the 'catch' Received on Fri Jun 11 2004 - 12:57:45 CDT