Thursday, January 22, 2009

Example of global partitioned, global non-partitioned and local Indexes

Index on the partitioned table can be of three types.
1)Global Non-partitioned Index.
2)Global Partitioned Index.
3)Local Partitioned Index.

With an example I will make you clear of these three different types of indexes on the partitioned table.

1)Create a partitioned table.

CREATE TABLE test_partition
(
id number,
created_date date,
col3 varchar2(20)
)
PARTITION BY RANGE (created_date)
(
PARTITION part1 VALUES LESS THAN (to_date('11-oct-2008','dd-mon-yyyy')) ,
PARTITION part2 VALUES LESS THAN (to_date('12-oct-2008','dd-mon-yyyy')) ,
PARTITION part3 VALUES LESS THAN (to_date('13-oct-2008','dd-mon-yyyy')) ,
PARTITION part4 VALUES LESS THAN (to_date('14-oct-2008','dd-mon-yyyy')) ,
PARTITION junk VALUES LESS THAN (MAXVALUE)
)
/
Table created.

Global Non Partitioned Index
This index span all over the table. Hence it is global and index is not partitioned.
SQL> create index tpid_i on test_partition(id);

Index created.

SQL> select index_name,partitioned, index_type from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME PAR INDEX_TYPE
------------------------------ --- ---------------------------
TPID_I NO NORMAL

Local Partitioned Index

SQL> create index tpid_i on test_partition(id) local;

Index created.

SQL> select index_name,partitioned, index_type from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME PAR INDEX_TYPE
------------------------------ --- ---------------------------
TPID_I YES NORMAL


SQL> select partition_name, status from user_ind_partitions where
2 index_name = 'TPID_I';


PARTITION_NAME STATUS
------------------------------ --------
JUNK USABLE
PART1 USABLE
PART2 USABLE
PART3 USABLE
PART4 USABLE

Global Partitioned Index

CREATE INDEX tpid_g ON test_partition(id)
GLOBAL PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
Index created.
SQL> select index_name,partitioned, index_type from user_indexes where table_name='TEST_PARTITION';

INDEX_NAME PAR INDEX_TYPE
------------------------------ --- ---------------------------
TPID_G YES NORMAL

No comments:

Post a Comment