Oracle 11g 虚拟列子分区实验
以下两个create 语句均为创建成功;
需求描述:
项目要求对3个字段进行分区,如时间,小时,还有个区域ID,便联想到虚拟列。
droptabletest_partpurge; createtabletest_part (t_timedate,hournumber,city_idnumber,hour_cityvarchar2(108)GENERATEDALWAYSAS(to_char(hour)||'|'||to_char(city_id))) partitionbyrange(t_time)subpartitionbyhash(hour_city) (partitionpart_201705valueslessthan(to_date('20170601','yyyymmdd')) (subpartitionsubpart_201705_01,subpartitionsubpart_201705_02),partitionpart_201706valueslessthan(to_date('20170701','yyyymmdd')) (subpartitionsubpart_201706_01,subpartitionsubpart_201706_02)) createtabletest_part (t_timedate,hournumber(2),day_hourvarchar2(50)GENERATEDALWAYSAS(to_char(t_time,'yyyymmdd')||'|'||to_char(hour))) partitionbyrange(day_hour)subpartitionbyhash(city_id) (partitionpart_201705valueslessthan('2017060100') (subpartitionsubpart_201705_01,partitionpart_201706valueslessthan('2017070100') (subpartitionsubpart_201706_01,subpartitionsubpart_201706_02))