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 |
