Oracle学习笔记（八）
range单字段分区：
-- 范围分区示例
drop table range_part_tab purge;
--注意，此分区为范围分区
--例子1
create table range_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
partition by range (deal_date)
(
partition p_201301 values less than (TO_DATE('2013-02-01', 'YYYY-MM-DD')),
partition p_201302 values less than (TO_DATE('2013-03-01', 'YYYY-MM-DD')),
partition p_201303 values less than (TO_DATE('2013-04-01', 'YYYY-MM-DD')),
partition p_201304 values less than (TO_DATE('2013-05-01', 'YYYY-MM-DD')),
partition p_201305 values less than (TO_DATE('2013-06-01', 'YYYY-MM-DD')),
partition p_201306 values less than (TO_DATE('2013-07-01', 'YYYY-MM-DD')),
partition p_201307 values less than (TO_DATE('2013-08-01', 'YYYY-MM-DD')),
partition p_201308 values less than (TO_DATE('2013-09-01', 'YYYY-MM-DD')),
partition p_201309 values less than (TO_DATE('2013-10-01', 'YYYY-MM-DD')),
partition p_201310 values less than (TO_DATE('2013-11-01', 'YYYY-MM-DD')),
partition p_201311 values less than (TO_DATE('2013-12-01', 'YYYY-MM-DD')),
partition p_201312 values less than (TO_DATE('2014-01-01', 'YYYY-MM-DD')),
partition p_201401 values less than (TO_DATE('2014-02-01', 'YYYY-MM-DD')),
partition p_201402 values less than (TO_DATE('2014-03-01', 'YYYY-MM-DD')),
partition p_max values less than (maxvalue)
)
;
--以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into range_part_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,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
--以下是插入2014年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into range_part_tab (id,deal_date,area_code,nbr,contents)
select rownum,
to_date( to_char(sysdate,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
ceil(dbms_random.value(591,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
--全局索引
create index idx_part_id on range_part_tab (id) ;
--局部索引
create index idx_part_nbr on range_part_tab (nbr) local;
--统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'RANGE_PART_TAB',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
select min(deal_date),max(deal_date) from range_part_tab;
select count(*) from range_part_tab partition (p_201301);
select count(*) from range_part_tab partition (p_201302);
select count(*) from range_part_tab partition (p_201303);
select count(*) from range_part_tab partition (p_201304);
select count(*) from range_part_tab partition (p_201305);
select count(*) from range_part_tab partition (p_201306);
select count(*) from range_part_tab partition (p_201307);
select count(*) from range_part_tab partition (p_201308);
select count(*) from range_part_tab partition (p_201309);
select count(*) from range_part_tab partition (p_201310);
select count(*) from range_part_tab partition (p_201311);
select count(*) from range_part_tab partition (p_201312);
select count(*) from range_part_tab partition (p_max);
--查看当前用户下分区表及对应索引的相关信息
--（表及索引多大，多少个分区，在哪一个列建了分区，表及索引的分区的类型是什么，有无子分区）
set linesize 366
col partitioning_type format a10
col subpartitioning_type format a10
col partition_count format 99999
col column_name format a20
col object_type format a10
col blevel format 9
col num_rows format 99999999
col leaf_blocks format 999999
col column_position format 9
col partition_name format a28
col segment_type format a20
col index_name format a28
col table_name format a30
col last_analyzed format date
col status format a8
---01 该表是否是分区表，分区表的分区类型是什么，是否有子分区，分区总数有多少
select partitioning_type,
subpartitioning_type,
partition_count
from user_part_tables
where table_name ='RANGE_PART_TAB';
PARTITIONI SUBPARTITI PARTITION_COUNT
---------- ---------- ---------------
RANGE NONE 15
--02 该分区表在哪一列上建分区,有无多列联合建分区
select column_name,
object_type,
column_position
from user_part_key_columns
where name ='RANGE_PART_TAB';
COLUMN_NAME OBJECT_TYP COLUMN_POSITION
-------------------- ---------- ---------------
DEAL_DATE TABLE 1
--03 该分区表有多大？
select sum(bytes) / 1024 / 1024
from user_segments
where segment_name ='RANGE_PART_TAB';
SUM(BYTES)/1024/1024
--------------------
110
--04 该分区表各分区分别有多大，各个分区名是什么。
select partition_name,
segment_type,
bytes
from user_segments
where segment_name ='RANGE_PART_TAB';
PARTITION_NAME SEGMENT_TYPE BYTES
---------------------------- -------------------- ----------
P_201301 TABLE PARTITION 9437184
P_201302 TABLE PARTITION 4194304
P_201303 TABLE PARTITION 5242880
P_201304 TABLE PARTITION 5242880
P_201305 TABLE PARTITION 5242880
P_201306 TABLE PARTITION 5242880
P_201307 TABLE PARTITION 5242880
P_201308 TABLE PARTITION 5242880
P_201309 TABLE PARTITION 5242880
P_201310 TABLE PARTITION 5242880
P_201311 TABLE PARTITION 5242880
P_201312 TABLE PARTITION 5242880
P_201401 TABLE PARTITION 5242880
P_201402 TABLE PARTITION 4194304
P_MAX TABLE PARTITION 39845888
已选择15行。
--05 该分区表的统计信息收集情况
select table_name,
partition_name,
last_analyzed,
partition_position,
num_rows
from user_tab_statistics t
where table_name ='RANGE_PART_TAB';
TABLE_NAME PARTITION_NAME LAST_ANALYZED PARTITION_POSITION NUM_ROWS
------------------------------ ---------------------------- -------------- ------------------ ---------
RANGE_PART_TAB 01-12月-13 200080
RANGE_PART_TAB P_201301 01-12月-13 1 17010
RANGE_PART_TAB P_201302 01-12月-13 2 7855
RANGE_PART_TAB P_201303 01-12月-13 3 8378
RANGE_PART_TAB P_201304 01-12月-13 4 8132
RANGE_PART_TAB P_201305 01-12月-13 5 8430
RANGE_PART_TAB P_201306 01-12月-13 6 8317
RANGE_PART_TAB P_201307 01-12月-13 7 8571
RANGE_PART_TAB P_201308 01-12月-13 8 8312
RANGE_PART_TAB P_201309 01-12月-13 9 8327
RANGE_PART_TAB P_201310 01-12月-13 10 8469
RANGE_PART_TAB P_201311 01-12月-13 11 8218
RANGE_PART_TAB P_201312 01-12月-13 12 8387
RANGE_PART_TAB P_201401 01-12月-13 13 8434
RANGE_PART_TAB P_201402 01-12月-13 14 7663
RANGE_PART_TAB P_MAX 01-12月-13 15 76150
--06 查该分区表有无索引，分别什么类型,全局索引是否失效，此外还可看统计信息收集情况。
--(其中status值为N/A 表示分区索引，分区索引是否失效是在user_ind_partitions中查看)
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/A
RANGE_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 1
IDX_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 5
IDX_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 5242880
IDX_PART_NBR INDEX PARTITION 458752
IDX_PART_NBR INDEX PARTITION 262144
IDX_PART_NBR INDEX PARTITION 262144
IDX_PART_NBR INDEX PARTITION 262144
IDX_PART_NBR INDEX PARTITION 262144
IDX_PART_NBR INDEX PARTITION 262144
IDX_PART_NBR INDEX PARTITION 262144
IDX_PART_NBR INDEX PARTITION 262144
IDX_PART_NBR INDEX PARTITION 262144
IDX_PART_NBR INDEX PARTITION 262144
IDX_PART_NBR INDEX PARTITION 262144
IDX_PART_NBR INDEX PARTITION 262144
IDX_PART_NBR INDEX PARTITION 262144
IDX_PART_NBR INDEX PARTITION 262144
IDX_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 t2
where 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 USABLE
RANGE_PART_TAB IDX_PART_NBR P_201302 01-12月-13 1 7876 21 USABLE
RANGE_PART_TAB IDX_PART_NBR P_201303 01-12月-13 1 8448 23 USABLE
RANGE_PART_TAB IDX_PART_NBR P_201304 01-12月-13 1 8295 22 USABLE
RANGE_PART_TAB IDX_PART_NBR P_201305 01-12月-13 1 8388 23 USABLE
RANGE_PART_TAB IDX_PART_NBR P_201306 01-12月-13 1 8234 22 USABLE
RANGE_PART_TAB IDX_PART_NBR P_201307 01-12月-13 1 8540 23 USABLE
RANGE_PART_TAB IDX_PART_NBR P_201308 01-12月-13 1 8312 22 USABLE
RANGE_PART_TAB IDX_PART_NBR P_201309 01-12月-13 1 8350 23 USABLE
RANGE_PART_TAB IDX_PART_NBR P_201310 01-12月-13 1 8496 23 USABLE
RANGE_PART_TAB IDX_PART_NBR P_201311 01-12月-13 1 8178 22 USABLE
RANGE_PART_TAB IDX_PART_NBR P_201312 01-12月-13 1 8425 23 USABLE
RANGE_PART_TAB IDX_PART_NBR P_201401 01-12月-13 1 8477 23 USABLE
RANGE_PART_TAB IDX_PART_NBR P_201402 01-12月-13 1 7628 21 USABLE
RANGE_PART_TAB IDX_PART_NBR P_MAX 01-12月-13 1 75470 200 USABLE
联合字段分区：
-- 范围分区示例
drop table range_part_mult_col_tab purge;
--注意，此分区为联合字段的范围分区
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),
partition p_593_201301 values less than (593,TO_DATE('2013-02-01', 'YYYY-MM-DD')),
partition p_593_201302 values less than (593,TO_DATE('2013-03-01', 'YYYY-MM-DD')),
partition p_593_201303 values less than (593,TO_DATE('2013-04-01', 'YYYY-MM-DD')),
partition p_593_201304 values less than (593,TO_DATE('2013-05-01', 'YYYY-MM-DD')),
partition p_593_201305 values less than (593,TO_DATE('2013-06-01', 'YYYY-MM-DD')),
partition p_593_201306 values less than (593,TO_DATE('2013-07-01', 'YYYY-MM-DD')),
partition p_593_201307 values less than (593,TO_DATE('2013-08-01', 'YYYY-MM-DD')),
partition p_593_201308 values less than (593,TO_DATE('2013-09-01', 'YYYY-MM-DD')),
partition p_593_201309 values less than (593,TO_DATE('2013-10-01', 'YYYY-MM-DD')),
partition p_593_201310 values less than (593,TO_DATE('2013-11-01', 'YYYY-MM-DD')),
partition p_593_201311 values less than (593,TO_DATE('2013-12-01', 'YYYY-MM-DD')),
partition p_593_201312 values less than (593,TO_DATE('2014-01-01', 'YYYY-MM-DD')),
partition p_593_201401 values less than (593,TO_DATE('2014-02-01', 'YYYY-MM-DD')),
partition p_593_201402 values less than (593,TO_DATE('2014-03-01', 'YYYY-MM-DD')),
partition p_593_max values less than (593,maxvalue)
)
;
--以下是插入2013年一整年日期随机数和表示福州，厦门，宁德三地的地区号含义（591到593）的随机数记录，共有10万条，如下：
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 count(*) from range_part_mult_col_tab partition (p_591_201301);
select count(*) from range_part_mult_col_tab partition (p_591_201302);
select count(*) from range_part_mult_col_tab partition (p_591_201303);
select count(*) from range_part_mult_col_tab partition (p_591_201304);
select count(*) from range_part_mult_col_tab partition (p_591_201305);
select count(*) from range_part_mult_col_tab partition (p_591_201306);
select count(*) from range_part_mult_col_tab partition (p_591_201307);
select count(*) from range_part_mult_col_tab partition (p_591_201308);
select count(*) from range_part_mult_col_tab partition (p_591_201309);
select count(*) from range_part_mult_col_tab partition (p_591_201310);
select count(*) from range_part_mult_col_tab partition (p_591_201311);
select count(*) from range_part_mult_col_tab partition (p_591_201312);
select count(*) from range_part_mult_col_tab partition (p_591_max);
select count(*) from range_part_mult_col_tab partition (p_592_201301);
select count(*) from range_part_mult_col_tab partition (p_592_201302);
select count(*) from range_part_mult_col_tab partition (p_592_201303);
select count(*) from range_part_mult_col_tab partition (p_592_201304);
select count(*) from range_part_mult_col_tab partition (p_592_201305);
select count(*) from range_part_mult_col_tab partition (p_592_201306);
select count(*) from range_part_mult_col_tab partition (p_592_201307);
select count(*) from range_part_mult_col_tab partition (p_592_201308);
select count(*) from range_part_mult_col_tab partition (p_592_201309);
select count(*) from range_part_mult_col_tab partition (p_592_201310);
select count(*) from range_part_mult_col_tab partition (p_592_201311);
select count(*) from range_part_mult_col_tab partition (p_592_201312);
select count(*) from range_part_mult_col_tab partition (p_592_max);
select count(*) from range_part_mult_col_tab partition (p_593_201301);
select count(*) from range_part_mult_col_tab partition (p_593_201302);
select count(*) from range_part_mult_col_tab partition (p_593_201303);
select count(*) from range_part_mult_col_tab partition (p_593_201304);
select count(*) from range_part_mult_col_tab partition (p_593_201305);
select count(*) from range_part_mult_col_tab partition (p_593_201306);
select count(*) from range_part_mult_col_tab partition (p_593_201307);
select count(*) from range_part_mult_col_tab partition (p_593_201308);
select count(*) from range_part_mult_col_tab partition (p_593_201309);
select count(*) from range_part_mult_col_tab partition (p_593_201310);
select count(*) from range_part_mult_col_tab partition (p_593_201311);
select count(*) from range_part_mult_col_tab partition (p_593_201312);
select count(*) from range_part_mult_col_tab partition (p_593_max);
create index idx_part_mul_id on range_part_mult_col_tab (id) ;
create index idx_part_mul_nbr on range_part_mult_col_tab (nbr) local;
--统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'RANGE_PART_MULT_COL_TAB',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
--查看当前用户下分区表及对应索引的相关信息
--（表及索引多大，多少个分区，在哪一个列建了分区，表及索引的分区的类型是什么，有无子分区）
set linesize 366
col partitioning_type format a10
col subpartitioning_type format a10
col partition_count format 99999
col column_name format a20
col object_type format a10
col blevel format 9
col num_rows format 99999999
col leaf_blocks format 999999
col column_position format 9
col partition_name format a28
col segment_type format a20
col index_name format a28
col table_name format a30
col last_analyzed format date
col status format a8
---该表是否是分区表，分区表的分区类型是什么，是否有子分区，分区总数有多少
select partitioning_type,
subpartitioning_type,
partition_count
from user_part_tables
where table_name ='RANGE_PART_MULT_COL_TAB';
--该分区表在哪一列上建分区,有无多列联合建分区
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';
--查该分区表有无索引，分别什么类型,全局索引是否失效，此外还可看统计信息收集情况。
--(其中status值为N/A 表示分区索引，分区索引是否失效是在user_ind_partitions中查看)
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');
--分区索引相关信息及统计信息、是否失效查看。
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 t2
where t1.index_name = t2.index_name
and t2.table_name='RANGE_PART_MULT_COL_TAB';
列表分区示例：
--列表分区示例
drop table list_part_tab purge;
--注意，此分区为列表分区
create table list_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
partition by list (area_code)
(
partition p_591 values (591),
partition p_592 values (592),
partition p_593 values (593),
partition p_594 values (594),
partition p_595 values (595),
partition p_596 values (596),
partition p_597 values (597),
partition p_598 values (598),
partition p_599 values (599),
partition p_other values (DEFAULT)
)
;
--以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into list_part_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(590,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
select count(*) from list_part_tab partition(p_591);
select count(*) from list_part_tab partition(p_592);
select count(*) from list_part_tab partition(p_593);
select count(*) from list_part_tab partition(p_594);
select count(*) from list_part_tab partition(p_595);
select count(*) from list_part_tab partition(p_596);
select count(*) from list_part_tab partition(p_597);
select count(*) from list_part_tab partition(p_598);
select count(*) from list_part_tab partition(p_599);
select count(*) from list_part_tab partition(p_other);
create index idx_list_part_id on list_part_tab (id) ;
create index idx_list_part_nbr on list_part_tab (nbr) local;
--统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'LIST_PART_TAB',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
--查看当前用户下分区表及对应索引的相关信息
--（表及索引多大，多少个分区，在哪一个列建了分区，表及索引的分区的类型是什么，有无子分区）
set linesize 366
col partitioning_type format a10
col subpartitioning_type format a10
col partition_count format 99999
col column_name format a20
col object_type format a10
col blevel format 9
col num_rows format 99999999
col leaf_blocks format 999999
col column_position format 9
col partition_name format a28
col segment_type format a20
col index_name format a28
col table_name format a30
col last_analyzed format date
col status format a8
---该表是否是分区表，分区表的分区类型是什么，是否有子分区，分区总数有多少
select partitioning_type,
subpartitioning_type,
partition_count
from user_part_tables
where table_name ='LIST_PART_TAB';
--该分区表在哪一列上建分区,有无多列联合建分区
select column_name,
object_type,
column_position
from user_part_key_columns
where name ='LIST_PART_TAB';
--该分区表有多大？
select sum(bytes) / 1024 / 1024
from user_segments
where segment_name ='LIST_PART_TAB';
--该分区表各分区分别有多大，各个分区名是什么。
select partition_name,
segment_type,
bytes
from user_segments
where segment_name ='LIST_PART_TAB';
--该分区表的统计信息收集情况
select table_name,
partition_name,
last_analyzed,
partition_position,
num_rows
from user_tab_statistics t
where table_name ='LIST_PART_TAB';
--查该分区表有无索引，分别什么类型,全局索引是否失效，此外还可看统计信息收集情况。
--(其中status值为N/A 表示分区索引，分区索引是否失效是在user_ind_partitions中查看)
select table_name,
index_name,
last_analyzed,
blevel,
num_rows,
leaf_blocks,
distinct_keys,
status
from user_indexes
where table_name ='LIST_PART_TAB';
--该分区表在哪些列上建了索引
select index_name,
column_name,
column_position
from user_ind_columns
where table_name = 'LIST_PART_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 ='LIST_PART_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 ='LIST_PART_TAB');
--分区索引相关信息及统计信息、是否失效查看。
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 t2
where t1.index_name = t2.index_name
and t2.table_name='LIST_PART_TAB';
hash单字段分区：
--散列分区示例
drop table hash_part_tab purge;
--注意，此分区HASH分区
create table hash_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
partition by hash (deal_date)
PARTITIONS 12
;
--以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into hash_part_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(590,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
--以下分区名是通过数据字典user_segments的partition_name查出来的，详见后面说明。
select partition_name,
segment_type,
bytes,
'select count(*) from hash_part_tab partition('||partition_name||');'
from user_segments
where segment_name ='HASH_PART_TAB';
create index idx_hash_part_id on hash_part_tab (id) ;
create index idx_hash_part_nbr on hash_part_tab (nbr) local;
--统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'HASH_PART_TAB',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
--查看当前用户下分区表及对应索引的相关信息
--（表及索引多大，多少个分区，在哪一个列建了分区，表及索引的分区的类型是什么，有无子分区）
set linesize 366
col partitioning_type format a10
col subpartitioning_type format a10
col partition_count format 99999
col column_name format a20
col object_type format a10
col blevel format 9
col num_rows format 99999999
col leaf_blocks format 999999
col column_position format 9
col partition_name format a28
col segment_type format a20
col index_name format a28
col table_name format a30
col last_analyzed format date
col status format a8
---该表是否是分区表，分区表的分区类型是什么，是否有子分区，分区总数有多少
select partitioning_type,
subpartitioning_type,
partition_count
from user_part_tables
where table_name ='HASH_PART_TAB';
--该分区表在哪一列上建分区,有无多列联合建分区
select column_name,
object_type,
column_position
from user_part_key_columns
where name ='HASH_PART_TAB';
--该分区表有多大？
select sum(bytes) / 1024 / 1024
from user_segments
where segment_name ='HASH_PART_TAB';
--该分区表各分区分别有多大，各个分区名是什么。
select partition_name,
segment_type,
bytes
from user_segments
where segment_name ='HASH_PART_TAB';
--该分区表的统计信息收集情况
select table_name,
partition_name,
last_analyzed,
partition_position,
num_rows
from user_tab_statistics t
where table_name ='HASH_PART_TAB';
--查该分区表有无索引，分别什么类型,全局索引是否失效，此外还可看统计信息收集情况。
--(其中status值为N/A 表示分区索引，分区索引是否失效是在user_ind_partitions中查看)
select table_name,
index_name,
last_analyzed,
blevel,
num_rows,
leaf_blocks,
distinct_keys,
status
from user_indexes
where table_name ='HASH_PART_TAB';
--该分区表在哪些列上建了索引
select index_name,
column_name,
column_position
from user_ind_columns
where table_name = 'HASH_PART_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 ='HASH_PART_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 ='HASH_PART_TAB');
--分区索引相关信息及统计信息、是否失效查看。
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 t2
where t1.index_name = t2.index_name
and t2.table_name='HASH_PART_TAB';
组合分区：
--组合分区示例
drop table range_list_part_tab purge;
--注意，此分区为范围分区
create table range_list_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
partition by range (deal_date)
subpartition by list (area_code)
subpartition TEMPLATE
(subpartition p_591 values (591),
subpartition p_592 values (592),
subpartition p_593 values (593),
subpartition p_594 values (594),
subpartition p_595 values (595),
subpartition p_596 values (596),
subpartition p_597 values (597),
subpartition p_598 values (598),
subpartition p_599 values (599),
subpartition p_other values (DEFAULT))
( partition p_201301 values less than (TO_DATE('2013-02-01', 'YYYY-MM-DD')),
partition p_201302 values less than (TO_DATE('2013-03-01', 'YYYY-MM-DD')),
partition p_201303 values less than (TO_DATE('2013-04-01', 'YYYY-MM-DD')),
partition p_201304 values less than (TO_DATE('2013-05-01', 'YYYY-MM-DD')),
partition p_201305 values less than (TO_DATE('2013-06-01', 'YYYY-MM-DD')),
partition p_201306 values less than (TO_DATE('2013-07-01', 'YYYY-MM-DD')),
partition p_201307 values less than (TO_DATE('2013-08-01', 'YYYY-MM-DD')),
partition p_201308 values less than (TO_DATE('2013-09-01', 'YYYY-MM-DD')),
partition p_201309 values less than (TO_DATE('2013-10-01', 'YYYY-MM-DD')),
partition p_201310 values less than (TO_DATE('2013-11-01', 'YYYY-MM-DD')),
partition p_201311 values less than (TO_DATE('2013-12-01', 'YYYY-MM-DD')),
partition p_201312 values less than (TO_DATE('2014-01-01', 'YYYY-MM-DD')),
partition p_201401 values less than (TO_DATE('2014-02-01', 'YYYY-MM-DD')),
partition p_201402 values less than (TO_DATE('2014-03-01', 'YYYY-MM-DD')),
partition p_max values less than (maxvalue))
;
--以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into range_list_part_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(590,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
select count(*) from range_list_part_tab partition (p_591);
select count(*) from range_list_part_tab partition (p_201302);
select count(*) from range_list_part_tab partition (p_201303);
select count(*) from range_list_part_tab partition (p_201304);
select count(*) from range_list_part_tab partition (p_201305);
select count(*) from range_list_part_tab partition (p_201306);
select count(*) from range_list_part_tab partition (p_201307);
select count(*) from range_list_part_tab partition (p_201308);
select count(*) from range_list_part_tab partition (p_201309);
select count(*) from range_list_part_tab partition (p_201310);
select count(*) from range_list_part_tab partition (p_201311);
select count(*) from range_list_part_tab partition (p_201312);
select count(*) from range_list_part_tab partition (p_max);
--注意，模板的形式，子分区名是被自动命名了，系统自动组合在一起，如P_201301_P_591
select count(*) from range_list_part_tab subpartition(P_201301_P_591);
create index idx_ran_list_part_id on range_list_part_tab (id) ;
create index idx_ran_list_part_nbr on range_list_part_tab (nbr) local;
--统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'RANGE_LIST_PART_TAB',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
--查看当前用户下分区表及对应索引的相关信息
--（表及索引多大，多少个分区，在哪一个列建了分区，表及索引的分区的类型是什么，有无子分区）
set linesize 366
col partitioning_type format a10
col subpartitioning_type format a10
col partition_count format 99999
col column_name format a20
col object_type format a10
col blevel format 9
col num_rows format 99999999
col leaf_blocks format 999999
col column_position format 9
col partition_name format a28
col segment_type format a20
col index_name format a28
col table_name format a30
col last_analyzed format date
col status format a8
---该表是否是分区表，分区表的分区类型是什么，是否有子分区，分区总数有多少
select partitioning_type,
subpartitioning_type,
partition_count
from user_part_tables
where table_name ='RANGE_LIST_PART_TAB';
--该分区表在哪一列上建分区,有无多列联合建分区
select column_name,
object_type,
column_position
from user_part_key_columns
where name ='RANGE_LIST_PART_TAB';
--该分区表有多大？
select sum(bytes) / 1024 / 1024
from user_segments
where segment_name ='RANGE_LIST_PART_TAB';
--该分区表各分区分别有多大，各个分区名是什么。
select partition_name,
segment_type,
bytes
from user_segments
where segment_name ='RANGE_LIST_PART_TAB';
--该分区表的统计信息收集情况
select table_name,
partition_name,
last_analyzed,
partition_position,
num_rows
from user_tab_statistics t
where table_name ='RANGE_LIST_PART_TAB';
--查该分区表有无索引，分别什么类型,全局索引是否失效，此外还可看统计信息收集情况。
--(其中status值为N/A 表示分区索引，分区索引是否失效是在user_ind_partitions中查看)
select table_name,
index_name,
last_analyzed,
blevel,
num_rows,
leaf_blocks,
distinct_keys,
status
from user_indexes
where table_name ='RANGE_LIST_PART_TAB';
--该分区表在哪些列上建了索引
select index_name,
column_name,
column_position
from user_ind_columns
where table_name = 'RANGE_LIST_PART_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_LIST_PART_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_LIST_PART_TAB');
--分区索引相关信息及统计信息、是否失效查看。
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 t2
where t1.index_name = t2.index_name
and t2.table_name='RANGE_LIST_PART_TAB';
分区表的优势：减少访问路径.
--分区原理分析之普通表构造与数据插入
drop table norm_tab purge;
create table norm_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000));
insert into norm_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(590,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
--分区原理分析之分区表构造与数据插入
drop table range_part_tab purge;
create table range_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
partition by range (deal_date)
(
partition p_201301 values less than (TO_DATE('2013-02-01', 'YYYY-MM-DD')),
partition p_201302 values less than (TO_DATE('2013-03-01', 'YYYY-MM-DD')),
partition p_201303 values less than (TO_DATE('2013-04-01', 'YYYY-MM-DD')),
partition p_201304 values less than (TO_DATE('2013-05-01', 'YYYY-MM-DD')),
partition p_201305 values less than (TO_DATE('2013-06-01', 'YYYY-MM-DD')),
partition p_201306 values less than (TO_DATE('2013-07-01', 'YYYY-MM-DD')),
partition p_201307 values less than (TO_DATE('2013-08-01', 'YYYY-MM-DD')),
partition p_201308 values less than (TO_DATE('2013-09-01', 'YYYY-MM-DD')),
partition p_201309 values less than (TO_DATE('2013-10-01', 'YYYY-MM-DD')),
partition p_201310 values less than (TO_DATE('2013-11-01', 'YYYY-MM-DD')),
partition p_201311 values less than (TO_DATE('2013-12-01', 'YYYY-MM-DD')),
partition p_201312 values less than (TO_DATE('2014-01-01', 'YYYY-MM-DD')),
partition p_201401 values less than (TO_DATE('2014-02-01', 'YYYY-MM-DD')),
partition p_201402 values less than (TO_DATE('2014-03-01', 'YYYY-MM-DD')),
partition p_max values less than (maxvalue)
)
;
--以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into range_part_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,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
--分区原理分析之普通表与分区表在段分配上的差异
SET LINESIZE 666
set pagesize 5000
column segment_name format a20
column partition_name format a20
column segment_type format a20
select segment_name,
partition_name,
segment_type,
bytes / 1024 / 1024 "字节数(M)",
tablespace_name
from user_segments
where segment_name IN('RANGE_PART_TAB','NORM_TAB');
--观察范围分区表的分区消除带来的性能优势
set linesize 1000
set autotrace traceonly
set timing on
select *
from range_part_tab
where deal_date >= TO_DATE('2013-08-04', 'YYYY-MM-DD')
and deal_date <= TO_DATE('2013-08-07', 'YYYY-MM-DD');
执行计划
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 958 | 1917K| 171 (1)| 00:00:03 | | |
| 1 | PARTITION RANGE SINGLE| | 958 | 1917K| 171 (1)| 00:00:03 | 8 | 8 |
|* 2 | TABLE ACCESS FULL | RANGE_PART_TAB | 958 | 1917K| 171 (1)| 00:00:03 | 8 | 8 |
---------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
657 consistent gets
0 physical reads
0 redo size
42053 bytes sent via SQL*Net to client
1240 bytes received via SQL*Net from client
77 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1136 rows processed
--比较相同语句，普通表无法用到DEAL_DATE条件进行分区消除的情况
select *
from norm_tab
where deal_date >= TO_DATE('2013-08-04', 'YYYY-MM-DD')
and deal_date <= TO_DATE('2013-08-07', 'YYYY-MM-DD');
执行计划
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 599 | 1199K| 1709 (1)| 00:00:21 |
|* 1 | TABLE ACCESS FULL| NORM_TAB | 599 | 1199K| 1709 (1)| 00:00:21 |
------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
6373 consistent gets
0 physical reads
0 redo size
42222 bytes sent via SQL*Net to client
1240 bytes received via SQL*Net from client
77 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1137 rows processed
操作方便（truncate）：
--操作会导致全局索引失效（要考虑including indexes 或者重建索引）
---关于分区表的truncate分区
--分区表的truncate
connect ljb/ljb
drop table part_tab_trunc purge;
create table part_tab_trunc (id int,col2 int,col3 int,contents varchar2(4000))
partition by range (id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (maxvalue)
)
;
insert into part_tab_trunc select rownum ,rownum+1,rownum+2, rpad('*',400,'*') from dual connect by rownum <=50000;
commit;
--truncate 分区前
select count(*) from part_tab_trunc partition(p1);
COUNT(*)
---------
9999
alter table part_tab_trunc truncate partition p1 ;
--truncate 分区后
select count(*) from part_tab_trunc partition(p1);
COUNT(*)
---------
0
操作方便（drop）：
--操作会导致全局索引失效（要考虑including indexes 或者重建索引），
--分区表的drop
sqlplus ljb/ljb
drop table part_tab_drop purge;
create table part_tab_drop (id int,col2 int ,col3 int,contents varchar2(4000))
partition by range (id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (maxvalue)
)
;
insert into part_tab_drop select rownum ,rownum+1,rownum+2,rpad('*',400,'*') from dual connect by rownum <=50000;
commit;
--drop 分区前
select partition_name, segment_type, bytes
from user_segments
where segment_name ='PART_TAB_DROP';
PARTITION_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
P1 TABLE PARTITION 5242880
P2 TABLE PARTITION 5242880
P3 TABLE PARTITION 15728640
alter table part_tab_drop drop partition p1 ;
--drop 分区后
select partition_name, segment_type, bytes
from user_segments
where segment_name ='PART_TAB_DROP';
PARTITION_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
P2 TABLE PARTITION 5242880
P3 TABLE PARTITION 15728640
操作方便（split）
---分区表的SPLIT
drop table part_tab_split purge;
create table part_tab_split (id int,col2 int ,col3 int ,contents varchar2(4000))
partition by range (id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p_max values less than (maxvalue)
)
;
insert into part_tab_split select rownum ,rownum+1,rownum+2,rpad('*',400,'*') from dual connect by rownum <=90000;
commit;
--split 分区前
select partition_name, segment_type, bytes
from user_segments
where segment_name ='PART_TAB_SPLIT';
PARTITION_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
P1 TABLE PARTITION 5242880
P2 TABLE PARTITION 5242880
P_MAX TABLE PARTITION 34603008
已选择3行。
alter table part_tab_split SPLIT PARTITION P_MAX at (30000) into (PARTITION p3 ,PARTITION P_MAX);
alter table part_tab_split SPLIT PARTITION P_MAX at (40000) into (PARTITION p4 ,PARTITION P_MAX);
alter table part_tab_split SPLIT PARTITION P_MAX at (50000) into (PARTITION p5 ,PARTITION P_MAX);
alter table part_tab_split SPLIT PARTITION P_MAX at (60000) into (PARTITION p6 ,PARTITION P_MAX);
alter table part_tab_split SPLIT PARTITION P_MAX at (70000) into (PARTITION p7 ,PARTITION P_MAX);
--split 分区后
select partition_name, segment_type, bytes
from user_segments
where segment_name ='PART_TAB_SPLIT';
PARTITION_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
P1 TABLE PARTITION 5242880
P2 TABLE PARTITION 5242880
P3 TABLE PARTITION 5242880
P4 TABLE PARTITION 5242880
P5 TABLE PARTITION 5242880
P6 TABLE PARTITION 5242880
P7 TABLE PARTITION 5242880
P_MAX TABLE PARTITION 10485760
已选择8行。
操作方便add：
drop table part_tab_add purge;
create table part_tab_add (id int,col2 int,col3 int,contents varchar2(4000))
partition by range (id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (30000),
partition p4 values less than (40000),
partition p5 values less than (50000),
partition p_max values less than (maxvalue)
)
;
insert into part_tab_add select rownum ,rownum+1,rownum+2,rpad('*',400,'*') from dual connect by rownum <=45000;
commit;
--add 分区前
select partition_name, segment_type, bytes
from user_segments
where segment_name ='PART_TAB_ADD';
PARTITION_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
P1 TABLE PARTITION 5242880
P2 TABLE PARTITION 5242880
P3 TABLE PARTITION 5242880
P4 TABLE PARTITION 5242880
P5 TABLE PARTITION 3145728
P_MAX TABLE PARTITION 65536
已选择6行。
--注意：必须要把默认分区去掉，再add分区，再增加默认分区,这里要担心丢数据!
alter table part_tab_add drop partition p_max;
alter table part_tab_add add PARTITION p6 values less than (60000);
alter table part_tab_add add PARTITION p_max values less than (maxvalue);
--add 分区后
select partition_name, segment_type, bytes
from user_segments
where segment_name ='PART_TAB_ADD';
PARTITION_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
P1 TABLE PARTITION 5242880
P2 TABLE PARTITION 5242880
P3 TABLE PARTITION 5242880
P4 TABLE PARTITION 5242880
P5 TABLE PARTITION 3145728
P6 TABLE PARTITION 65536
P_MAX TABLE PARTITION 65536
已选择7行。
操作方便（exchange）：
--操作会导致全局索引失效（要考虑including indexes 或者重建索引），
--分区表的exchange
drop table part_tab_exch purge;
create table part_tab_exch (id int,col2 int,col3 int,contents varchar2(4000))
partition by range (id)
(
partition p1 values less than (10000),
partition p2 values less than (20000),
partition p3 values less than (30000),
partition p4 values less than (40000),
partition p5 values less than (50000),
partition p_max values less than (maxvalue)
)
;
insert into part_tab_exch select rownum ,rownum+1,rownum+2, rpad('*',400,'*') from dual connect by rownum <=60000;
commit;
create index idx_part_exch_col2 on part_tab_exch(col2) local;
create index idx_part_exch_col3 on part_tab_exch (col3);
--分区表的EXCHANGE（某分区和普通表之间的数据进行交换）
drop table normal_tab purge;
create table normal_tab (id int,col2 int,col3 int,contents varchar2(4000));
create index idx_norm_col2 on normal_tab (col2);
--交换前数据情况
select count(*) from normal_tab;
COUNT(*)
----------
0
select count(*) from part_tab partition(p1);
COUNT(*)
----------
999
--其中including indexes 可选，为了保证全局索引不要失效
alter table part_tab_exch exchange partition p1 with table normal_tab including indexes update global indexes;
--交换后数据情况
select count(*) from normal_tab;
COUNT(*)
----------
999
select count(*) from part_tab_exch partition(p1);
COUNT(*)
----------
0
---分区表的分区数据转移后会更改ROWID
set autotrace off
drop table part_tab_rowid purge;
create table part_tab_rowid (id int,col2 int,col3 int,contents varchar2(4000))
partition by range (id)
(
partition p1 values less than (10),
partition p2 values less than (20),
partition p_max values less than (maxvalue)
)
;
insert into part_tab_rowid select rownum ,rownum+1,rownum+2,rpad('*',400,'*') from dual connect by rownum <=30;
commit;
select t.id,t.rowid from part_tab_rowid partition (p1) t;
ID ROWID
---------- ------------------
1 AAAZdQAAGAAATxjAAA
2 AAAZdQAAGAAATxjAAB
3 AAAZdQAAGAAATxjAAC
4 AAAZdQAAGAAATxjAAD
5 AAAZdQAAGAAATxjAAE
6 AAAZdQAAGAAATxjAAF
7 AAAZdQAAGAAATxjAAG
8 AAAZdQAAGAAATxjAAH
9 AAAZdQAAGAAATxjAAI
已选择9行。
select t.id,t.rowid from part_tab_rowid partition (p2) t;
ID ROWID
---------- ------------------
10 AAAZdRAAGAAATxrAAA
11 AAAZdRAAGAAATxrAAB
12 AAAZdRAAGAAATxrAAC
13 AAAZdRAAGAAATxrAAD
14 AAAZdRAAGAAATxrAAE
15 AAAZdRAAGAAATxrAAF
16 AAAZdRAAGAAATxrAAG
17 AAAZdRAAGAAATxrAAH
18 AAAZdRAAGAAATxrAAI
19 AAAZdRAAGAAATxrAAJ
已选择10行。
select dbms_rowid.rowid_object('AAAZdQAAGAAATxjAAA') data_object_id#,
dbms_rowid.rowid_relative_fno('AAAZdQAAGAAATxjAAA') rfile#,
dbms_rowid.rowid_block_number('AAAZdQAAGAAATxjAAA') block#,
dbms_rowid.rowid_row_number('AAAZdQAAGAAATxjAAA') row# from dual;
DATA_OBJECT_ID# RFILE# BLOCK# ROW#
--------------- ---------- ---------- ----------
104272 6 80995 0
--以下这个enable row movement必须操作，否则会出现ORA-014402:更新分区关键字列导致分区更改
alter table part_tab_rowid enable row movement;
update part_tab_rowid set id=12 where id=1;
commit;
select t.id,t.rowid from part_tab_rowid partition(p1) t;
ID ROWID
---------- ------------------
2 AAAZdQAAGAAATxjAAB
3 AAAZdQAAGAAATxjAAC
4 AAAZdQAAGAAATxjAAD
5 AAAZdQAAGAAATxjAAE
6 AAAZdQAAGAAATxjAAF
7 AAAZdQAAGAAATxjAAG
8 AAAZdQAAGAAATxjAAH
9 AAAZdQAAGAAATxjAAI
已选择8行。
SQL> select t.id,t.rowid from part_tab_rowid partition(p2) t;
ID ROWID
---------- ------------------
10 AAAZdRAAGAAATxrAAA
11 AAAZdRAAGAAATxrAAB
12 AAAZdRAAGAAATxrAAC
13 AAAZdRAAGAAATxrAAD
14 AAAZdRAAGAAATxrAAE
15 AAAZdRAAGAAATxrAAF
16 AAAZdRAAGAAATxrAAG
17 AAAZdRAAGAAATxrAAH
18 AAAZdRAAGAAATxrAAI
19 AAAZdRAAGAAATxrAAJ
12 AAAZdRAAGAAATxrAAK
已选择11行。
--由此可以发现，rowid的记录发生了改变。
---一般来说普通表的变更是不会变更rowid的，如下
create table normal_tab(id int);
insert into normal_tab1 select rownum from dual connect by rownum<=10;
commit;
select t.*,t.rowid from normal_tab t;
ID ROWID
---------- ------------------
1 AAAazDAAEAAATRdAAA
2 AAAazDAAEAAATRdAAB
3 AAAazDAAEAAATRdAAC
4 AAAazDAAEAAATRdAAD
5 AAAazDAAEAAATRdAAE
6 AAAazDAAEAAATRdAAF
7 AAAazDAAEAAATRdAAG
8 AAAazDAAEAAATRdAAH
9 AAAazDAAEAAATRdAAI
10 AAAazDAAEAAATRdAAJ
已选择10行。
update normal_tab set id=999 where id=1;
commit;
select t.*,t.rowid from normal_tab t;
ID ROWID
---------- ------------------
999 AAAazDAAEAAATRdAAA
2 AAAazDAAEAAATRdAAB
3 AAAazDAAEAAATRdAAC
4 AAAazDAAEAAATRdAAD
5 AAAazDAAEAAATRdAAE
6 AAAazDAAEAAATRdAAF
7 AAAazDAAEAAATRdAAG
8 AAAazDAAEAAATRdAAH
9 AAAazDAAEAAATRdAAI
10 AAAazDAAEAAATRdAAJ
已选择10行。
统计信息.
1. 传统的analyze的方式对收集分区表统计信息不准确。
2. 可以只收集某分区的统计信息
-- 范围分区示例
drop table range_part_tab purge;
--注意，此分区为范围分区
--例子1
create table range_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
partition by range (deal_date)
(
partition p_201301 values less than (TO_DATE('2013-02-01', 'YYYY-MM-DD')),
partition p_201302 values less than (TO_DATE('2013-03-01', 'YYYY-MM-DD')),
partition p_201303 values less than (TO_DATE('2013-04-01', 'YYYY-MM-DD')),
partition p_201304 values less than (TO_DATE('2013-05-01', 'YYYY-MM-DD')),
partition p_201305 values less than (TO_DATE('2013-06-01', 'YYYY-MM-DD')),
partition p_201306 values less than (TO_DATE('2013-07-01', 'YYYY-MM-DD')),
partition p_201307 values less than (TO_DATE('2013-08-01', 'YYYY-MM-DD')),
partition p_201308 values less than (TO_DATE('2013-09-01', 'YYYY-MM-DD')),
partition p_201309 values less than (TO_DATE('2013-10-01', 'YYYY-MM-DD')),
partition p_201310 values less than (TO_DATE('2013-11-01', 'YYYY-MM-DD')),
partition p_201311 values less than (TO_DATE('2013-12-01', 'YYYY-MM-DD')),
partition p_201312 values less than (TO_DATE('2014-01-01', 'YYYY-MM-DD')),
partition p_201401 values less than (TO_DATE('2014-02-01', 'YYYY-MM-DD')),
partition p_201402 values less than (TO_DATE('2014-03-01', 'YYYY-MM-DD')),
partition p_max values less than (maxvalue)
)
;
--以下是插入2013年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into range_part_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,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
--以下是插入2014年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into range_part_tab (id,deal_date,area_code,nbr,contents)
select rownum,
to_date( to_char(sysdate,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
ceil(dbms_random.value(591,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'RANGE_PART_TAB',partname =>'p_201312', estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
set linesize 366
col partition_count format 99999
col num_rows format 99999999
col partition_name format a28
col table_name format a30
col last_analyzed format date
---收集完p_201312分区的统计结构后，可以通过如下方式查询到结果：
select table_name,
partition_name,
last_analyzed,
partition_position,
num_rows
from user_tab_statistics t
where table_name ='RANGE_PART_TAB';
TABLE_NAME PARTITION_NAME LAST_ANALYZED PARTITION_POSITION NUM_ROWS
------------------------------ ---------------------------- -------------- ------------------ ---------
RANGE_PART_TAB 01-12月-13 201230
RANGE_PART_TAB P_201301 1
RANGE_PART_TAB P_201302 2
RANGE_PART_TAB P_201303 3
RANGE_PART_TAB P_201304 4
RANGE_PART_TAB P_201305 5
RANGE_PART_TAB P_201306 6
RANGE_PART_TAB P_201307 7
RANGE_PART_TAB P_201308 8
RANGE_PART_TAB P_201309 9
RANGE_PART_TAB P_201310 10
RANGE_PART_TAB P_201311 11
RANGE_PART_TAB P_201312 01-12月-13 12 8474
RANGE_PART_TAB P_201401 13
RANGE_PART_TAB P_201402 14
RANGE_PART_TAB P_MAX 15
已选择16行。
数据字典信息：
---显示数据库所有分区表的信息：DBA_PART_TABLES
---显示表分区信息 显示数据库所有分区表的详细分区信息：DBA_TAB_PARTITIONS
---显示子分区信息 显示数据库所有组合分区表的子分区信息：DBA_TAB_SUBPARTITIONS
---显示分区列 显示数据库所有分区表的分区列信息：DBA_PART_KEY_COLUMNS
--显示子分区列 显示数据库所有分区表的子分区列信息：DBA_SUBPART_KEY_COLUMNS
--查看当前用户下分区表及对应索引的相关信息
--（表及索引多大，多少个分区，在哪一个列建了分区，表及索引的分区的类型是什么，有无子分区）
set linesize 366
col partitioning_type format a10
col subpartitioning_type format a10
col partition_count format 99999
col column_name format a20
col object_type format a10
col blevel format 9
col num_rows format 99999999
col leaf_blocks format 999999
col column_position format 9
col partition_name format a28
col segment_type format a20
col index_name format a28
col table_name format a30
col last_analyzed format date
col status format a8
---01 该表是否是分区表，分区表的分区类型是什么，是否有子分区，分区总数有多少
select partitioning_type,
subpartitioning_type,
partition_count
from user_part_tables
where table_name ='RANGE_PART_TAB';
--02 该分区表在哪一列上建分区,有无多列联合建分区
select column_name,
object_type,
column_position
from user_part_key_columns
where name ='RANGE_PART_TAB';
--03 该分区表有多大？
select sum(bytes) / 1024 / 1024
from user_segments
where segment_name ='RANGE_PART_TAB';
--04 该分区表各分区分别有多大，各个分区名是什么。
select partition_name,
segment_type,
bytes
from user_segments
where segment_name ='RANGE_PART_TAB';
--05 该分区表的统计信息收集情况
select table_name,
partition_name,
last_analyzed,
partition_position,
num_rows
from user_tab_statistics t
where table_name ='RANGE_PART_TAB';
--06 查该分区表有无索引，分别什么类型,全局索引是否失效，此外还可看统计信息收集情况。
--(其中status值为N/A 表示分区索引，分区索引是否失效是在user_ind_partitions中查看)
select table_name,
index_name,
last_analyzed,
blevel,
num_rows,
leaf_blocks,
distinct_keys,
status
from user_indexes
where table_name ='RANGE_PART_TAB';
--07 该分区表在哪些列上建了索引
select index_name,
column_name,
column_position
from user_ind_columns
where table_name = 'RANGE_PART_TAB';
--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 ;
--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');
--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 t2
where t1.index_name = t2.index_name
and t2.table_name='RANGE_PART_TAB';
