主页 / dba / 问题 / 318418
Accepted
Mark Barinstein
Asked: 2022-10-20 08:05:49 +0800 CST

Db2: EXP0059W 在使用带有 IN 或 EXISTS 谓词的统计视图的查询的访问计划中

  • 772

该问题在 9.7.1 中列为已解决，但在所有最近的 11.5 和 11.1 版本中都存在相同的问题。
IC62904：针对具有 SELECT DISTINCT 的 STATVIEWS 发出 EXP0059W。
访问计划中的 EXP0059W 仍然相同。
重现步骤：

connect to mydb;
create table ta (a1 int);
create table tb (b1 int);
create view sv as (select * from ta where a1 in (select b1 from tb));                                                           
alter view sv enable query optimization;

explain all with snapshot for
select * from ta where a1 in (select b1 from  tb);              

!db2exfmt -d mydb -1 -o sv.plan;

访问计划包含以下消息，类似于链接中描述的消息：

Diagnostic Details:     EXP0059W  The following MQT or statistical view was
            not eligible because of one or more of the
            following reasons: (1) the MQT had extra table
            joins or GROUP BY columns that were not present in
            the query, or (2) the query had an SQL construct
            such as ORDER BY, FETCH FIRST n ROWS, DISTINCT, or
            had a subquery that could not be matched:
            "DB2ADMIN"."SV".

我无法通过 IBM 支持打开案例。
问题是：是否有人知道如何通过某些注册表变量设置或其他工具来解决问题？

db2

1 个回答

  1. Best Answer

    您可以在视图中重新表述您的查询吗？

    create or replace view sv as (select ta.* from ta join tb on a1 = b1);
alter view sv enable query optimization;

explain all with snapshot for
select * from ta where a1 in (select b1 from  tb);

    [...]

    Extended Diagnostic Information:
--------------------------------

[...]
Diagnostic Identifier:  3
Diagnostic Details:     EXP0147W  The following statistical view was used
                        by the optimizer to estimate cardinalities:
                        "DB2INST1"."SV".
Diagnostic Identifier:  4
Diagnostic Details:     EXP0148W  The following MQT or statistical view was
                        considered in query matching: "DB2INST1"."SV".

    我试过：

    db2set DB2_INLIST_TO_NLJN=YES

    但这似乎并没有改善

    一些额外的观察，不知道它是否有帮助。像这样的视图：

    create or replace view sv1 as (
    select DISTINCT ta.* from ta where a1 in (select b1 from tb)
);

    不能用于优化查询 (SQL20278W)，但是：

    create or replace view sv1 as (
    select ta.* from ta where a1 in (select b1 from tb)
);

    能够。如果我们查看查询：

    select 
  * 
from 
  ta 
where 
  a1 in 
  (select 
     b1 
   from 
     tb
  )

    它被优化为：

    SELECT 
  DISTINCT Q2.A1 AS "A1" 
FROM 
  DB2INST1.TB AS Q1,
  DB2INST1.TA AS Q2 
WHERE 
  (Q2.A1 = Q1.B1)

    如果我们添加额外的约束：

    create table tb (b1 int not null unique);

    查询优化为：

    SELECT 
  Q2.A1 AS "A1"     -- DISTINCT not necessary
FROM 
  DB2INST1.TB AS Q1,
  DB2INST1.TA AS Q2 
WHERE 
  (Q2.A1 = Q1.B1)

    因此，Db2 可以将视图用于优化语句：

    Diagnostic Identifier:  3
Diagnostic Details:     EXP0147W  The following statistical view was used
                        by the optimizer to estimate cardinalities:
                        "DB2INST1"."SV1".
Diagnostic Identifier:  4
Diagnostic Details:     EXP0148W  The following MQT or statistical view was
                        considered in query matching: "DB2INST1"."SV1".

    您可以添加一些约束来消除优化查询中 DISTINCT 的必要性吗？

    一种危险的方法是添加信息约束：

    create table tb (b1 int not null);

alter table tb add constraint abc
        unique (b1)
not enforced
enable query optimization;
    • 1

相关问题