本地索引和全域性索引的適用場景

【背景】分割槽表建立好了之後，如果需要最大化分割槽表的效能就需要結合索引的使用，分割槽表有兩種索引：本地索引和全域性索引。既然存在著兩種的索引型別，相信存在即合理。既然存在就會有存在的原因，也就是在特定的場景中就更能發揮出索引的效能的；

本文件透過測試，總結出兩種索引的適合的場景；

 

【測試環境】

資料庫版本：11.2.0.3

分割槽表的建立指令碼：

1. CREATE TABLE SCOTT.PTB
2. (
3. GG1DM VARCHAR2(9 BYTE),
4. SL NUMBER(18,4) ,
5. DJBH VARCHAR2(20 BYTE)
6. )
7. NOCOMPRESS
8. PARTITION BY LIST (GG1DM)
9. (
10. PARTITION PTABLE_P1 VALUES ('07')，
11. PARTITION PTABLE_P2 VALUES ('08')，
12. PARTITION PTABLE_P3 VALUES ('09')
13. )

    然後插入大量的資料，再進行統計資訊的更新；

14. select t3.table_name, t3.partition_name,t3.high_value,t3.num_rows,t3.blocks,t3.empty_blocks,t3.last_analyzed
15. from dba_tab_partitions t3 where t3.table_name='PTABLE' order by t3.num_rows desc;
16. 

       

    【開始測試】

    測試一、跨分割槽的資料查詢

    1.1 建立本地索引（注意：該列不是分割槽的列）

17. SQL> CREATE INDEX SCOTT.IN_PTB ON SCOTT.PTB
18. (DJBH)
19. LOGGING
20. LOCAL (
21. PARTITION PTABLE_P1
22. LOGGING
23. NOCOMPRESS ,
24. PARTITION PTABLE_P2
25. LOGGING
26. NOCOMPRESS ,
27. PARTITION PTABLE_P3
28. LOGGING
29. NOCOMPRESS
30. )
31.  
32. SQL> select Segment_NAME,PARTITION_NAME,SEGMENT_TYPE from dba_segments a where a.segment_name='IN_PTB';
33.  
34. SEGMENT_NAME PARTITION_NAME         SEGMENT_TYPE
35. ---------------- --------------------- ------------------
36. IN_PTB PTABLE_P1         INDEX PARTITION
37. IN_PTB PTABLE_P2         INDEX PARTITION
38. IN_PTB PTABLE_P3     INDEX PARTITION
39. LOCAL索引會在每個分割槽上面單獨建立INDEX PARTITION，類似於三個子索引；

     

     

    進行執行計劃的檢視

40. SQL> select count(1) from scott.ptb where djbh='R23NAA002138250';
41.  
42. COUNT(1)
43. ----------
44. 512

    

     

    1.2 建立全域性索引，原先的索引先drop（注意：該列不是分割槽的列）

45. SQL> CREATE INDEX SCOTT.IN_PTB_L ON SCOTT.PTB
46. (DJBH)
47. NOLOGGING
48. STORAGE (
49. BUFFER_POOL DEFAULT
50. FLASH_CACHE DEFAULT
51. CELL_FLASH_CACHE DEFAULT
52. )
53. NOPARALLEL;
54. SQL> select Segment_NAME,PARTITION_NAME,SEGMENT_TYPE from dba_segments a where a.segment_name='IN_PTB_L';
55.  
56. SEGMENT_NAME PARTITION_NAME         SEGMENT_TYPE
57. -------------- --------------------- --------------------
58. IN_PTB_L             INDEX

     

    進行執行計劃的檢視

    需要先重新整理buffer：

59. alter system flush buffer_cache;
60. select count(1) from scott.ptb where djbh='R23NAA002138250';

     

     測試一總結：以上那種情況因為djbh這一列是需要跨分割槽的，當查詢的條件是需要跨分割槽查詢內容的時候，LOCAL INDEX的效率比GLOBAL INDEX的效率要低，透過consistent gets和db block gets的對比可以看出來；

     

    測試二、分割槽內部的查詢

    2.1 分割槽內使用本地索引

61. alter system flush buffer_cache;
62. select count(1) from scott.ptb where djbh='R23NAA002138250' and GG1DM='07'; #該條件可以確定在單個分割槽裡面

    

     

    2.2 分割槽內使用全域性索引

63. alter system flush buffer_cache;
64. select  /*+ index(PTB IN_PTB_L) */  count(1) from scott.ptb where djbh='R23NAA002138250' and GG1DM='07';

    

    測試二總結：透過這組實驗可以看出來如果查詢的條件是在單個分割槽裡面查詢的時候，那麼LOCAL INDEX的效率比GLOBAL INDEX的效率要高。

     

    【總結】經過以上的測試可以發現全域性索引和本地索引的使用效率跟查詢條件有直接的影響，建立索引的時候需要根據業務的使用場景進行建立；

    而分割槽表的建立也是受使用場景所影響的，所以在建立分割槽表和分割槽索引的時候都需要事先了解業務的需求，儘量把業務需要統計的資訊放在一個同一個分割槽。這樣使分割槽表的效能實現最大化；