Partitioned Tables
--
?Range?Partitioning?Example
CREATE
?
TABLE
?sales_range?
(salesman_id??
NUMBER
(
5
),?
salesman_name?
VARCHAR2
(
30
),?
sales_amount??
NUMBER
(
10
),?
sales_date????DATE)
PARTITION?
BY
?RANGE(sales_date)?
(
PARTITION?sales_jan2006?
VALUES
?LESS?THAN(TO_DATE(
'
02/01/2006
'
,
'
MM/DD/YYYY
'
)),
PARTITION?sales_feb2006?
VALUES
?LESS?THAN(TO_DATE(
'
03/01/2006
'
,
'
MM/DD/YYYY
'
)),
PARTITION?sales_mar2006?
VALUES
?LESS?THAN(TO_DATE(
'
04/01/2006
'
,
'
MM/DD/YYYY
'
)),
PARTITION?sales_apr2006?
VALUES
?LESS?THAN(TO_DATE(
'
05/01/2006
'
,
'
MM/DD/YYYY
'
)),
PARTITION?sales_may2006?
VALUES
?LESS?THAN(TO_DATE(
'
06/01/2006
'
,
'
MM/DD/YYYY
'
))
);
insert
?
into
?sales_range?
values
(
12345
,?
'
test
'
,?
1000
,?to_date(
'
2006-01-15
'
,?
'
yyyy-mm-dd
'
));
insert
?
into
?sales_range?
values
(
12345
,?
'
test
'
,?
1000
,?to_date(
'
2006-02-15
'
,?
'
yyyy-mm-dd
'
));
insert
?
into
?sales_range?
values
(
12345
,?
'
test
'
,?
1000
,?to_date(
'
2006-03-15
'
,?
'
yyyy-mm-dd
'
));
insert
?
into
?sales_range?
values
(
12345
,?
'
test
'
,?
1000
,?to_date(
'
2006-04-15
'
,?
'
yyyy-mm-dd
'
));
insert
?
into
?sales_range?
values
(
12345
,?
'
test
'
,?
1000
,?to_date(
'
2006-05-15
'
,?
'
yyyy-mm-dd
'
));
create
?
index
?idx_sales_range_sales_date?
on
?sales_range?(sales_date);
ALTER
?
TABLE
?sales_range?
DROP
?PARTITION?sales_jan2006?
UPDATE
?GLOBAL?INDEXES;
When to Partition a Table
Here are some suggestions for when to partition a table:
- Tables greater than 2GB should always be considered for partitioning.
- Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read-only.
參考:? ora92_doc/server.920/a96524/c12parti.htm
詳細請參考oracle文檔