Why Oracle not using index?
Hi,
I got two tables in a Oracle 9i (9.2.0.8) as follows
TABLE stucture:
CREATE TABLE parent (a DATE, b VARCHAR2(20), e VARCHAR2(30), CONSTRAINT parentp1 PRIMARY KEY (a,b));
CREATE TABLE child (a DATE, b VARCHAR2(20), c VARCHAR2(30), d VARCHAR2(40), CONSTRAINT childp1 PRIMARY KEY (a,b,c));
data: huge amount OF data
Statement:
SELECT p.*
FROM parent p, child c
where p.a = c.a
AND p.b = c.b
The plan shows a full table scan on child table. The join uses key columns. Why Oracle does not use childp1 index?
I have already gathered statistics for the two tables.
TABLE stucture:
CREATE TABLE parent (a DATE, b VARCHAR2(20), e VARCHAR2(30), CONSTRAINT parentp1 PRIMARY KEY (a,b));
CREATE TABLE child (a DATE, b VARCHAR2(20), c VARCHAR2(30), d VARCHAR2(40), CONSTRAINT childp1 PRIMARY KEY (a,b,c));
data: huge amount OF data
Statement:
SELECT p.*
FROM parent p, child c
where p.a = c.a
AND p.b = c.b
The plan shows a full table scan on child table. The join uses key columns. Why Oracle does not use childp1 index?
I have already gathered statistics for the two tables.
0