Partitioned tables, available since 8.0.5, have been incrementally enhanced in every version of Oracle including the current version. The below article is written in oracle10g.
Advantage of Partitions 1. We can divide and more managable pieces of data.
2. Recovery time can be reduced
3. The impact level is minimal when the data are corrupted.
4. Logical import/export can be done on partition level.
5. Accessing time is faster - Partition Pruning, partition wise join
6. Easy of Administration
Before we start partition the table, we need to think about two important factors. One is what would be the partition key in a table. Another important factor is, what type of partition we need to implement.
Here are the type of partitions.
1.
Range Partition (Introduced in 8.0.5)
2.
Hash Partition (Introduced in 8i)
3.
Range - Hash Partition (Introduced in 8i)
4.
List Partition (Introduced in 9i)
5.
Range - List Partition (Introduced in 9i)
6.
Range - Range Partition (Introduced in 11g)
7.
List - Range Partition (Introduced in 11g)
8.
List - List Partition (Introduced in 11g)
10.
List - Hash Partition (Introduced in 11g)
Oracle 11g also introduced
REF Partitions, Interval Partitions, Virtual columns based Partitions.
Range PartitionsThe data is distributed based on a range of values of the partitioning key. For example, if we choose a date column as the partitioning key, the partition “JAN-2007” will contain all the rows that have the partition key values between 01-JAN-2007 and 31-JAN-2007 (assuming the range of the partition is from first of the month to the last date in the month). Range partition is often used with dates.
Here is the sample code to range partition in a table.
create table trans (
trans_id number,
trans_dt date,
product_code number,
store_id number,
trans_amount number(12,2),
status varchar2(10))
partition by range (trans_dt) (
partition q105 values less than (to_date('04/01/2005','mm/dd/yyyy')) tablespace y05q1,
partition q205 values less than (to_date('07/01/2005','mm/dd/yyyy')) tablespace y05q2,
partition q305 values less than (to_date('10/01/2005','mm/dd/yyyy')) tablespace y05q3,
partition q405 values less than (to_date('01/01/2006','mm/dd/yyyy')) tablespace y05q4,
partition pmax values less than (maxvalue) tablespace users )
Hash Partitions Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to a partitioning key that you identify. The hashing algorithm evenly distributes rows among partitions, given partitions approximately the same size. Hash partitioning is useful when there is no obvious range key, or range partitioning will cause uneven distribution of data. The number of partitions must be a power of 2 (2, 4, 8, 16...) This is typically used where ranges aren't appropriate, i.e. customer number, product ID.
Here is the sample code for Hash Partitions.
CREATE TABLE invoices(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY HASH (invoice_no)
PARTITIONS 4
STORE IN (users, users, users, users);
or we can specify individually.
CREATE TABLE invoices(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY HASH (invoice_no)
(PARTITION invoices_q1 TABLESPACE users,
PARTITION invoices_q2 TABLESPACE users,
PARTITION invoices_q3 TABLESPACE users,
PARTITION invoices_q4 TABLESPACE users);
Range - Hash PartitionsIt is combination of range and hash partitions. So basically, the first step is that the data is divided using the range partition and then each range partitioned data is further subdivided into a hash partition using hash key values. All sub partitions, together, represent a logical subset of the data.
The following statement creates a range-hash partitioned table. In this example, three range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the STORE IN clause distributes them across the 4 specified tablespaces (ts1, ...,ts4).
CREATE TABLE equipment(
equipno NUMBER,
equipname VARCHAR(32),
price NUMBER)
PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)
SUBPARTITIONS 8 STORE IN (ts1,ts2,ts3,ts4)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
List PartitionsThe data distribution is defined by a list of values of the partitioning key. This is useful for discrete lists. e.g: Regions, States etc. List partitioning enables you to control explicitly how rows map to partitions. You do this by specifying a list of discrete values for the partitioning column in the description for each partition.
create table sales (
product_id number,
trans_amt number,
sales_dt date,
state_code varchar2(2))
partition by list (state_code)
(partition ct values ('CT'),
partition ca values ('CA'),
partition def values (default));
Range-List PartitionThis is also a combination of Range and List Partitions, basically first the data is divided using the Range partition and then each Range partitioned data is further subdivided into List partitions using List key values. Each sub partitions individually represents logical subset of the data not like composite Range-Hash Partition.
Like the composite range-hash partitioning method, the composite range-list partitioning method provides for partitioning based on a two level hierarchy. The first level of partitioning is based on a range of values, as for range partitioning; the second level is based on discrete values, as for list partitioning. This form of composite partitioning is well suited for historical data, but lets you further group the rows of data based on unordered or unrelated column values.
CREATE TABLE quarterly_regional_sales
(deptno number,
item_no varchar2(20),
txn_date date,
txn_amount number,
state varchar2(2))
TABLESPACE ts4
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
(PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX') ),
PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
(SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')),
PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
(SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')),
PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
(SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')));
Oracle10g new features in Partitions
1. Global index can be Hash Partitioned in oracle10g.In previous releases of the Oracle Database, only the range-partitioned global indexes were supported. Oracle 10g Database introduces the Hash Partitioning method for global indexes.
Hash partitioned global indexes can improve the performance of indexes where a small number of leaf blocks in the index have high contention in multiuser OLTP environments. It is useful when we have many processes accessing the same areas of the index.
CREATE INDEX hgidx ON tab (c1,c2,c3)
GLOBAL PARTITION BY HASH (c1,c2)
(PARTITION p1 TABLESPACE tbs_1,
PARTITION p2 TABLESPACE tbs_2,
PARTITION p3 TABLESPACE tbs_3,
PARTITION p4 TABLESPACE tbs_4);
2. Invalidating the dependent objects while dropping the partitionIn previous release(oracle9i), the dependent objects will be invalid when we drop the particular partitions. But it is taken care in oracle 10g.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> CREATE TABLE partitioned_table
2 ( x INT
3 , y INT )
4 PARTITION BY LIST (y)
5 ( PARTITION p1 VALUES (10)
6 , PARTITION p2 VALUES (20)
7 , PARTITION p3 VALUES (30) );
Table created.
SQL> CREATE PROCEDURE dependant_procedure AS
2 v_cnt PLS_INTEGER;
3 BEGIN
4 SELECT COUNT(*) INTO v_cnt
5 FROM partitioned_table PARTITION (p1);
6 END dependant_procedure;
7 /
Procedure created.
SQL> CREATE VIEW dependant_view
2 AS
3 SELECT *
4 FROM partitioned_table PARTITION (p1);
View created.
SQL> SELECT substr(object_name,1,20),status
2 FROM user_objects
3 WHERE object_name LIKE 'DEPENDANT%';
SUBSTR(OBJECT_NAME,1 STATUS
-------------------- -------
DEPENDANT_PROCEDURE VALID
DEPENDANT_VIEW VALID
SQL> ALTER TABLE partitioned_table DROP PARTITION p1;
Table altered.
SQL> SELECT substr(object_name,1,20),status
2 FROM user_objects
3 WHERE object_name LIKE 'DEPENDANT%';
SUBSTR(OBJECT_NAME,1 STATUS
-------------------- -------
DEPENDANT_PROCEDURE VALID
DEPENDANT_VIEW VALID
The dependent objects are valid when we drop the partitions. Let us do the same in oracle9i database and see the dependent object status.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> CREATE TABLE partitioned_table
2 ( x INT
3 , y INT )
4 PARTITION BY LIST (y)
5 ( PARTITION p1 VALUES (10)
6 , PARTITION p2 VALUES (20)
7 , PARTITION p3 VALUES (30) );
Table created.
SQL> CREATE PROCEDURE dependant_procedure AS
2 v_cnt PLS_INTEGER;
3 BEGIN
4 SELECT COUNT(*) INTO v_cnt
5 FROM partitioned_table PARTITION (p1);
6 END dependant_procedure;
7 /
Procedure created.
SQL>
SQL> CREATE VIEW dependant_view
2 AS
3 SELECT *
4 FROM partitioned_table PARTITION (p1);
View created.
SQL> SELECT substr(status,1,20)
2 FROM user_objects
3* WHERE object_name LIKE 'DEPENDANT%'
SQL> /
SUBSTR(
-------
VALID
VALID
SQL> ALTER TABLE partitioned_table DROP PARTITION p1;
Table altered.
SQL> SELECT substr(status,1,20)
2 FROM user_objects
3 WHERE object_name LIKE 'DEPENDANT%'
4 /
SUBSTR(
-------
INVALID
INVALID
The dependent objects are INVALID in oracle9i when we drop the partition.
3. Redefining the Partition OnlineIn Previous release(prior to 10g) of oracle, if we want to move the partitioned table to different tablespace, it would move the entire table. This will result in massive amount undo and redo generation. In oracle10g R2, you can move single partition at a time without any downtime. So, if the table has 4 partitions, then we have to do the same step four times. It will result less amount of undo/redo generation when we do partition by partition.
In oracle10gR2, online redefinition can be done on single partition. Suppose you have a table TRANS that contains history of transactions. This table is partitioned on the TRANS_DATE, with each quarter as a partition. During the normal course of business, the most recent partitions are updated frequently. After a quarter is complete, there may not be much activity on that partition and it can be moved to a different location. However, the move itself will require a lock on the table, denying public access to the partition. In oracle10gR2, we can move the specific partition to different tablespace with no impact on its availability.
Steps to move the partition to different tablespace. 1. Create the table with partition.Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> CREATE TABLE accounts
2 ( invoice_no NUMBER,
3 sale_year INT NOT NULL )
4 PARTITION BY RANGE (sale_year)
5 ( PARTITION p1 VALUES LESS THAN (1999),
6 PARTITION p2 VALUES LESS THAN (2000),
7 PARTITION p3 VALUES LESS THAN (2001),
8 PARTITION p4 VALUES LESS THAN (2002));
Table created.
SQL> create index global_idx_accounts
2 on accounts(sale_year);
Index created.
SQL> select partition_name, tablespace_name from user_tab_partitions;
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P1 USERS
P2 USERS
P3 USERS
P4 USERS
Create the temp tables with same structure of partition tableSQL> create table accounts_int
2 tablespace sysaux
3 as
4 select * from accounts
5 where 1=2
6 /
Table created.
SQL>
3. Confirm that we can redefine the partition
SQL> begin
2 dbms_redefinition.can_redef_table(
3 uname => 'SCOTT',
4 tname => 'ACCOUNTS',
5 options_flag => dbms_redefinition.cons_use_rowid,
6 part_name => 'P1');
7 end;
8 /
PL/SQL procedure successfully completed.
4. Start the redefinition
SQL> alter table accounts add primary key(invoice_no);
Table altered.
SQL> begin
2 dbms_redefinition.start_redef_table (
3 uname => 'SCOTT',
4 orig_table => 'ACCOUNTS',
5 int_table => 'ACCOUNTS_INT',
6 part_name => 'P1'
7 );
8 end;
9 /
PL/SQL procedure successfully completed.
5. Sync the redefinition
SQL> begin
2 dbms_redefinition.sync_interim_table (
3 uname => 'SCOTT',
4 orig_table => 'ACCOUNTS',
5 int_table => 'ACCOUNTS_INT',
6 part_name => 'P1');
7 end;
8 /
PL/SQL procedure successfully completed.
6. Finish the redefinition
SQL> begin
2 dbms_redefinition.finish_redef_table (
3 uname => 'SCOTT',
4 orig_table => 'ACCOUNTS',
5 int_table => 'ACCOUNTS_INT',
6 part_name => 'P1'
7 );
8 end;
9 /
PL/SQL procedure successfully completed.
7. Check the that partition is moved to different tablespace
SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 /
PARTITION_NAME TABLESPACE_NAME
------------------------------ -----------------------------
P1 SYSAUX
P2 USERS
P3 USERS
P4 USERS
SQL> select status from user_indexes where table_name='ACCOUNTS';
STATUS
--------
UNUSABLE
UNUSABLE
Note: If there is any global index on the table, they will be marked as UNUSABLE and must be rebuilt.
4. Drop a Table in Chunks Prior to 10g, when we drop the huge table in peak time, it would take lot of CPU, and more resoures. It would slow down the DB performance. It is taken care in oracle 10g. In Oracle Database 10g Release 2, when you drop a partitioned table, partitions are dropped one by one. Because each partition is dropped individually, fewer resources are required than when the table is dropped as a whole.
To demonstrate this new behavior, you can trace the session with a 10046 trace. alter session set events '10046 trace name context forever, level 12';
Then drop the table. If you examine the trace file, you'll see how a partitioned-table drop looks:
delete from tabpart$ where bo# = :1
delete from partobj$ where obj#=:1
delete from partcol$ where obj#=:1
delete from subpartcol$ where obj#=:1
Note that the partitions are deleted in serial fashion. This approach minimizes resource utilization during the drop process and enhances performance.
5. Partition-Change Tracking: No Need for MV Logs In oracle10g, it avoids the materialized view log when the master table is partitioned. The ability to fast-refresh an MV even when the base tables do not have MV logs is a powerful and useful feature, allowing you to do fast refreshes in partitioned MVs without the added performance overhead.
6.Quick Partition Split for Partitioned, Index-Organized Tables In oracle10g R2, quick split partition is introduced for IOT. Let us say, we have Partitions till Dec 2008. When we enter the data for Jan 2009, the data will be stored in MAX partition. Later, when we split the partition for Jan 2009, Prior to 10g, oracle create a new partition and moves the data from MAX partition to Jan 2009 partition. It requires more resources. To avoid this, oracle introduced new feature Quick split partition for IOT. In Oracle10gR2, oracle rename the MAX partition to Jan 2009 partition and create a new partition as a MAX partition. So it avoids moving the data from one partition to another partition.
Managing Oracle Partitions
1. Add Partition
2. Split Partition
3. Drop Partition
4. Exchange Partition
5. Merge Partition
6. Move Partition
7. Rename Partition
8. Truncate Partition
9. Coalesce Partition
Adding Partition
This section describes how to add new partitions to a partitioned table
Adding Partition on Range Partitioned table
Use the ALTER TABLE ... ADD PARTITION statement to add a new partition to the "high" end (the point after the last existing partition). To add a partition at the beginning or in the middle of a table, use the SPLIT PARTITION clause.
SQL> CREATE TABLE employees
2 (employee_id NUMBER(4) NOT NULL,
3 last_name VARCHAR2(10),
4 department_id NUMBER(2))
5 PARTITION BY RANGE (department_id)
6 (PARTITION employees_part1 VALUES LESS THAN (10) TABLESPACE tab1,
7 PARTITION employees_part2 VALUES LESS THAN (20) TABLESPACE tab2,
8 PARTITION employees_part3 VALUES LESS THAN (30) TABLESPACE tab3);
Table created.
SQL> CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL;
Index created.
SQL> CREATE INDEX employees_global_idx ON employees(last_name);
Index created.
SQL> insert into employees values(1,'Mike',10);
1 row created.
SQL> insert into employees values(2,'Scott',20);
1 row created.
SQL> insert into employees values(3,'Rob',30);
1 row created.
SQL>
SQL> alter table employees
2 add partition employees_part4 values less than (41)
3 tablespace ods_stage_data
4 /
Table altered.
SQL> insert into employees values(50,'Mike',40);
1 row created.
SQL>
After adding new partition, what would happen for Local and Global indexes.. Let us check the indexes.
SQL> select index_name,status from user_indexes where
2 table_name='EMPLOYEES';
INDEX_NAME STATUS
------------------------------ --------
EMPLOYEES_GLOBAL_IDX VALID
EMPLOYEES_LOCAL_IDX N/A
SQL> select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
EMPLOYEES_LOCAL_IDX EMPLOYEES_PART1 USABLE
EMPLOYEES_LOCAL_IDX EMPLOYEES_PART2 USABLE
EMPLOYEES_LOCAL_IDX EMPLOYEES_PART3 USABLE
EMPLOYEES_LOCAL_IDX EMPLOYEES_PART4 USABLE
SQL>
Local and global indexes associated with the range-partitioned table remain usable when we add new partitions.
Adding Partition on Hash partition table
When you add a partition to a hash-partitioned table, the database populates the new partition with rows rehashed from an existing partition as determined by the hash function.
SQL> CREATE TABLE invoices
2 (invoice_no NUMBER NOT NULL,
3 invoice_date DATE NOT NULL,
4 comments VARCHAR2(500))
5 PARTITION BY HASH (invoice_no)
6 PARTITIONS 4
7 STORE IN (ods_stage_data,ods_stage_data,ods_stage_data,ods_stage_data);
Table created.
SQL>
SQL> CREATE INDEX invoices_local_idx ON invoices(invoice_no) LOCAL;
Index created.
SQL>
SQL> CREATE INDEX invoices_global_idx ON invoices(invoice_date);
Index created.
SQL>
SQL> declare
2 v_no number := 1;
3 begin
4 for i in 1..10000 loop
5 insert into invoices values(v_no,sysdate+v_no,'xyz');
6 v_no := v_no +1;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> alter table invoices add partition;
Table altered.
SQL> select index_name ,status
2 from user_indexes where table_name='INVOICES';
INDEX_NAME STATUS
------------------------------ --------
INVOICES_GLOBAL_IDX UNUSABLE
INVOICES_LOCAL_IDX N/A
SQL>
SQL> select partition_name, status from user_ind_partitions
2 where index_name='INVOICES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
SYS_P156 UNUSABLE
SYS_P152 UNUSABLE
SYS_P153 USABLE
SYS_P154 USABLE
SYS_P155 USABLE
SQL> ALTER INDEX INVOICES_GLOBAL_IDX rebuild;
Index altered.
SQL> alter table invoices modify partition SYS_P156
2 rebuild unusable local indexes;
Table altered.
SQL> alter table invoices modify partition SYS_P152
2 rebuild unusable local indexes;
Table altered.
SQL> select index_name ,status
2 from user_indexes where table_name='INVOICES';
INDEX_NAME STATUS
------------------------------ --------
INVOICES_GLOBAL_IDX VALID
INVOICES_LOCAL_IDX N/A
SQL> select partition_name, status from user_ind_partitions
2 where index_name='INVOICES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
SYS_P156 USABLE
SYS_P152 USABLE
SYS_P153 USABLE
SYS_P154 USABLE
SYS_P155 USABLE
SQL> alter table invoices
2 add partition MAX VALUES LESS THAN (MAXVALUE);
add partition MAX VALUES LESS THAN (MAXVALUE)
*
ERROR at line 2:
ORA-14261: partition bound may not be specified when adding this Hash partition
SQL> ALTER TABLE invoices
2 add partition dummypart;
Table altered.
SQL> select partition_name from user_tab_partitions
2 where table_name='INVOICES';
PARTITION_NAME
------------------------------
SYS_P156
SYS_P157
DUMMYPART
SYS_P152
SYS_P153
SYS_P154
SYS_P155
7 rows selected.
The local indexes for the new partition, and for the existing partition from which rows were redistributed, are marked UNUSABLE and must be rebuilt. All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.
Adding Partition on List Partitioned table
We can add new partition with new list of values. We can not add default partition on List partition table.
SQL> create table sales
2 (deptno number,
3 deptname varchar2(20),
4 quarterly_sales number(10, 2),
5 state varchar2(2))
6 PARTITION BY LIST (state)
7 (PARTITION q1_northwest VALUES ('OR', 'WA'),
8 PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
9 PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ'),
10 PARTITION q1_southeast VALUES ('FL', 'GA'),
11 PARTITION q1_northcentral VALUES ('SD', 'WI'),
12 PARTITION q1_southcentral VALUES ('OK', 'TX'))
13 /
Table created.
SQL> create index sales_local_idx on sales(deptno) local;
Index created.
SQL> create index sales_global_idx on sales(state);
Index created.
SQL> alter table sales
2 add partition MAX VALUES LESS THAN(MAXVALUE);
add partition MAX VALUES LESS THAN(MAXVALUE)
*
ERROR at line 2:
ORA-14310: VALUES LESS THAN or AT clause cannot be used with List partitioned
tables
SQL> alter table sales
2 ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')
3 ;
Table altered.
SQL> select index_name ,status
2 from user_indexes where table_name='SALES';
INDEX_NAME STATUS
------------------------------ --------
SALES_GLOBAL_IDX VALID
SALES_LOCAL_IDX N/A
SQL>
SQL> select partition_name, status from user_ind_partitions
2 where index_name='SALES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
Q1_NORTHWEST USABLE
Q1_SOUTHWEST USABLE
Q1_NORTHEAST USABLE
Q1_SOUTHEAST USABLE
Q1_NORTHCENTRAL USABLE
Q1_SOUTHCENTRAL USABLE
Q1_NONMAINLAND USABLE
7 rows selected.
Local and global indexes associated with the list-partitioned table remain usable.
We can also add new partition, sub partition on Range-Hash partition and Range-List partition.
Splitting Partition
The SPLIT PARTITION clause of the ALTER TABLE or ALTER INDEX statement is used to redistribute the contents of a partition into two new partitions. Consider doing this when a partition becomes too large and causes backup, recovery, or maintenance operations to take a long time to complete. You can also use the SPLIT PARTITION clause to redistribute the I/O load.
Splitting partition on Range partition table
You split a range partition using the ALTER TABLE ... SPLIT PARTITION statement. You specify a value of the partitioning key column within the range of the partition at which to split the partition. The first of the resulting two new partitions includes all rows in the original partition whose partitioning key column values map lower that the specified value. The second partition contains all rows whose partitioning key column values map greater than or equal to the specified value.
Let us take the Employees table. Now we add MAX partition on this table.
SQL> alter table employees add partition MAX VALUES LESS THAN (MAXVALUE);
Table altered.
SQL> insert into employees values(100,'Hendry',50);
1 row created.
Now we will try to add new partition. But at this scenario, oracle would not let to create a new partition. The only option is to split the MAX partition.
SQL> alter table employees
2 add partition employees_part5 values less than (51)
3 tablespace ods_stage_data
4 /
add partition employees_part5 values less than (51)
*
ERROR at line 2:
ORA-14074: partition bound must collate higher than that of the last partition
SQL>
Now we need to split the MAX partition.
SQL> Alter table employees
2 Split partition MAX at (51)
3 INTO
4 (PARTITION employees_part6 tablespace ods_stage_data,
5 PARTITION MAX)
6 ;
Table altered.
SQL> select index_name,status from user_indexes where table_name='EMPLOYEES';
INDEX_NAME STATUS
------------------------------ --------
EMPLOYEES_GLOBAL_IDX VALID
EMPLOYEES_LOCAL_IDX N/A
SQL> select index_name, partition_name, status from user_ind_partitions;
PARTITION_NAME STATUS
------------------------------ ------------------------------
EMPLOYEES_PART1 USABLE
EMPLOYEES_PART2 USABLE
EMPLOYEES_PART3 USABLE
EMPLOYEES_PART4 USABLE
EMPLOYEES_PART5 USABLE
MAX USABLE
6 rows selected.
Note that, the indexs are usable, because, MAX partition data are belonging to only new partition employees_part6. So Oracle rename the MAX partition as new partition and creates new partition as MAX partition.
SQL> insert into employees values(101,'Mani',51)
1 row created.
SQL> insert into employees values(102,'Mani',61)
1 row created.
SQL>insert into employees values(102,'Mani',71)
1 row created.
SQL> insert into employees values(102,'Mani',72)
1 row created.
SQL> Alter table employees
2 Split partition MAX at (61)
3 INTO
4 (PARTITION employees_part6,
5 PARTITION MAX)
6 ;
Table altered.
SQL>
SQL> select status from user_indexes where table_name='EMPLOYEES';
STATUS
--------
UNUSABLE
N/A
SQL> select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
EMPLOYEES_LOCAL_IDX EMPLOYEES_PART1 USABLE
EMPLOYEES_LOCAL_IDX EMPLOYEES_PART2 USABLE
EMPLOYEES_LOCAL_IDX EMPLOYEES_PART3 USABLE
EMPLOYEES_LOCAL_IDX EMPLOYEES_PART6 UNUSABLE
EMPLOYEES_LOCAL_IDX EMPLOYEES_PART4 USABLE
EMPLOYEES_LOCAL_IDX MAX UNUSABLE
EMPLOYEES_LOCAL_IDX EMPLOYEES_PART5 USABLE
7 rows selected.
Now GLOBAL index is UNUSABLE and local index is UNUSABLE for partitions(EMPLOYEES_PART6 ,MAX). Because, we splitted only MAX partition. so other partition local indxes are intact.
SQL> insert into employees values(101,'xx',60);
insert into employees values(101,'xx',60)
*
ERROR at line 1:
ORA-01502: index 'SCREPORT.EMPLOYEES_LOCAL_IDX' or partition of such index is
in unusable state
SQL> ALTER TABLE employees MODIFY PARTITION EMPLOYEES_PART6
2 REBUILD UNUSABLE LOCAL INDEXES;
Table altered.
SQL>
SQL> ALTER TABLE employees MODIFY PARTITION MAX
2 REBUILD UNUSABLE LOCAL INDEXES;
Table altered.
SQL> alter index employees_global_idx rebuild;
Index altered.
SQL> insert into employees values(101,'xx',60);
1 row created.
In the below example, global index is rebuild automatically when we use UPDATE GLOBAL INDEX. But we need to rebuild the local index manually.
SQL> Alter table employees
2 Split partition MAX at (71)
3 INTO
4 (PARTITION employees_part7,
5 PARTITION MAX)
6 UPDATE GLOBAL INDEXES
7 ;
Table altered.
SQL> select status from user_indexes where table_name='EMPLOYEES';
STATUS
--------
VALID
N/A
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='EMPLOYEES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
EMPLOYEES_PART1 USABLE
EMPLOYEES_PART2 USABLE
EMPLOYEES_PART3 USABLE
EMPLOYEES_PART6 USABLE
EMPLOYEES_PART4 USABLE
EMPLOYEES_PART7 UNUSABLE
MAX UNUSABLE
EMPLOYEES_PART5 USABLE
8 rows selected.
SQL> ALTER TABLE employees MODIFY PARTITION EMPLOYEES_PART7
2 REBUILD UNUSABLE LOCAL INDEXES;
Table altered.
SQL> ALTER TABLE employees MODIFY PARTITION MAX
2 REBUILD UNUSABLE LOCAL INDEXES;
Table altered.
Splitting partition on Hash partition table
This option is not available in Hash partitions.
Splitting partition on List partition table
You split a list partition using the ALTER TABLE ... SPLIT PARTITION statement. The SPLIT PARTITION clause lets you specify a value list of literal values that define a partition into which rows with corresponding partitioning key values are inserted. The remaining rows of the original partition are inserted into a second partition whole value list is the remaining values from the original partition.
SQL> ALTER TABLE sales
2 SPLIT PARTITION q1_northwest VALUES ('OR')
3 INTO
4 ( PARTITION q1_northwest_1
5 TABLESPACE ods_stage_data,
6 PARTITION q1_northwest_2
7 TABLESPACE ods_stage_data)
8 /
Table altered.
SQL>
Originally q1_northwest partition has two values(OR,WA). After splitting this partition, this region becomes two paritions.
1. q1_northwest_1 which contains OR
2.q1_northwest_2 which contains WA
SQL> select status from user_indexes where table_name='SALES';
STATUS
--------
UNUSABLE
N/A
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='SALES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
Q1_NORTHWEST_1 UNUSABLE
Q1_NORTHWEST_2 UNUSABLE
Q1_SOUTHWEST USABLE
Q1_NORTHEAST USABLE
Q1_SOUTHEAST USABLE
Q1_NORTHCENTRAL USABLE
Q1_SOUTHCENTRAL USABLE
Q1_NONMAINLAND USABLE
8 rows selected.
SQL> alter index sales_global_idx rebuild;
Index altered.
SQL> alter table sales MODIFY PARTITION Q1_NORTHWEST_1
2 REBUILD UNUSABLE LOCAL INDEXES;
Table altered.
SQL> alter table sales MODIFY PARTITION Q1_NORTHWEST_2
2 REBUILD UNUSABLE LOCAL INDEXES;
Table altered.
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='SALES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
Q1_NORTHWEST_1 USABLE
Q1_NORTHWEST_2 USABLE
Q1_SOUTHWEST USABLE
Q1_NORTHEAST USABLE
Q1_SOUTHEAST USABLE
Q1_NORTHCENTRAL USABLE
Q1_SOUTHCENTRAL USABLE
Q1_NONMAINLAND USABLE
8 rows selected.
SQL> select index_name from user_indexes where table_name='SALES';
INDEX_NAME
------------------------------
SALES_GLOBAL_IDX
SALES_LOCAL_IDX
SQL>
The global index and associated local indexs are unusable. We need to rebuild after splitting the partitions.
We can also split Range-Hash, Rang-List partition.
Dropping partitions
You can drop partitions from range, list, or composite range-list partitioned tables. For hash-partitioned tables, or hash subpartitions of range-hash partitioned tables, you must perform a coalesce operation instead.
Dropping partition on Range partition table
Dropping partition making global index UNUSABLE. But local index is intact.
SQL> alter table employees drop partition EMPLOYEES_PART7;
Table altered.
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='EMPLOYEES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
EMPLOYEES_PART1 USABLE
EMPLOYEES_PART2 USABLE
EMPLOYEES_PART3 USABLE
EMPLOYEES_PART6 USABLE
EMPLOYEES_PART4 USABLE
MAX USABLE
EMPLOYEES_PART5 USABLE
7 rows selected.
SQL> select index_name,status from user_indexes where table_name='EMPLOYEES';
INDEX_NAME STATUS
------------------------------ --------
EMPLOYEES_GLOBAL_IDX UNUSABLE
EMPLOYEES_LOCAL_IDX N/A
SQL> alter index employees_global_idx rebuild;
Index altered.
SQL> select index_name,status from user_indexes where table_name='EMPLOYEES';
INDEX_NAME STATUS
------------------------------ --------
EMPLOYEES_GLOBAL_IDX VALID
EMPLOYEES_LOCAL_IDX N/A
SQL>
Dropping Partition on Hash partitioned table
We can not drop any partition on Hash partitioned table.
Dropping Partition on List partitioned table
SQL> alter table sales drop partition Q1_NORTHWEST_1;
Table altered.
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='SALES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
Q1_NORTHWEST_2 USABLE
Q1_SOUTHWEST USABLE
Q1_NORTHEAST USABLE
Q1_SOUTHEAST USABLE
Q1_NORTHCENTRAL USABLE
Q1_SOUTHCENTRAL USABLE
Q1_NONMAINLAND USABLE
7 rows selected.
SQL>
SQL> select index_name,status from user_indexes where table_name='SALES';
INDEX_NAME STATUS
------------------------------ --------
SALES_GLOBAL_IDX UNUSABLE
SALES_LOCAL_IDX N/A
SQL>
SQL> alter index sales_global_idx rebuild;
Index altered.
SQL>
SQL> select index_name,status from user_indexes where table_name='SALES';
INDEX_NAME STATUS
------------------------------ --------
SALES_GLOBAL_IDX VALID
SALES_LOCAL_IDX N/A
Dropping partition invalidate the global index. But the local indexes are intact..
We can also drop individual partitions on Rang-List, Rang-Hash partitions.
Exchange Partitions
We now switch the segments associated with the source table and the partition in the destination table using the EXCHANGE PARTITION
Exchanging Partition on Range partition table
SQL> declare
2 v_no number:=52;
3 begin
4 for i in 1..10 loop
5 insert into tempemp values(110,'yy',v_no);
6 v_no := v_no+1;
7 exit when v_no=60;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> Alter table employees exchange PARTITION employees_part6 WITH TABLE tempemp;
Table altered.
SQL> select index_name,status from user_indexes where table_name='EMPLOYEES';
INDEX_NAME STATUS
------------------------------ --------
EMPLOYEES_GLOBAL_IDX UNUSABLE
EMPLOYEES_LOCAL_IDX N/A
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='EMPLOYEES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
EMPLOYEES_PART1 USABLE
EMPLOYEES_PART2 USABLE
EMPLOYEES_PART3 USABLE
EMPLOYEES_PART6 UNUSABLE
EMPLOYEES_PART4 USABLE
MAX USABLE
EMPLOYEES_PART5 USABLE
7 rows selected.
SQL> alter index employees_global_idx rebuild;
Index altered.
SQL> ALTER TABLE employees MODIFY PARTITION EMPLOYEES_PART6
2 REBUILD UNUSABLE LOCAL INDEXES;
Table altered.
SQL> select index_name,status from user_indexes where table_name='EMPLOYEES';
INDEX_NAME STATUS
------------------------------ --------
EMPLOYEES_GLOBAL_IDX VALID
EMPLOYEES_LOCAL_IDX N/A
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='EMPLOYEES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
EMPLOYEES_PART1 USABLE
EMPLOYEES_PART2 USABLE
EMPLOYEES_PART3 USABLE
EMPLOYEES_PART6 USABLE
EMPLOYEES_PART4 USABLE
MAX USABLE
EMPLOYEES_PART5 USABLE
7 rows selected.
SQL>
Exchanging data with Hash Partition table
SQL> alter table invoices exchange partition sys_p152 with table exchinv;
Table altered.
SQL> select partition_name, status from user_ind_partitions
2 where index_name='INVOICES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
SYS_P156 USABLE
SYS_P152 UNUSABLE
SYS_P157 USABLE
DUMMYPART USABLE
SYS_P153 USABLE
SYS_P154 USABLE
SYS_P155 USABLE
7 rows selected.
SQL> select index_name ,status
2 from user_indexes where table_name='INVOICES';
INDEX_NAME STATUS
------------------------------ --------
INVOICES_GLOBAL_IDX UNUSABLE
INVOICES_LOCAL_IDX N/A
SQL> alter table invoices modify partition SYS_P152
2 rebuild unusable local indexes;
Table altered.
SQL> alter index INVOICES_GLOBAL_IDX rebuild;
Index altered.
Exchanging data with List Partition table
SQL> alter table sales exchange partition Q1_NORTHWEST_2 with table exchsales;
Table altered.
SQL> select partition_name, status from user_ind_partitions
2 where index_name='SALES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
Q1_NORTHWEST_2 UNUSABLE
Q1_SOUTHWEST USABLE
Q1_NORTHEAST USABLE
Q1_SOUTHEAST USABLE
Q1_NORTHCENTRAL USABLE
Q1_SOUTHCENTRAL USABLE
Q1_NONMAINLAND USABLE
7 rows selected.
SQL>
SQL> select index_name ,status
2 from user_indexes where table_name='SALES';
INDEX_NAME STATUS
------------------------------ --------
SALES_GLOBAL_IDX UNUSABLE
SALES_LOCAL_IDX N/A
SQL> select partition_name, status from user_ind_partitions
2 where index_name='SALES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
Q1_NORTHWEST_2 UNUSABLE
Q1_SOUTHWEST USABLE
Q1_NORTHEAST USABLE
Q1_SOUTHEAST USABLE
Q1_NORTHCENTRAL USABLE
Q1_SOUTHCENTRAL USABLE
Q1_NONMAINLAND USABLE
7 rows selected.
SQL> alter table sales modify partition Q1_NORTHWEST_2
2 rebuild unusable local indexes;
Table altered.
SQL>
SQL> alter index SALES_GLOBAL_IDX rebuild;
Index altered.
SQL> select partition_name, status from user_ind_partitions
2 where index_name='SALES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
Q1_NORTHWEST_2 USABLE
Q1_SOUTHWEST USABLE
Q1_NORTHEAST USABLE
Q1_SOUTHEAST USABLE
Q1_NORTHCENTRAL USABLE
Q1_SOUTHCENTRAL USABLE
Q1_NONMAINLAND USABLE
7 rows selected.
SQL> select index_name ,status
2 from user_indexes where table_name='SALES';
INDEX_NAME STATUS
------------------------------ --------
SALES_GLOBAL_IDX VALID
SALES_LOCAL_IDX N/A
Global index and associated local index will be unusuable and it needs to rebuild. We can exchange partition, sub partition on range-hash, range-list partitions.
Merge Partitions
We can use the ALTER TABLE ... MERGE PARTITION statement to merge the contents of two partitions into one partition. The two original partitions are dropped, as are any corresponding local indexes. We cannot use this statement for a hash-partitioned table or for hash subpartitions of a range-hash partitioned table.
Merging Partition on Range partition table
SQL> Alter table employees
2 Merge partitions employees_part1, employees_part6
3 Into partition employees_part16
4 /
Merge partitions employees_part1, employees_part6
*
ERROR at line 2:
ORA-14274: partitions being merged are not adjacent
SQL> Alter table employees
2 Merge partitions employees_part4, employees_part5
3 Into partition employees_part15
4 tablespace ods_stage_data;
Table altered.
SQL> select index_name,status from user_indexes where table_name='EMPLOYEES';
INDEX_NAME STATUS
------------------------------ --------
EMPLOYEES_GLOBAL_IDX UNUSABLE
EMPLOYEES_LOCAL_IDX N/A
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='EMPLOYEES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
EMPLOYEES_PART1 USABLE
EMPLOYEES_PART2 USABLE
EMPLOYEES_PART3 USABLE
EMPLOYEES_PART6 USABLE
EMPLOYEES_PART15 UNUSABLE
MAX USABLE
6 rows selected.
SQL>
SQL> alter index employees_global_idx rebuild;
Index altered.
SQL>
SQL> ALTER TABLE employees MODIFY PARTITION EMPLOYEES_PART15
2 REBUILD UNUSABLE LOCAL INDEXES;
Table altered.
SQL> select index_name,status from user_indexes where table_name='EMPLOYEES';
INDEX_NAME STATUS
------------------------------ --------
EMPLOYEES_GLOBAL_IDX VALID
EMPLOYEES_LOCAL_IDX N/A
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='EMPLOYEES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
EMPLOYEES_PART1 USABLE
EMPLOYEES_PART2 USABLE
EMPLOYEES_PART3 USABLE
EMPLOYEES_PART6 USABLE
EMPLOYEES_PART15 USABLE
MAX USABLE
6 rows selected.
SQL>
Merging Hash Partitions - We can not merge two hash partitions...
Merging List Partitions
SQL> Alter table sales
2 Merge partitions Q1_NORTHWEST_2, Q1_SOUTHCENTRAL
3 Into partition newpart
4 /
Table altered.
SQL> select index_name,status from user_indexes where table_name='SALES';
INDEX_NAME STATUS
------------------------------ --------
SALES_GLOBAL_IDX UNUSABLE
SALES_LOCAL_IDX N/A
SQL>
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='SALES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
NEWPART UNUSABLE
Q1_SOUTHWEST USABLE
Q1_NORTHEAST USABLE
Q1_SOUTHEAST USABLE
Q1_NORTHCENTRAL USABLE
Q1_NONMAINLAND USABLE
6 rows selected.
SQL> alter index sales_global_idx rebuild;
Index altered.
SQL> ALTER TABLE sales MODIFY PARTITION newpart
2 REBUILD UNUSABLE LOCAL INDEXES;
Table altered.
SQL> select index_name,status from user_indexes where table_name='SALES';
INDEX_NAME STATUS
------------------------------ --------
SALES_GLOBAL_IDX VALID
SALES_LOCAL_IDX N/A
SQL>
SQL>
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='SALES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
NEWPART USABLE
Q1_SOUTHWEST USABLE
Q1_NORTHEAST USABLE
Q1_SOUTHEAST USABLE
Q1_NORTHCENTRAL USABLE
Q1_NONMAINLAND USABLE
6 rows selected.
SQL>
For range partition, we can merge only adjecent partitions. But in List partition, we can merge any two partitions. The global index, associated local index needs to be rebuilt after merging the partitions.
Moving Partitions
We can move partitions/sub partitions from one location to another location. We can move the partitions for any one of the below purpose.
1.Re-cluster data and reduce fragmentation
2.Move a partition to another tablespace
3.Modify create-time attributes
4.Store the data in compressed format using table compression
We can Move a range, hash, or list partition, using the ALTER TABLE ... MOVE PARTITION statement.
SQL> alter table sales
2 move partition newpart
3 tablespace ods_stage_data
4 update global indexes;
Table altered.
SQL> select index_name,status from user_indexes where table_name='SALES';
INDEX_NAME STATUS
------------------------------ --------
SALES_GLOBAL_IDX VALID
SALES_LOCAL_IDX N/A
SQL> ALTER TABLE sales MODIFY PARTITION newpart
2 REBUILD UNUSABLE LOCAL INDEXES;
Table altered.
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='SALES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
NEWPART USABLE
Q1_SOUTHWEST USABLE
Q1_NORTHEAST USABLE
Q1_SOUTHEAST USABLE
Q1_NORTHCENTRAL USABLE
Q1_NONMAINLAND USABLE
6 rows selected.
SQL>
Renaming Partitions
It is possible to rename partitions and subpartitions of both tables and indexes. One reason for renaming a partition might be to assign a meaningful name, as opposed to a default system name that was assigned to the partition in another maintenance operation.
SQL> alter table employees
2 rename partition employees_part1 to employees_part111;
Table altered.
SQL> select index_name,status from user_indexes where table_name='EMPLOYEES';
INDEX_NAME STATUS
------------------------------ --------
EMPLOYEES_GLOBAL_IDX VALID
EMPLOYEES_LOCAL_IDX N/A
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='EMPLOYEES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
EMPLOYEES_PART1 USABLE
EMPLOYEES_PART2 USABLE
EMPLOYEES_PART3 USABLE
EMPLOYEES_PART6 USABLE
EMPLOYEES_PART15 USABLE
MAX USABLE
6 rows selected.
SQL> select partition_name from user_tab_partitions
2 where table_name='EMPLOYEES';
PARTITION_NAME
------------------------------
EMPLOYEES_PART2
EMPLOYEES_PART3
EMPLOYEES_PART111
EMPLOYEES_PART15
MAX
EMPLOYEES_PART6
6 rows selected.
SQL>
Renaming partitions would not impact the indexes. Index will be intact after renaming the partitions.
Truncating Partitions
We can use the ALTER TABLE ... TRUNCATE PARTITION statement to remove all rows from a table partition. Truncating a partition is similar to dropping a partition, except that the partition is emptied of its data, but not physically dropped.
You cannot truncate an index partition. However, if local indexes are defined for the table, the ALTER TABLE ... TRUNCATE PARTITION statement truncates the matching partition in each local index. Unless you specify UPDATE INDEXES (cannot be specified for index-organized tables), any global indexes are marked UNUSABLE and must be rebuilt.
SQL> alter table employees
2 truncate partition employees_part111;
Table truncated.
SQL> select index_name,status from user_indexes where table_name='EMPLOYEES';
INDEX_NAME STATUS
------------------------------ --------
EMPLOYEES_GLOBAL_IDX UNUSABLE
EMPLOYEES_LOCAL_IDX N/A
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='EMPLOYEES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
EMPLOYEES_PART1 USABLE
EMPLOYEES_PART2 USABLE
EMPLOYEES_PART3 USABLE
EMPLOYEES_PART6 USABLE
EMPLOYEES_PART15 USABLE
MAX USABLE
6 rows selected.
SQL> alter index employees_global_idx rebuild;
Index altered.
SQL> select index_name,status from user_indexes where table_name='EMPLOYEES';
INDEX_NAME STATUS
------------------------------ --------
EMPLOYEES_GLOBAL_IDX VALID
EMPLOYEES_LOCAL_IDX N/A
SQL>
Coalescing Partitions
Coalescing partitions is a way of reducing the number of partitions in a hash-partitioned table, or the number of subpartitions in a composite-partitioned table. When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by Oracle, and is dropped after its contents have been redistributed.
The ALTER TABLE ... COALESCE PARTITION statement is used to coalesce a partition in a hash-partitioned table. The following statement reduces by one the number of partitions in a table by coalescing a partition
SQL> alter table invoices
2 COALESCE PARTITION;
Table altered.
SQL> select partition_name, status from user_ind_partitions
2 WHERE index_name='INVOICES_LOCAL_IDX';
PARTITION_NAME STATUS
------------------------------ --------
SYS_P156 USABLE
SYS_P152 USABLE
SYS_P157 USABLE
SYS_P153 USABLE
SYS_P154 UNUSABLE
SYS_P155 USABLE
6 rows selected.
SQL> select index_name,status from user_indexes where table_name='INVOICES';
INDEX_NAME STATUS
------------------------------ --------
INVOICES_GLOBAL_IDX UNUSABLE
INVOICES_LOCAL_IDX N/A
SQL> alter index INVOICES_GLOBAL_IDX rebuild;
Index altered.
SQL> alter table invoices MODIFY PARTITION SYS_P154
2 REBUILD UNUSABLE LOCAL INDEXES;
Table altered.
SQL>
Modifying List Partition
List partitioning allows you the option of adding and dropping literal values from the defining value list.
Adding values for List Partitions.
Use the MODIFY PARTITION ... ADD VALUES clause of the ALTER TABLE statement to extend the value list of an existing partition. Literal values being added must not have been included in any other partition value list. The partition value list for any corresponding local index partition is correspondingly extended, and any global index, or global or local index partitions, remain usable.
The following statement adds a new set of state code ('KS') to an existing partition list.
SQL> ALTER TABLE sales
2 MODIFY PARTITION q1_southeast
3 ADD VALUES ('KS');
Table altered.
This changes would not make index UNUSABLE. We do not need to rebuild the index.
Dropping values for List Partitions.
Use the MODIFY PARTITION ... DROP VALUES clause of the ALTER TABLE statement to remove literal values from the value list of an existing partition. The statement is always executed with validation, meaning that it checks to see if any rows exist in the partition that correspond to the set of values being dropped. If any such rows are found then the database returns an error message and the operation fails. When necessary, use a DELETE statement to delete corresponding rows from the table before attempting to drop values.
SQL> ALTER TABLE sales
2 MODIFY PARTITION q1_southwest
3 DROP VALUES ('AZ');
ALTER TABLE sales
*
ERROR at line 1:
ORA-14518: partition contains rows corresponding to values being dropped
SQL> delete sales where state='AZ';
1 row deleted.
SQL> ALTER TABLE sales
2 MODIFY PARTITION q1_southwest
3 DROP VALUES ('AZ');
Table altered.
SQL>
Oracle11g New features in Partitions
Interval Partitioning: Introduced in 11g, interval partitions are extensions to range partitioning. These provide automation for equi-sized range partitions. Partitions are created as metadata and only the start partition is made persistent. The additional segments are allocated as the data arrives. The additional partitions and local indexes are automatically created
REF Partitioning: This partitioning scheme has been introduced with the assumption that related tables would benefit from same partitioning strategy. The detail table inherits the partitioning strategy of the master table through PK-FK relationship. There is no need for the partitioning key to be stored in the detail table and by specifying “PARTITION BY REFERENCE” keyword, the detail table inherits the partitioning strategy of the master table.
Virtual Column Based partitioning: In the previous versions of Oracle, a table could be partitioned only if the partition key physically existed in the table. The new functionality in Oracle 11G, “Virtual columns”, removes this restriction and allows partitioning key to be defined by an expression that uses one or more columns of a table. The virtual columns are stored as metadata only.
The below two data dictionary is used to view the partition info.
1. dba_tab_partitions2. dba_part_tables
