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;
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;
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';
|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);
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.