|
Problem IT33796
|
Status: Closed |
VIEW NOT RESPECTING SUBQUERY THEREFORE PRODUCING TOO MANY ROWS WHICH LEADS TO LONGER RUNTIME OF THE QUERY
|
| product: |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 |
| Problem description: |
A view used in a select with a subquery does not apply subquery
when being created and therefore producing to many rows
Example:
select
...
where test_view.serial_vk in (select my_serial_1 from my_tab_1
where ....)
subquery is applied when using the view (which is slow)
3) (Temp Table For View): SEQUENTIAL SCAN
Filters:
Table Scan Filters: (Temp Table For View).serial_vk = ANY
subquery is applied when creating the view (which is fast)
1) informix.my_tab_2: INDEX PATH
(1) Index Name: informix.my_ind_2
Index Keys: my_serial_2 (Key-Only) (Serial, fragments:
ALL)
Lower Index Filter: informix.my_tab_2.my_serial = ANY |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users of Informix Server prior to 12.10.xC15 and 14.10.xC5. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Informix Server 12.10.xC15 (when available) or *
* 14.10.xC5. *
****************************************************************
This fix removes a previous restriction with SQL_DEF_CTRL bit
0x40000000, by default it's disabled.
steps to set SQL_DEF_CTRL
1) get the current SQL_DEF_CTL value
onstat -g cfg SQL_DEF_CTRL
name current value
SQL_DEF_CTRL 0x44004b0
2) the new value to enable that bit
SQL_DEF_CTRL | 0x40000000 = 0x444004b0
3) set with onmode or in ONCONFIG
onmode -wf SQL_DEF_CTRL=0x444004b0 |
| Local Fix: |
|
| Solution |
|
| Workaround |
****************************************************************
* USERS AFFECTED: *
* Users of Informix Server prior to 12.10.xC15 and 14.10.xC5. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Informix Server 12.10.xC15 (when available) or *
* 14.10.xC5. *
****************************************************************
This fix removes a previous restriction with SQL_DEF_CTRL bit
0x40000000, by default it's disabled.
steps to set SQL_DEF_CTRL
1) get the current SQL_DEF_CTL value
onstat -g cfg SQL_DEF_CTRL
name current value
SQL_DEF_CTRL 0x44004b0
2) the new value to enable that bit
SQL_DEF_CTRL | 0x40000000 = 0x444004b0
3) set with onmode or in ONCONFIG
onmode -wf SQL_DEF_CTRL=0x444004b0 |
| Comment |
Fixed in Informix Server 12.10.xC15 and 14.10.xC5. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 05.08.2020
22.01.2021
22.01.2021 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
