【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/

● 本系列題目來源於作者的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

● 題目解答若有不當之處，還望各位朋友批評指正，共同進步