Informix - Problem description
Problem IT30688 | Status: Closed |
12.10 CHOOSES SEQUENTIAL SCAN OVER INDEX AS IN 11.70 IF DS_TOTAL_MEMORY / DS_MAX_QUERIES QUANTUM < 3700KB ON NON-PDQ QUERY | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
See the sqexplain differences: 12.10.FC12 QUERY: (OPTIMIZATION TIMESTAMP: 06-11-2019 12:04:13) ------ select first 501 distinct client0_.client_id as col_0_0_, client0_.first_name as col_1_0_, client0_.middle_name as col_2_0_, client0_.last_name as col_3_0_, client0_.generation as col_4_0_, client0_.date_of_birth as col_5_0_, client0_.fbi_no as col_6_0_, client0_.ssn as col_7_0_, client0_.regis_marsh_num as col_8_0_, client0_.ins_no as col_9_0_ from client client0_ inner join alternate_name alternaten1_ on client0_.client_id=alternaten1_.client_id and alternaten1_.lcd_id=client0_.lcd_id left outer join client_address clientaddr2_ on client0_.client_id=clientaddr2_.client_id and clientaddr2_.lcd_id=client0_.lcd_id where 1=1 and (lower(client0_.last_name)=lower('Smith') or lower(alternaten1_.last_name)=lower('Smith')) and client0_.lcd_id= "0970" order by client0_.last_name, client0_.first_name, client0_.middle_name Estimated Cost: 1860568 Estimated # of Rows Returned: 44582 Temporary Files Required For: Order By 1) informix.client0_: INDEX PATH (SKIP SCAN) (1) Index Name: informix. 167_4447 Index Keys: lcd_id second_country_citizenship (Serial, fragments: ALL) Lower Index Filter: informix.client0_.lcd_id = '0970' 2) informix.alternaten1_: SEQUENTIAL SCAN Filters: Table Scan Filters: informix.alternaten1_.lcd_id = '0970' DYNAMIC HASH JOIN (Build Outer) Dynamic Hash Filters: informix.client0_.client_id = informix.alternaten1_.client_id Other Join Filters: (LOWER(informix.client0_.last_name ) = 'smith' OR LOWER(informix.alternaten1_.last_name ) = 'smith' ) 3) informix.clientaddr2_: INDEX PATH (1) Index Name: informix. 531_4323 Index Keys: lcd_id client_id (Key-Only) (Serial, fragments: ALL) Lower Index Filter: (informix.client0_.client_id = informix.clientaddr2_.client_id AND informix.clientaddr2_.lcd_id = informix.client0_.lcd_id ) NESTED LOOP JOIN 11.70.FC8: QUERY: (OPTIMIZATION TIMESTAMP: 06-11-2019 12:36:46) ------ select first 501 distinct client0_.client_id as col_0_0_, client0_.first_name as col_1_0_, client0_.middle_name as col_2_0_, client0_.last_name as col_3_0_, client0_.generation as col_4_0_, client0_.date_of_birth as col_5_0_, client0_.fbi_no as col_6_0_, client0_.ssn as col_7_0_, client0_.regis_marsh_num as col_8_0_, client0_.ins_no as col_9_0_ from client client0_ inner join alternate_name alternaten1_ on client0_.client_id=alternaten1_.client_id and alternaten1_.lcd_id=client0_.lcd_id left outer join client_address clientaddr2_ on client0_.client_id=clientaddr2_.client_id and clientaddr2_.lcd_id=client0_.lcd_id where 1=1 and (lower(client0_.last_name)=lower('Smith') or lower(alternaten1_.last_name)=lower('Smith')) and client0_.lcd_id= "0970" order by client0_.last_name, client0_.first_name, client0_.middle_name Estimated Cost: 2007810 Estimated # of Rows Returned: 47971 Temporary Files Required For: Order By 1) informix.client0_: INDEX PATH (1) Index Name: informix. 167_4447 Index Keys: lcd_id second_country_citizenship (Serial, fragments: ALL) Lower Index Filter: informix.client0_.lcd_id = '0970' 2) informix.alternaten1_: INDEX PATH (1) Index Name: informix.idx_altnnm_clntid Index Keys: lcd_id client_id (Key-First) (Serial, fragments: ALL) Lower Index Filter: (informix.client0_.client_id = informix.alternaten1_.client_id AND informix.alternaten1_.lcd_id = informix.client0_.lcd_id ) Index Key Filters: (informix.alternaten1_.lcd_id = '0970' ) ON-Filters:(informix.client0_.client_id = informix.alternaten1_.client_id AND informix.alternaten1_.lcd_id = informix.client0_.lcd_id ) Other Join Filters: (LOWER(informix.client0_.last_name ) = 'smith' OR LOWER(informix.alternaten1_.last_name ) = 'smith' ) NESTED LOOP JOIN 3) informix.clientaddr2_: INDEX PATH (1) Index Name: informix. 531_4323 Index Keys: lcd_id client_id (Key-Only) (Serial, fragments: ALL) Lower Index Filter: (informix.client0_.client_id = informix.clientaddr2_.client_id AND informix.clientaddr2_.lcd_id = informix.client0_.lcd_id ) ON-Filters:(informix.client0_.client_id = informix.clientaddr2_.client_id AND informix.clientaddr2_.lcd_id = informix.client0_.lcd_id ) NESTED LOOP JOIN(LEFT OUTER JOIN) PostJoin-Filters:(LOWER(informix.client0_.last_name ) = 'smith' OR LOWER(informix.alternaten1_.last_name ) = 'smith' ) Onconfig relevant parameters: MAX_PDQPRIORITY 0 DS_MAX_QUERIES 100 DS_TOTAL_MEMORY 7000000 DS_MAX_SCANS 1048576 DS_NONPDQ_QUERY_MEM 1500000 DATASKIP off If the DS_TOTAL_MEMORY / DS_MAX_QUERIES quantum ~ 3700kb then optimizer chooses the INDEX SCAN | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC14 and 14.10.xC3. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Update to Informix Server 12.10.xC14 or 14.10.xC3. * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC14 and 14.10.xC3. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Update to Informix Server 12.10.xC14 or 14.10.xC3. * **************************************************************** | |
Comment | |
Fixed in Informix Server 12.10.xC14 and 14.10.xC3. | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 23.10.2019 26.02.2020 26.02.2020 |
Problem solved at the following versions (IBM BugInfos) | |
12.10.xC14, 14.10.xC3 | |
Problem solved according to the fixlist(s) of the following version(s) |