时间戳

首页 > 解决方案 > DB2 存储过程优化器未使用最有效的索引

xml - DB2 存储过程优化器未使用最有效的索引

问题描述

我有一个存储过程，它根据 p_qds_startTime 和 p_qds_endTime 参数返回数据，这些参数通常设置为 365 天的报告期，但报告期可以设置为短至 1 天。过程查询的表有数百万行，并包含一个包含 xml 数据的列。

优化器选择使用针对 sys_start 的索引，这对于 365 天的报告期可能非常低效。对于较大的报告期，id 像优化器一样使用与策略状态相关的 xml 索引，因为只有 300,000 条记录的策略状态为“应用程序”。

策略状态索引设置正确，如果我运行底层查询，用输入参数替换时间戳，解释计划显示优化器在报告期超过 10 天时使用此索引。如果报告期小于 10 天，它使用同样需要的 sys_start 索引。

我的问题：有没有办法确保优化器检查输入值并重新评估每次运行时将使用的计划？这是一个每天一次的批处理作业，因此执行此操作的开销并不是一个真正的问题。

将其放在上下文中，批处理作业最多需要 10 个小时才能运行，而当我在本地运行基础查询时，点击策略状态索引，只需要几分钟即可运行。

这是 SP 中的基础查询

select 
      policy_number
      ,year_1_commission_amount
from(
select 
      xml_policy.policy_number,
      t002.SYS_START,
      xml_policy.policy_status,
      xml_policy.year_1_commission_amount,
      ROW_NUMBER() over (partition by xml_policy.POLICY_NUMBER order by t002.SYS_START DESC) RN
from 
      DB.t002
      ,xmltable
      (
      '$i/*:AddProtQuoteResponse/*:plan[1]/*:policy[*:policyStatus = "Application"]' passing t002.QDS_XML AS "i"
      columns
      policy_number varchar(30) path '*:policyNumber',
      policy_status varchar(12) path '*:policyStatus',
      year_1_commission_amount decimal(11,2) path ''
      ) as xml_policy
where 
      sys_start > CAST(p_qds_startTime AS TIMESTAMP(12)) 
      and sys_start <= CAST(p_qds_endTime AS TIMESTAMP(12)) 
      and t002.QDS_XML_TYPE_ID = 3 
)
where rn = 1;

标签： xmloptimizationindexingdb2

解决方案

尝试使用以下REOPT ALWAYS选项重新绑定常规包：

CALL SYSPROC.REBIND_ROUTINE_PACKAGE ('SP', 'SP_SCHEMA.SP_SPECIFICNAME', 'REOPT ALWAYS');

其中第二个参数是以下结果：

SELECT RTRIM(ROUTINESCHEMA)||'.'||SPECIFICNAME
--, PARM_COUNT
FROM SYSCAT.ROUTINES R
WHERE ROUTINESCHEMA='SP_SCHEMA' AND ROUTINENAME='SP_NAME';

SP_SCHEMA & SP_NAME - 程序的模式和名称。您可能在上述查询的输出中有许多行（如果您有许多具有相同这一对的例程），您必须选择具有相应特定名称的正确行。

推荐阅读