《收获，不止SQL优化》读书笔记 (7)

普通表和分区表区别，分区表分成几部分就有几个segment

select segment_name, partition_name, segment_type, bytes / 1024 / 1024 "字节数(M)", tablespace_name from user_segments where segment_name IN ('RANGE_PART_TAB', 'NOR_TAB');

分区相关操作

Split分区
拆分分区，范围分区和列表分区都适合分区，注意不能对HASH类型的分区进行拆分

create table list_part_tab (seq number,deal_date date,unit_code number,remark varchar2(100)) partition by list (unit_code) ( partition p1 values (211), partition p2 values (212), partition p3 values (213), partition p4 values (214), partition p5 values (215), partition p6 values (216), partition p7 values (217), partition p8 values (218), partition p9 values (219), partition p10 values (220), partition p0 values (DEFAULT) ); alter table list_part_tab split partition p10 at(220) into (PARTITION p11,PARTITION p12);

新增分区

ALTER TABLE list_part_tab ADD PARTITION P13 VALUES LESS THAN(250);

新增子分区

ALTER TABLE list_part_tab MODIFY PARTITION P13 ADD SUBPARTITION P13SUB1 VALUES(350);

删除分区

ALTER TABLE list_part_tab DROP PARTITION P13;

删除子分区

ALTER TABLE list_part_tab DROP SUBPARTITION P13SUB1;

TRUNCATE分区
TRUNCATE是指删除分区的数据，并不会删除分区

ALTER TABLE list_part_tab TRUNCATE PARTITION P2;

TRUNCATE子分区

ALTER TABLE list_part_tab TRUNCATE SUBPARTITION P13SUB1;

合并分区
合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区

ALTER TABLE list_part_tab MERGE PARTITIONS P1,P2 INTO PARTITION P2;

接合分区(coalesca)
将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，注意接合只适用于散列分区

ALTER TABLE list_part_tab COALESCA PARTITION;

重命名分区

ALTER TABLE SAlist_part_tabLES RENAME PARTITION P11 TO P1;

交换分区
交换分区是说交换两张表结构一样的表的数据，注意最好加上including indexs更新全局索引，不加的话，全局索引会失效

alter table list_part_tab exchange partition p1 with table range_part_tab including indexs update global indexs;

分区相关查询
*查询数据库所有分区表的信息

select * from DBA_PART_TABLES

查询分区表类型、是否有子分区，分区总数

select pt.partitioning_type, pt.subpartitioning_type, pt.partition_count from user_part_tables pt

查询分区详细详细：

SELECT tab.* FROM USER_TAB_PARTITIONS tab 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 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 table_name, partition_name, last_analyzed, partition_position, num_rows from user_tab_statistics where table_name = 'LIST_PART_TAB';

查询分区表索引情况

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 ind.index_name, ind.table_name, ind.blevel, ind.num_rows, ind.leaf_blocks, ind.distinct_keys from user_indexes ind where status = 'INVALID';