--list_range 示例 drop table list_range_tab purge;create table list_range_tab(n1 number,n2 date)partition by list(n1)subpartition by range(n2)(partition p01 values(1) ( subpartition subp01 values less than (to_date('3001-01-01 22:00:00','yyyy-mm-dd hh24:mi:ss')), subpartition subp_default values less than (maxvalue) )) --range_list 示例 drop table range_list_tab purge;create table range_list_tab(n1 date,n2 number)partition by range(n1)subpartition by list(n2)(partition p01 values less than (to_date('3001-01-01 22:00:00','yyyy-mm-dd hh24:mi:ss')) ( subpartition subp01 values(1) ), partition p_default values less than (maxvalue) ); --range_list_inter 主分区自动分区 子分区自动分区示例 drop table range_list_tab_inter purge; --按天自动分区create table range_list_tab_inter_dd(n1 date,n2 number)partition by range(n1)interval(numtodsinterval(1,'day'))subpartition by list(n2)(partition p01 values less than (to_date('3001-01-01 22:00:00','yyyy-mm-dd hh24:mi:ss')) ( subpartition subp01 values(1) , subpartition subp_default values(default)) ); insert into range_list_tab_inter_dd values(to_date('3001-01-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),1);insert into range_list_tab_inter_dd values(to_date('3001-02-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),1);insert into range_list_tab_inter_dd values(to_date('3001-03-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),1);insert into range_list_tab_inter_dd values(to_date('3001-01-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),111);insert into range_list_tab_inter_dd values(to_date('3001-02-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),222);insert into range_list_tab_inter_dd values(to_date('3001-03-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),333);insert into range_list_tab_inter_dd values(to_date('3001-03-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),444);insert into range_list_tab_inter_dd values(to_date('3001-03-01 22:00:00','yyyy-mm-dd hh24:mi:ss'),555);select t1.table_name, t1.partition_position, t1.partition_name, t2.subpartition_position, t2.subpartition_name from user_tab_partitions t1, user_tab_subpartitions t2 where t1.table_name = t2.table_name and t1.table_name = 'RANGE_LIST_TAB_INTER' order by t1.partition_position asc,t2.subpartition_position asc;select * from user_tab_subpartitions where table_name = 'RANGE_LIST_TAB_INTER_DD'--按月自动分区drop table range_list_tab_inter_mm purge;delete from range_list_tab_inter_mm ;create table range_list_tab_inter_mm(n1 date,n2 number)partition by range(n1) --指定 主分区 以 n1 字段做 range 分区interval(numtoyminterval(1,'month')) --指定主分区的扩展方式subpartition by list(n2) --指定 子分区 以 n2 字段 做 list 分区(partition p01 values less than (to_date('3001-01-01 22:00:00','yyyy-mm-dd hh24:mi:ss')) --指定主分区1的上限 ( subpartition subp01 values(1) , --指定子分区1 的 值 subpartition subp_default values(default)) --指定子分区 的 默认值，保证所有数据都可以入库 ); insert into range_list_tab_inter_mm values(to_date('3001-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),1);insert into range_list_tab_inter_mm values(to_date('3001-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),1);insert into range_list_tab_inter_mm values(to_date('3001-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),3);select t1.table_name, t1.partition_position, t1.partition_name, t2.subpartition_position, t2.subpartition_name from user_tab_partitions t1, user_tab_subpartitions t2 where t1.table_name = t2.table_name and t1.table_name = 'RANGE_LIST_TAB_INTER_MM' order by t1.partition_position asc,t2.subpartition_position asc;
转载于:https://www.cnblogs.com/iyoume2008/p/7735635.html
