SQL Performance (MOSC)

MOSC Banner

Index are not used

edited Oct 27, 2023 6:17PM in SQL Performance (MOSC) 18 commentsAnswered

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#)

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.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center