Using Application-Controlled (System) Partitioning Application-controlled partitioning, also
known as system partitioning, relies on the application logic to place rows into the appropriate
partition. Only the partition names and the number of partitions are specified when the table is
created, as in this example:
create table order_hist_sys_part
(order_num NUMBER(15),
cust_id NUMBER(12),
order_dt date,
order_total NUMBER(10,2)
)
partition by system
(partition p1 tablespace users1,
partition p2 tablespace users2,
partition p3 tablespace users3,
partition p4 tablespace users4
)
;
Any insert statements on this table must specify the partition number; otherwise, the insert
will fail. Here is an example:
SQL> insert into order_hist_sys_part
2 partition (p3)
3 values (49809233,93934011,sysdate,122.12);
1 row created.
SQL>
Using Virtual Column Partitioning Virtual columns, available in Oracle Database 11g, can
also be used as a partition key; any partition method that uses a regular column can use a virtual
column. In this example, you create a partitioned table for order items based on the total cost of
the line item, in other words, number of items multiplied by the item price:
create table line_item_value
(order_num number(15) not null,
line_item_num number(3) not null,
product_num number(10),
item_price number(8,2),
item_qty number(8),
total_price as (item_price * item_qty)
)
partition by range (total_price)
(
partition small values less than (100),
partition medium values less than (500),
partition large values less than (1000),
partition xlarge values less than (maxvalue)
);
Chapter 16: Managing Large Databases 571
Compressed Partitioned Tables Partitioned tables can be compressed just as nonpartitioned
tables can; in addition, the partitions of a partitioned table can be selectively compressed.
Pages:
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849