excel表如何根據顏色排序（excel表如何按照顏色排序）
615
2025-04-04
Oracle SQL調優系列之表設計與性能
在看《收獲，不止sql優化》一書，并做了筆記，本博客介紹一下一些和調優相關的表比如分區表、臨時表、索引組織表、簇表以及表壓縮技術
分區表使用與查詢頻繁而更新數據不頻繁的情況，不過要記得加全局索引，而不加分區索引，分區類型：分區分為范圍分區、列表分區、HASH分區、組合分區四種，用了分區表，查詢時就定位到對應的區，而不用全表，所以查詢效率比普通表好，當然有很多細節，還是建議看《收獲，不止sql優化》一書
分區表詳細看：https://smilenicky.blog.csdn.net/article/details/90315716
范圍分區
關鍵字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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
列表分區
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
散列分區
散列分區也叫hash分區，partitions后接分區數，盡量設置為偶數，
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
組合分區
主要有兩種：oracle11之前只支持范圍列表分區(RANGE-LIST)和范圍散列分區(RANGE-HASH)，oracle11之后支持(范圍范圍分區)RANGE-RANGE、 (列表范圍分區)LIST-RANGE、(列表散列分區)LIST-HASH、(列表列表分區)LIST-LIST這幾種組合，為了避免每個主分區中都寫相同的從分區，可以用模板方式(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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
普通表和分區表區別，分區表分成幾部分就有幾個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');
1
2
3
4
5
6
7
分區相關操作
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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
新增分區
ALTER TABLE list_part_tab ADD PARTITION P13 VALUES LESS THAN(250);
1
新增子分區
ALTER TABLE list_part_tab MODIFY PARTITION P13 ADD SUBPARTITION P13SUB1 VALUES(350);
1
刪除分區
ALTER TABLE list_part_tab DROP PARTITION P13;
1
刪除子分區
ALTER TABLE list_part_tab DROP SUBPARTITION P13SUB1;
1
TRUNCATE分區
TRUNCATE是指刪除分區的數據，并不會刪除分區
ALTER TABLE list_part_tab TRUNCATE PARTITION P2;
1
TRUNCATE子分區
ALTER TABLE list_part_tab TRUNCATE SUBPARTITION P13SUB1;
1
合并分區
合并分區是將相鄰的分區合并成一個分區，結果分區將采用較高分區的界限，值得注意的是，不能將分區合并到界限較低的分區
ALTER TABLE list_part_tab MERGE PARTITIONS P1,P2 INTO PARTITION P2;
1
接合分區(coalesca)
將散列分區中的數據接合到其它分區中，當散列分區中的數據比較大時，可以增加散列分區，然后進行接合，注意接合只適用于散列分區
ALTER TABLE list_part_tab COALESCA PARTITION;
1
重命名分區
ALTER TABLE SAlist_part_tabLES RENAME PARTITION P11 TO P1;
1
交換分區
交換分區是說交換兩張表結構一樣的表的數據，注意最好加上including indexs更新全局索引，不加的話，全局索引會失效
alter table list_part_tab exchange partition p1 with table range_part_tab including indexs update global indexs;
1
分區相關查詢
*查詢數據庫所有分區表的信息
select * from DBA_PART_TABLES
1
查詢分區表類型、是否有子分區，分區總數
select pt.partitioning_type, pt.subpartitioning_type, pt.partition_count from user_part_tables pt
1
2
3
查詢分區詳細詳細：
SELECT tab.* FROM USER_TAB_PARTITIONS tab WHERE TABLE_NAME='LIST_PART_TAB'
1
2
查詢分區表哪列建分區
select column_name, object_type, column_position from user_part_key_columns where name = 'LIST_PART_TAB';
1
2
3
查詢分區表大小
select sum(bytes / 1024 / 1024) from user_segments where segment_name = 'LIST_PART_TAB';
1
2
3
查詢分區表各分區的大小和分區名
select partition_name, segment_type, bytes from user_segments where segment_name = 'LIST_PART_TAB';
1
2
3
4
查詢分區表各索引大小
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;
1
2
3
4
5
6
7
查詢分區表的統計信息
select table_name, partition_name, last_analyzed, partition_position, num_rows from user_tab_statistics where table_name = 'LIST_PART_TAB';
1
2
3
4
5
6
7
8
查詢分區表索引情況
select table_name, index_name, last_analyzed, blevel, num_rows, leaf_blocks, distinct_keys, status from user_indexes where table_name = 'LIST_PART_TAB';
1
2
3
4
5
6
7
8
9
10
查詢索引在哪些列上
select index_name, column_name, column_position from user_ind_columns where table_name = 'LIST_PART_TAB';
1
2
3
4
查詢普通表失效的索引
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';
1
2
3
4
5
6
7
8
查詢分區表失效的索引
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';
1
2
3
4
5
6
7
8
9
10
11
全局臨時表：全局臨時表分為兩種類型，一種是基于會話的全局臨時表(on commit preserve rows);一種是基于事務的全局臨時表(on commit delete rows)
create global temporary table [臨時表名] on commit (preserve rows)|(delete rows) as select * from [數據表];
1
eg:
create global temporary table tmp on commit preserve rows as select * from dba_objects;
1
全局臨時表特點：
一、高效刪除記錄；
二、不同會話訪問臨時表看到的會話是不同的
select * from v$mystat where rownum=1;
1
ps：基于事務的臨時表在事務提交和會話連接退出時，臨時表數據會被刪除；基于會話的臨時表就是在會話連接退出時，臨時表數據被刪除
索引組織表：
壓縮技術
表壓縮
ALTER TABLE t MOVE COMPRESS ;
1
索引壓縮
create index idx2_object_union on t2 (owner , object_type , object_name ); ALTER index idx2_object_union rebuild COMPRESS ;
1
2
簇表：簇由一組共享多個數據塊的多個表組成，它將這些表的相關行一起存儲到相同數據塊中，這樣可以減少查詢數據所需的磁盤讀取量。新建簇之后，在簇中新建的表被稱為簇表
ps:表結構設計時，最好存放什么數據就設計為什么類型，避免執行時類型轉換，影響性能
Oracle SQL
版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。
版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。
相關文章
