Partitioned index properties

The following query will reveal information about the indexes on a partitioned table.

Very useful when you want to investigate if a partitioned table is correctly and efficiently indexed:

SELECT C.TABLE_NAME,I.INDEX_NAME,I.PARTITIONING_TYPE "part type", I.SUBPARTITIONING_TYPE "sub type", I.LOCALITY,I.ALIGNMENT,C.COLUMN_NAME,C.COLUMN_POSITION "col pos", I.INTERVAL,I.AUTOLIST, II.VISIBILITY
FROM DBA_PART_INDEXES I JOIN DBA_IND_COLUMNS C
ON (I.INDEX_NAME = C.INDEX_NAME)
 JOIN DBA_INDEXES II
   ON (II.INDEX_NAME = I.INDEX_NAME)
WHERE I.TABLE_NAME IN (SELECT TABLE_NAME FROM dba_tables WHERE PARTITIONED='YES' and table_name='MYTABLE')
ORDER BY I.INDEX_NAME,C.COLUMN_POSITION;

In my case, the results were:

TABLE_NAME	INDEX_NAME	part type	sub type	LOCALITY	ALIGNMENT	COLUMN_NAME	col pos	INTERVAL	AUTOLIST	VISIBILITY
MYTABLE	MYTABLE_SI1	RANGE	NONE	LOCAL	PREFIXED	CREATED_DAY	1	NUMTOYMINTERVAL(1,'MONTH')	NO	VISIBLE
MYTABLE	MYTABLE_SI1	RANGE	NONE	LOCAL	PREFIXED	SEQ_NUM	2	NUMTOYMINTERVAL(1,'MONTH')	NO	VISIBLE
MYTABLE	MYTABLE_SI2	RANGE	NONE	LOCAL	NON_PREFIXED	SUBMITTED_DATE	1	NUMTOYMINTERVAL(1,'MONTH')	NO	VISIBLE