Oracle: Get the indexes created explicitely with CREATE [UNIQUE] INDEX

Using Oracle (19.3 and 21.3), we would like to find the indexes created with a real CREATE [UNIQUE] INDEX instruction, and exclude those that are automatically created by constraints like PRIMARY KEY and UNIQUE.

Seems not to be so easy with existing system views, as we have to make a quite complex query to exclude indexes referenced in ALL_CONSTRAINTS.

The bad news is that this query takes a while to execute (~6s with 19.3 et ~15s with 21.3!)

Take care: From my understanding, ALL_INDEXES.GENERATED column indicates if the index name was automatically generated by Oracle, not the fact that the index itself was automatically created by a table constraint.

We know about dbms_metadata.GET_DDL() but that one returns all kind of indexes...

Any clue?

Thanks!
Seb

Here the test SQL:

CREATE TABLE tab3 (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(50) UNIQUE
);
CREATE TABLE tab1 (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(50) UNIQUE,
crea DATE,
ratio DECIMAL(10,2),
FOREIGN KEY (name) REFERENCES tab3 (name)
);
CREATE INDEX ix1_tab1 ON tab1 ( crea );
CREATE TABLE tab2 (
id INTEGER NOT NULL,
name VARCHAR(50),
CONSTRAINT pktab2 PRIMARY KEY (id),
CONSTRAINT uktab2_1 UNIQUE (name),
CONSTRAINT fktab2_1 FOREIGN KEY (name) REFERENCES tab3 (name)
);
SET LINESIZE 400
SET PAGESIZE 400
COLUMN OWNER format a20
COLUMN TABLE_NAME format a20
COLUMN COLUMN_NAME format a20
COLUMN INDEX_NAME format a20
COLUMN CONSTRAINT_NAME format a20
SELECT c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME, c.CONSTRAINT_TYPE T, c.INDEX_NAME
FROM ALL_CONSTRAINTS c
WHERE UPPER(c.OWNER)=UPPER(USER)
AND c.CONSTRAINT_TYPE IN ('C','P','U','R')
AND c.CONSTRAINT_NAME NOT LIKE 'BIN$%'
ORDER BY c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME
;
SELECT i.OWNER, i.TABLE_NAME, i.INDEX_NAME, ic.COLUMN_NAME, i.UNIQUENESS, i.GENERATED
FROM ALL_INDEXES i
INNER JOIN ALL_IND_COLUMNS ic
ON (i.TABLE_NAME = ic.TABLE_NAME
AND i.INDEX_NAME = ic.INDEX_NAME
AND i.TABLE_OWNER = ic.TABLE_OWNER)
WHERE i.OWNER <> 'SYS' AND i.OWNER <> 'SYSTEM'
AND UPPER(i.OWNER)=UPPER(USER)
ORDER BY i.TABLE_NAME, i.INDEX_NAME, ic.COLUMN_POSITION
;
SELECT i.OWNER, i.TABLE_NAME, i.INDEX_NAME, ic.COLUMN_NAME, i.UNIQUENESS
FROM ALL_INDEXES i
INNER JOIN ALL_IND_COLUMNS ic
ON (i.TABLE_NAME = ic.TABLE_NAME
AND i.INDEX_NAME = ic.INDEX_NAME
AND i.TABLE_OWNER = ic.TABLE_OWNER)
WHERE i.OWNER <> 'SYS' AND i.OWNER <> 'SYSTEM'
AND UPPER(i.OWNER)=UPPER(USER)
AND i.INDEX_NAME NOT IN (
SELECT c.INDEX_NAME
FROM ALL_CONSTRAINTS c
WHERE UPPER(c.OWNER)=UPPER(USER)
AND c.CONSTRAINT_TYPE IN ('P','U')
AND c.CONSTRAINT_NAME NOT LIKE 'BIN$%'
)
ORDER BY i.TABLE_NAME, i.INDEX_NAME, ic.COLUMN_POSITION

Use the constraint_index column of the *_indexes views. Using the DDL above:

select index_name, constraint_index 
from   user_indexes
where  table_name like 'TAB_';
INDEX_NAME      CONSTRAINT_INDEX    
SYS_C0044774    YES                 
SYS_C0044775    YES                 
IX1_TAB1        NO                  
PKTAB2          YES                 
UKTAB2_1        YES                 
SYS_C0044771    YES                 
SYS_C0044772    YES