Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Quickly Enumerate Indexes in Oracle 11gR2

DZone's Guide to

How to Quickly Enumerate Indexes in Oracle 11gR2

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:

· Database Zone
Free Resource

Download the Guide to Open Source Database Selection: MySQL vs. MariaDB and see how the side-by-side comparison of must-have features will ease the journey. Brought to you in partnership with MariaDB.

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 run 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.

Interested in reducing database costs by moving from Oracle Enterprise to open source subscription?  Read the total cost of ownership (TCO) analysis. Brought to you in partnership with MariaDB.

Topics:
11gr2 ,oracle ,sql ,listagg

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}