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_name
The 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 ...)
Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder
I tried this and got ORA-00923: FROM keyword not found where expected
With what version? I think LISTAGG was added to 11gR2
Ah—thanks that was with 10g Enterprise Edition Release 10.2.0.5.0