主页 / user-165222

Mark Barinstein's questions

Martin Hope
Mark Barinstein
Asked: 2022-10-20 08:05:49 +0800 CST
  • 5

该问题在 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