Table partitions, improve performance

Sometimes there is a table in your database that has a low performance, maybe it is big and contains a lot of historical old data, or maybe a lot of queries are running against this table.

Is time to think about Oracle Table Partitioning...

I´m going to use Online Redefinition method and RANGE partitioning which is good for historical tables, but you can do same for RANGE or LIST partitioning


1-Online Redefinition:

Fisrt, we are going to extract hr.test_table DDL. TEST_TABLE is a table that contains 5000 rows stored in tablespace USERS

SQL> set long 3000
SQL> set pages 200
SQL> set linesize 190
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TEST_TABLE', 'HR') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','TEST_TABLE','HR')
--------------------------------------------------------------------------------

  CREATE TABLE "HR"."TEST_TABLE"
   (    "ID" NUMBER,
        "SMALL_NUMBER" NUMBER(5,0),
        "BIG_NUMBER" NUMBER,
        "SHORT_STRING" VARCHAR2(50),
        "LONG_STRING" VARCHAR2(400),
        "CREATED_DATE" DATE,
         CONSTRAINT "RANDOM_DATA_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

So, we have a standard table with 5000 rows that we are going to partitioning with online redefinition package (version >10g).

second we are going to create an empty partition table with same column and constraint definition as HR.test_table.

SQL> CREATE TABLE HR.partition_test_table (
  id           NUMBER,
  small_number NUMBER(5),
  big_number   NUMBER,
  short_string VARCHAR2(50),
  long_string  VARCHAR2(400),
  created_date DATE,
  CONSTRAINT partition_test_table PRIMARY KEY (id)
)
PARTITION BY RANGE (id)
(
PARTITION P1 VALUES LESS THAN (1000) TABLESPACE USERS,
PARTITION P2 VALUES LESS THAN (2000) TABLESPACE USERS,
PARTITION P3 VALUES LESS THAN (3000) TABLESPACE USERS,
PARTITION P4 VALUES LESS THAN (4000) TABLESPACE USERS,
PARTITION P5 VALUES LESS THAN (5000) TABLESPACE USERS,
PARTITION P6 VALUES LESS THAN (MAXVALUE) TABLESPACE USERS
);  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18

Table created.

*In this example i created all partitions in same tablespace, but is highly recommended you create partitions in different tablepaces to increase performace.
*MAXVALUE is to aboid errors when somebody tries to add a row that contain a id>5000
Now, we run this three packages:

SQL> EXEC dbms_redefinition.start_redef_table('HR', 'test_table', 'partition_test_table');

PL/SQL procedure successfully completed.

SQL> DECLARE
 error_count pls_integer := 0;
BEGIN
  dbms_redefinition.copy_table_dependents('HR', 'test_table', 'partition_test_table', 0, true, false, true, false, error_count);
  dbms_output.put_line('errors := ' || to_char(error_count));
END;
/
  2    3    4    5    6    7
PL/SQL procedure successfully completed.

SQL> EXEC  dbms_redefinition.finish_redef_table('HR', 'test_table', 'partition_test_table');

PL/SQL procedure successfully completed.

SQL>

and we check partition table is successfully migrated to test_table by doing:

SQL> select TABLE_OWNER, TABLE_NAME, PARTITION_NAME from dba_tab_partitions where TABLE_NAME like 'TEST_TABLE';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
HR                             TEST_TABLE                     P1
HR                             TEST_TABLE                     P2
HR                             TEST_TABLE                     P3
HR                             TEST_TABLE                     P4
HR                             TEST_TABLE                     P5
HR                             TEST_TABLE                     P6

Now, we can drop pivot table HR.partition_test_table

SQL> drop table HR.partition_test_table;

Table dropped.

The only snag is that indexes in the table remains as non-partitioned indexes, so if you add another partition or drop any of them, index will become unusable..
To avoid this you should to recreate this index with partitions...

SQL> select OWNER, INDEX_NAME, TABLE_NAME, PARTITIONED, STATUS  from dba_indexes where TABLE_NAME like 'TEST_TABLE';

OWNER INDEX_NAME                     TABLE_NAME                     PARTITIONED STATUS
----- ------------------------------ ------------------------------ ----------- --------
HR    PARTITION_TEST_TABLE           TEST_TABLE                     NO          VALID

SQL> alter table HR.test_table drop partition p1;

Table altered.

SQL>  select OWNER, INDEX_NAME, TABLE_NAME, PARTITIONED, STATUS  from dba_indexes where TABLE_NAME like 'TEST_TABLE';

OWNER INDEX_NAME                     TABLE_NAME                     PARTITIONED STATUS
----- ------------------------------ ------------------------------ ----------- --------
HR    PARTITION_TEST_TABLE           TEST_TABLE                     NO          UNUSABLE