Menu
I jotted down the following query which will list the index name and its columns for a particular table:
select b.uniqueness, a.index_name, a.table_name, a.column_name from all_ind_columns a, all_indexes b where a.index_name=b.index_name and a.table_name = upper('table_name') order by a.table_name, a.index_name, a.column_position; I want to modify this so that if I pass in a view or synonym also it works. Our system has variations of views, synonyms so it will be really helpful to have one query to which i can just supply the name (be it view synonym or table) and it would spit out the indexes and their columns.
581
To show indexes for a particular table in Oracle use the following command: select index_name from dba_indexes where table_name='tablename';
To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA. STATISTICS WHERE TABLE_SCHEMA = 'your_schema'; Removing the where clause will show you all indexes in all schemas.
ALL_IND_COLUMNS describes the columns of indexes on all tables accessible to the current user. Note: For join indexes, the TABLE_NAME and TABLE_OWNER columns in this view may not match the TABLE_NAME and TABLE_OWNER columns you find in the *_INDEXES (and other similar) data dictionary views.
Oracle SQL standards do not support creating indexes on views. If you need to index documents whose contents are in different tables, you can create a data storage preference using the USER_DATASTORE object.
Your query should work for synonyms as well as the tables. However, you seem to expect indexes on views where there are not. Maybe is it materialized views ?
57
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
