Do you want to know real quick what kind of indexes there are on any given table in your Oracle schema? Nothing simpler than that. Just run the following query:
SELECT
i.index_name,
listagg(c.column_name, ', ')
WITHIN GROUP (ORDER BY c.column_position)
AS columns
FROM all_indexes i
JOIN all_ind_columns c
ON i.index_name = c.index_name
WHERE i.table_name = 'FILM_ACTOR'
GROUP BY i.index_nameThe above query is ran against the Sakila database. Just replace the “FILM_ACTOR” table by your table and you’re all set. The result looks like:
INDEX_NAME COLUMNS ------------------------------------------- IDX_FK_FILM_ACTOR_ACTOR ACTOR_ID IDX_FK_FILM_ACTOR_FILM FILM_ID SYS_C007155 ACTOR_ID, FILM_ID
Sometimes, it’s the simple things…
Explanation about LISTAGG
LISTAGG is a so-called ordered-set aggregate function, added in 11gR2, i.e. the aggregate function will aggregate the data “WITHIN a GROUP” given a specific “ORDER”. This is obviously useful for string concatenation, but it can be very useful also for a variety of other functions, such as inverse distribution functions, such as the MEDIAN, which we’ve blogged about here.
The syntax is:
function(...) WITHIN GROUP (ORDER BY ...)
| Reference: | How to Quickly Enumerate Indexes in Oracle 11gR2 from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
