Symptom
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
Read more...
Environment
SAP IQ 16.0
Product
Keywords
CR814045, CR#814045, 814045, "union all" , union, view, , KBA , BC-SYB-IQ , Sybase IQ , Bug Filed
About this page
This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.