|
摘录部分我的笔记的中doc，和大家一起感受Oracle 11g在分区方面的增强--基于虚拟列的分区 Virtual Column是11g中的一个新功能，这种列中的数据并不实际存储于磁盘上（可以看成是一个逻辑列或类似Function运算的列）,只有当读取的时候才实时计算. 假设系统中有这样一张表 create table emp
( empno number(4),
sal number,
t_grade varchar2(1)
);
我们需要根据员工的薪水和技术等级来显示一下这些员工的分类，比如
sal t_grade t_class (实际不存在的列)
0 -2000 t_grade为any，则属于0段
2001 -5000 t_grade为C, 则属于1段
2001 -5000 t_grade为B, 则属于2段
2001 -5000 t_grade为A， 则属于3段
5001 -8000 t_grade为C， 则属于4段
5001 -8000 t_grade为B， 则属于5段
5001 -8000 t_grade为A， 则属于6段
8001 -15000 t_grade为C, 则属于7段
8001 -15000 t_grade为B, 则属于8段
8001 -15000 t_grade为A, 则属于9段
计划按照t_class列对该表进行分区,可实际上t_class列不存在，那怎么办呢.在11g之前，这个问题对DBA来说就束手无策了，而这一切，在11g中
迎刃而解。11g中新增加的虚拟列和虚拟列分区技术可以解决这个问题（详细的Virtual Column技术本案例中不做过多的介绍）
create table emp
( empno number(4),
sal number,
t_grade varchar2(1),
t_class number
as
(
case
when sal <= 2000 then 0
when sal > 2000 and sal <= 5000
then case
when t_grade='C' then 1
when t_grade='B' then 2
when t_grade='A' then 3
end
when sal > 5000 and sal <= 8000
then case
when t_grade='C' then 4
when t_grade='B' then 5
when t_grade='A' then 6
end
when sal > 8000
then case
when t_grade='C' then 7
when t_grade='B' then 8
when t_grade='A' then 9
else
10
end
end
)
)
partition by list (t_class)
(
partition p_0 values (0),
partition p_1 values (1),
partition p_2 values (2),
partition p_3 values (3),
partition p_4 values (4),
partition p_5 values (5),
partition p_6 values (6),
partition p_7 values (7),
partition p_8 values (8),
partition p_9 values (9),
partition p_10 values (10)
);
下面插入一些具体的值来看看 注意：对于使用了Virtual Column的表，在insert的时候，要么全部指定非Virtual Column, 要么对Virtual Column采用default值，如下面两种方式 insert into emp(empno,sal,t_grade) values(1001,1500,'C');
insert into emp values(1001,1500,'C',default); SQL> select table_name,partition_name,high_value,partition_position
2 from user_tab_partitions
3 where table_name='EMP'
4 order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION
--------------- ----------------- ------------ ------------------
EMP P_0 0 1
EMP P_1 1 2
EMP P_2 2 3
EMP P_3 3 4
EMP P_4 4 5
EMP P_5 5 6
EMP P_6 6 7
EMP P_7 7 8
EMP P_8 8 9
EMP P_9 9 10
EMP P_10 10 11
11 rows selected
SQL>
insert into emp(empno,sal,t_grade) values(1001,1500,'C');
insert into emp(empno,sal,t_grade) values(1002,1500,'B');
insert into emp(empno,sal,t_grade) values(1003,1500,'A');
insert into emp(empno,sal,t_grade) values(1004,2500,'C');
insert into emp(empno,sal,t_grade) values(1005,3000,'B');
insert into emp(empno,sal,t_grade) values(1006,4500,'C');
insert into emp(empno,sal,t_grade) values(1007,6500,'C');
insert into emp(empno,sal,t_grade) values(1008,6500,'A');
insert into emp(empno,sal,t_grade) values(1009,7500,'C');
insert into emp(empno,sal,t_grade) values(1010,7000,'B');
insert into emp(empno,sal,t_grade) values(1011,8500,'C');
insert into emp(empno,sal,t_grade) values(1012,8500,'B');
insert into emp(empno,sal,t_grade) values(1013,8500,'A');
insert into emp(empno,sal,t_grade) values(1014,9500,'C');
insert into emp(empno,sal,t_grade) values(1015,10500,'B');
insert into emp(empno,sal,t_grade) values(1016,12000,'C');
insert into emp(empno,sal,t_grade) values(1017,12500,'B');
insert into emp(empno,sal,t_grade) values(1018,15000,'C');
commit;
SQL> select * from emp;
EMPNO SAL T_GRADE T_CLASS
----- ---------- ------- ----------
1001 1500 C 0
1002 1500 B 0
1003 1500 A 0
1004 2500 C 1
1006 4500 C 1
1005 3000 B 2
1007 6500 C 4
1009 7500 C 4
1010 7000 B 5
1008 6500 A 6
1011 8500 C 7
1014 9500 C 7
1016 12000 C 7
1018 15000 C 7
1012 8500 B 8
1015 10500 B 8
1017 12500 B 8
1013 8500 A 9
18 rows selected
SQL> select * from emp partition (p_0);
EMPNO SAL T_GRADE T_CLASS
----- ---------- ------- ----------
1001 1500 C 0
1002 1500 B 0
1003 1500 A 0
SQL> select * from emp partition (p_1);
EMPNO SAL T_GRADE T_CLASS
----- ---------- ------- ----------
1004 2500 C 1
1006 4500 C 1
SQL> select * from emp partition (p_2);
EMPNO SAL T_GRADE T_CLASS
----- ---------- ------- ----------
1005 3000 B 2
SQL> select * from emp partition (p_3);
EMPNO SAL T_GRADE T_CLASS
----- ---------- ------- ----------
SQL> select * from emp partition (p_4);
EMPNO SAL T_GRADE T_CLASS
----- ---------- ------- ----------
1007 6500 C 4
1009 7500 C 4
SQL> select * from emp partition (p_5);
EMPNO SAL T_GRADE T_CLASS
----- ---------- ------- ----------
1010 7000 B 5
SQL> select * from emp partition (p_6);
EMPNO SAL T_GRADE T_CLASS
----- ---------- ------- ----------
1008 6500 A 6
SQL> select * from emp partition (p_7);
EMPNO SAL T_GRADE T_CLASS
----- ---------- ------- ----------
1011 8500 C 7
1014 9500 C 7
1016 12000 C 7
1018 15000 C 7
SQL> select * from emp partition (p_8);
EMPNO SAL T_GRADE T_CLASS
----- ---------- ------- ----------
1012 8500 B 8
1015 10500 B 8
1017 12500 B 8
SQL> select * from emp partition (p_9);
EMPNO SAL T_GRADE T_CLASS
----- ---------- ------- ----------
1013 8500 A 9
SQL> select * from emp partition (p_10);
EMPNO SAL T_GRADE T_CLASS
----- ---------- ------- ----------
SQL>
说明：Partition pruning takes place for virtual column partition keys when the predicates on the partitioning key are of the following types:
Equality or Like
List
Range
TBL$
Partition extended names
Given a join operation between two tables, the optimizer recognizes when partition-wise join (full or partial) is applicable, decides whether to use it or not and annotate the join properly when it decides to use it. This applies to both serial and parallel cases.
To recognize full partition-wise join the optimizer relies on the definition of equi-partitioning of two objects, this definition includes the equivalence of the virtual expression on which the tables were partitioned.
|
