How to Find Global & Local Indexes in Oracle Partitions.

SQL> CREATE INDEX  EMP_IDX_G ON EMP_TAB (JOIN DATE)
         GLOBAL PARTITION BY RANGE (JOIN DATE)
         (PARTITION P1 VALUES LESS THAN (TO_DATE ('01-JAN-2020','DD-MON-YYYY')) tablespace users,
          PARTITION P2 VALUES LESS THAN (TO_DATE ('01-JAN-2021','DD-MON-YYYY')) tablespace users,
          PARTITION P3 VALUES LESS THAN (TO_DATE ('01-JAN-2022','DD-MON-YYYY')) tablespace users,
          PARTITION P4 VALUES LESS THAN (MAXVALUE) tablespace users
        );

Index created.


SQL> select table_name,index_name,locality from user_part_indexes where table_name='EMP_TAB';

TABLE_NAME                                INDEX_NAME                     LOCALITY
------------------------------ ------------------------------ --------------------------------
EMP_TAB                                          EMP_IDX_G                       GLOBAL



SQL> CREATE INDEX IX_EMP ON EMP_TAB (SDHSCD,SDMEDN,SDCASN) LOCAL;

Index created.


SQL> select table_name,index_name,locality from user_part_indexes where table_name='EMP_TAB';

TABLE_NAME                     INDEX_NAME                     LOCALITY
------------------------------ ------------------------------ -------------------------------
EMP_TAB                                  IX_EMP                               LOCAL




Happy Learning...