Oracle数据库分区表与索引

最近在排查项目识别码查询慢的原因时，发现有个功能的查询SQL执行效率较高，毫秒级即可返回查询结果。

        因此通过筛查耗时SQL排行，SQL如下：

        select *

from (select v.sql_id,
v.child_number,
v.sql_text,
v.SQL_FULLTEXT,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
v.LOCKED_TOTAL,
v.LAST_ACTIVE_TIME,
rank() over(order by v.elapsed_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 100

筛选出了识别码查询的SQL，进一步筛查耗时排名在识别码查询SQL之前的SQL语句，发现，排名第一的sql类似如下写法：

select * from tablename where ITEMNO ='20160000000000000000000000000000000114704788';

该SQL单独执行5分钟未返回结果。

查看该表的数据总量，2000W。

查看该SQL的执行结果数量，为两条，如图：

    

查看该SQL在生产上的执行计划如下：

    

分析该表未增加分区字段查询，因此增加分区字段，结果执行计划如下：

   select * from tablename where VIRTUAL_COMCODE='4100' and ITEMNO='201600000000000000000000000000000000000034085';

    

该查询不走列索引YZXBD_ITEMNO，而是只执行了分区索引。

在测试环境上测试，发现无论是否带分区字段查询，都只走YZXBD_ITEMNO索引，而不走分区索引。

因此得出结论，分区表如果同时将分区字段和普通索引字段作为查询条件，则数据库只会执行两种索引中的一个，具体执行哪一个索引，由数据库自己算法决定。

          优化方案：考虑针对分区表创建分区索引。

            具体SQL命令如下：

            create index idx_tablename_ITEMNO on tablename(ITEMNO) local;

         创建完成后，继续执行：select * from tablename where VIRTUAL_COMCODE='4100' and ITEMNO='201600000000000000000000000000000000000034085';

            执行计划如下：

                

        

        可以看到创建分区索引后，该查询即走了单分区索引，也走了列索引。

        因此，针对该sql的优化建议：查询增加分区字段条件，索引创建为分区索引以保证相关索引生效。