Oracle SQL调优系列之表设计与性能

网友投稿 527 2022-05-29

在看《收获，不止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

Oracle SQL调优系列之表设计与性能

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小时内删除侵权内容。

标签：关键字 表分区 varchar
上一篇:面试官：Redis如何实现持久化的、主从哨兵又是什么？
下一篇:2019年Java大厂面试题讲解（周阳） 之 JVM参数调优
相关文章