Menu
I have created an index in Oracle SQl-
create index ind_cname on country(capital)
When executed I got a success message.Now I want to see the created Index. Please help in the syntax to show index.
I am new to Oracle .I want a query which shows indexes for the table.
Thanks
394
An index stores the values in the indexed column (s). And for each value the locations of the rows that have it. Just like the index at the back of a book. This enables you to hone in on just the data that you're interested in.
Here is the query on how to find indexes on a table in oracle With 12c, Oracle has introduced the concept of Partial indexes in Partitioned table.The *_INDEXES view has been modified to include an INDEXING column, which indicates if the index is FULL or PARTIAL. Check the indexing status of the index.
That way Oracle Database can answer the query by accessing just the index. Avoiding the table access can save you some work. In most other cases I'd stick with the single column event index.
But there is one case where you need to manually create the index: Function-based unique constraints. You can't use functions in unique constraints. For example, you might want to build a "dates" table that stores one row for each calendar day. Unfortunately, Oracle Database doesn't have a "day" data type.
If you have the privileges, you can use the ALL_INDEXES or USER_INDEXES views. The query would be:
SELECT *
FROM all_indexes
WHERE table_name = 'COUNTRY';
If you want some information on the columns included in the index, you can select those from ALL_IND_COLUMNS. Documentation regarding these views can be found here Static Data Dictionary Views: ALL_ALL_TABLES to ALL_MVIEWS
131
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
