Index are not used
We have a table that can be queried by distinct ways according to input parameters pass to the query.
We don't want to use dynamic SQL.
These are the details:
DB version : 19.20 (ODA)
CREATE TABLE GATE_USER_APPLICATION_JOB2
( "ORAUSER" VARCHAR2(15 BYTE) NOT NULL,
"APPLICATION#" VARCHAR2(2 BYTE) NOT NULL,
"JOB#" VARCHAR2(4 BYTE) NOT NULL,
"START_DATE" DATE NOT NULL,
"EXPIRY_DATE" DATE NOT NULL,
"ACTIVE" VARCHAR2(1 BYTE) NOT NULL
)
/
CREATE UNIQUE INDEX GATE_USER_APPLICATION_JOB2_PK ON GATE_USER_APPLICATION_JOB2( ORAUSER, APPLICATION# )
/
ALTER TABLE GATE_USER_APPLICATION_JOB2 ADD CONSTRAINT GATE_USER_APPLICATION_JOB2_PK PRIMARY KEY (ORAUSER, APPLICATION#)