Find Oracle indexes that are global vs locally partitioned.

We often run into cases where we have to find whether indexes are locally partitioned or are global across the whole table.

We are all familiar with the dba_indexes view, however, here are a couple of more views that make global vs local index queries possible.

To find local indexes use dba_part_indexes. The "locality" field from this view should be queried.

select owner, table_name, index_name, locality from dba_part_indexes;

OWNER TABLE_NAME                     INDEX_NAME                     LOCALI
----- ------------------------------ ------------------------------ ------
SYS   STREAMS$_APPLY_SPILL_MSGS_PART I_STREAMS_APPLY_SPILL_MSGS_PT1 LOCAL
SYS   WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_SESSION_HISTORY_PK LOCAL
SYS   WRH$_SYSTEM_EVENT              WRH$_SYSTEM_EVENT_PK           LOCAL
SYS   WRH$_OSSTAT                    WRH$_OSSTAT_PK                 LOCAL
SYS   WRH$_PARAMETER                 WRH$_PARAMETER_PK              LOCAL
SYS   WRH$_SYSSTAT                   WRH$_SYSSTAT_PK                LOCAL
SYS   WRH$_SYS_TIME_MODEL            WRH$_SYS_TIME_MODEL_PK         LOCAL

The locality field will show as "LOCAL".

Global indexes are by definition, not partitioned, so they will not show in this view.

To find global indexes, we need to find all tables that are partitioned, but have indexes that are not partitioned. In order to do this, we use the following two views: dba_indexes and dba_part_tables.

select i.table_owner, i.table_name, i.owner, i.index_name
                  from dba_indexes i, 
                       dba_part_tables t
                  where i.table_owner = t.owner
                  and i.table_name = t.table_name
                  and i.partitioned = 'NO';

TABLE_OWNER TABLE_NAME                     OWNER INDEX_NAME
----------- ------------------------------ ----- ------------------------------
SYS         WRI$_OPTSTAT_HISTGRM_HISTORY   SYS   I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST
SYS         WRI$_OPTSTAT_HISTGRM_HISTORY   SYS   I_WRI$_OPTSTAT_H_ST
SYS         WRI$_OPTSTAT_HISTHEAD_HISTORY  SYS   I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST
SYS         WRI$_OPTSTAT_HISTHEAD_HISTORY  SYS   I_WRI$_OPTSTAT_HH_ST