Partitioning In oracle

This Document Covers

1)What is Partitioning, What Partitions Do Oracle Support and What Not

2)Advantages Of Partitions

3)Types Of Partitions with Examples

   3.1)Range Partitions

   3.2)List Partitions         

   3.3)Hash Partitions

   3.4)Composite Partitions

            3.4.1)Range-Hash

            3.4.2)Range-List

            3.4.3)Range-Range

            3.4.4)List-Range

            3.4.5)List-Hash

            3.4.6)List-List

            3.4.7)Interval-Range

            3.4.8)Interval-List

            3.4.9)Interval-Interval

4)Related Base Tables and Views for Partitions

5)Converting Existing Non Partitioned Table to Partitioned Table

   5.1)Using Export/Import Method

   5.2)Using Insert With SubQuery Method

   5.3)Using Partition Exchange Method

   5.4)Using DBMS_REDEFINITION Method

6)Converting Existing Partitioned Table to Non  Partitioned Table

   6.1)Using Export/Import Method

   6.2)Using Insert With SubQuery Method

   6.3)Using Partition Exchange Method

   6.4)Using DBMS_REDEFINITION Method

7)Global and Local Indexes

  7.1)Local Indexes

  7.2)Global Indexes

            7.2.1)Non Partitioned Global Indexes

             7.2.2)Partitioned Global Indexes

8)Partition Pruning

9)References

PARTITIONING IN ORACLE:

Partitioning allows a table, index or index-organized table to be subdivided into smaller pieces. Each piece of the database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics. From the perspective of a database administrator, a partitioned

object has multiple pieces that can be managed either collectively or individually. A Partitioned table is

identical to a non-partitioned table; no modifications are necessary when accessing a partitioned table using SQL DML commands.

Database objects - tables, indexes, and index-organized tables - are partitioned using a 'partitioning key', a set of columns which determine in which partition a given row will reside(simply partitioning key describes , based on which column you want to partition table).

Once partitions are defined, SQL statements can access and manipulate the partitions rather than entire tables or indexes or M-View.

Oracle supports

(i)partitioning only for tables, indexes on tables/materialized views, and materialized views.

Oracle does not support

(i) Partitioning of clustered tables or indexes on clustered tables.

(ii) LONG & LONG RAW datatypes.

Oracle8i, supports up to 64,000 partitions per table or index.

Advantages of partitioning.

1.       Partitions can be independently managed.

2.       Backup & recovery can be done on individual partitions. Hence maintenance windows will be reduced.

3.       Partitions that may be unavailable (say if one disk goes down, that doesn't stop the show).

4.       Partitions can be moved from one tablespace to another.

5.       Can be added, dropped and truncated at the partition level.

6.       Select, insert, update and delete can be done at the partition level, instead of table level and can be done in parallel.

7.       Oracle optimizer eliminates partitions that need not be scanned. This is  called partition pruning.

8.       Partitions can be scanned in parallel (OPQO - Oracle Parallel Query Option).

9.       The size of the temporary segments used for sorting can be reduced.

10.   we can load balance partitions across physical devices.

11.   We can do Export/Import data from/to at partition level, by using normal export/import or datapump export/import.
$ exp ... table=emp:partition2
$ imp ... table=emp:partition2
$ expdp ... tables=test.test_tab:part_2007
$ impdp ... partition_options=departition dumpfile=part_2007.dmp

12.   SQL*Loader can load data into a specified partition.

Types of Partition:-

The following table lists the partitioning features (high level) that have evolved over each version/release:

Oracle Database Version	Partitioning Features
8.0.5	Introduced Range Partitioning
8i	Introduced Hash and composite Range-Hash partitioning.
9i	Introduced List Partitioning, Composite Range-List partitioning.
10G	Introduced Range, List and Hash partitioning of Index Organized tables. Also introduced other composite partitioning options.
11G	Introduced partition extensions: -Interval partitioning -REF partitioning -Virtual Column-based partitioning -Introduced Partition Advisor.

Range Partitioning:-

The data is distributed based on a range of values of the partitioning key. To creaete a range partitioning table we must specify “PARTITION BY RANGE” clause while creating the table.

Range partitioning is useful when you have distinct ranges of data you want to store together

The classic example of this is the use of dates. Partitioning a table using date ranges allows all data of a similar age to be stored in same partition. Once historical data is no longer needed the whole partition can be removed.

Example for Range partition table.

CREATE TABLE  invoices

(invoice_no    NUMBER  NOT  NULL,

 invoice_date  DATE   NOT  NULL,

 comments      VARCHAR2(500))

PARTITION BY RANGE (invoice_date)

(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,

 PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,

 PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,

 PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users);

In the above example invoice_date is partition key , so the data is splited into partitions based on invoice_date column.

Check Whether partition is created or not:

select TABLE_NAME,TABLE_OWNER,PARTITION_NAME,PARTITION_POSITION,TABLESPACE_NAME from DBA_TAB_PARTITIONS where table_name like '%CUST%';

To select data from single partition:

select * from  cust_sales partition(cust_sales_q1);

select * from  cust_sales partition(cust_sales_q2);

LIST Partitioning:

 The data distribution is defined by a list of values of the partitioning key. This is useful for discrete lists. e.g: Regions, States etc. For list partitioning, the partitioning key can only be a single column name from the table.

we specify a PARTITION BY LIST clause in the CREATE TABLE statement,

The DEFAULT partition enables you to avoid specifying all possible values for a list partitioned table by using a default partition, so that all rows that do not map to any other partition do not generate an error.(It means if we insert data which do not point towards any partition then data will reside in DEFAULT partition)

Example for List Partitioning:

create table dept_part8(deptno number(2), dname varchar2(14), loc varchar2(13))

partition by list (dname)

(partition d1_east values ('BOSTON', 'NEW YORK') tablespace users,

 partition d2_west values ('SAN FRANCISCO', 'LOS ANGELES') tablespace sysaux,

 partition d3_south values ('ATLANTA', 'DALLAS') tablespace users,

 partition d4_north values (DEFAULT) tablespace users);

Check Whether partition is created or not:

select TABLE_NAME,TABLE_OWNER,PARTITION_NAME,PARTITION_POSITION,TABLESPACE_NAME from DBA_TAB_PARTITIONS where table_name like '%DEPT_PART%’;

To select data from single partition:

select * from  cust_sales partition(D4_NORTH);

select * from  cust_sales partition(D1_EAST);

HASH Partitioning:-

Hash partitioning enables partitioning of data that does not lend itself to range or list partitioning. A hash algorithm is applied to the partitioning key to determine the partition for a given row. This provides I/O balancing, but cannot be used for range or inequality queries.

Hash partitioning does not have any logical meaning to the partitions as do the range partitioning.

Example to Create Hash Partitioning:

CREATE TABLE invoices2

(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);

Check Whether partition is created or not:

select TABLE_NAME,TABLE_OWNER,PARTITION_NAME,PARTITION_POSITION,TABLESPACE_NAME from DBA_TAB_PARTITIONS where table_name like 'INVOICES%’;

To select data from single partition:

select * from  cust_sales partition(invoices_q1);

select * from  cust_sales partition(invoices_q2);

NTERVAL Partitioning:-

Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition.

Interval partitioning defined by an interval, providing equi-width ranges. With the exception of the first partition all partitions are automatically created as-needed when matching data arrives.

CREATE TABLE SALES_PART1

(TIME_ID    NUMBER,

REGION_ID NUMBER,

ORDER_ID NUMBER,

ORDER_DATE DATE,

SALES_QTY NUMBER(10,2),

SALES_AMOUNT NUMBER(12,2))

PARTITION BY RANGE (ORDER_DATE)

INTERVAL (NUMTOYMINTERVAL(1,'month'))

(PARTITION p_first VALUES LESS THAN ('01-JAN-2006'));

In Interval partitioning,

·         You can only specify one partitioning key column, and it must be of NUMBER or DATE type.

·         Interval partitioning is not supported for index-organized tables.

·         You cannot create a domain index on an interval partitioned table.

·         At least one partition must be defined when the table is created.

·         A MAXVALUE partition cannot be defined for an interval partitioned table.

·         NULL values are not allowed in the partition column.

Composite Partitioning

Composite partitioning is a combination of the basic partitioning techniques of Range, List, Hash, and Interval Partitionings.  The table is initially partitioned by the first data distribution method and then each partition is sub-partitioned by the second data distribution method

Different types of Composite Partitionings

1.    Range-Hash (from 8i)

2.    Range-List (from 9i)

3.    Range-Range (from 11g)

4.    List-Hash (from 11g)

5.    List-List (from 11g)

6.    List-Range (from 11g)

7.    Interval-Range (from 11g)

8.    Interval-List (from 11g)

9.    Interval-Interval (from 11g)

1)Range-Hash Partitioning

This is basically a combination of range and hash partitions. 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.

CREATE TABLE composite_rng_hash (

cust_id     NUMBER(10),

cust_name   VARCHAR2(25),

amount_sold NUMBER(10,2),

time_id     DATE)

PARTITION BY RANGE(time_id)

SUBPARTITION BY HASH(cust_id)

SUBPARTITION TEMPLATE(

SUBPARTITION sp1 TABLESPACE users,

SUBPARTITION sp2 TABLESPACE users,

SUBPARTITION sp3 TABLESPACE users,

SUBPARTITION sp4 TABLESPACE users) (

PARTITION sales_pre05

VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),

PARTITION sales_2005

VALUES LESS THAN(TO_DATE('01/01/2006','DD/MM/YYYY')),

PARTITION sales_2006

VALUES LESS THAN(TO_DATE('01/01/2007','DD/MM/YYYY')),

PARTITION sales_2007

VALUES LESS THAN(TO_DATE('01/01/2008','DD/MM/YYYY')),

PARTITION sales_2008

VALUES LESS THAN(TO_DATE('01/01/2009','DD/MM/YYYY')),

PARTITION sales_future

VALUES LESS THAN(MAXVALUE));

2)Range-List Partitioning

This is a combination of Range and List partitions, 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 subpartition individually represents logical subset of the data not like composite Range-Hash Partition.

create table rang_list (

sales_dt date,

state char(2),

amount number)

partition by range (sales_dt)

subpartition by list (state)

(

partition s2004q1

values less than (to_date('04-2004','MM-YYYY'))

(subpartition s2004q1_south values  ('TX','LA','OK'),

subpartition s2004q1_north values ('NY','DE','MA'),

subpartition s2004q1_others values (DEFAULT)),

partition s2004q2

values less than (to_date('07-2004','MM-YYYY'))

(subpartition s2004q2_south values  ('TX','LA','OK'),

subpartition s2004q2_north values ('NY','DE','MA'),

subpartition s2004q2_others values (DEFAULT))

);

3)Range-Range Partitioning

Composite range-range partitioning enables logical range partitioning along two dimensions.

e.g. partition by order_date and range subpartition by shipping_date.

CREATE TABLE composite_rng_rng (

cust_id     NUMBER(10),

cust_name   VARCHAR2(25),

cust_state  VARCHAR2(2),

time_id     DATE)

PARTITION BY RANGE(time_id)

SUBPARTITION BY RANGE (cust_id)

SUBPARTITION TEMPLATE(

SUBPARTITION original VALUES LESS THAN (1001) TABLESPACE users,

SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACE users,

SUBPARTITION recent VALUES LESS THAN (MAXVALUE) TABLESPACE users) (

PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),

PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),

PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),

PARTITION future VALUES LESS THAN (MAXVALUE));

Check for partitions

SELECT table_name, partition_name, composite, high_value, subpartition_name, num_rows  FROM user_tab_partitions where table_name like '%COMPOSITE%';

4)List-Range Partitioning

Composite list-range partitioning enables logical range subpartitioning within a given list partitioning strategy.

CREATE TABLE composite_list_rng (

cust_id     NUMBER(10),

cust_name   VARCHAR2(25),

cust_state  VARCHAR2(2),

time_id     DATE)

PARTITION BY LIST(cust_state)

SUBPARTITION BY RANGE (cust_id)

SUBPARTITION TEMPLATE(

SUBPARTITION original VALUES LESS THAN (1001) TABLESPACE users,

SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACE users,

SUBPARTITION recent VALUES LESS THAN (MAXVALUE) TABLESPACE users) (

PARTITION west VALUES ('OR', 'WA'),

PARTITION east VALUES ('NY', 'CT'),

PARTITION cent VALUES ('IL', 'MN'));

5)List-Hash Partitioning

Composite list-hash partitioning enables hash sub partitioning of a list-partitioned object.

e.g. to enable partition-wise joins.

CREATE TABLE composite_lis_has (

cust_id     NUMBER(10),

cust_name   VARCHAR2(25),

cust_state  VARCHAR2(2),

time_id     DATE)

PARTITION BY LIST(cust_state)

SUBPARTITION BY HASH (cust_id)

SUBPARTITION TEMPLATE(

SUBPARTITION sp1 TABLESPACE users,

SUBPARTITION sp2 TABLESPACE users,

SUBPARTITION sp3 TABLESPACE users,

SUBPARTITION sp4 TABLESPACE users) (

PARTITION west VALUES ('OR', 'WA'),

PARTITION east VALUES ('NY', 'CT'),

PARTITION cent VALUES ('IL', 'MN'));

6)List-List Partitioning

Composite list-list partitioning enables logical list partitioning along two dimensions.

e.g. list partition by country_id and list subpartition by sales_channel.

CREATE TABLE composite_list_list (

cust_id     NUMBER(10),

cust_name   VARCHAR2(25),

cust_state  VARCHAR2(2),

time_id     DATE)

PARTITION BY LIST(cust_state)

SUBPARTITION BY LIST (cust_id)

SUBPARTITION TEMPLATE(

SUBPARTITION beg VALUES (1,3,5) TABLESPACE users,

SUBPARTITION mid VALUES (2,4,6) TABLESPACE users,

SUBPARTITION end VALUES (7,8,9,0) TABLESPACE users) (

PARTITION west VALUES ('OR', 'WA'),

PARTITION east VALUES ('NY', 'CT'),

PARTITION cent VALUES ('IL', 'MN'));

7)Interval-Range Partitioning

This is a combination of Interval and Range partitions, first the data is divided using the Interval partition and then each Interval partitioned data is further subdivided into Range partitions.

8)Interval-List Partitioning

This is a combination of Interval and List partitions, first the data is divided using the Interval partition and then each Interval partitioned data is further subdivided into List partitions.

9)Interval-Interval Partitioning

First the data is divided using the Interval partition and then each Interval partitioned data is further subdivided into Interval partitions.

Related Base Tables

tabpart$

tabsubpart$

indpart$

indsubpart$

partobj$

indpart_param$

partlob$

subpartcol$

Related Views

dba_part_tables

dba_tab_partitions

dba_tab_subpartitions

dba_tab_cols

dba_part_indexes

dba_ind_partitions

dba_ind_subpartitions

dba_part_lobs

dba_lob_partitions

dba_lob_subpartitions

dba_subpartition_templates

dba_subpart_key_columns

##################################################################################################################

##################################################################################################################

1)Converting Existing Non Partitioned table to Partitioned table

You can convert non-partitioned to portioned table in one of four ways:

A) Export/import method
B) Insert with a subquery method
C) Partition exchange method
D) DBMS_REDEFINITION

Either of these four methods will create a partitioned table from an existing non-partitioned table.

A. Export/import method

1) Export table:

exp file=emp.dmp log=emp.log  tables=emp

2) Drop the table:

drop table emp;

3) Recreate the table with partitions:

create table emp (sno number(3), sname varchar2(15)) partition by range (sno) (partition p1 values less than (501),partition p2 values less than (maxvalue));

4) Import the table with ignore=y:

imp  file=emp.dmp ignore=y

The ignore=y causes the import to skip the table creation and continues to load all rows.

Check for partitioned table

select * from emp partition(P1);

select * from emp partition(P2);

B) Insert with a subquery method 

1) Create a partitioned table:

create table pattbl (qty number(3),

name varchar2(15))

partition by range (qty)
(partition p1 values less than (501),

partition p2 values less than (maxvalue));

2) Insert into the partitioned table with a subquery from the existing  non-partitioned table:

insert into partbl (qty, name)  select * from origtbl;

3) If you want the partitioned table to have the same name as the original table, then drop the original table and rename the new table:

drop table origtbl;
alter table partbl rename to origtbl;

C) Partition exchange method

ALTER TABLE EXCHANGE PARTITION can be used to convert a partition (or subpartition) into a non-partitioned table and a non-partitioned table into a partition (or subpartition) of a partitioned table by exchanging their data and index segments.

1) Create table dummy_t as select with the required partitions

2) Alter table EXCHANGE partition partition_name with table non-partition_table;

Example:-
SQL> CREATE TABLE p_emp1 (sal NUMBER(7,2))

 PARTITION BY RANGE(sal)
(partition emp_p1 VALUES LESS THAN (2000),
 partition emp_p2 VALUES LESS THAN (4000));
Table created.

SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL
--------- ---------- --------- --------- --------- ---------
7369 SMITH CLERK 7902 17-DEC-80 800
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
7521 WARD SALESMAN 7698 22-FEB-81 1250
7566 JONES MANAGER 7839 02-APR-81 2975
7654 MARTIN SALESMAN 7698 28-SEP-81 1250
7698 BLAKE MANAGER 7839 01-MAY-81 2850
7782 CLARK MANAGER 7839 09-JUN-81 2450
7788 SCOTT ANALYST 7566 19-APR-87 3000
7839 KING PRESIDENT 17-NOV-81 5000
7844 TURNER SALESMAN 7698 08-SEP-81 1500
7876 ADAMS CLERK 7788 23-MAY-87 1100
7900 JAMES CLERK 7698 03-DEC-81 950
7902 FORD ANALYST 7566 03-DEC-81 3000
7934 MILLER CLERK 7782 23-JAN-82 1300
14 rows selected.

SQL> CREATE TABLE dummy_y as SELECT sal FROM emp WHERE sal<2000;
Table created.

SQL> CREATE TABLE dummy_z as SELECT sal FROM emp WHERE sal BETWEEN 2000 AND 3999;
Table created.

SQL> alter table p_emp exchange partition emp_p1 with table dummy_y;
Table altered.

SQL> alter table p_emp exchange partition emp_p2 with table dummy_z;
Table altered.

Check for partitions:

Select * from p_emp partition(emp_p2);

select * from p_emp partition(emp_p1);

Here in this case first  we are creating a partitioned table. Then we are creating a dummy table with selected range values. Then we are exchanging partioned table without data from  dummy table with data.

D. Using DBMS_REDEFINITION Method

Some restrictions of Online Redefinition:

·         Cannot belong to SYS or SYSTEM Schema.

·         The table to be redefined online should not be using User-defined data types

·         Should not be a clustered table.

·         Should not have MV Logs or MVs defined

·         You cannot move a table from one schema to another using Online Redefinition feature.

Step by step instructions on how to convert unpartitioned table to partitioned one using dbms_redefinition package.

1) Create unpartitioned table with the name unpar_table

SQL> CREATE TABLE unpar_table (
id NUMBER(10),
create_date DATE,
name VARCHAR2(100)
);

2) Apply some constraints to the table:

SQL> ALTER TABLE unpar_table ADD (
CONSTRAINT unpar_table_pk PRIMARY KEY (id)
);

SQL> CREATE INDEX create_date_ind ON unpar_table(create_date);

3) Gather statistics on the table:

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

4) Create a Partitioned Interim Table:

SQL> CREATE TABLE par_table (
id NUMBER(10),
create_date DATE,
name VARCHAR2(100)
)
PARTITION BY RANGE (create_date)
(PARTITION unpar_table_2005 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
PARTITION unpar_table_2006 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')),
PARTITION unpar_table_2007 VALUES LESS THAN (MAXVALUE));

5) Start the Redefinition Process:

a) Check the redefinition is possible using the following command:

SQL> EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table'); 

b)If no errors are reported, start the redefintion using the following command:

SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/

Note: This operation can take quite some time to complete.

c) Optionally synchronize new table with interim name before index creation:

SQL> BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/ 

d) Create Constraints and Indexes:

ALTER TABLE par_table ADD (CONSTRAINT unpar_table_pk2 PRIMARY KEY (id) );
CREATE INDEX create_date_ind2 ON par_table(create_date); 

e) Gather statistics on the new table:

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

f) Complete the Redefintion Process:

SQL> BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/
At this point the interim table has become the "real" table and their names have been switched in the name dictionary.

g) Remove original table which now has the name of the interim table:

SQL> DROP TABLE par_table; 

h)Rename all the constraints and indexes to match the original names.

ALTER TABLE unpar_table RENAME CONSTRAINT unpar_table_pk2 TO unpar_table_pk;
ALTER INDEX create_date_ind2 RENAME TO create_date_ind; 

i) Check whether partitioning is successful or not:

SQL> SELECT partitioned  FROM user_tables  WHERE table_name = 'unpar_table';

PAR
---
YES

1 row selected.

SQL> SELECT partition_name  FROM user_tab_partitions  WHERE table_name = 'unpar_table';

PARTITION_NAME
------------------------------
unpar_table_2005
unpar_table_2006
unpar_table_2007

###########################################################################

Second method using  DBMS_REDEFINITION(This is working)

In this section we need to execute all DBMS packages as sys user

Step 1
Check to make sure that the table can use the online redefintion feature

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProductionWith the Partitioning, OLAP and Data Mining options
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','EMP');
PL/SQL procedure successfully completed.

Step 2
Create the temporary partition table as same structure of original table.

SQL> Create table EMP_part
2 (EMPNO NUMBER(4),
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 COMM NUMBER(7,2),
9 DEPTNO NUMBER(2))
10 partition by range (SAL)
11 (Partition p1 values less than (1000),
12 Partition p2 values less than (2000),
13 Partition p3 values less than (3000),
14 Partition p4 values less than (4000),
15 Partition max values less than (maxvalue))
16 tablespace users;

Table created.

Step 3
Start the online redefinition process.

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT','EMP','EMP_PART');

PL/SQL procedure successfully completed.

Step 4
Here is where oracle10g feature come into play. We do not need to copy any dependent objects to part_emp table. Dependent objects are like grants, synonym, triggers etc.

SQL> VARIABLE NUM_ERRORS NUMBER;
SQL> EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT','EMP','EMP_PART', 1,TRUE,TRUE,TRUE,FALSE,
:NUM_ERRORS,FALSE);

PL/SQL procedure successfully completed.

SQL> PRINT NUM_ERRORS

NUM_ERRORS
----------
0

Step 5
Resync the table

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT','EMP','EMP_PART');

PL/SQL procedure successfully completed.

Step 6

Complete the online redefinition

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','EMP','EMP_PART');

PL/SQL procedure successfully completed.

Step 7

Make sure emp table has all partitions as we created in emp_part table

SQL> Select partition_name, high_value from user_tab_partitions where table_name='EMP';

PARTITION_NAME HIGH_VALUE
------------------------------ ---------------------------------------------------------------------
MAX MAXVALUE
P1 1000
P2 2000
P3 3000
P4 4000

SQL> select partition_name, high_value from user_tab_partitions where table_name='EMP_PART';

no rows selected

Step 8
Make sure all the dependent objects are copied .

SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS WHERE TABLE_NAME='EMP';

TRIGGER_NAME
------------------------------
EMPTRIG

SQL> select constraint_name from user_constraints where table_name='EMP';

CONSTRAINT_NAME
------------------------------
PK_EMP
FK_DEPTNO

Note : The only problem i see here is, if we have any index on the original table, it will convert to global index on partition table. If we need the index to be local index, then we have to drop and recreate the index.

############################################################################################################################################################

2)Converting Existing Partitioned table to Non Partitioned table

A)Export Import Method

1)create a partitioned table and insert some rows.

2)check whether table is partitioned or not,

3)Export table using exp or expdp

4)Drop the table

5)Create table with same columns as in partitioned table

6)Now import table using ignore=y option.

7) check whether table is unpartitioned or not

During the import, there are three options: NONE, DEPARTITION and MERGE.

NONE means to import the structure exactly the same as it was in the source database, therefore, partitioned if it was so.

DEPARTITION means to create a separate table for every partition and sub-partition

MERGE-

B) INSERT WITH A SUBQUERY METHOD

1)create a partitioned table

2)insert  some rows into partitioned table

3)create non partitioned table from partitioned table(Using CTAS )

4)drop partitioned table

5)rename newly created non partitioned

6)check for partitioned table.

SQL> create table honcho_partition (id number, name varchar2(20),time date)
  2  partition by range (time)
  3  (partition t1 values less than (to_date('01-01-2012','dd-mm-yyyy')),
  4   partition t2 values less than (to_date('01-02-2012','dd-mm-yyyy')),
  5   partition t3 values less than (to_date('01-03-2012','dd-mm-yyyy')),
  6   partition t4 values less than (maxvalue));
 

SQL> insert into honcho_partition values (1,'honcho',to_date('01-12-2011','dd-mm-yyyy'));

Insert some more rows 

select * from honcho_partition;

 1 honcho               2012-03-15 00:00:00

SQL> create table honcho_test as select * from honcho_partition;

Table created.

SQL> select table_name,partition_name from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
HONCHO_PARTITION               T1
HONCHO_PARTITION               T2
HONCHO_PARTITION               T3
HONCHO_PARTITION               T4

SQL> drop table honcho_partition purge;

Table dropped.

SQL> alter table honcho_test rename to honcho_partition;

Table altered.


SQL> select * from honcho_partition;

        ID NAME                 TIME
---------- -------------------- -------------------
         1 honcho               2011-12-01 00:00:00
         1 honcho               2011-12-15 00:00:00
         1 honcho               2012-01-01 00:00:00
         1 honcho               2012-01-15 00:00:00
         1 honcho               2012-02-01 00:00:00
         1 honcho               2012-02-15 00:00:00
         1 honcho               2012-03-01 00:00:00
         1 honcho               2012-03-15 00:00:00
         1 honcho               2012-04-01 00:00:00

9 rows selected.

SQL> select table_name,partition_name from user_tab_partitions;

no rows selected

C. USING REDIFINITION METHOD

With DBMS_REDEFINITION, you can perform an online redefinition of tables. To achieve this online redefinition, incrementally maintainable local materialized views are used. Snapshot logs need to be defined on the master tables to support incrementally maintainable materialized views. These logs keep track of the changes to the master tables and are used by the materialized views during refresh synchronization. 

1. Create a interim table with same structure as of the souce table WITHOUT partition.

2. Check the redefinition is possible using the following command on source table

EXEC Dbms_Redefinition.Can_Redef_Table(USER, 'SOURCE_TABLE');

3. If no errors are reported proceed with the redefintion using the following command.

BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'SOURCE_TABLE',
int_table => 'INTERIM_TABLE');
END;
/

4. Synchronize new table with interim data before index creation

BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'SOURCE_TABLE',

int_table => 'ITERIM_TABLE');
END;
/

5. Create Contraints and indexes. The constraints and indexes from the original table must be applied to interim table using alternate names to prevent errors.

6. Complete the redefinition process

BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'SOURCE_TABLE',
int_table => 'INTERIM_TABLE');
END;
/

Now the interim table has become the real table and their names have been switched in the data dictionary. Now perform some cleanup operations.

7. Remove original table which now has the name of the interim table

DROP TABLE ‘INTERIM_TABLE'

- Rename all the constraints and indexes to original name
- Make grants as on original table

##########################################################################################################################################################################

Global and Local Indexes:

Index:- Oracle indexes provides faster access to table rows by storing sorted values in specific columns and using those sorted values to easily lookup the associated table rows. This means that you can lookup data without having to look at more than a small fraction of the total rows within the table

When using Oracle partitioning, you can specify the “global” or “local” parameter in the create index syntax

Local Index: Local partitioned indexes are easier to manage than other types of partitioned indexes. The reason for this is equipartitioning: each partition of a local index is associated with exactly one partition of the table. This enables Oracle to automatically keep the index partitions in sync with the table partitions, and makes each table-index pair independent. Any actions that make one partition's data invalid or unavailable only affect a single partition.

Local partitioned indexes support more availability when there are partition or subpartition maintenance operations on the table.

We cannot explicitly add a partition to a local index. New partitions are added to local indexes only when you add a partition to the underlying table. Likewise, you cannot explicitly drop a partition from a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.

A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns.

Main advantage of local indexes is we can take individual partitions of a table and indexes offline for maintenance (or reorganization) without affecting the other partitions and indexes in the table.

Example to create Local Index:

1)Create a partitioned table

CREATE TABLE tabind1

(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);

2)Create a local index for above table.

Here  we are creating local index so for every partition in the table we must create an Index.

CREATE INDEX tabind_ind1

on tabind1 (invoice_no)

LOCAL

(PARTITION invoices1 tablespace users,

PARTITION invoices2 tablespace users

PARTITION invoices3 tablespace users,

partition invoices4 tablespace users);

Global Index:-

Global indexes are of two types

i)Non partitioned Global index and    ii)Partitioned Global Index

i)Non partitioned Global index

Global nonpartitioned indexes behave just like a nonpartitioned index. They are commonly used in OLTP environments and offer efficient access to any individual record .

ii)Partitioned Global Index

There are two types of global partitioned index: range partitioned and hash partitioned.

Global partitioned index partition key is independent of Table partition key. The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. If you want to add new partition, always, you need to split the MAX partition. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.

Note:-A global partitioned index is used for all other indexes except for the one that is used as the table partition key.

Example for Global partitioned indexes:-

CREATE TABLE  invoices55

(invoice_no    NUMBER  NOT  NULL,

 invoice_date  DATE   NOT  NULL,

 comments      VARCHAR2(500),

invoice_id NUMBER NOT NULL)

PARTITION BY RANGE (invoice_date)

(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,

 PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,

 PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,

 PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users);

Create Partitioned Index for Partitioned table

CREATE INDEX invoices_idx ON invoices55(invoice_id)

GLOBAL PARTITION BY RANGE(invoice_id)

(PARTITION p1 VALUES LESS THAN(3),

PARTITION p2 VALUES LESS THAN(6),

PARTITION p3 VALUES LESS THAN(11),

PARTITION p4 VALUES LESS THAN(20),

PARTITION p5 VALUES LESS THAN(MAXVALUE));

Here, we see that the item index has been defined with five partitions, each containing a subset of the index range values. Note that it is irrelevant that the base table is in three partitions. In fact, it is acceptable to create a global partitioned index on a table that does not have any partitioning.

To get information about INDEXES:

select INDEX_NAME,PARTITION_NAME,STATUS,GLOBAL_STATs,tablespace_name from DBA_IND_PARTITIONS where index_name like 'TABIND1';

Partition Pruning:-

In other words, partition pruning is the skipping of unnecessary index and data partitions or subpartitions in a query. The Oracle database server explicitly recognizes partitions and subpartitions. It then optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access by those SQL statements

For each SQL statement, depending on the selection criteria specified, unneeded partitions or subpartitions can be eliminated. For example, if a query only involves March sales data, then there is no need to retrieve data for the remaining eleven months. Such intelligent pruning can dramatically reduce the data volume, resulting in substantial improvements in query performance.

If the optimizer determines that the selection criteria used for pruning are satisfied by all the rows in the accessed partition or subpartition, it removes those criteria from the predicate list (WHERE clause) during evaluation in order to improve performance. However, the optimizer cannot prune partitions if the SQL statement applies a function to the partitioning column (with the exception of the TO_DATE function). Similarly, the optimizer cannot use an index if the SQL statement applies a function to the indexed column, unless it is a function-based index.

Pruning can eliminate index partitions even when the underlying table's partitions cannot be eliminated, but only when the index and table are partitioned on different columns. You can often improve the performance of operations on large tables by creating partitioned indexes that reduce the amount of data that your SQL statements need to access or modify.

Partition Pruning Example

We have a partitioned table called cust_orders. The partition key for cust_orders is order_date. Let us assume that cust_orders has six months of data, January to June, with a partition for each month of data. If the following query is run:

SELECT SUM(value)

FROM cust_orders

WHERE order_date BETWEEN '28-MAR-98' AND '23-APR-98';

Partition pruning is achieved by:

·         First, partition elimination of January, February, May, and June data partitions.

·         An index scan of the March and April data partition due to high index selectivity

·         A full scan of the March and April data partition due to low index selectivity

References:-

http://docs.oracle.com/cd/B19306_01/server.102/b14220/partconc.htm

http://myorastuff.blogspot.in/2008/08/local-index-versus-global-index-on.html

http://www.dba-oracle.com/t_global_local_partitioned_index.htm

http://docs.oracle.com/cd/B10501_01/appdev.920/a96612/d_redefi.htm

http://24x7dba.blogspot.in/2007/08/converting-partitioned-table-to.html

http://www.oracler.net/?p=182

http://myorastuff.blogspot.in/2008/11/convert-non-partitioned-to-partitioned.html

http://learnappsdbaskills.blogspot.com/2013/07/blog-post.html