转载

本地索引和全局索引的适用场景

【背景】分区表创建好了之后,如果需要最大化分区表的性能就需要结合索引的使用,分区表有两种索引:本地索引和全局索引。既然存在着两种的索引类型,相信存在即合理。既然存在就会有存在的原因,也就是在特定的场景中就更能发挥出索引的性能的;

本文档通过测试,总结出两种索引的适合的场景;

 

【测试环境】

数据库版本: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 getsdb 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的效率要高。

     

    【总结】经过以上的测试可以发现全局索引和本地索引的使用效率跟查询条件有直接的影响,创建索引的时候需要根据业务的使用场景进行创建;

    而分区表的创建也是受使用场景所影响的,所以在创建分区表和分区索引的时候都需要事先了解业务的需求,尽量把业务需要统计的信息放在一个同一个分区。这样使分区表的性能实现最大化;

正文到此结束
Loading...