PARTITIONING Clause

Specify table partitioning

Syntax:

   PARTITION BY RANGE (column,column,…) [subptn_clause] 
     (PARTITION partition VALUES LESS THAN (values_list)
          Partition_def
      [,PARTITION partition…])

   PARTITION BY HASH (column) individual or by qty - see the Oracle docs.

   PARTITION BY LIST (column)
     (PARTITION partition
         VALUES (DEFAULT | NULL | value,…)
           Partition_def
      [,PARTITION partition…])

Partition_def:
   storage_options [[NO]COMPRESS] [OVERFLOW storage_options]
      [LOB_storage_clause] [varray_storage_clause]
         [(SUBPARTITION subpartion [LOB_storage_clause] [varray_storage_clause] )]

storage_options:
   PCTFREE int
   PCTUSED int
   INITTRANS int
   MAXTRANS int
   STORAGE storage_clause
   TABLESPACE tablespace
   [LOGGING|NOLOGGING]

subptn_clause:
   SUBPARTITION BY HASH (column,column,…)
     [SUBPARTITIONS quantity [STORE IN (tablespace,…)] ]
     [subpartition_template]
   SUBPARTITION BY LIST (column)  [subpartition_template] 

Example

partition by range (SA_DATE_PART) (
  partition P01_JAN 
     values less than (to_date('2005-02-01','yyyy-mm-dd'))
     tablespace DATA01,
  partition P02_FEB
     values less than (to_date('2005-03-01','yyyy-mm-dd'))
     tablespace DATA02,
  partition P03_MAR 
     values less than (to_date('2005-04-01','yyyy-mm-dd'))
     tablespace DATA03,
  partition P04_APR
     values less than (to_date('2005-05-01','yyyy-mm-dd'))
     tablespace DATA04, 
  partition P05_REST
     values less than (maxvalue))
     tablespace DATA;

Related Commands:

TABLE - CREATE TABLE
TABLE - ALTER TABLE

Related Views:

DBA_PART_COL_STATISTICS  ALL_PART_COL_STATISTICS  USER_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS   ALL_PART_HISTOGRAMS   USER_PART_HISTOGRAMS
DBA_PART_INDEXES      ALL_PART_INDEXES      USER_PART_INDEXES
DBA_PART_KEY_COLUMNS  ALL_PART_KEY_COLUMNS  USER_PART_KEY_COLUMNS
DBA_PART_LOBS         ALL_PART_LOBS         USER_PART_LOBS
DBA_PART_TABLES       ALL_PART_TABLES       USER_PART_TABLES


© Copyright SS64.com 1999-2013
Some rights reserved