1.按日期范围分区创建表

方式1：

create table t_range_partition (id number,update_date date,age number,name varchar2(128)) partition by range (update_date) ( partition p_2019_1 values less than (TO_DATE('2019-04-01', 'YYYY-MM-DD')), partition p_2019_2 values less than (TO_DATE('2019-07-01', 'YYYY-MM-DD')), partition p_2019_3 values less than (TO_DATE('2019-10-01', 'YYYY-MM-DD')), partition p_max values less than (maxvalue) ) ;

方式2：指定第一个分区，之后自动按月进行分区

CREATE TABLE t_range_partition(id number,update_date date,age number,name varchar2(128)) PARTITION BY RANGE(update_date) interval(NUMTOYMINTERVAL(1,'MONTH'))(PARTITION p_2019_1 VALUES LESS THAN(TO_DATE('2019-09-01','YYYY-MM-DD')));

2.往表中插入2019年的数据

insert into t_range_partition (id,update_date,age,name) select rownum, to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'), ceil(dbms_random.value(1,100)), rpad('*',100,'*') from dual connect by rownum <= 100000;commit;

3.创建表索引：

--索引可以建全局索引和分区索引，在分区表中建议建分区索引，因为如果是全局索引，当分区发生变更的时候，会导致全局索引失效

--主键或者唯一索引如果索引字段不包含分区字段，是不支持建分区索引的

create index t_range_partition_id on t_range_partition (id) ;create index t_range_partition_name on t_range_partition (name) local;

select count(*) from t_range_partition partition (p_2019_1);select count(*) from t_range_partition partition (p_2019_2);select count(*) from t_range_partition partition (p_2019_3);

4.查询该表的分区类型，分区数量

select partitioning_type,  subpartitioning_type,  partition_count from user_part_tables where table_name ='T_RANGE_PARTITION';

5.查询该表的分区字段

select column_name,  object_type,  column_position from user_part_key_columns where name ='T_RANGE_PARTITION';

6.查询该表的分区名称，分区大小

select partition_name,  segment_type,  bytes from user_segments where segment_name ='T_RANGE_PARTITION';

7.查询该表的索引信息

select table_name,  index_name,  last_analyzed, blevel, num_rows, leaf_blocks, distinct_keys, status from user_indexes where table_name ='T_RANGE_PARTITION';