首页 > 代码库 > oracle 优化 —— 分区表
oracle 优化 —— 分区表
一、分区表简介
分区表类型：【范围分区】、【列表分区】 【hash分区】 【这些分区的组合分区】
范围分区：以某一个范围进行分区。eg:时间段划分。
列表分区：以某一些几个值进行分区。eg:地区分区，省份进行划分。
hash分区：以hash算法进行分块。可以有效的消除io的竞争。 更多用在组合分区的子分区中。
组合分区：11g前仅有两种组合分区 （range- *） eg: 范围 -列表（月份地区），范围- hash 两种组合
11g后新增四种。（range-range，list-list，list-hash，list-range） 考虑到兼容性等问题尽量使用 范围开头的组合分区。
使用分区表优点：
减少访问路径，提升性能外
更方便的批量操作数据从而维护方便。
不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
二、分区表实战
范围分区示例
1 -- 范围分区示例 2 drop table range_part_tab purge; 3 --注意，此分区为范围分区 4 5 --例子1 6 create table range_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000)) 7 partition by range (deal_date) 8 ( 9 partition p_201301 values less than (TO_DATE(‘2013-02-01‘, ‘YYYY-MM-DD‘)),10 partition p_201302 values less than (TO_DATE(‘2013-03-01‘, ‘YYYY-MM-DD‘)),11 partition p_201303 values less than (TO_DATE(‘2013-04-01‘, ‘YYYY-MM-DD‘)),12 partition p_201304 values less than (TO_DATE(‘2013-05-01‘, ‘YYYY-MM-DD‘)),13 partition p_201305 values less than (TO_DATE(‘2013-06-01‘, ‘YYYY-MM-DD‘)),14 partition p_201306 values less than (TO_DATE(‘2013-07-01‘, ‘YYYY-MM-DD‘)),15 partition p_201307 values less than (TO_DATE(‘2013-08-01‘, ‘YYYY-MM-DD‘)),16 partition p_201308 values less than (TO_DATE(‘2013-09-01‘, ‘YYYY-MM-DD‘)),17 partition p_201309 values less than (TO_DATE(‘2013-10-01‘, ‘YYYY-MM-DD‘)),18 partition p_201310 values less than (TO_DATE(‘2013-11-01‘, ‘YYYY-MM-DD‘)),19 partition p_201311 values less than (TO_DATE(‘2013-12-01‘, ‘YYYY-MM-DD‘)),20 partition p_201312 values less than (TO_DATE(‘2014-01-01‘, ‘YYYY-MM-DD‘)),21 partition p_201401 values less than (TO_DATE(‘2014-02-01‘, ‘YYYY-MM-DD‘)),22 partition p_201402 values less than (TO_DATE(‘2014-03-01‘, ‘YYYY-MM-DD‘)),23 partition p_max values less than (maxvalue)24 )25 ;26 27 28 --以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：29 insert into range_part_tab (id,deal_date,area_code,nbr,contents)30 select rownum,31 to_date( to_char(sysdate-365,‘J‘)+TRUNC(DBMS_RANDOM.VALUE(0,365)),‘J‘),32 ceil(dbms_random.value(591,599)),33 ceil(dbms_random.value(18900000001,18999999999)),34 rpad(‘*‘,400,‘*‘)35 from dual36 connect by rownum <= 100000;37 commit;38 39 40 41 --以下是插入2014年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：42 insert into range_part_tab (id,deal_date,area_code,nbr,contents)43 select rownum,44 to_date( to_char(sysdate,‘J‘)+TRUNC(DBMS_RANDOM.VALUE(0,365)),‘J‘),45 ceil(dbms_random.value(591,599)),46 ceil(dbms_random.value(18900000001,18999999999)),47 rpad(‘*‘,400,‘*‘)48 from dual49 connect by rownum <= 100000;50 commit;51 52 53 ---添加一个全局索引、一个局部索引后，后面会提到分区操作对索引的影响。54 create index idx_part_id on range_part_tab (id) ;55 create index idx_part_nbr on range_part_tab (nbr) local;56 57 --统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试58 exec dbms_stats.gather_table_stats(ownname => ‘LJB‘,tabname => ‘RANGE_PART_TAB‘,estimate_percent => 10,method_opt=> ‘for all indexed columns‘,cascade=>TRUE) ; 59 60 61 select min(deal_date),max(deal_date) from range_part_tab;62 63 --查看每个分区一共保存了多少条数据64 select count(*) from range_part_tab partition (p_201301);65 select count(*) from range_part_tab partition (p_201302);66 select count(*) from range_part_tab partition (p_201303);67 select count(*) from range_part_tab partition (p_201304);68 select count(*) from range_part_tab partition (p_201305);69 select count(*) from range_part_tab partition (p_201306);70 select count(*) from range_part_tab partition (p_201307);71 select count(*) from range_part_tab partition (p_201308);72 select count(*) from range_part_tab partition (p_201309);73 select count(*) from range_part_tab partition (p_201310);74 select count(*) from range_part_tab partition (p_201311);75 select count(*) from range_part_tab partition (p_201312);76 select count(*) from range_part_tab partition (p_max);
列表分区示例
1 --列表分区示例 2 drop table list_part_tab purge; 3 --注意，此分区为列表分区 4 create table list_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000)) 5 partition by list (area_code) 6 ( 7 partition p_591 values (591), 8 partition p_592 values (592), 9 partition p_593 values (593),10 partition p_594 values (594),11 partition p_595 values (595),12 partition p_596 values (596),13 partition p_597 values (597),14 partition p_598 values (598),15 partition p_599 values (599),16 partition p_other values (DEFAULT)17 )18 ;19 20 --以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：21 insert into list_part_tab (id,deal_date,area_code,nbr,contents)22 select rownum,23 to_date( to_char(sysdate-365,‘J‘)+TRUNC(DBMS_RANDOM.VALUE(0,365)),‘J‘),24 ceil(dbms_random.value(590,599)),25 ceil(dbms_random.value(18900000001,18999999999)),26 rpad(‘*‘,400,‘*‘)27 from dual28 connect by rownum <= 100000;29 commit;30 31 32 select count(*) from list_part_tab partition(p_591);33 select count(*) from list_part_tab partition(p_592);34 select count(*) from list_part_tab partition(p_593);35 select count(*) from list_part_tab partition(p_594);36 select count(*) from list_part_tab partition(p_595);37 select count(*) from list_part_tab partition(p_596);38 select count(*) from list_part_tab partition(p_597);39 select count(*) from list_part_tab partition(p_598);40 select count(*) from list_part_tab partition(p_599);41 select count(*) from list_part_tab partition(p_other);42 43 44 create index idx_list_part_id on list_part_tab (id) ;45 create index idx_list_part_nbr on list_part_tab (nbr) local;46 47 --统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试48 exec dbms_stats.gather_table_stats(ownname => ‘LJB‘,tabname => ‘LIST_PART_TAB‘,estimate_percent => 10,method_opt=> ‘for all indexed columns‘,cascade=>TRUE) ;
hash分区示例
1 --散列分区示例 2 drop table hash_part_tab purge; 3 --注意，此分区HASH分区 4 create table hash_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000)) 5 partition by hash (deal_date) 6 PARTITIONS 12 7 ; 8 --以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下： 9 insert into hash_part_tab(id,deal_date,area_code,nbr,contents)10 select rownum,11 to_date( to_char(sysdate-365,‘J‘)+TRUNC(DBMS_RANDOM.VALUE(0,365)),‘J‘),12 ceil(dbms_random.value(590,599)),13 ceil(dbms_random.value(18900000001,18999999999)),14 rpad(‘*‘,400,‘*‘)15 from dual16 connect by rownum <= 100000;17 commit;18 19 20 21 --以下分区名是通过数据字典user_segments的partition_name查出来的，详见后面说明。22 ---每个分区存放多少数据23 select partition_name, 24 segment_type, 25 bytes,26 ‘select count(*) from hash_part_tab partition(‘||partition_name||‘);‘27 from user_segments28 where segment_name =‘HASH_PART_TAB‘;
联合字段分区（两种联合起来进行分区）
1 -- 范围分区示例 2 drop table range_part_mult_col_tab purge; 3 --注意，此分区为联合字段的范围分区 4 5 create table range_part_mult_col_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000)) 6 partition by range (area_code,deal_date) 7 ( 8 partition p_591_201301 values less than (591,TO_DATE(‘2013-02-01‘, ‘YYYY-MM-DD‘)), 9 partition p_591_201302 values less than (591,TO_DATE(‘2013-03-01‘, ‘YYYY-MM-DD‘)), 10 partition p_591_201303 values less than (591,TO_DATE(‘2013-04-01‘, ‘YYYY-MM-DD‘)), 11 partition p_591_201304 values less than (591,TO_DATE(‘2013-05-01‘, ‘YYYY-MM-DD‘)), 12 partition p_591_201305 values less than (591,TO_DATE(‘2013-06-01‘, ‘YYYY-MM-DD‘)), 13 partition p_591_201306 values less than (591,TO_DATE(‘2013-07-01‘, ‘YYYY-MM-DD‘)), 14 partition p_591_201307 values less than (591,TO_DATE(‘2013-08-01‘, ‘YYYY-MM-DD‘)), 15 partition p_591_201308 values less than (591,TO_DATE(‘2013-09-01‘, ‘YYYY-MM-DD‘)), 16 partition p_591_201309 values less than (591,TO_DATE(‘2013-10-01‘, ‘YYYY-MM-DD‘)), 17 partition p_591_201310 values less than (591,TO_DATE(‘2013-11-01‘, ‘YYYY-MM-DD‘)), 18 partition p_591_201311 values less than (591,TO_DATE(‘2013-12-01‘, ‘YYYY-MM-DD‘)), 19 partition p_591_201312 values less than (591,TO_DATE(‘2014-01-01‘, ‘YYYY-MM-DD‘)), 20 partition p_591_201401 values less than (591,TO_DATE(‘2014-02-01‘, ‘YYYY-MM-DD‘)), 21 partition p_591_201402 values less than (591,TO_DATE(‘2014-03-01‘, ‘YYYY-MM-DD‘)), 22 partition p_591_max values less than (591,maxvalue), 23 partition p_592_201301 values less than (592,TO_DATE(‘2013-02-01‘, ‘YYYY-MM-DD‘)), 24 partition p_592_201302 values less than (592,TO_DATE(‘2013-03-01‘, ‘YYYY-MM-DD‘)), 25 partition p_592_201303 values less than (592,TO_DATE(‘2013-04-01‘, ‘YYYY-MM-DD‘)), 26 partition p_592_201304 values less than (592,TO_DATE(‘2013-05-01‘, ‘YYYY-MM-DD‘)), 27 partition p_592_201305 values less than (592,TO_DATE(‘2013-06-01‘, ‘YYYY-MM-DD‘)), 28 partition p_592_201306 values less than (592,TO_DATE(‘2013-07-01‘, ‘YYYY-MM-DD‘)), 29 partition p_592_201307 values less than (592,TO_DATE(‘2013-08-01‘, ‘YYYY-MM-DD‘)), 30 partition p_592_201308 values less than (592,TO_DATE(‘2013-09-01‘, ‘YYYY-MM-DD‘)), 31 partition p_592_201309 values less than (592,TO_DATE(‘2013-10-01‘, ‘YYYY-MM-DD‘)), 32 partition p_592_201310 values less than (592,TO_DATE(‘2013-11-01‘, ‘YYYY-MM-DD‘)), 33 partition p_592_201311 values less than (592,TO_DATE(‘2013-12-01‘, ‘YYYY-MM-DD‘)), 34 partition p_592_201312 values less than (592,TO_DATE(‘2014-01-01‘, ‘YYYY-MM-DD‘)), 35 partition p_592_201401 values less than (592,TO_DATE(‘2014-02-01‘, ‘YYYY-MM-DD‘)), 36 partition p_592_201402 values less than (592,TO_DATE(‘2014-03-01‘, ‘YYYY-MM-DD‘)), 37 partition p_592_max values less than (592,maxvalue), 38 partition p_593_201301 values less than (593,TO_DATE(‘2013-02-01‘, ‘YYYY-MM-DD‘)), 39 partition p_593_201302 values less than (593,TO_DATE(‘2013-03-01‘, ‘YYYY-MM-DD‘)), 40 partition p_593_201303 values less than (593,TO_DATE(‘2013-04-01‘, ‘YYYY-MM-DD‘)), 41 partition p_593_201304 values less than (593,TO_DATE(‘2013-05-01‘, ‘YYYY-MM-DD‘)), 42 partition p_593_201305 values less than (593,TO_DATE(‘2013-06-01‘, ‘YYYY-MM-DD‘)), 43 partition p_593_201306 values less than (593,TO_DATE(‘2013-07-01‘, ‘YYYY-MM-DD‘)), 44 partition p_593_201307 values less than (593,TO_DATE(‘2013-08-01‘, ‘YYYY-MM-DD‘)), 45 partition p_593_201308 values less than (593,TO_DATE(‘2013-09-01‘, ‘YYYY-MM-DD‘)), 46 partition p_593_201309 values less than (593,TO_DATE(‘2013-10-01‘, ‘YYYY-MM-DD‘)), 47 partition p_593_201310 values less than (593,TO_DATE(‘2013-11-01‘, ‘YYYY-MM-DD‘)), 48 partition p_593_201311 values less than (593,TO_DATE(‘2013-12-01‘, ‘YYYY-MM-DD‘)), 49 partition p_593_201312 values less than (593,TO_DATE(‘2014-01-01‘, ‘YYYY-MM-DD‘)), 50 partition p_593_201401 values less than (593,TO_DATE(‘2014-02-01‘, ‘YYYY-MM-DD‘)), 51 partition p_593_201402 values less than (593,TO_DATE(‘2014-03-01‘, ‘YYYY-MM-DD‘)), 52 partition p_593_max values less than (593,maxvalue) 53 ) 54 ; 55 56 57 58 59 --以下是插入2013年一整年日期随机数和表示福州，厦门，宁德三地的地区号含义（591到593）的随机数记录，共有10万条，如下： 60 insert into range_part_mult_col_tab (id,deal_date,area_code,nbr,contents) 61 select rownum, 62 to_date( to_char(sysdate-365,‘J‘)+TRUNC(DBMS_RANDOM.VALUE(0,365)),‘J‘), 63 ceil(dbms_random.value(591,593)), 64 ceil(dbms_random.value(18900000001,18999999999)), 65 rpad(‘*‘,400,‘*‘) 66 from dual 67 connect by rownum <= 100000; 68 commit; 69 70 71 72 select count(*) from range_part_mult_col_tab partition (p_591_201301); 73 select count(*) from range_part_mult_col_tab partition (p_591_201302); 74 select count(*) from range_part_mult_col_tab partition (p_591_201303); 75 select count(*) from range_part_mult_col_tab partition (p_591_201304); 76 select count(*) from range_part_mult_col_tab partition (p_591_201305); 77 select count(*) from range_part_mult_col_tab partition (p_591_201306); 78 select count(*) from range_part_mult_col_tab partition (p_591_201307); 79 select count(*) from range_part_mult_col_tab partition (p_591_201308); 80 select count(*) from range_part_mult_col_tab partition (p_591_201309); 81 select count(*) from range_part_mult_col_tab partition (p_591_201310); 82 select count(*) from range_part_mult_col_tab partition (p_591_201311); 83 select count(*) from range_part_mult_col_tab partition (p_591_201312); 84 select count(*) from range_part_mult_col_tab partition (p_591_max); 85 select count(*) from range_part_mult_col_tab partition (p_592_201301); 86 select count(*) from range_part_mult_col_tab partition (p_592_201302); 87 select count(*) from range_part_mult_col_tab partition (p_592_201303); 88 select count(*) from range_part_mult_col_tab partition (p_592_201304); 89 select count(*) from range_part_mult_col_tab partition (p_592_201305); 90 select count(*) from range_part_mult_col_tab partition (p_592_201306); 91 select count(*) from range_part_mult_col_tab partition (p_592_201307); 92 select count(*) from range_part_mult_col_tab partition (p_592_201308); 93 select count(*) from range_part_mult_col_tab partition (p_592_201309); 94 select count(*) from range_part_mult_col_tab partition (p_592_201310); 95 select count(*) from range_part_mult_col_tab partition (p_592_201311); 96 select count(*) from range_part_mult_col_tab partition (p_592_201312); 97 select count(*) from range_part_mult_col_tab partition (p_592_max); 98 select count(*) from range_part_mult_col_tab partition (p_593_201301); 99 select count(*) from range_part_mult_col_tab partition (p_593_201302);100 select count(*) from range_part_mult_col_tab partition (p_593_201303);101 select count(*) from range_part_mult_col_tab partition (p_593_201304);102 select count(*) from range_part_mult_col_tab partition (p_593_201305);103 select count(*) from range_part_mult_col_tab partition (p_593_201306);104 select count(*) from range_part_mult_col_tab partition (p_593_201307);105 select count(*) from range_part_mult_col_tab partition (p_593_201308);106 select count(*) from range_part_mult_col_tab partition (p_593_201309);107 select count(*) from range_part_mult_col_tab partition (p_593_201310);108 select count(*) from range_part_mult_col_tab partition (p_593_201311);109 select count(*) from range_part_mult_col_tab partition (p_593_201312);110 select count(*) from range_part_mult_col_tab partition (p_593_max);111 112 113 114 115 create index idx_part_mul_id on range_part_mult_col_tab (id) ;116 create index idx_part_mul_nbr on range_part_mult_col_tab (nbr) local;
组合分区
1 --组合分区示例 2 drop table range_list_part_tab purge; 3 --注意，此分区为范围分区 4 create table range_list_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000)) 5 partition by range (deal_date) 6 subpartition by list (area_code) 7 subpartition TEMPLATE 8 (subpartition p_591 values (591), 9 subpartition p_592 values (592),10 subpartition p_593 values (593),11 subpartition p_594 values (594),12 subpartition p_595 values (595),13 subpartition p_596 values (596),14 subpartition p_597 values (597),15 subpartition p_598 values (598),16 subpartition p_599 values (599),17 subpartition p_other values (DEFAULT))18 ( partition p_201301 values less than (TO_DATE(‘2013-02-01‘, ‘YYYY-MM-DD‘)),19 partition p_201302 values less than (TO_DATE(‘2013-03-01‘, ‘YYYY-MM-DD‘)),20 partition p_201303 values less than (TO_DATE(‘2013-04-01‘, ‘YYYY-MM-DD‘)),21 partition p_201304 values less than (TO_DATE(‘2013-05-01‘, ‘YYYY-MM-DD‘)),22 partition p_201305 values less than (TO_DATE(‘2013-06-01‘, ‘YYYY-MM-DD‘)),23 partition p_201306 values less than (TO_DATE(‘2013-07-01‘, ‘YYYY-MM-DD‘)),24 partition p_201307 values less than (TO_DATE(‘2013-08-01‘, ‘YYYY-MM-DD‘)),25 partition p_201308 values less than (TO_DATE(‘2013-09-01‘, ‘YYYY-MM-DD‘)),26 partition p_201309 values less than (TO_DATE(‘2013-10-01‘, ‘YYYY-MM-DD‘)),27 partition p_201310 values less than (TO_DATE(‘2013-11-01‘, ‘YYYY-MM-DD‘)),28 partition p_201311 values less than (TO_DATE(‘2013-12-01‘, ‘YYYY-MM-DD‘)),29 partition p_201312 values less than (TO_DATE(‘2014-01-01‘, ‘YYYY-MM-DD‘)),30 partition p_201401 values less than (TO_DATE(‘2014-02-01‘, ‘YYYY-MM-DD‘)),31 partition p_201402 values less than (TO_DATE(‘2014-03-01‘, ‘YYYY-MM-DD‘)),32 partition p_max values less than (maxvalue))33 ;34 35 36 37 --以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：38 insert into range_list_part_tab(id,deal_date,area_code,nbr,contents)39 select rownum,40 to_date( to_char(sysdate-365,‘J‘)+TRUNC(DBMS_RANDOM.VALUE(0,365)),‘J‘),41 ceil(dbms_random.value(590,599)),42 ceil(dbms_random.value(18900000001,18999999999)),43 rpad(‘*‘,400,‘*‘)44 from dual45 connect by rownum <= 100000;46 commit;47 48 49 select count(*) from range_list_part_tab partition (p_591);50 select count(*) from range_list_part_tab partition (p_201302);51 select count(*) from range_list_part_tab partition (p_201303);52 select count(*) from range_list_part_tab partition (p_201304);53 select count(*) from range_list_part_tab partition (p_201305);54 select count(*) from range_list_part_tab partition (p_201306);55 select count(*) from range_list_part_tab partition (p_201307);56 select count(*) from range_list_part_tab partition (p_201308);57 select count(*) from range_list_part_tab partition (p_201309);58 select count(*) from range_list_part_tab partition (p_201310);59 select count(*) from range_list_part_tab partition (p_201311);60 select count(*) from range_list_part_tab partition (p_201312);61 select count(*) from range_list_part_tab partition (p_max);62 63 --注意，模板的形式，子分区名是被自动命名了，系统自动组合在一起，如P_201301_P_59164 select count(*) from range_list_part_tab subpartition(P_201301_P_591);65 66 create index idx_ran_list_part_id on range_list_part_tab (id) ;67 create index idx_ran_list_part_nbr on range_list_part_tab (nbr) local;
三、分区表相关信息的查询脚本
该表是否是分区表，分区表的分区类型是什么，是否有子分区，分区总数有多少
SELECT partitioning_type, subpartitioning_type, partition_count FROM user_part_tables WHERE table_name = ‘TABLE‘;
该分区表在哪一列上建分区,有无多列联合建分区
SELECT column_name, object_type, column_positionFROM user_part_key_columnsWHERE NAME = ‘TABLE‘;
该分区表有多大
select sum(bytes) / 1024 / 1024from user_segmentswhere segment_name =‘TABLE‘;
该分区表各分区分别有多大，各个分区名是什么
select partition_name, segment_type, bytes from user_segments where segment_name =‘TABLE‘;
该分区表的统计信息收集情况
select table_name, partition_name, last_analyzed, partition_position, num_rows from user_tab_statistics t where table_name =‘TABLE‘;
分区表索引相关查该分区表有无索引，分别什么类型,全局索引是否失效，此外还可看统计信息收集情况。--(其中status值为N/A 表示分区索引，分区索引是否失效是在user_ind_partitions中查看)
---RANGE_PART_TAB 第一个范围分区测试脚本中直接执行下面脚本即可有相同的结果select table_name, index_name, last_analyzed, blevel, num_rows, leaf_blocks, distinct_keys, status from user_indexes where table_name =‘RANGE_PART_TAB‘; TABLE_NAME INDEX_NAME LAST_ANALYZED BLEVEL NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS STATUS------------------------------ ---------------------------- -------------- ------ --------- ----------- ------------- --------RANGE_PART_TAB IDX_PART_NBR 01-12月-13 1 200000 536 199774 N/ARANGE_PART_TAB IDX_PART_ID 01-12月-13 1 200000 555 100000 VALID--07 该分区表在哪些列上建了索引select index_name, column_name, column_position from user_ind_columns where table_name = ‘RANGE_PART_TAB‘; INDEX_NAME COLUMN_NAME COLUMN_POSITION---------------------------- -------------------- ---------------IDX_PART_ID ID 1IDX_PART_NBR NBR 1--08 该分区表上的各索引分别有多大。 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_TAB‘)group by segment_name,segment_type ; SEGMENT_NAME SEGMENT_TYPE SUM(BYTES)/1024/1024------------------------------------------ --------------------IDX_PART_ID INDEX 5IDX_PART_NBR INDEX PARTITION 5.6875--09 该分区表的索引段的分配情况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_TAB‘);PARTITION_NAME SEGMENT_TYPE BYTES---------------------------- -------------------- ----------IDX_PART_ID INDEX 5242880IDX_PART_NBR INDEX PARTITION 458752IDX_PART_NBR INDEX PARTITION 262144IDX_PART_NBR INDEX PARTITION 262144IDX_PART_NBR INDEX PARTITION 262144IDX_PART_NBR INDEX PARTITION 262144IDX_PART_NBR INDEX PARTITION 262144IDX_PART_NBR INDEX PARTITION 262144IDX_PART_NBR INDEX PARTITION 262144IDX_PART_NBR INDEX PARTITION 262144IDX_PART_NBR INDEX PARTITION 262144IDX_PART_NBR INDEX PARTITION 262144IDX_PART_NBR INDEX PARTITION 262144IDX_PART_NBR INDEX PARTITION 262144IDX_PART_NBR INDEX PARTITION 262144IDX_PART_NBR INDEX PARTITION 2097152已选择16行。 --10 分区索引相关信息及统计信息、是否失效查看。select t2.table_name, t1.index_name, t1.partition_name, t1.last_analyzed, t1.blevel, t1.num_rows, t1.leaf_blocks, t1.status from user_ind_partitions t1, user_indexes t2where t1.index_name = t2.index_name and t2.table_name=‘RANGE_PART_TAB‘; TABLE_NAME INDEX_NAME PARTITION_NAME LAST_ANALYZED BLEVEL NUM_ROWS LEAF_BLOCKS STATUS-------------------------------------------------------------- ------ --------- ----------- -------RANGE_PART_TAB IDX_PART_NBR P_201301 01-12月-13 1 16883 45 USABLERANGE_PART_TAB IDX_PART_NBR P_201302 01-12月-13 1 7876 21 USABLERANGE_PART_TAB IDX_PART_NBR P_201303 01-12月-13 1 8448 23 USABLERANGE_PART_TAB IDX_PART_NBR P_201304 01-12月-13 1 8295 22 USABLERANGE_PART_TAB IDX_PART_NBR P_201305 01-12月-13 1 8388 23 USABLERANGE_PART_TAB IDX_PART_NBR P_201306 01-12月-13 1 8234 22 USABLERANGE_PART_TAB IDX_PART_NBR P_201307 01-12月-13 1 8540 23 USABLERANGE_PART_TAB IDX_PART_NBR P_201308 01-12月-13 1 8312 22 USABLERANGE_PART_TAB IDX_PART_NBR P_201309 01-12月-13 1 8350 23 USABLERANGE_PART_TAB IDX_PART_NBR P_201310 01-12月-13 1 8496 23 USABLERANGE_PART_TAB IDX_PART_NBR P_201311 01-12月-13 1 8178 22 USABLERANGE_PART_TAB IDX_PART_NBR P_201312 01-12月-13 1 8425 23 USABLERANGE_PART_TAB IDX_PART_NBR P_201401 01-12月-13 1 8477 23 USABLERANGE_PART_TAB IDX_PART_NBR P_201402 01-12月-13 1 7628 21 USABLERANGE_PART_TAB IDX_PART_NBR P_MAX 01-12月-13 1 75470 200 USABLE
oracle 优化 —— 分区表
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。