Oracle - Create an index only if not exists

Question

Is there any way to create indexes in oracle only if they don't exists ?

Something like

CREATE INDEX IF NOT EXISTS ord_customer_ix
   ON orders (customer_id);

No, there is not. Only programmatically (using dynamic SQL) catching `ORA-01408` error — Nick Krasnov, Jun 14 '17 at 08:43
@NicholasKrasnov "Is there any way ?" Can you please show me ? — Adelin, Jun 14 '17 at 08:51

score 21 · Accepted Answer · edited Jun 14 '17 at 09:39

21

Add an index only if not exists:

declare 
  already_exists  exception; 
  columns_indexed exception;
  pragma exception_init( already_exists, -955 ); 
  pragma exception_init(columns_indexed, -1408);
begin 
  execute immediate 'create index ord_customer_ix on orders (customer_id)'; 
  dbms_output.put_line( 'created' ); 
exception 
  when already_exists or columns_indexed then 
  dbms_output.put_line( 'skipped' );  
end;

edited Jun 14 '17 at 09:39

am2

380
5
21

answered Jun 14 '17 at 08:51

Ori Marko

56,308
23
131
233

2

Seams to be the official answer from Ask Tom: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:54643197712655 – Hans Deragon Dec 06 '17 at 14:40

Kumar Abhishek · Answer 2 · 2019-04-19T11:38:49.030

CREATE INDEX IN ORACLE IF NOT EXISTS.

ALTER SESSION SET CURRENT_SCHEMA = PROD_INTG;
DECLARE
  INDEX_EXISTS NUMBER;
BEGIN
  SELECT COUNT(1)
  INTO INDEX_EXISTS
  FROM ALL_INDEXES AI,
    ALL_IND_COLUMNS AIC
  WHERE AI.TABLE_OWNER = 'PROD_INTG'
        AND AI.TABLE_NAME = 'PROCESS_APPLICATION'
        AND AI.INDEX_NAME = AIC.INDEX_NAME
        AND AI.OWNER = AIC.INDEX_OWNER
        AND AIC.COLUMN_NAME IN ('PST_CODE', 'PIZ_TYPE_ID');
  IF (INDEX_EXISTS) > 0
  THEN
    DBMS_OUTPUT.PUT_LINE('INDEX EXISTS  :');
  ELSE
    EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = PROD_INTG';
    EXECUTE IMMEDIATE 'CREATE INDEX PROD_INTG.IDX_IQC_APPS_IN_PROC_PST_PIZ
      ON PROD_INTG.PROCESS_APPLICATION (PST_CODE, PIZ_TYPE_ID) PARALLEL 16';
    EXECUTE IMMEDIATE 'ALTER INDEX PROD_INTG.IDX_IQC_APPS_IN_PROC_PST_PIZ NOPARALLEL';
    DBMS_OUTPUT.PUT_LINE('INDEX created  :');
  END IF;
  EXCEPTION
  WHEN OTHERS THEN
  IF SQLCODE IN (-2275, -955, -02431, -01430, -01451, -01408)
  THEN
    NULL;
  ELSE
    RAISE;
  END IF;
END;
/

score -2 · Answer 3 · answered Oct 25 '21 at 23:28

A script can be used to drop the index if it exists, then create it. With error checking on the drop if exists:

BEGIN
   EXECUTE IMMEDIATE 'DROP INDEX ord_customer_ix';
EXCEPTION
   WHEN OTHERS
   THEN
      IF SQLCODE != -955
      THEN                           -- ORA-00955 index does not exist
         RAISE;
      END IF;
END;
/

CREATE INDEX ord_customer_ix
  ON orders (customer_id);
;

This is pretty straight-forward and easy to code and understand.

Oracle - Create an index only if not exists

3 Answers3

Linked