在看《收獲，不止sql優(yōu)化》一書绕沈，并做了筆記，本博客介紹一下一些和調(diào)優(yōu)相關(guān)的表比如分區(qū)表帮寻、臨時表乍狐、索引組織表、簇表以及表壓縮技術(shù)
分區(qū)表使用與查詢頻繁而更新數(shù)據(jù)不頻繁的情況固逗，不過要記得加全局索引澜躺，而不加分區(qū)索引，分區(qū)類型：分區(qū)分為范圍分區(qū)抒蚜、列表分區(qū)掘鄙、HASH分區(qū)、組合分區(qū)四種嗡髓，用了分區(qū)表操漠，查詢時就定位到對應(yīng)的區(qū)，而不用全表饿这，所以查詢效率比普通表好浊伙，當(dāng)然有很多細節(jié)，還是建議看《收獲长捧，不止sql優(yōu)化》一書
分區(qū)表詳細看：https://smilenicky.blog.csdn.net/article/details/90315716
- 范圍分區(qū)
關(guān)鍵字partition by range
create table range_part_tab (seq number,deal_date date,unit_code number,remark varchar2(100))
partition by range (deal_date)
(
partition p1 values less than (TO_DATE('2018-11-01','YYYY-MM-DD')),
partition p2 values less than (TO_DATE('2018-12-02','YYYY-MM-DD')),
partition p3 values less than (TO_DATE('2019-01-01','YYYY-MM-DD')),
partition p4 values less than (TO_DATE('2019-02-01','YYYY-MM-DD')),
partition p5 values less than (TO_DATE('2019-03-01','YYYY-MM-DD')),
partition p6 values less than (TO_DATE('2019-04-01','YYYY-MM-DD')),
partition p7 values less than (TO_DATE('2019-05-01','YYYY-MM-DD')),
partition p8 values less than (TO_DATE('2019-06-01','YYYY-MM-DD')),
partition p9 values less than (TO_DATE('2019-07-01','YYYY-MM-DD')),
partition p10 values less than (TO_DATE('2019-08-01','YYYY-MM-DD'))
);
insert into range_part_tab
(seq, deal_date, unit_code, remark)
select rownum,
to_date(to_char(sysdate-365, 'J') +
trunc(DBMS_RANDOM.value(0, 365)),'J'),
ceil(dbms_random.value(210,220)),
rpad('*', 1, '*')
from dual
connect by rownum <= 1000;
- 列表分區(qū)
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)
);
insert into list_part_tab
(seq, deal_date, unit_code, remark)
select rownum,
to_date(to_char(sysdate-365, 'J') +
trunc(DBMS_RANDOM.value(0, 365)),'J'),
ceil(dbms_random.value(210,220)),
rpad('*', 1, '*')
from dual
connect by rownum <= 1000;
commit;
- 散列分區(qū)
散列分區(qū)也叫hash分區(qū)嚣鄙，partitions后接分區(qū)數(shù)，盡量設(shè)置為偶數(shù)串结，
create table hash_part_tab (seq number,deal_date date,unit_code number,remark varchar2(100))
partition by hash (deal_date)
partitions 12;
insert into hash_part_tab
(seq, deal_date, unit_code, remark)
select rownum,
to_date(to_char(sysdate-365, 'J') +
trunc(DBMS_RANDOM.value(0, 365)),'J'),
ceil(dbms_random.value(210,220)),
rpad('*', 1, '*')
from dual
connect by rownum <= 1000;
commit;
- 組合分區(qū)
主要有兩種：oracle11之前只支持范圍列表分區(qū)(RANGE-LIST)和范圍散列分區(qū)(RANGE-HASH)哑子，oracle11之后支持(范圍范圍分區(qū))RANGE-RANGE、 (列表范圍分區(qū))LIST-RANGE肌割、(列表散列分區(qū))LIST-HASH卧蜓、(列表列表分區(qū))LIST-LIST這幾種組合，為了避免每個主分區(qū)中都寫相同的從分區(qū)把敞，可以用模板方式(subpartition template)
create table range_list_part_tab (seq number,deal_date date,unit_code number,remark varchar2(100))
partition by range (deal_date)
subpartition by list (unit_code)
subpartition template
(subpartition s1 values (211),
subpartition s2 values (212),
subpartition s3 values (213),
subpartition s4 values (214),
subpartition s5 values (215),
subpartition s6 values (216),
subpartition s7 values (217),
subpartition s8 values (218),
subpartition s9 values (219),
subpartition s10 values (220),
subpartition s0 values (DEFAULT) )
(
partition p1 values less than (TO_DATE('2018-11-01','YYYY-MM-DD')),
partition p2 values less than (TO_DATE('2018-12-02','YYYY-MM-DD')),
partition p3 values less than (TO_DATE('2019-01-01','YYYY-MM-DD')),
partition p4 values less than (TO_DATE('2019-02-01','YYYY-MM-DD')),
partition p5 values less than (TO_DATE('2019-03-01','YYYY-MM-DD')),
partition p6 values less than (TO_DATE('2019-04-01','YYYY-MM-DD')),
partition p7 values less than (TO_DATE('2019-05-01','YYYY-MM-DD')),
partition p8 values less than (TO_DATE('2019-06-01','YYYY-MM-DD')),
partition p9 values less than (TO_DATE('2019-07-01','YYYY-MM-DD')),
partition p10 values less than (TO_DATE('2019-08-01','YYYY-MM-DD'))
);
insert into range_list_part_tab
(seq, deal_date, unit_code, remark)
select rownum,
to_date(to_char(sysdate-365, 'J') +
trunc(DBMS_RANDOM.value(0, 365)),'J'),
ceil(dbms_random.value(210,220)),
rpad('*', 1, '*')
from dual
connect by rownum <= 1000;
commit;
普通表和分區(qū)表區(qū)別弥奸，分區(qū)表分成幾部分就有幾個segment
select segment_name,
partition_name,
segment_type,
bytes / 1024 / 1024 "字節(jié)數(shù)(M)",
tablespace_name
from user_segments
where segment_name IN ('RANGE_PART_TAB', 'NOR_TAB');
分區(qū)相關(guān)操作
- Split分區(qū)
拆分分區(qū)，范圍分區(qū)和列表分區(qū)都適合分區(qū)奋早，注意不能對HASH類型的分區(qū)進行拆分
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);
- 新增分區(qū)
ALTER TABLE list_part_tab ADD PARTITION P13 VALUES LESS THAN(250);
新增子分區(qū)
ALTER TABLE list_part_tab MODIFY PARTITION P13 ADD SUBPARTITION P13SUB1 VALUES(350);
- 刪除分區(qū)
ALTER TABLE list_part_tab DROP PARTITION P13;
刪除子分區(qū)
ALTER TABLE list_part_tab DROP SUBPARTITION P13SUB1;
- TRUNCATE分區(qū)
TRUNCATE是指刪除分區(qū)的數(shù)據(jù)盛霎，并不會刪除分區(qū)
ALTER TABLE list_part_tab TRUNCATE PARTITION P2;
TRUNCATE子分區(qū)
ALTER TABLE list_part_tab TRUNCATE SUBPARTITION P13SUB1;
- 合并分區(qū)
合并分區(qū)是將相鄰的分區(qū)合并成一個分區(qū)赠橙，結(jié)果分區(qū)將采用較高分區(qū)的界限，值得注意的是愤炸，不能將分區(qū)合并到界限較低的分區(qū)
ALTER TABLE list_part_tab MERGE PARTITIONS P1,P2 INTO PARTITION P2;
- 接合分區(qū)(coalesca)
將散列分區(qū)中的數(shù)據(jù)接合到其它分區(qū)中期揪，當(dāng)散列分區(qū)中的數(shù)據(jù)比較大時，可以增加散列分區(qū)摇幻，然后進行接合，注意接合只適用于散列分區(qū)
ALTER TABLE list_part_tab COALESCA PARTITION;
- 重命名分區(qū)
ALTER TABLE SAlist_part_tabLES RENAME PARTITION P11 TO P1;
- 交換分區(qū)
交換分區(qū)是說交換兩張表結(jié)構(gòu)一樣的表的數(shù)據(jù)挥萌，注意最好加上including indexs更新全局索引绰姻，不加的話，全局索引會失效
alter table list_part_tab exchange partition p1 with table range_part_tab including indexs update global indexs;
分區(qū)相關(guān)查詢
*查詢數(shù)據(jù)庫所有分區(qū)表的信息
select * from DBA_PART_TABLES
- 查詢分區(qū)表類型引瀑、是否有子分區(qū)狂芋，分區(qū)總數(shù)
select pt.partitioning_type, pt.subpartitioning_type, pt.partition_count
from user_part_tables pt
- 查詢分區(qū)詳細詳細：
SELECT tab.* FROM USER_TAB_PARTITIONS tab WHERE TABLE_NAME='LIST_PART_TAB'
- 查詢分區(qū)表哪列建分區(qū)
select column_name, object_type, column_position
from user_part_key_columns
where name = 'LIST_PART_TAB';
- 查詢分區(qū)表大小
select sum(bytes / 1024 / 1024)
from user_segments
where segment_name = 'LIST_PART_TAB';
- 查詢分區(qū)表各分區(qū)的大小和分區(qū)名
select partition_name, segment_type, bytes
from user_segments
where segment_name = 'LIST_PART_TAB';
- 查詢分區(qū)表各索引大小
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;
- 查詢分區(qū)表的統(tǒng)計信息
select table_name,
partition_name,
last_analyzed,
partition_position,
num_rows
from user_tab_statistics
where table_name = 'LIST_PART_TAB';
- 查詢分區(qū)表索引情況
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';
- 查詢分區(qū)表失效的索引
select a.blevel,
a.leaf_blocks,
a.index_name,
b.table_name,
a.partition_name,
a.status
from user_ind_partitions a, user_indexes b
where a.index_name = b.index_name
and a.status = 'UNUSABLE';
分區(qū)表索引失效的操作，表格來自《收獲憨栽，不止SQL優(yōu)化》一書作者的歸納
| 操作動作 | 操作命令 | 是否失效(全局索引) | 如何避免(全局索引) | 是否失效(分區(qū)索引) | 如何避免(分區(qū)索引) |
|---|---|---|---|---|---|
| truncate分區(qū) | alter table part_tab_trunc truncate partition p1 ; | 失效 | alter table part_tab_trunc truncate partition p1 Update GLOBAL indexes; | 沒影響 | N/A |
| drop分區(qū) | alter table part_tab_drop drop partition p1; | 失效 | alter table part_tab_drop drop partition p1 Update GLOBAL indexes; | 沒影響 | N/A |
| split分區(qū) | 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 (30000) into (PARTITION p3,PARTITION P_MAX) update global indexes; | 沒影響 | N/A |
| add分區(qū) | alter table part_tab_add add PARTITION p6 values less than (60000); | 沒影響 | N/A | 沒影響 | N/A |
| exchange分區(qū) | alter table part_tab_exch exchange partition p1 with table normal_tab including indexes; | 失效 | alter table part_tab_exch exchange partition p1 with table normal_tab including indexes update global indexes; | 沒影響 | N/A |
全局臨時表：全局臨時表分為兩種類型帜矾，一種是基于會話的全局臨時表(on commit preserve rows);一種是基于事務(wù)的全局臨時表(on commit delete rows)
create global temporary table [臨時表名] on commit (preserve rows)|(delete rows) as select * from [數(shù)據(jù)表];
eg:
create global temporary table tmp on commit preserve rows as select * from dba_objects;
全局臨時表特點：
- 一、高效刪除記錄屑柔；
- 二屡萤、不同會話訪問臨時表看到的會話是不同的
select * from v$mystat where rownum=1;
ps：基于事務(wù)的臨時表在事務(wù)提交和會話連接退出時，臨時表數(shù)據(jù)會被刪除掸宛；基于會話的臨時表就是在會話連接退出時死陆，臨時表數(shù)據(jù)被刪除
索引組織表：
壓縮技術(shù)
- 表壓縮
ALTER TABLE t MOVE COMPRESS ;
- 索引壓縮
create index idx2_object_union on t2 (owner , object_type , object_name );
ALTER index idx2_object_union rebuild COMPRESS ;
簇表：簇由一組共享多個數(shù)據(jù)塊的多個表組成，它將這些表的相關(guān)行一起存儲到相同數(shù)據(jù)塊中唧瘾，這樣可以減少查詢數(shù)據(jù)所需的磁盤讀取量措译。新建簇之后，在簇中新建的表被稱為簇表
ps:表結(jié)構(gòu)設(shè)計時饰序，最好存放什么數(shù)據(jù)就設(shè)計為什么類型领虹，避免執(zhí)行時類型轉(zhuǎn)換，影響性能
