Square DBA – Oracle DBA Talk

I deal a lot with call detail records. Currently we process several million calls per day so that means I get several million rows or data daily. To deal with all that data I use Oracle’s partitioned tables. Oracle partitions addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions. Once partitions are defined, SQL statements can access and manipulate the partitions rather than entire tables or indexes. Partitions are especially useful in data warehouse applications, which commonly store and analyze large amounts of historical data. Here are some common SQL statements I use, some of them I use daily.

SELECT * FROM schema.table PARTITION (part_name);

ALTER TABLE aaa MOVE PARTITION bbb
TABLESPACE rrr NOLOGGING;

ALTER TABLE edu
ADD PARTITION jan99 VALUES LESS THAN ( '990201' )
TABLESPACE tsjan99;

ALTER TABLE DROP PARTITION bbb

ALTER TABLE TRUNCATE PARTITION bbb