Local index or Global index？

某物流客户系统查询快递单的SQL，IO消耗为TOP 1：

SQL如下：

select id,op_code, to_char(create_time, :"SYS_B_1") as create_time,...

from T_EXP_OP_RECORD_CONTAINER  A 

where status <> :"SYS_B_4" and  ID = :1 and rownum = :"SYS_B_5";

其中T_EXP_OP_RECORD_CONTAINER 表是一个在Create_time字段按天进行一级分区、在op_code字段按地区二级分区的分区表，ID字段保存的是快递单号信息，字段上存在索引。

SQL中出现了"SYS_B_n" 字样的绑定变量，这是因为数据库参数的cursor_sharging被设置为FORCE（强烈建议保持默认值EXACT），SQL中使用的常量值被强制转换成了绑定变量。rownum=后面的常量被强制转换成了绑定变量，这个值根据常识可以判断为1，因为只有1才有意义。

快递单号基本上是唯一的，这样的SQL，正常执行时间应该在1毫秒左右，而下图使用awrsqrpt收集的SQL实际执行情况是：每次执行耗时1.236秒。

SQL执行计划如下：

看到上面的执行计划后，就会明白平均执行时间是1秒多正常了：这个查询要到6030个local index里面检索数据，平均每个local index至少要扫描3个buffers 才能判断记录是否存在，因为有rownum=1 谓词条件，最好的情况是扫描local index的第一个分支就找到了结果，不再继续扫描下去；最差的情况是扫描到 local index 的最后一个分支才找到结果，或是没有找到结果。

一般情况下，local index索引的使用，需要配合分区字段一起做谓词条件，才能只扫描少数的索引分支。而这个SQL由于业务原因，不能增加分区字段作为谓词条件。这种情况就需要将local index改成Global index，才会使SQL性能达到最佳。

但是，因为该表非常庞大（表和索引占用的空间达到T级），需要定期删除（转移）历史分区，只保留最近一年的数据，如果创建的是global index，删除历史分区后，需要对global index进行重建，维护时间窗口很难完成（有多个类似表）。这是个两难的问题。

    

针对快递业务的特点，老虎刘给出的建议是：

• 仍使用local index，重建表，减少分区数量：按天分区改为按月分区，不要子分区；
• 因为很少有用户会查询1个月以上的快递单，该表只保留最近2个月分区数据，其他数据转移到历史分区，正常情况只需要最多扫描2个分区，而不是原来的6030个分区。
• 通过plsql实现查询：当前分区没有查询到结果，再去查询历史分区。这样也能保证超过2个月的快递单也能正常查询。

 

总结：

分区表，到底选择global index还是local index，需要根据具体的业务和运维的实际需求而定。不需要删除历史分区数据的分区表，可以创建global index（如基础数据表）；需要定期删除历史分区的分区表，最好是创建local index，如果遇到分区字段无法成为查询条件时，建议尽量减少分区数，避免过多的local index 扫描，影响SQL性能。