Databases Reference
In-Depth Information
INDEX_NAME NULL STATUS
------------------------------ ------------------------------ --------
EMPLOYEES_PARTTEST_GI1 MANAGER_100 UNUSABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_500 UNUSABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_900 UNUSABLE
EMPLOYEES_PARTTEST_GI1 MANAGER_MAX UNUSABLE
EMPLOYEES_PARTTEST_I1 UNUSABLE
EMPLOYEES_PART_LI1 PI1990 USABLE
EMPLOYEES_PART_LI1 PI1995 USABLE
EMPLOYEES_PART_LI1 P1999 UNUSABLE
EMPLOYEES_PART_LI1 PIMAX UNUSABLE
An important note to stress is that if a partition split operation is necessary to essentially add
partitions to a table because there is a need for high-end partition to hold default data of some kind,
partition split operations just to add an empty partition can take quite a bit of time. Oracle needs to
analyze every row in an existing partition in order to effectively perform the split correctly. In this case,
for tables with a high-end value or one where
MAXVALUE
is specified, it may be beneficial to add an unused
"dummy" partition below the high-end partition that is always empty. Then, if you add historical
partitions to your table, you can always use the empty "dummy" partition to split the partition. This
offers two key benefits: the partition split operation will be fast, as there is no data to analyze. Second, no
indexes will be marked unusable because there is no data in either partition, the partition being split or
the new partition.
See the following CREATE TABLE DDL statement. For the
EMPLOYEES_PART
table, once employees
left the company, their
HIRE_DATE
was altered to
MAXVALUE
and left in the table for historical purposes. In
this case, you will always have data in the
PMAX
partition. When you add partitions to the table for future
years by doing a partition split on the
PMAX
partition, it will always take time to do, and the underlying
index partitions will be marked
UNUSABLE
. By creating the P9999 partition as a "dummy" partition, you
will never add any rows into this partition. Then when you split the P9999 partition to add a partition for
2001 data and beyond, it will always be split on an empty partition. Thus, the split operation will be fast,
and all underlying local index partitions will be usable because the split operation occurred on an empty
partition.
CREATE TABLE employees_part
(
EMPLOYEE_ID NUMBER(6) NOT NULL
,FIRST_NAME VARCHAR2(20)
,LAST_NAME VARCHAR2(25) NOT NULL
,EMAIL VARCHAR2(25) NOT NULL
,PHONE_NUMBER VARCHAR2(20)
,HIRE_DATE DATE NOT NULL
,JOB_ID VARCHAR2(10) NOT NULL
,SALARY NUMBER(8,2)
,COMMISSION_PCT NUMBER(2,2)
,MANAGER_ID NUMBER(6)
,DEPARTMENT_ID NUMBER(4)
,constraint employees_part_pk primary key (employee_id, hire_date)
)
partition by range(hire_date)
(
partition p1990 values less than ('1991-01-01'),
partition p1991 values less than ('1992-01-01'),
