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

Related Documents
How to convert non-partitioned table to partition table using re-definition
Partitioning in Oracle- How to do partition
How to make partitioning in Oracle more Quickly

How to avoid rebuild of indexes if DDL performed on partitioned table

On the partitioned table, if we don't do anything with the index then the index against the partitioned table remain global Non partitioned indexes and thus whenever we perform any DDL operation against the table partition like drop a partition or truncate a partition or add a partition or merge a partition or split a partition then the associated global indexes become invalid. Thus after doing DDL we need to rebuild the indexes. This may be very cumbersome and it may need several hours if the table is big enough.

With an example I am demonstrating how global indexes against a table become invalid if I do any DDL against the 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.

2)Insert data into the partitioned table.

insert into test_partition
    select rownum, to_date('10-oct-2008','dd-mon-yyyy')+mod(rownum,7),
           rownum
      from dba_users;

41 rows created.

3)Create index on the partitioned table.
SQL> create index tpid_i on test_partition(id);
Index created.

4)Be sure partitioned is done successful.
SQL> select count(*) from test_partition partition (part2);

COUNT(*)
----------
6

Let's see some sample data.

SQL> select * from test_partition partition (part2);
        ID CREATED_D COL3
---------- --------- --------------------
         1 11-OCT-08 1
         8 11-OCT-08 8
        15 11-OCT-08 15
        22 11-OCT-08 22
        29 11-OCT-08 29
        36 11-OCT-08 36

6 rows selected.

5)Let us see the status of the index against the partitioned table.
Note that since we have made global non-partitioned indexes so we will not get any entry about valid/unusable index in view user_ind_partitions. To get index status we have to query from user_indexes view.

SQL> select partition_name, status from user_ind_partitions where
index_name = 'TPID_I';
no rows selected

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

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         VALID

6)Do some DDL operation on the partitioned table.
SQL> alter table test_partition truncate partition part2;
Table truncated.

Check by,
SQL> select count(*) from test_partition partition (part2);

COUNT(*)
----------
0

7)Check the index status now.

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

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         UNUSABLE

Since we have global index so any DDL under the table marks the index unusable. If you want to make the index valid then you have to rebuild the index.

SQL> alter index tpid_i rebuild;
Index altered.

SQL> select index_name, status from user_indexes where table_name='TEST_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         VALID

This rebuild process really time consuming. And for production server it may not be tolerable.

8)The solution is to make the index as local partitioned index.
SQL> drop index tpid_i;
Index dropped.


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

Check status by,

SQL>  select index_name, status from user_indexes where table_name='TEST_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         N/A

Since it is local partitioned index we have to query from user_ind_partitions.
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

SQL> select count(*) from test_partition partition (part3);
COUNT(*)
----------
6

9)Let's do some DDL operation and test.
SQL> alter table test_partition truncate partition part3;
Table truncated.

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

INDEX_NAME                     STATUS
------------------------------ --------
TPID_I                         N/A

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

And it still remains in USABLE status. So you might able to save a lots of time as well as non-interrupt service and effort against it.

ORA-14120: incompletely specified partition bound for a DATE column

In order to create partitioning table of FORM_AT whenever I try to create a temporary partitioned table name FORM_AT_PART it fails with ORA-14120: incompletely specified partition bound for a DATE column error as below.

SQL>CREATE TABLE "FORM_AT_PART"
( "ID" NUMBER NOT NULL ENABLE,
"AT" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
)partition by range (created_date)
(
partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-06','DD-MON-YY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-06','DD-MON-YY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-06','DD-MON-YY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-07','DD-MON-YY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-07','DD-MON-YY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-07','DD-MON-YY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-07','DD-MON-YY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-08','DD-MON-YY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-08','DD-MON-YY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);

partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02,
*
ERROR at line 7:
ORA-14120: incompletely specified partition bound for a DATE column

Cause of The Problem

There may be several causes behind the problem. One reason is you don't use TO_DATE conversion of date column while range partition and you specified date format inside partitioning column does not match with the NLS_DATE_FORMAT setting.

Another reason is starting with 8.0.3, Oracle insists that a partition bound for a DATE
partitioning column is fully specified. Fully specified indicates that it will at least contain day, month and year with 4 digits. Here inside to_date conversion I used 2 digits of date and hence error appears. Instead of '01-JUN-08' we have to use '01-JUN-2008'.

Solution of The Problem
Along with to_date conversion use 4 digits of year for a DATE partitioning column in oracle.
After fixing format it look like below,
SQL>CREATE TABLE "FORM_AT_PART"
( "ID" NUMBER NOT NULL ENABLE,
"AT" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
)partition by range (created_date)
(
partition fa_part1 values less than (to_date('01-JAN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);

Table created.

Partitioning in Oracle- How to do partition

In this post I have given an example how we can do partition of a table. In other post I will explain which type of partition we will choose and how we can improve partitioning performance etc.
STEP 1:

Create a temporary table with same definition of table, that is to be partitioned.

CREATE TABLE big_table2 (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2007 VALUES LESS THAN (MAXVALUE));

Add constraints

ALTER TABLE big_table2 ADD (
CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);

Create Index

CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;

Add foreign key,

ALTER TABLE big_table2 ADD (
CONSTRAINT bita_look_fk2
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);


Step 2

If the existing table name is big_table then,

ALTER TABLE big_table2
EXCHANGE PARTITION big_table_2007
WITH TABLE big_table
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;

Once this is complete we can drop the old table and rename the new table and all it's constraints.

DROP TABLE big_table;
RENAME big_table2 TO big_table;

ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;

ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;


STEP 3:

Next, we split the single large partition into smaller partitions as required.Syntax is,

ALTER TABLE table_name
SPLIT PARTITION partition_name
AT range_definition
INTO (PARTITION first_partition, PARTITION second_partition)
UPDATE GLOBAL INDEXES;

For our example,

ALTER TABLE big_table
SPLIT PARTITION big_table_2007
AT (TO_DATE('31-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2005, PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;

ALTER TABLE big_table
SPLIT PARTITION big_table_2007
AT (TO_DATE('31-DEC-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2006,
PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;

STEP 4:

The following queries show that the partitioning was successful.

SELECT partitioned FROM user_tables WHERE table_name = 'BIG_TABLE';

SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'BIG_TABLE';

Dont be hopeless. Run the following query and then select user_tables.

EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);

STEP 5:

Merge,

ALTER TABLE table_name
MERGE PARTITIONS partition_name1,partition_name2
INTO PARTITION partition_name1 second_partition first_partition range_definition partition_name table_name ;