Examples of global and local indexes and global indexes

Examples of global and local indexes and global indexes
-- Create a table
Create table lm_t1 (id number, name varchar2 (10), sex varchar2 (10 ))
Partition by range (id)
(
Partition p1 values less than (10 ),
Partition p2 values less than (20 ),
Partition pm values less than (maxvalue)
);
-- Global partition index (only hash and range supported)
Create index g_ind_lm_t1 on lm_t1 (id)
Global partition by range (id)
(
Partition p1 values less than (10 ),
Partition p2 values less than (20 ),
Partition pm values less than (maxvalue)
); Create index gh_ind_lm_t1 on lm_t1 (name)
Global partition by hash (name)
Partitions 3;
-- Local partition Index
Create index l_ind_lm_t1 on lm_t1 (sex)
Local (partition p1, partition p2, partition p3); -- View information
Select * from user_indexes d where d. table_name = upper ('lm _ t1 ');
Select * from user_part_indexes d where d. table_name = upper ('lm _ t1 ');
Select * from user_ind_partitions d where d. index_name in ('G _ IND_LM_T1 ', 'l _ IND_LM_T1', 'gh _ IND_LM_T1 ')

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.