0

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?

1 Answer 1

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;
4
  • Well, it helps somehow to estimate cardinality of the original query, but I'm afraid with some limitations. The original query is rewritten by the optimizer in the form of SELECT DISTINCT ta.* FROM ta JOIN tb on tb.b1 = ta.a1, if ta doesn't contain lobs. SV is really considered in this case, but it's hard to say what algorithms are used to compute the cardinality. Cardinalities for SELECT DISTINCT ta.* ... (real) and SELECT ta.* ... (gathered by RUNSTATS) might be different significantly. What I see in the access plan is the result of some optimizer "estimations / computations"... Oct 20, 2022 at 10:53
  • As for DISTINCT. It must be in the rewritten query (if the optimizer has decided to rewrite the query so), if there is probability of more than 1 matching row in tb to avoid wrong results. If it's impossible to rewrite it using JOIN (lobs are in ta, for example), then SV is not considered by the optimizer. And yes, I'd avoid adding "fake" constraints on tb... Oct 20, 2022 at 11:03
  • Agreed, it was just a long shot that there is a constraint in reality that Db2 is not aware of. In that case adding that constraint provides information for the optimizer to make a better decision Oct 20, 2022 at 11:11
  • 1
    Ok, it's not an ideal solution, but it much, much better than nothing. It helps me to tell the optimizer, that it had XXX times cardinality estimation error in my case, and the precise estimation is probably not required. Thanks a lot! Oct 20, 2022 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.