2618614 - Usefulness and Selectivity in descending order could lead to slower performance for some queries - SAP IQ 

For example

• For 7 tables with the same DDL and HG index like the following.

CREATE TABLE "Events_OTHER_part01" (
,"ID1" char(36) NULL
,"ID12" decimal(8,5) NULL
,"ID13" decimal(8,5) NULL
,"EventTimeMins" datetime NULL);

CREATE HG INDEX "Events_OTHERS_EventTimeMins_HG" ON "Events_part01" ( EventTimeMins")

• And a Union All view with the 7 tables:

create view "Events" as
select * from "Events_OTHER_part01" union all
select * from "Events_OTHER_part02" union all
select * from "Events_OTHER_part03" union all
select * from "Events_OTHER_part04" union all
select * from "Events_OTHER_part05" union all
select * from "Events_OTHER_part06" union all
select * from "Events_OTHER_part07";

• Each table contains data for a single month.

• Only the table "Events_part04" contains the matching rows for Nov 2017.

• Consider this query on the view:

select top 10000 "ID1"
from "Events"
where("EventTimeMins" between '2017-11-02 18:34' and '2017-11-23 2:23')
and "ID12" > 103.754876358246
and "ID12" < 103.870919449066
and "ID13" > 1.31681572675404
and "ID13" < 1.43213896153067

The optimizer has detected the condition on "EventTimeMins" is not useful and not selective enough for all the tables as most tables don't have data for Nov 2017.
Thus the optimizer assigns Selectivity =0 and Usefulness = 4.
The other conditions have a higher Selectivity and higher Usefulness.

As conditions are examined after Selectivity and Usefulness in descending order, the optimizer spends
a quite a time to examined the other conditions and the condition on "EventTimeMins" is examined last.

If the optimizer had examined, first, the condition on "EventTimeMins", the execution could complete faster.

See an excerpt of query plan below for the leaf node "Events_OTHER_part06". Please see the complete HTLM query plan attached.

Condition 1 (Invariant) Events_OTHER_part06.ID12 BETWEEN (103.754876358246 AND 103.870919449066)
Condition 1 Selectivity 0.00207015
Condition 1 Usefulness 8.99298966
Condition 1 Distincts in Range 11,602
Condition 1 Elapsed time 32.7380 sec.
Condition 1 Rows remaining after condition 146,537,361
Condition 1 Execution Method Column scan expression into bitmap cursor
Condition 1 Note Was inferred
Condition 1 Index HG(3-tier) DBA.Events_OTHER_part06.Events_OTHERS_ID12_HG

Condition 2 (Invariant) Events_OTHER_part06.ID13 BETWEEN (1.31681572675404 AND 1.43213896153067)
Condition 2 Selectivity 0.00370016
Condition 2 Usefulness 8.98604801
Condition 2 Distincts in Range 11,531
Condition 2 Elapsed time 170.0830 sec.
Condition 2 Rows remaining after condition 31,170,729
Condition 2 Execution Method Column scan expression into bitmap cursor
Condition 2 Note Was inferred
Condition 2 Index FP DBA.Events_OTHER_part06.ASIQ_IDX_T815_C14_FP
Condition 2 Note Internally converted to conjunctive normal form

Condition 3 (Invariant) Events_OTHER_part06.EventTimeMins BETWEEN [2017-11-02 18:34:00.000 AND 2017-11-23 02:23:00.000]
Condition 3 Selectivity 0.00000000
Condition 3 Selectivity Estimation Time 0:00:00.002041
Condition 3 Usefulness 4.00000000
Condition 3 Elapsed time 0.0440 sec.
Condition 3 Rows remaining after condition 0
Condition 3 Execution Method Column scan expression into bitmap cursor
Condition 3 Note Was inferred
Condition 3 Index HG DBA.Events_OTHER_part06.Events_OTHERS_EventTimeMins_HG