Db2: EXP0059W in the access plan for queries using statistical view with IN or EXISTS predicate

Question

The problem is listed as resolved in 9.7.1, but the same one exists in all recent 11.5 and 11.1 versions.
IC62904: ISSUE EXP0059W FOR STATVIEWS WITH SELECT DISTINCT.
Still the same EXP0059W in the access plan.
The steps to reproduce:

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;

The access plan contains the following message similar to the one described at the link:

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".

I can't open a Case with IBM support.
The question is: does somebody know how to resolve the problem with, say, some registry variable setting or with other facilities?

Answer 1

Can you rephrase your query in the view?

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".

I tried with:

db2set DB2_INLIST_TO_NLJN=YES

but it does not seem to improve things

Some additional observations, don't know if it helps. A view like:

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

can not be used to optimize queries (SQL20278W), but:

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

can. If we look at the query:

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

it is optimized as:

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

If we add additional constraints:

create table tb (b1 int not null unique);

the query is optimized as:

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

Hence, Db2 can use the view for the optimized statement:

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".

Can you add some constraints that remove the necessity for DISTINCT in the optimized query?

A dangerous way is to add an informational constraint:

create table tb (b1 int not null);

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