【DB笔试面试475】分区表性能注意事项有哪些？
- 2019 年 10 月 11 日
- 筆記
题目部分
分区表性能注意事项有哪些？
答案部分
在使用分区表的时候需要注意以下几方面的内容：
① 在查询分区表时尽量带上分区键过滤条件，否则可能引起全分区扫描。
② 在设计分区表时，避免数据都进入默认分区，从而导致出现默认分区超大或各个分区大小严重不均衡的情况，失去分区表的意义。
③ 需要特别注意分区表性能比普通表性能差的情况。这种情况的本质原因是，虽然分区表的分区索引比全局索引要小很多，但是由于没有扫描指定的分区，而是扫描了很多个小的索引，这些小索引的高度累计起来一般都比全局索引要高。索引的范围检索性能是由索引的高度（BLEVEL）决定的，而不是由索引的大小决定。所以，性能差异很明显。示例如下所示：
--创建分区表和索引 DROP TABLE T_PART_20170619_LHR PURGE; CREATE TABLE T_PART_20170619_LHR (ID INT,COL2 INT,COL3 INT) 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 P6 VALUES LESS THAN (60000), PARTITION P7 VALUES LESS THAN (70000), PARTITION P8 VALUES LESS THAN (80000), PARTITION P9 VALUES LESS THAN (90000), PARTITION P10 VALUES LESS THAN (100000), PARTITION P11 VALUES LESS THAN (MAXVALUE) ); INSERT INTO T_PART_20170619_LHR SELECT ROWNUM,ROWNUM+1,ROWNUM+2 FROM DUAL CONNECT BY ROWNUM <=110000; COMMIT; CREATE INDEX IDX_PART_COL2_LHR ON T_PART_20170619_LHR(COL2) LOCAL; CREATE INDEX IDX_PART_COL3_LHR ON T_PART_20170619_LHR(COL3) ; --构造普通表，表结构和数据量都与分区表一样 DROP TABLE T_NM_20170619_LHR PURGE; CREATE TABLE T_NM_20170619_LHR (ID INT,COL2 INT,COL3 INT); INSERT INTO T_NM_20170619_LHR SELECT ROWNUM,ROWNUM+1,ROWNUM+2 FROM DUAL CONNECT BY ROWNUM <=110000; COMMIT; CREATE INDEX IDX_NM_COL2_LHR ON T_NM_20170619_LHR(COL2) ; CREATE INDEX IDX_NM_COL3_LHR ON T_NM_20170619_LHR(COL3) ;
分别查询分区表和普通表：
LHR@orclasm > SELECT * FROM T_PART_20170619_LHR WHERE COL2=8 ; ID COL2 COL3 ---------- ---------- ---------- 7 8 9 Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 548042063 -------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 39 | 13 (0)| 00:00:01 | | | | 1 | PARTITION RANGE ALL | | 1 | 39 | 13 (0)| 00:00:01 | 1 | 11 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PART_20170619_LHR | 1 | 39 | 13 (0)| 00:00:01 | 1 | 11 | |* 3 | INDEX RANGE SCAN | IDX_PART_COL2_LHR | 1 | | 12 (0)| 00:00:01 | 1 | 11 | -------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("COL2"=8) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 24 consistent gets 0 physical reads 0 redo size 658 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed LHR@orclasm > SELECT * FROM T_NM_20170619_LHR WHERE COL2=8 ; ID COL2 COL3 ---------- ---------- ---------- 7 8 9 Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 3820625032 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_NM_20170619_LHR | 1 | 39 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_NM_COL2_LHR | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL2"=8) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 662 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 可以看到，查询分区表的逻辑读为24，而普通表的逻辑读仅为4。从执行计划很明显的看出，Oracle扫描分区表的11个分区，它们的索引详情如下所示： LHR@orclasm > SET TIMING ON LHR@orclasm > SET AUTOTRACE OFF LHR@orclasm > SELECT INDEX_NAME, 2 BLEVEL, 3 LEAF_BLOCKS, 4 NUM_ROWS, 5 DISTINCT_KEYS, 6 CLUSTERING_FACTOR 7 FROM USER_IND_STATISTICS 8 WHERE TABLE_NAME IN( 'T_NM_20170619_LHR'); INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR ------------------------------ ---------- ----------- ---------- ------------- ----------------- IDX_NM_COL2_LHR 1 244 110000 110000 299 IDX_NM_COL3_LHR 1 244 110000 110000 299 Elapsed: 00:00:00.01 LHR@orclasm > SELECT INDEX_NAME, 2 BLEVEL, 3 LEAF_BLOCKS, 4 NUM_ROWS, 5 DISTINCT_KEYS, 6 CLUSTERING_FACTOR 7 FROM USER_IND_PARTITIONS 8 WHERE INDEX_NAME='IDX_PART_COL2_LHR'; INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR ------------------------------ ---------- ----------- ---------- ------------- ----------------- IDX_PART_COL2_LHR 1 21 9999 9999 24 IDX_PART_COL2_LHR 1 23 10000 10000 28 IDX_PART_COL2_LHR 1 23 10001 10001 28 IDX_PART_COL2_LHR 1 23 10000 10000 28 IDX_PART_COL2_LHR 1 23 10000 10000 28 IDX_PART_COL2_LHR 1 23 10000 10000 28 IDX_PART_COL2_LHR 1 23 10000 10000 28 IDX_PART_COL2_LHR 1 23 10000 10000 28 IDX_PART_COL2_LHR 1 23 10000 10000 28 IDX_PART_COL2_LHR 1 23 10000 10000 28 IDX_PART_COL2_LHR 1 23 10000 10000 28 11 rows selected. 分区表的分区索引虽然很小，但是需要扫描11个小的分区索引，而索引的范围检索性能是由索引的高度（BLEVEL）决定的，而不是由索引的大小决定。所以，11个小的分区索引总高度为22，远大于普通表的高度2。故可以把分区索引改造为全局索引或者想法让Oracle少扫描一些分区索引，则比如性能会提升，如下所示： LHR@orclasm > SELECT * FROM T_PART_20170619_LHR PARTITION(P1) WHERE COL2=8 ; ID COL2 COL3 ---------- ---------- ---------- 7 8 9 Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 4015980707 -------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE | | 1 | 39 | 2 (0)| 00:00:01 | 1 | 1 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PART_20170619_LHR | 1 | 39 | 2 (0)| 00:00:01 | 1 | 1 | |* 3 | INDEX RANGE SCAN | IDX_PART_COL2_LHR | 1 | | 1 (0)| 00:00:01 | 1 | 1 | -------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("COL2"=8) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 658 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed LHR@orclasm > SELECT * FROM T_PART_20170619_LHR WHERE ID<COL2 AND COL2=8 ; ID COL2 COL3 ---------- ---------- ---------- 7 8 9 Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 4015980707 -------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE | | 1 | 39 | 2 (0)| 00:00:01 | 1 | 1 | |* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PART_20170619_LHR | 1 | 39 | 2 (0)| 00:00:01 | 1 | 1 | |* 3 | INDEX RANGE SCAN | IDX_PART_COL2_LHR | 1 | | 1 (0)| 00:00:01 | 1 | 1 | -------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"<"COL2" AND "ID"<8) 3 - access("COL2"=8) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 658 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 由此可见，在查询分区表的时候，应该尽量加上分区键过滤条件，从而避免全分区扫描。如下查询可看到分区表的优点： LHR@orclasm > SELECT * FROM T_NM_20170619_LHR T WHERE T.ID IN (666,66666,77777,88888); ID COL2 COL3 ---------- ---------- ---------- 666 667 668 66666 66667 66668 88888 88889 88890 77777 77778 77779 Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 610368810 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 12 | 468 | 103 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| T_NM_20170619_LHR | 12 | 468 | 103 (1)| 00:00:02 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T"."ID"=666 OR "T"."ID"=66666 OR "T"."ID"=77777 OR "T"."ID"=88888) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 1 db block gets 336 consistent gets 0 physical reads 0 redo size 767 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4 rows processed LHR@orclasm > SELECT * FROM T_PART_20170619_LHR T WHERE T.ID IN (666,66666,77777,88888); ID COL2 COL3 ---------- ---------- ---------- 666 667 668 66666 66667 66668 77777 77778 77779 88888 88889 88890 Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 4018516498 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 117 | 53 (4)| 00:00:01 | | | | 1 | PARTITION RANGE INLIST| | 3 | 117 | 53 (4)| 00:00:01 |KEY(I) |KEY(I) | |* 2 | TABLE ACCESS FULL | T_PART_20170619_LHR | 3 | 117 | 53 (4)| 00:00:01 |KEY(I) |KEY(I) | -------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T"."ID"=666 OR "T"."ID"=66666 OR "T"."ID"=77777 OR "T"."ID"=88888) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 217 consistent gets 0 physical reads 0 redo size 767 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 4 rows processed
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
