04/03/2007
1.在ORACLE中有3种方式可以用于对表进行分区(详见oracle专家高级编程P601)
1)范围分区-执行应该存储在一起的数据的范围。关键字RANGE
2)散列分区-对一列或几列应用散列函数。关键字HASH
3)混合分区-先按范围分区,在范围内按散列分区。
例1根据范围分区:
CREATE TABLE range_example
(rid number, rvalue varchar2(20))
PARTITION BY RANGE(rid)
(
PARTITION part_1 VALUES LESS THAN (5),
PARTITION part_2 VALUES LESS THAN (10)
);/
SQL> SELECT * FROM range_example;
RID RVALUE
---------- --------------------
SQL> INSERT INTO range_example VALUES (1,10);
1 row inserted
SQL> INSERT INTO range_example VALUES (3,30);
1 row inserted
SQL> INSERT INTO range_example VALUES (5,50);
1 row inserted
SQL> INSERT INTO range_example VALUES (6,60);
1 row inserted
SQL> INSERT INTO range_example VALUES (9,90);
1 row inserted
SQL> INSERT INTO range_example VALUES (10,100);
INSERT INTO range_example VALUES (10,100)
ORA-14400: 插入的分区关键字未映射到任何分区
SQL> INSERT INTO range_example VALUES (11,110);
INSERT INTO range_example VALUES (11,110)
ORA-14400: 插入的分区关键字未映射到任何分区
SQL> SELECT * FROM range_example PARTITION( part_1);
RID RVALUE
---------- --------------------
1 10
3 30
SQL> SELECT * FROM range_example PARTITION( part_2);
RID RVALUE
---------- --------------------
5 50
6 60
9 90
SQL> UPDATE range_example SET rid = 7 WHERE rid= 6;
1 row updated
SQL> UPDATE range_example SET rid = 4 WHERE rid= 7;
UPDATE range_example SET rid = 4 WHERE rid= 7
ORA-14402: 更新分区关键字列将导致分区的更改
SQL> ALTER TABLE range_example ENABLE ROW MOVEMENT;
Table altered
SQL> UPDATE range_example SET rid = 4 WHERE rid= 7;
1 row updated
SQL>
例2根据散列函数分区:
CREATE TABLE hash_example
(hid number, hvalue varchar2(20))
PARTITION BY HASH(hid)
(
PARTITION part_1 tablespace p1,
PARTITION part_2 tablespace p2
);/
2.如果有使范围分区有意义的数据,推荐最好使用范围分区而不是散列分区。散列分区增加了分区的许多好处,但
是就分区排除而言,它不如范围分区那么有用。