Why FULL TABLE SCAN ?

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
3. HARTMANN_SCHEMGEN_ARCHIVERTEST.SG_TracedNodes_p TABLE ACCESS [FULL] (estimated cost: 923, estimated rows returned: 151.172, estimated KB returned: 3.247,836
4. DELETE
5. DELETE STATEMENT (estimated cost: 923, estimated rows returned: 151.172, estimated KB returned: 3.247,836
6. 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 )
7. 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" )