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.
