CODEWITHSUNDEEP
oracle-databaseindexing
jeff
jeff

Reputation: 3742

Oracle Conditional Index with two identifiers

Is it possible to create a conditional index on two columns? 

CREATE UNIQUE INDEX idx_dup_wfc
    ON WF_WORKFLOW_CLASS (CASE WHEN is_active = 1
      THEN (NAME, DEPT_OWNER)
         ELSE NULL
      END)

returns ORA-00906: missing left parenthesis however the following works

CREATE UNIQUE INDEX idx_dup_wfc
    ON WF_WORKFLOW_CLASS (CASE WHEN is_active = 1
      THEN NAME
         ELSE NULL
      END)

Upvotes: 4

Views: 2085

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132580

Yes, but you have to perform a CASE per column:

CREATE UNIQUE INDEX idx_dup_wfc
    ON WF_WORKFLOW_CLASS 
     (CASE WHEN is_active = 1 THEN NAME END
     ,CASE WHEN is_active = 1 THEN DEPT_OWNER END
     )

(The ELSE in your code is superfluous).

Upvotes: 6

Related Questions