M(y)Elwood

Index Informationen für Oracle

Wie bekommt detaillierte Informationen zu den Indices einer Oracle Tabelle?
Hier die Lösung:

SELECT
    aic.index_name,
    aic.column_name,
    aic.column_position,
    aic.descend,
    aic.table_owner,
    CASE alc.constraint_type
        WHEN 'U' THEN 'UNIQUE'
        WHEN 'P' THEN 'PRIMARY KEY'
        ELSE ''
        END AS index_type
FROM
    all_ind_columns aic
    LEFT JOIN
        all_constraints alc
    ON
        aic.index_name = alc.constraint_name
    AND
        aic.table_name = alc.table_name
    AND
        aic.table_owner = alc.owner
WHERE
    aic.table_name = <TABLE_NAME>
AND
    aic.index_name = <INDEX_NAME>
ORDER BY
    column_position

<TABLE_NAME> und <INDEX_NAME> mit den entsprechenden Werten ersetzen.