创建range联合分区:
create table range_part_mult_col_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
partition by range (area_code,deal_date)
(
partition p_591_201301 values less than (591,TO_DATE('2013-02-01', 'YYYY-MM-DD')),
partition p_591_201302 values less than (591,TO_DATE('2013-03-01', 'YYYY-MM-DD')),
partition p_591_201303 values less than (591,TO_DATE('2013-04-01', 'YYYY-MM-DD')),
partition p_591_201304 values less than (591,TO_DATE('2013-05-01', 'YYYY-MM-DD')),
partition p_591_201305 values less than (591,TO_DATE('2013-06-01', 'YYYY-MM-DD')),
partition p_591_201306 values less than (591,TO_DATE('2013-07-01', 'YYYY-MM-DD')),
partition p_591_201307 values less than (591,TO_DATE('2013-08-01', 'YYYY-MM-DD')),
partition p_591_201308 values less than (591,TO_DATE('2013-09-01', 'YYYY-MM-DD')),
partition p_591_201309 values less than (591,TO_DATE('2013-10-01', 'YYYY-MM-DD')),
partition p_591_201310 values less than (591,TO_DATE('2013-11-01', 'YYYY-MM-DD')),
partition p_591_201311 values less than (591,TO_DATE('2013-12-01', 'YYYY-MM-DD')),
partition p_591_201312 values less than (591,TO_DATE('2014-01-01', 'YYYY-MM-DD')),
partition p_591_201401 values less than (591,TO_DATE('2014-02-01', 'YYYY-MM-DD')),
partition p_591_201402 values less than (591,TO_DATE('2014-03-01', 'YYYY-MM-DD')),
partition p_591_max values less than (591,maxvalue),
partition p_592_201301 values less than (592,TO_DATE('2013-02-01', 'YYYY-MM-DD')),
partition p_592_201302 values less than (592,TO_DATE('2013-03-01', 'YYYY-MM-DD')),
partition p_592_201303 values less than (592,TO_DATE('2013-04-01', 'YYYY-MM-DD')),
partition p_592_201304 values less than (592,TO_DATE('2013-05-01', 'YYYY-MM-DD')),
partition p_592_201305 values less than (592,TO_DATE('2013-06-01', 'YYYY-MM-DD')),
partition p_592_201306 values less than (592,TO_DATE('2013-07-01', 'YYYY-MM-DD')),
partition p_592_201307 values less than (592,TO_DATE('2013-08-01', 'YYYY-MM-DD')),
partition p_592_201308 values less than (592,TO_DATE('2013-09-01', 'YYYY-MM-DD')),
partition p_592_201309 values less than (592,TO_DATE('2013-10-01', 'YYYY-MM-DD')),
partition p_592_201310 values less than (592,TO_DATE('2013-11-01', 'YYYY-MM-DD')),
partition p_592_201311 values less than (592,TO_DATE('2013-12-01', 'YYYY-MM-DD')),
partition p_592_201312 values less than (592,TO_DATE('2014-01-01', 'YYYY-MM-DD')),
partition p_592_201401 values less than (592,TO_DATE('2014-02-01', 'YYYY-MM-DD')),
partition p_592_201402 values less than (592,TO_DATE('2014-03-01', 'YYYY-MM-DD')),
partition p_592_max values less than (592,maxvalue),
插入数据:
insert into range_part_mult_col_tab (id,deal_date,area_code,nbr,contents)
select rownum,
to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
ceil(dbms_random.value(591,593)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
该表是否是分区表,分区表的分区类型是什么,是否有子分区,分区总数有多少:
该分区表在哪一列上建分区,有无多列联合建分区:
select column_name, object_type, column_position from user_part_key_columns where name ='RANGE_PART_MULT_COL_TAB';
该分区表有多大:
select sum(bytes) / 1024 / 1024 from user_segments where segment_name ='RANGE_PART_MULT_COL_TAB';
该分区表各分区分别有多大,各个分区名是什么:
select partition_name, segment_type, bytes from user_segments where segment_name ='RANGE_PART_MULT_COL_TAB';
该分区表的统计信息收集情况:
select table_name, partition_name, last_analyzed, partition_position, num_rows from user_tab_statistics t where table_name ='RANGE_PART_MULT_COL_TAB';
该分区表有无索引,分别什么类型,全局索引是否失效,此外还可看统计信息收集情况:
select table_name,
index_name,
last_analyzed,
blevel,
num_rows,
leaf_blocks,
distinct_keys,
status
from user_indexes
where table_name ='RANGE_PART_MULT_COL_TAB';
分区表在哪些列上建了索引:
select index_name, column_name, column_position from user_ind_columns where table_name = 'RANGE_PART_MULT_COL_TAB';
该分区表上的各索引分别有多大:
select segment_name,segment_type,sum(bytes)/1024/1024
from user_segments
where segment_name in
(select index_name
from user_indexes
where table_name ='RANGE_PART_MULT_COL_TAB'
)
group by segment_name,segment_type ;
该分区表的索引段的分配情况:
select segment_name
partition_name,
segment_type,
bytes
from user_segments
where segment_name in
(select index_name
from user_indexes
where table_name ='RANGE_PART_MULT_COL_TAB');
分区索引相关信息及统计信息、是否失效查看: