Discussions
Oracle incorrectly favours INDEX RANGE SCAN over INDEX UNIQUE SCAN
I have a table and indexes that look like this:
CREATE TABLE "INDEXES_A"
( "CLIENT_ID" VARCHAR2(255 CHAR) NOT NULL ENABLE,
"PRODUCT_ID" NUMBER(19,0) NOT NULL ENABLE,
"ID" VARCHAR2(255 CHAR) NOT NULL ENABLE,
"FILE_ID" NUMBER(19,0) NOT NULL ENABLE,
"IND_" NUMBER(19,0) NOT NULL ENABLE,
"LEN_" NUMBER(19,0) NOT NULL ENABLE,
"CREATION_DATE" TIMESTAMP (6) NOT NULL ENABLE,
"UPDATED_DATE" TIMESTAMP (6) NOT NULL ENABLE,
"JOB_ID" NUMBER(19,0) NOT NULL ENABLE,
"TIME_SEGMENT" VARCHAR2(64) NOT NULL ENABLE,
"TEMPLATE_ID" NUMBER(19,0),
"DISC1" VARCHAR2(255 CHAR),
"DISC2" VARCHAR2(255 CHAR),
CONSTRAINT "FK_DF_FILE_ID" FOREIGN KEY ("FILE_ID") REFERENCES "DATA_FILES" ("ID") ENABLE
)
PARTITION BY LIST ("CLIENT_ID"); CREATE UNIQUE INDEX INDEXES_A_LOCAL_IDX ON INDEXES_A (CLIENT_ID, PRODUCT_ID, ID) LOCAL;
0