Showing posts with label index columns. Show all posts
Showing posts with label index columns. Show all posts

Monday, January 31, 2022

Simple SQL to list

The following SQL lists the indexes defined on a table, along with the columns and their positioning:
SELECT I.INDEX_NAME,I.INDEX_TYPE,I.NUM_ROWS,I.DEGREE, C.COLUMN_NAME,C.COLUMN_POSITION
FROM DBA_INDEXES I JOIN DBA_IND_COLUMNS C
ON (I.INDEX_NAME = C.INDEX_NAME)
WHERE I.OWNER='MYSCHEMA'
AND I.OWNER = C.INDEX_OWNER
AND I.TABLE_NAME='MYTABLE'
ORDER BY I.INDEX_NAME, C.COLUMN_POSITION;