Reporting on Partitioned Indexes
There are many things you can glean from the data dictionary regarding index portions, including the following:
- Partition names
- Type of index
- Status of index partitions (need to query appropriate view)
- Size of the index partitions
In the following example, you simply want to get a list of the index name, partition names, and status for your EMPLOYEES_PARTTEST table. On this table, since you have both partitioned and non-partitioned indexes, you UNION two queries together.
SQL> select table_name, index_name, partition_name, p.status
2 from user_ind_partitions p join user_indexes i using(index_name)
3 where table_name = 'EMPLOYEES_PARTTEST'
4 union
5 select table_name, index_name, null, status 6 from ...Get Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
