HOW TO PARTITION A ORACLE TABLE

HOW TO PARTITION A ORACLE TABLE


SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 29 15:36:36 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

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

SQL> DECLARE
2 l_lookup_id lookup.id%TYPE;
3 l_create_date DATE;
4 BEGIN
5 FOR i IN 1 .. 1000000 LOOP
6 IF MOD(i, 3) = 0 THEN
7 l_create_date := ADD_MONTHS(SYSDATE, -24);
8 l_lookup_id := 2;
9 ELSIF MOD(i, 2) = 0 THEN
10 l_create_date := ADD_MONTHS(SYSDATE, -12);
11 l_lookup_id := 1;
12 ELSE
13 l_create_date := SYSDATE;
14 l_lookup_id := 3;
15 END IF;
16
17 INSERT INTO big_table (id, created_date, lookup_id, data)
18 VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
19 END LOOP;
20 COMMIT;
21 .
SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 l_lookup_id lookup.id%TYPE;
3 l_create_date DATE;
4 BEGIN
5 FOR i IN 1 .. 300000 LOOP
6 IF MOD(i, 3) = 0 THEN
7 l_create_date := ADD_MONTHS(SYSDATE, -24);
8 l_lookup_id := 2;
9 ELSIF MOD(i, 2) = 0 THEN
10 l_create_date := ADD_MONTHS(SYSDATE, -12);
11 l_lookup_id := 1;
12 ELSE
13 l_create_date := SYSDATE;
14 l_lookup_id := 3;
15 END IF;
16 INSERT INTO big_table (id, created_date, lookup_id, data)
17 VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
18 END LOOP;
19* COMMIT;
20 .

SQL> spool d:/table_part_new
SQL> spool on
SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 l_lookup_id lookup.id%TYPE;
3 l_create_date DATE;
4 BEGIN
5 FOR i IN 1 .. 300000 LOOP
6 IF MOD(i, 3) = 0 THEN
7 l_create_date := ADD_MONTHS(SYSDATE, -24);
8 l_lookup_id := 2;
9 ELSIF MOD(i, 2) = 0 THEN
10 l_create_date := ADD_MONTHS(SYSDATE, -12);
11 l_lookup_id := 1;
12 ELSE
13 l_create_date := SYSDATE;
14 l_lookup_id := 3;
15 END IF;
16 INSERT INTO big_table (id, created_date, lookup_id, data)
17 VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
18 END LOOP;
19 COMMIT;
20* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> ALTER TABLE big_table ADD (
2 CONSTRAINT big_table_pk PRIMARY KEY (id)
3 );
CONSTRAINT big_table_pk PRIMARY KEY (id)
*
ERROR at line 2:
ORA-02437: cannot validate (SCOTT.BIG_TABLE_PK) - primary key violated


SQL>
SQL>
SQL> delete from big_table
2 /

1400000 rows deleted.

SQL> DECLARE
2 l_lookup_id lookup.id%TYPE;
3 l_create_date DATE;
4 BEGIN
5 FOR i IN 1 .. 300000 LOOP
6 IF MOD(i, 3) = 0 THEN
7 l_create_date := ADD_MONTHS(SYSDATE, -24);
8 l_lookup_id := 2;
9 ELSIF MOD(i, 2) = 0 THEN
10 l_create_date := ADD_MONTHS(SYSDATE, -12);
11 l_lookup_id := 1;
12 ELSE
13 l_create_date := SYSDATE;
14 l_lookup_id := 3;
15 END IF;
16 INSERT INTO big_table (id, created_date, lookup_id, data)
17 VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
18 END LOOP;
19 COMMIT;
20 .
SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 l_lookup_id lookup.id%TYPE;
3 l_create_date DATE;
4 BEGIN
5 FOR i IN 1 .. 1000000 LOOP
6 IF MOD(i, 3) = 0 THEN
7 l_create_date := ADD_MONTHS(SYSDATE, -24);
8 l_lookup_id := 2;
9 ELSIF MOD(i, 2) = 0 THEN
10 l_create_date := ADD_MONTHS(SYSDATE, -12);
11 l_lookup_id := 1;
12 ELSE
13 l_create_date := SYSDATE;
14 l_lookup_id := 3;
15 END IF;
16 INSERT INTO big_table (id, created_date, lookup_id, data)
17 VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
18 END LOOP;
19 COMMIT;
20* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> ALTER TABLE big_table ADD (
2 CONSTRAINT big_table_pk PRIMARY KEY (id)
3 );

Table altered.

SQL> CREATE INDEX bita_created_date_i ON big_table(created_date);

Index created.

SQL>
SQL> CREATE INDEX bita_look_fk_i ON big_table(lookup_id);

Index created.

SQL>
SQL> ALTER TABLE big_table ADD (
2 CONSTRAINT bita_look_fk
3 FOREIGN KEY (lookup_id)
4 REFERENCES lookup(id)
5 );

Table altered.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL>
SQL> -- Create partitioned table.
SQL> CREATE TABLE big_table2 (
2 id NUMBER(10),
3 created_date DATE,
4 lookup_id NUMBER(10),
5 data VARCHAR2(50)
6 )
7 PARTITION BY RANGE (created_date)
8 (PARTITION big_table_2007 VALUES LESS THAN (MAXVALUE));

Table created.

SQL> select * from big_table2;

no rows selected

SQL> -- Add new keys, FKs and triggers.
SQL> ALTER TABLE big_table2 ADD (
2 CONSTRAINT big_table_pk2 PRIMARY KEY (id)
3 );

Table altered.

SQL>
SQL> CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;

Index created.

SQL>
SQL> CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;

Index created.

SQL>
SQL> ALTER TABLE big_table2 ADD (
2 CONSTRAINT bita_look_fk2
3 FOREIGN KEY (lookup_id)
4 REFERENCES lookup(id)
5 );

Table altered.

SQL> ALTER TABLE big_table2
2 EXCHANGE PARTITION big_table_2007
3 WITH TABLE big_table
4 WITHOUT VALIDATION
5 UPDATE GLOBAL INDEXES;

Table altered.

SQL>
SQL> DROP TABLE big_table;

Table dropped.

SQL> RENAME big_table2 TO big_table;

Table renamed.

SQL> ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;

Table altered.

SQL> ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;

Table altered.

SQL> ALTER INDEX big_table_pk2 RENAME TO big_table_pk;

Index altered.

SQL> ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;

Index altered.

SQL> ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;

Index altered.

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

Table altered.

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

Table altered.

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

PL/SQL procedure successfully completed.

SQL>
SQL> select partitioned
2 from user_tables
3 where table_name='big_table';

no rows selected

SQL> SELECT partitioned
2 FROM user_tables
3 WHERE table_name = 'BIG_TABLE';

PAR
---
YES

SQL> SELECT partition_name, num_rows
2 FROM user_tab_partitions
3 WHERE table_name = 'BIG_TABLE';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
BIG_TABLE_2005 334452
BIG_TABLE_2006 335772
BIG_TABLE_2007 333403

SQL>
SQL> commit;

Commit complete.

SQL> select * from big_table
2 where partition_name='BIG_TABLE_2005'
3 /
where partition_name='BIG_TABLE_2005'
*
ERROR at line 2:
ORA-00904: "PARTITION_NAME": invalid identifier


SQL> select * from big_table
2 where
3 /
where
*
ERROR at line 2:
ORA-00936: missing expression


SQL> desc big_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
CREATED_DATE DATE
LOOKUP_ID NUMBER(10)
DATA VARCHAR2(50)

SQL> select * from big_table