Table partitioning is the best feature ever added to the Oracle RDBMS.
Chunks of data can be appended, replaced, or purged very easily when using
table partitioning. When you have more than 20 million rows in a non-static table,
partitioning is recommended. We found that bitmap indexes work better
than standard indexes on partitioned tables.
Add a partition
ALTER TABLE PHONE_DATA ADD PARTITION
p2004JUL VALUES (200407) TABLESPACE TS01 ;
Populate a partition, replaces existing data, if it exists
ALTER TABLE PHONE_DATA EXCHANGE PARTITION
p2004JUL WITH TABLE TMP_SWITCH_DATA ;
Move a partition
alter table PHONE_DATA move partition P2004JUL tablespace TS01 nologging ;
Truncate a partition
ALTER TABLE PHONE_DATA TRUNCATE PARTITION ;
Drop a partition
ALTER TABLE PHONE_DATA DROP PARTITION p2004JUL update global indexes ;
Get partition information
set pagesize 0
set linesize 120
select table_name, partition_name, blocks, tablespace_name from user_tab_partitions
order by table_name, partition_name ;
Create a local partition index
CREATE BITMAP INDEX BIX_PHONE_DATA ON PHONE_DATA
(PERIOD_KEY)
TABLESPACE TS02
LOCAL
;
exec dbms_stats.gather_table_stats ('USER77','PHONE_DATA',granularity=>'ALL',estimate_percent => 25, degree=>3 );
-- rebuild
ALTER TABLE PHONE_DATA MODIFY PARTITION
p2004JUL REBUILD UNUSABLE LOCAL INDEXES;
|
|
