Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





Oracle » DDL » Design » Partitioning

Partitioned Tables

     



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;












Oracle : Related Topics

Oracle : DDL : Creating a table
Oracle : DDL : Creating indexes
Oracle : DDL : Creating constraints
Oracle : DDL : Creating and using a sequence
Oracle : DDL : Synonyms and Database Links
Oracle : DDL : Changing a column's type or name
Oracle : DDL : Alter a column
Oracle : DDL : Creating an index with the nosort option
Oracle : DDL : Oracle data types

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.