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. |
