Create index if not exist

Question

ALL,

SQL> SELECT 1 FROM all_indexes WHERE table_name = UPPER( 'abcatcol' ) AND index_name = UPPER( 'abcatcol_tnam_ownr_cnam' );

no rows selected

SQL> CREATE INDEX abcatcol_tnam_ownr_cnam ON abcatcol(abc_tnam, abc_ownr, abc_cnam);
CREATE INDEX abcatcol_tnam_ownr_cnam ON abcatcol(abc_tnam, abc_ownr, abc_cnam)
                                                 *
ERROR at line 1:
ORA-01408: such column list already indexed


SQL> SELECT 1 FROM all_indexes WHERE table_name = UPPER( 'abcatcol' );

         1
----------
         1

SQL> SELECT index_name FROM all_indexes WHERE table_name = UPPER( 'abcatcol' );

INDEX_NAME
------------------------------
SYS_C007087

SQL >

What am I missing? Why can't I create an index?

EDIT:

SQL> select index_name, listagg(column_name, ', ') within group(order by 1)-- over(partition by index_name)
  2    from dba_ind_columns
  3   where table_name = 'ABCATCOL'
  4   group by index_name;

INDEX_NAME
------------------------------
LISTAGG(COLUMN_NAME,',')WITHINGROUP(ORDERBY1)--OVER(PARTITIONBYINDEX_NAME)
--------------------------------------------------------------------------------
SYS_C007087
ABC_CNAM, ABC_OWNR, ABC_TNAM


SQL> SELECT index_name FROM all_indexes WHERE table_name = UPPER( 'abcatcol' );

INDEX_NAME
------------------------------
SYS_C007087

SQL>

EDIT2:

The suggested question utilizes PL/SQL. I want to understand how to do that using standard SQL and why my queries do not work as expected.

EDIT3:

This is the table definition:

CREATE TABLE abcatcol(abc_tnam char(129) NOT NULL, abc_tid integer, abc_ownr char(129) NOT NULL, abc_cnam char(129) NOT NULL, abc_cid smallint, abc_labl char(254), abc_lpos smallint, abc_hdr char(254), abc_hpos smallint, abc_itfy smallint, abc_mask char(31), abc_case smallint, abc_hght smallint, abc_wdth smallint, abc_ptrn char(31), abc_bmap char(1), abc_init char(254), abc_cmnt char(254), abc_edit char(31), abc_tag char(254), PRIMARY KEY( abc_tnam, abc_ownr, abc_cnam ));

So I guess since those fields are part of the PK Otacle already made the index, right?

Does this answer your question? [Oracle - Create an index only if not exists](https://stackoverflow.com/questions/44539663/oracle-create-an-index-only-if-not-exists) — Ori Marko, Jan 28 '21 at 10:26

ekochergin · Accepted Answer · 2021-01-28T10:32:49.283

1

You are looking for an index by it's name whereas oracle says to you this set of columns has been already indexed.

It means there is already an index with another name over that column set.

You need to check against dba_ind_columns table to get the index name over that column set

UPD. Here is the query to help you out to find the columns indexed

select index_name, listagg(column_name, ', ') within group(order by 1)-- over(partition by index_name)
  from dba_ind_columns
 where table_name = 'TABLE_NAME'
 group by index_name;

edited Jan 28 '21 at 10:32

answered Jan 28 '21 at 10:15

ekochergin

4,109
2
12
19

I updated the OP. And have a question - does this mean I have 2 indexes? Because it certinly seems that way... – Igor Jan 29 '21 at 00:39
@Igor, the first query from the first edit shows you only one index. It is just the first column (index_name) that doesn't fit to the output window. This is why you have 2 lines. This "SYS_" index has been created automatically by Oracle. – ekochergin Jan 29 '21 at 06:40

Create index if not exist

1 Answers1