I am using the following query to grab the index columns on a table along with their data type:
SELECT DISTINCT COL.COLUMN_NAME, COL.DATA_TYPE
FROM DBA_IND_COLUMNS IND
INNER JOIN DBA_TAB_COLUMNS COL
ON ( IND.TABLE_OWNER = COL.OWNER AND IND.TABLE_NAME = COL.TABLE_NAME
AND IND.COLUMN_NAME = COL.COLUMN_NAME)
WHERE IND.TABLE_NAME = 'MY_TABLE' AND TABLE_OWNER = 'SCHEMA'
But how can I grab the columns for just one index, instead of the columns for all the indexes?
For example: If a table has indexes:
INDEX1: column_a,column_b
INDEX2: column_c,column_d
My current query would result in:
column_a, varchar
column_b, varchar
column_c, varchar
column_d, varchar
but I want it to result in just:
column_a, varchar
column_b, varchar
Since you just want the columns for the first index (in alphabetical order), you can try:
SELECT DISTINCT COL.COLUMN_NAME, COL.DATA_TYPE
FROM DBA_IND_COLUMNS IND
INNER JOIN DBA_TAB_COLUMNS COL
ON ( IND.TABLE_OWNER = COL.OWNER AND IND.TABLE_NAME = COL.TABLE_NAME
AND IND.COLUMN_NAME = COL.COLUMN_NAME)
WHERE IND.TABLE_NAME = 'MY_TABLE' AND TABLE_OWNER = 'SCHEMA'
AND INDEX_NAME =
(
SELECT MIN(INDEX_NAME)
FROM DBA_IND_COLUMNS IIND
INNER JOIN DBA_TAB_COLUMNS CCOL
ON ( IIND.TABLE_OWNER = CCOL.OWNER AND IIND.TABLE_NAME = CCOL.TABLE_NAME )
)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments