Informix - Problem description
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) |