Create a partitioned table from an existing table

Partitioned tables are extremely powerful to manage large data. We can combine "create table as select" and "partition by" to build a new partitioned table based on an existing table.

For example, we have a transaction table that includes account_number and transaction date. We can create a partitioned table that has one partition for each day.

SQL> create table tbl_txn_par_by_day partition by range(txn_date) interval(numtodsinterval(1,'day')) (partition p0 values less than (to_date('20131 001','YYYYMMDD'))) as select * from tbl_txn;
Table created.

Or we can create a partitioned table that has 20 partitions based on the hash value of account numbers.

SQL> create table tbl_txn_par_by_acct_num partition by hash(account_number) partitions 20 as select * from tbl_txn;
Table created.

We can find out the partition names.

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

TABLE_NAME	PARTITION_NAME	HIGH_VALUE
TBL_TXN_PAR_BY_DAY	P0	TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TBL_TXN_PAR_BY_DAY	SYS_P45	TO_DATE(' 2013-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

We run SQL query against specific partitions.

SQL> select count(1) from TBL_TXN_PAR_BY_DAY partition(SYS_P45);

COUNT(1)
100125

Using partition tables, I was able to perform complex analysis on 50 million bank card transactions, including fuzzy matching multiple tables,etc., on a $600 desktop PC.