Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: PARTITIONS Jump to:  
Category: >> Oracle PL/SQL >> PARTITIONS Bookmark and Share

<< lastnext >>

Snippet Name: PARTITIONS

Description: Oracle allows us to ecompose a table or index into smaller, more manageable pieces, called partitions. Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.

Also see:
» PARTITIONS
» Create monthly partitions

Comment: Oracle supports partitioning only for tables, indexes on tables, materialized views, and indexes on materialized views. Oracle does not support partitioning of clustered tables or indexes on clustered tables.

Language:
Highlight Mode: PLSQL
Last Modified: March 16th, 2009

PARTITION Key 
Each ROW IN a partitioned TABLE IS unambiguously assigned 
TO a single PARTITION. The PARTITION key IS a SET OF FROM 
1 TO 16 columns that determines the PARTITION FOR each ROW. 
 
Subpartition Partitions created within partitions. 
They are just partitions themselves AND there IS nothing 
special about them. 
 
Composite Partitioning 
Composite partitioning IS a combination OF other partitioning 
methods. Oracle currently supports range-hash AND 
range-list composite partitioning. 
 
INTERVAL Partitioning 
INTERVAL partitioning IS an extension TO RANGE partitioning 
IN which, beyond a point IN TIME, partitions are defined 
BY an INTERVAL. INTERVAL partitions are automatically created 
BY the database WHEN data IS inserted INTO the PARTITION. 
 
 
Example OF PARTITION BY list -
 
CREATE TABLE list_part (
deptno          NUMBER(10), 
quarterly_sales NUMBER(10,2),
state           VARCHAR2(2))
PARTITION BY LIST (state) (
PARTITION q1_nw VALUES ('OR', 'WA') TABLESPACE part1,
PARTITION q1_sw VALUES ('AZ', 'CA', 'NM') TABLESPACE part2,
PARTITION q1_ne VALUES ('NY', 'VT', 'NJ') TABLESPACE part1,
PARTITION q1_se VALUES ('FL', 'GA') TABLESPACE part2,
PARTITION q1_nc VALUES ('MN', 'WI') TABLESPACE part1,
PARTITION q1_sc VALUES ('OK', 'TX') TABLESPACE part2);
 
 
Example OF PARTITION BY DATE ramge -
 
CREATE TABLE range_part (
prof_history_id NUMBER(10),
person_id       NUMBER(10) NOT NULL,
organization_id NUMBER(10) NOT NULL,
record_date     DATE NOT NULL)
PARTITION BY RANGE (record_date) (
PARTITION yr0 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY'))
TABLESPACE part1,
PARTITION yr7 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY'))
TABLESPACE part2,
PARTITION yr8 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
TABLESPACE part3,
PARTITION yr9 VALUES LESS THAN (MAXVALUE) TABLESPACE part4);


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 103 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?