Partitioned Table
Create Partition in an Old Table Oracle You can partition a non-partitioned table three different ways:A) export/import method
B) Insert with a subquery method
C) Partition exchange methodEither of these 3 methods will create a partitioned table from an existing non-partitioned table.
A. Export/import method 1) Export your table: exp usr/pswd tables=numbers file=exp.dmp 2) Drop the table: drop table numbers; 3) Recreate the table with partitions: create table numbers (qty number(3), name varchar2(15)) partition by range (qty) (partition p1 values less than (501), partition p2 values less than (maxvalue)); 4) Import the table with ignore=y: imp usr/pswd file=exp.dmp ignore=y The ignore=y causes the import to skip the table creation and continues to load all rows.
B. Insert with a subquery method 1) Create a partitioned table: create table partbl (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 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 methodALTER 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 with ; Example ------- SQL> CREATE TABLE p_emp 2 (sal NUMBER(7,2)) 3 PARTITION BY RANGE(sal) 4 (partition emp_p1 VALUES LESS THAN (2000), 5 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 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.
Another Detailed Example using Partition Exchange method -- Create and populate a small lookup table. CREATE TABLE lookup ( id NUMBER(10), description VARCHAR2(50) ); ALTER TABLE lookup ADD ( CONSTRAINT lookup_pk PRIMARY KEY (id) ); INSERT INTO lookup (id, description) VALUES (1, 'ONE'); INSERT INTO lookup (id, description) VALUES (2, 'TWO'); INSERT INTO lookup (id, description) VALUES (3, 'THREE'); COMMIT; -- Create and populate a larger table that we will later partition. CREATE TABLE big_table ( id NUMBER(10), created_date DATE, lookup_id NUMBER(10), data VARCHAR2(50) ); DECLARE l_lookup_id lookup.id%TYPE; l_create_date DATE; BEGIN FOR i IN 1 .. 1000000 LOOP IF MOD(i, 3) = 0 THEN l_create_date := ADD_MONTHS(SYSDATE, -24); l_lookup_id := 2; ELSIF MOD(i, 2) = 0 THEN l_create_date := ADD_MONTHS(SYSDATE, -12); l_lookup_id := 1; ELSE l_create_date := SYSDATE; l_lookup_id := 3; END IF; INSERT INTO big_table (id, created_date, lookup_id, data) VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i); END LOOP; COMMIT; END; -- Apply some constraints to the table. ALTER TABLE big_table ADD ( CONSTRAINT big_table_pk PRIMARY KEY (id) ); CREATE INDEX bita_created_date_i ON big_table(created_date); CREATE INDEX bita_look_fk_i ON big_table(lookup_id); ALTER TABLE big_table ADD ( CONSTRAINT bita_look_fk FOREIGN KEY (lookup_id) REFERENCES lookup(id) ); -- Gather statistics on the schema objects EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE); EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
-- Create partitioned table. 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 new keys, FKs and triggers. ALTER TABLE big_table2 ADD ( CONSTRAINT big_table_pk2 PRIMARY KEY (id) ); CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL; CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL; ALTER TABLE big_table2 ADD ( CONSTRAINT bita_look_fk2 FOREIGN KEY (lookup_id) REFERENCES lookup(id) );
ALTER TABLE big_table2 EXCHANGE PARTITION big_table_2007 WITH TABLE big_table WITHOUT VALIDATION UPDATE GLOBAL INDEXES; 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 big_table_pk2 RENAME TO big_table_pk; ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i; ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i; 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; EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
SELECT partitioned FROM user_tables WHERE table_name = 'BIG_TABLE'; PAR --- YES 1 row selected. SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'BIG_TABLE'; PARTITION_NAME NUM_ROWS ------------------------------ ---------- BIG_TABLE_2005 335326 BIG_TABLE_2006 332730 BIG_TABLE_2007 334340 3 rows selected.
B) Insert with a subquery method
C) Partition exchange methodEither of these 3 methods will create a partitioned table from an existing non-partitioned table.
A. Export/import method 1) Export your table: exp usr/pswd tables=numbers file=exp.dmp 2) Drop the table: drop table numbers; 3) Recreate the table with partitions: create table numbers (qty number(3), name varchar2(15)) partition by range (qty) (partition p1 values less than (501), partition p2 values less than (maxvalue)); 4) Import the table with ignore=y: imp usr/pswd file=exp.dmp ignore=y The ignore=y causes the import to skip the table creation and continues to load all rows.
B. Insert with a subquery method 1) Create a partitioned table: create table partbl (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 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 methodALTER 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 with ; Example ------- SQL> CREATE TABLE p_emp 2 (sal NUMBER(7,2)) 3 PARTITION BY RANGE(sal) 4 (partition emp_p1 VALUES LESS THAN (2000), 5 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 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.
Another Detailed Example using Partition Exchange method -- Create and populate a small lookup table. CREATE TABLE lookup ( id NUMBER(10), description VARCHAR2(50) ); ALTER TABLE lookup ADD ( CONSTRAINT lookup_pk PRIMARY KEY (id) ); INSERT INTO lookup (id, description) VALUES (1, 'ONE'); INSERT INTO lookup (id, description) VALUES (2, 'TWO'); INSERT INTO lookup (id, description) VALUES (3, 'THREE'); COMMIT; -- Create and populate a larger table that we will later partition. CREATE TABLE big_table ( id NUMBER(10), created_date DATE, lookup_id NUMBER(10), data VARCHAR2(50) ); DECLARE l_lookup_id lookup.id%TYPE; l_create_date DATE; BEGIN FOR i IN 1 .. 1000000 LOOP IF MOD(i, 3) = 0 THEN l_create_date := ADD_MONTHS(SYSDATE, -24); l_lookup_id := 2; ELSIF MOD(i, 2) = 0 THEN l_create_date := ADD_MONTHS(SYSDATE, -12); l_lookup_id := 1; ELSE l_create_date := SYSDATE; l_lookup_id := 3; END IF; INSERT INTO big_table (id, created_date, lookup_id, data) VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i); END LOOP; COMMIT; END; -- Apply some constraints to the table. ALTER TABLE big_table ADD ( CONSTRAINT big_table_pk PRIMARY KEY (id) ); CREATE INDEX bita_created_date_i ON big_table(created_date); CREATE INDEX bita_look_fk_i ON big_table(lookup_id); ALTER TABLE big_table ADD ( CONSTRAINT bita_look_fk FOREIGN KEY (lookup_id) REFERENCES lookup(id) ); -- Gather statistics on the schema objects EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE); EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
-- Create partitioned table. 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 new keys, FKs and triggers. ALTER TABLE big_table2 ADD ( CONSTRAINT big_table_pk2 PRIMARY KEY (id) ); CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL; CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL; ALTER TABLE big_table2 ADD ( CONSTRAINT bita_look_fk2 FOREIGN KEY (lookup_id) REFERENCES lookup(id) );
ALTER TABLE big_table2 EXCHANGE PARTITION big_table_2007 WITH TABLE big_table WITHOUT VALIDATION UPDATE GLOBAL INDEXES; 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 big_table_pk2 RENAME TO big_table_pk; ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i; ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i; 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; EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
SELECT partitioned FROM user_tables WHERE table_name = 'BIG_TABLE'; PAR --- YES 1 row selected. SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'BIG_TABLE'; PARTITION_NAME NUM_ROWS ------------------------------ ---------- BIG_TABLE_2005 335326 BIG_TABLE_2006 332730 BIG_TABLE_2007 334340 3 rows selected.