Informix - Problem description
Problem IT27310 | Status: Closed |
AFTER UPGRADE FROM 11.50 TO 12.10 QUERY USING ANSI JOIN SYNTAX TAKING QUERY PLAN THAT'S SLOWER AND CAUSES MORE BUFF READS | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
Here is the query plan after upgrade to 12.10.xC7 select NVL(SUM(ipe.invoice_amt * nvl(nvl(ipa.fund_alloc_exch_rt,qer.exchange_rate) ,1)),0)r_amt_qty1 FROM quote_ship_schd qss inner join invoice_po ip on ip.quote_id = qss.quote_id and ip.quote_ship_nbr = qss.quote_ship_nbr and cancel_entry_ind = 'N' inner join invoice_po_exp ipe on ipe.payment_office_id=ip.payment_office_id and ipe.invoice_id=ip.invoice_id and ipe.quote_id=ip.quote_id and ipe.quote_ship_nbr=ip.quote_ship_nbr and ipe.po_entry_nbr=ip.po_entry_nbr and ipe.expense_type = 20263 inner join invoice i on i.invoice_id = ip.invoice_id and i.payment_office_id = ip.payment_office_id inner join purchase_company pc on pc.payment_office_id = i.payment_office_id left outer join quote_exchg_rate qer on qer.quote_id = qss.quote_id and qer.to_currency_code = 'USD' and qer.from_currency_code = i.invc_currency_code left outer join invc_payment_auth ipa on ipa.payment_office_id = i.payment_office_id and ipa.invoice_id = i.invoice_id and ipa.post_date is not null and pc.currency_code = 'USD' WHERE qss.quote_id=12263933 AND qss.quote_ship_nbr=1 Estimated Cost: 486031 Estimated # of Rows Returned: 1 1) informix.pc: SEQUENTIAL SCAN 2) informix.i: INDEX PATH (1) Index Name: informix. 1237_16861 Index Keys: payment_office_id (Serial, fragments: ALL) Lower Index Filter: informix.pc.payment_office_id = informix.i.payment_office_id NESTED LOOP JOIN 3) informix.qss: INDEX PATH (1) Index Name: informix. 2057_13967 Index Keys: quote_id quote_ship_nbr (Key-Only) (Serial, fragments: ALL) Lower Index Filter: (informix.qss.quote_id = 12263933 AND informix.qss.quote_ship_nbr = 1 ) 4) informix.ipe: INDEX PATH Filters: informix.ipe.expense_type = 20263 (1) Index Name: informix.inv_po_exp_s01 Index Keys: quote_id quote_ship_nbr (Serial, fragments: ALL) Lower Index Filter: (informix.qss.quote_ship_nbr = informix.ipe.quote_ship_nbr AND informix.qss.quote_id = informix.ipe.quote_id ) NESTED LOOP JOIN DYNAMIC HASH JOIN Dynamic Hash Filters: (informix.ipe.payment_office_id = informix.pc.payment_office_id AND informix.ipe.invoice_id = informix.i.invoice_id ) 5) informix.ip: INDEX PATH Filters: informix.ip.cancel_entry_ind = 'N' (1) Index Name: informix.invoice_po_i03 Index Keys: invoice_id quote_id po_entry_nbr quote_ship_nbr payment_office_id (Key-First) (Serial, fragments: ALL) Lower Index Filter: ((((informix.ipe.payment_office_id = informix.ip.payment_office_id AND informix.ipe.invoice_id = informix.ip.invoice_id ) AND informix.ipe.quote_id = informix.ip.quote_id ) AND informix.ipe.quote_ship_nbr = informix.ip.quote_ship_nbr ) AND informix.ipe.po_entry_nbr = informix.ip.po_entry_nbr ) Index Key Filters: (informix.ip.quote_id = 12263933 ) AND (informix.ip.quote_ship_nbr = 1 ) Other Join Filters: ((((informix.ip.quote_id = informix.qss.quote_id AND informix.ip.quote_ship_nbr = informix.qss.quote_ship_nbr ) AND informix.ipe.payment_office_id = informix.ip.payment_office_id ) AND informix.ipe.invoice_id = informix.ip.invoice_id ) AND informix.ipe.po_entry_nbr = informix.ip.po_entry_nbr ) NESTED LOOP JOIN 6) informix.qer: INDEX PATH (1) Index Name: informix. 582_14067 Index Keys: quote_id from_currency_code to_currency_code (Serial, fragments: ALL) Lower Index Filter: ((informix.qer.quote_id = informix.qss.quote_id AND informix.qer.from_currency_code = informix.i.invc_currency_code ) AND informix.qer.to_currency_code = 'USD' ) ON-Filters:((informix.qer.quote_id = informix.qss.quote_id AND informix.qer.to_currency_code = 'USD' ) AND informix.qer.from_currency_code = informix.i.invc_currency_code ) NESTED LOOP JOIN(LEFT OUTER JOIN) 7) informix.ipa: INDEX PATH Filters: informix.ipa.post_date IS NOT NULL (1) Index Name: informix. 1723_14099 Index Keys: payment_office_id invoice_id seq_nbr (Serial, fragments: ALL) Lower Index Filter: (informix.ipa.payment_office_id = informix.i.payment_office_id AND informix.ipa.invoice_id = informix.i.invoice_id ) ON-Filters:(((informix.ipa.payment_office_id = informix.i.payment_office_id AND informix.ipa.invoice_id = informix.i.invoice_id ) AND informix.ipa.post_date IS NOT NULL ) AND informix.pc.currency_code = 'USD' ) NESTED LOOP JOIN(LEFT OUTER JOIN) Table map : ---------------------------- Internal name Table name ---------------------------- t1 pc t2 i t3 qss t4 ipe t5 ip t6 qer t7 ipa type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t1 55 55 55 00:00.00 6 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t2 2251580 1124709 1125790 00:02.79 5045 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 1125790 1124710 00:02.84 277465 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t3 1 1 1 00:00.00 1 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t4 0 1 14 00:00.00 2 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 0 1 00:00.00 3 type rows_prod est_rows rows_bld rows_prb novrflo time est_cost ---------------------------------------------------------------- -------------- hjoin 0 1 0 1125790 0 00:03.09 486029 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t5 0 7 0 00:00.00 1 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 0 1 00:03.09 486030 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t6 0 4728 0 00:00.00 1 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 0 1 00:03.09 486030 type table rows_prod est_rows rows_scan time est_cost ---------------------------------------------------------------- --- scan t7 0 1062874 0 00:00.00 1 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 0 1 00:03.09 486031 type rows_prod est_rows rows_cons time ------------------------------------------------- group 1 1 0 00:03.09 However, if SQL_FEAT_CTRL2 is set in the $ONCONFIG file to 0x4, the query plan switched to the following plan which has a much lower estimated cost, executed faster, and performed significantly fewer buff reads. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of IDS prior to 12.10.xC13. * **************************************************************** * PROBLEM DESCRIPTION: * * After upgrade from 11.50 to 12.10 query using ansi join * * syntax taking query plan that's slower and causes more buff * * reads. * **************************************************************** * RECOMMENDATION: * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 12.12.2018 26.09.2019 26.09.2019 |
Problem solved at the following versions (IBM BugInfos) | |
12.10.xC13 | |
Problem solved according to the fixlist(s) of the following version(s) |