SQL Performance (MOSC)

MOSC Banner

Discussions

Oracle incorrectly favours INDEX RANGE SCAN over INDEX UNIQUE SCAN

in SQL Performance (MOSC) 7 commentsAnswered ✓

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;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center