http://VietEnglish.com

http://Edgar411.com

http://ForumGrouper.com

http://hpricot.com

partitionDemo1.html
02-22-2001 Bikle  Creation
04-17-2001 Bikle  Added short discussion about partitions
12-08-2001 Bikle  Converted from .txt to .html
//////////////////////////////////////////////////////////

Dan's First Oracle Partitioning Demonstration

This is a simple document which discusses a few scenarios related to table
partitions in Oracle 8i.

Summary
-------

-Partition discussion
-Creation of a partitioned table
-Add partition to partitioned table
-Insert data into partitioned table
-Work-around ORA-14400
-Remove data via drop partition
-Merge partitions
-Remove all partitions from a table
-Add a partition to ordinary table
-Split a partition
-Enable row movement


Details
-------
-Partition discussion

What is a partition?  This question is best asked by a reader who is
able to define the terms listed below:

Table
Tablespace
Data File

A simple set of relationships of the above objects is written below
which holds true most of the time:

A tablespace may hold one or more tables.

A tablespace must be made up of one or more data files.

A table must reside in one and only one tablespace.

A data file may be bound to one and only one tablespace.

An easy way to conceptualize this is to think of a tablespace as a bucket
full of tables.  When the bucket fills up, we add data files to it so
it can hold more tables.

When Oracle.com released Oracle8, they added functionality such that we now
have a new object known as a "Partition".

This new object, the Partition, adds complexity to the relationship between
tables, tablespaces, and data files.

We will start with the easier relationships:

A table may be normal or it may be partitioned.

A partitioned table must be made up of one or more partitions.

Now we add a little bit of complexity:

A tablespace may contain one or more partitions.

A partition must reside in one and only one tablespace.

Actually that is not so complex; partitions relate to tablespaces
the way normal tables do.

Now let us ask some simple questions:

Q. If a table is made up of 3 partitions, do the partitions need to
reside in the same tablespace?
A. No

Q. If a partition exists must it be bound to a table?
A. Yes

Q. If a partition exists must it be bound to a tablespace?
A. Yes

Q. Do partitions change the relationship between tablespaces and data files?
A. No

Q. Do partitions change the relationship between tablespaces and tables?
A. Yes but only for partitioned tables.

Q. What is the relationship between tablespaces and partitioned tables?
A. A partitioned table may be made up of one or more partitions which themselves
   may reside in separate tablespaces.  So, a partitioned table may be tied to
   one or more tablespaces.

Q.  Why would I want to create a partitioned table?
A1. We can spread the table's I/O across a number of Tablespaces.
A2. Partitioned tables are less likely to get hit by full table scans.
A3. Partitioned tables are easier to move to different physical regions
    of the database than normal tables.  The only way to move a normal
    table across a tablespace boundary is to "rebuild" the table which
    means it cannot be done while the table is "hot".



-Creation of a partitioned table

The commands below demo the creation of a partitioned table:

--
-- create tablespaces
--

create tablespace ts1 datafile '/tmp/ts1.dbf' size 1m reuse;
create tablespace ts2 datafile '/tmp/ts2.dbf' size 1m reuse;
create tablespace ts3 datafile '/tmp/ts3.dbf' size 1m reuse;
create tablespace ts4 datafile '/tmp/ts4.dbf' size 1m reuse;

-- drop the table we intend to create
drop table timestamps;

-- notice the partitions get created during table creation
create table timestamps (atimestamp date)
partition by range (atimestamp)
(
partition p1 values less than (to_date('2001-01-01','yyyy-mm-dd')) tablespace ts1,
partition p2 values less than (to_date('2002-01-01','yyyy-mm-dd')) tablespace ts2,
partition p3 values less than (to_date('2003-01-01','yyyy-mm-dd')) tablespace ts3
);

-- Add partition to partitioned table
alter table timestamps add 
partition p4 values less than (to_date('2004-01-01', 'yyyy-mm-dd')) tablespace ts4;

-- Insert data into partitioned table
insert into timestamps values (to_date('2001-01-01','yyyy-mm-dd'));
insert into timestamps values (to_date('2002-01-01','yyyy-mm-dd'));
insert into timestamps values (to_date('2003-01-01','yyyy-mm-dd'));
insert into timestamps values (to_date('2004-01-01','yyyy-mm-dd'));

-- the last insert will give this error:
-- ORA-14400: inserted partition key is beyond highest legal partition key

-- Work-around ORA-14400

-- We fix this by dropping p4 and then recreating it such that the
-- highest legal partition key is very high.

-- Remove data via drop partition

alter table timestamps drop partition p4;
alter table timestamps add
partition p4 values less than (to_date('9999-01-01', 'yyyy-mm-dd')) tablespace ts4;
insert into timestamps values (to_date('2004-01-01','yyyy-mm-dd'));

-- Notice how we lost the row which was in p4
select * from timestamps;

-- we put it back in so now we have two rows in p4
insert into timestamps values (to_date('2003-01-01','yyyy-mm-dd'));
select * from timestamps;

-- Merge partitions
alter table timestamps merge partitions p1,p2 into partition p3;
-- command above gives this error:
-- ORA-14012: name conflicts with existing partition
-- command below works:
alter table timestamps merge partitions p1,p2 into partition p1p2 tablespace ts2;

-- Try renaming the partition (proper renaming allows workaround of the above error)
alter table timestamps  rename  partition p1p2 to blah;
alter table timestamps  rename  partition blah to p1p2;

-- see if our data is still there
select * from timestamps;

-- Now we drop ts1 since it is now empty
drop tablespace ts1;

-- Now we merge p1p2 with p3 and put result in ts4
alter table timestamps merge partitions p1p2,p3 into partition p1p2p3 tablespace ts4;

-- Now we can do what we tried before, we put everything in p3
alter table timestamps  rename  partition p1p2p3 to p3;

-- see if our data is still there
select * from timestamps;

-- Remove all partitions from a table

-- Now suppose we want to move all the rows into ts3 and get rid of the partitions.
create table timestamps2 tablespace ts3 as select * from timestamps;
drop table timestamps;
rename timestamps2 to timestamps;

-- Now we get rid of ts2 and ts4
drop tablespace ts2;
drop tablespace ts4;

-- Now we change our mind and decide we want the table in a new partition named p1
alter table timestamps add 
partition p1 values less than (to_date('9999-01-01', 'yyyy-mm-dd')) tablespace ts3;

-- we get error
-- ORA-14501: object is not partitioned
-- bummer

-- Add a partition to ordinary table

-- we do it the hard way
create table timestamps2 
partition by range (atimestamp)
(
partition p1 values less than (to_date('9999-01-01','yyyy-mm-dd')) tablespace ts3
)
as select * from timestamps;

drop table timestamps;
rename timestamps2 to timestamps;

-- check the data
select * from timestamps;

-- Split a partition
alter table timestamps split partition p1 at (to_date('2003-01-01','yyyy-mm-dd'))
into (partition px tablespace ts3, partition py tablespace ts3);

-- check the data
select * from timestamps;

-- try to move rows across partition boundary via an update
update timestamps set atimestamp = (to_date('2000-01-01','yyyy-mm-dd'));

-- we get error:
-- ORA-14402: updating partition key column would cause a partition change

-- Enable row movement
alter table timestamps enable row movement;

-- retry the update
update timestamps set atimestamp = (to_date('2000-01-01','yyyy-mm-dd'));

-- clean up the demo
drop tablespace ts3 including contents;
!/bin/rm /tmp/ts1.dbf /tmp/ts2.dbf /tmp/ts3.dbf /tmp/ts4.dbf

end of demo
///////////////////////////////////////////////////////////////////////////////////