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文档