oracle11g - 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?

question from:https://stackoverflow.com/questions/65934684/create-index-if-not-exist

Answer 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;