本文探讨了在Oracle数据库中，如何通过使用分区表和索引来优化查询性能。针对一个拥有2000万条记录的表，原先的查询耗时5分钟无法返回结果。通过增加分区字段，查询执行计划得到显著改善，从而显著提升了查询效率。
最近在排查项目识别码查询慢的原因时，发现有个功能的查询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的优化建议：查询增加分区字段条件，索引创建为分区索引以保证相关索引生效。
扫一扫
975
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
