The following problem was observed on AIX64 after customer
upgraded from
11.70.FC6 to 11.70.FC9.
A particular query utilizing ansi join is optimized with an
expense plan and results in a
long execution time for the query that has much more desirable
plan that is not chosen.
The good plan can be observed using the ORDERED optimizer
directive for this query.
The sqexplain.out output for the good plan using the ORDERED
optimizer directive and
the plan that is chosen in 11.70.FC6 follows:
****************************************************************
*
QUERY: (OPTIMIZATION TIMESTAMP: 12-12-2018 15:57:09)
------
SELECT --+ORDERED
doc.nrDocumento,
hum.noHumano,
inst.nrInscrEstadual,
inst.noRazaoSocial,
huminst.sqHumanoInst
FROM
(
(
(
(
(
(
tbPro_Processo proc
INNER JOIN tbPro_InteressePro intpro ON
intpro.nrProcesso = proc.nrProcesso
)
INNER JOIN tbCad_HumanoInst huminst ON
intpro.sqHumanoInst = huminst.sqHumanoInst
)
INNER JOIN tbCad_DocHumInst dhi ON dhi.sqHumanoInst
= huminst.sqHumanoInst
)
INNER JOIN tbCad_Documento doc ON doc.sqDocumento =
dhi.sqDocumento
)
LEFT OUTER JOIN tbCad_Humano hum ON huminst.sqHumanoInst =
hum.sqHumano
)
LEFT OUTER JOIN tbCad_Instituicao inst ON
huminst.sqHumanoInst = inst.sqHumanoInst
)
WHERE proc.nrProcesso = '15204520180'
AND doc.sqTpDocHumInst IN (2,3)
AND intpro.stRegistro = 1
AND dhi.stRegistro = 1
DIRECTIVES FOLLOWED:
ORDERED
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 10
Estimated # of Rows Returned: 1
1) informix.proc: INDEX PATH
(1) Index Name: informix. 163_518
Index Keys: nrprocesso
(Key-Only) (Serial, fragments: ALL)
Lower Index Filter:
informix.proc.nrprocesso = '15204520180'
2) informix.intpro: INDEX PATH
Filters:
informix.intpro.stregistro = 1
(1) Index Name: camila. 1722_262977
Index Keys: nrprocesso
(Serial, fragments: ALL)
Lower Index Filter:
informix.intpro.nrprocesso = informix.proc.nrprocesso
NESTED LOOP JOIN
3) informix.huminst: INDEX PATH
(1) Index Name: informix. 100_1
Index Keys: sqhumanoinst
(Key-Only) (Serial, fragments: ALL)
Lower Index Filter:
informix.intpro.sqhumanoinst = informix.huminst.sqhumanoinst
NESTED LOOP JOIN
4) informix.dhi: INDEX PATH
(1) Index Name: usudba.xie1tbcad_dochumin
Index Keys: stregistro sqhumanoinst
sqdocumento (Key-Only) (Serial, fragments: ALL)
Lower Index Filter:
(informix.intpro.sqhumanoinst = informix.dhi.sqhumanoinst AND
informix.dhi.stregistro = 1 )
Other Join Filters: informix.intpro.sqhumanoinst
= informix.dhi.sqhumanoinst
NESTED LOOP JOIN
5) informix.doc: INDEX PATH
Filters: informix.doc.sqtpdochuminst IN (2 ,
3 )
(1) Index Name: informix. 888_6817
Index Keys: sqdocumento (Serial,
fragments: ALL)
Lower Index Filter: informix.doc.sqdocumento
= informix.dhi.sqdocumento
NESTED LOOP JOIN
6) informix.hum: INDEX PATH
(1) Index Name: informix. 101_10
Index Keys: sqhumano (Serial, fragments: ALL)
Lower Index Filter:
informix.huminst.sqhumanoinst = informix.hum.sqhumano
ON-Filters:informix.huminst.sqhumanoinst =
informix.hum.sqhumano
NESTED LOOP JOIN(LEFT OUTER JOIN)
7) informix.inst: INDEX PATH
(1) Index Name: informix. 205_902
Index Keys: sqhumanoinst (Serial, fragments: ALL)
Lower Index Filter: informix.huminst.sqhumanoinst =
informix.inst.sqhumanoinst
ON-Filters:informix.huminst.sqhumanoinst =
informix.inst.sqhumanoinst
NESTED LOOP JOIN(LEFT OUTER JOIN)
****************************************************************
*
The poor plan that the 11.70.FC9 optimizer is choosing follows:
****************************************************************
*
QUERY: (OPTIMIZATION TIMESTAMP: 12-12-2018 15:51:44)
------
Estimated Cost: 19796000
Estimated # of Rows Returned: 1
1) informix.proc: INDEX PATH
(1) Index Name: informix. 163_518
Index Keys: nrprocesso (Key-Only)
(Serial, fragments: ALL)
Lower Index Filter:
informix.proc.nrprocesso = '15204520180'
2) informix.intpro: INDEX PATH
Filters: informix.intpro.stregistro
= 1
(1) Index Name: camila. 1722_262977
Index Keys: nrprocesso (Serial,
fragments: ALL)
Lower Index Filter:
informix.intpro.nrprocesso = informix.proc.nrprocesso
NESTED LOOP JOIN
3) informix.doc: SEQUENTIAL SCAN
Filters: informix.doc.sqtpdochuminst
IN (2 , 3 )
4) informix.dhi: INDEX PATH
Filters: informix.dhi.stregistro = 1
(1) Index Name: informix. 889_14844
Index Keys: sqdocumento (Serial,
fragments: ALL)
Lower Index Filter:
informix.doc.sqdocumento = informix.dhi.sqdocumento
NESTED LOOP JOIN
Other Join Filters: informix.intpro.sqhumanoinst
= informix.dhi.sqhumanoinst
NESTED LOOP JOIN
5) informix.huminst: INDEX PATH
(1) Index Name: informix. 100_1
Index Keys: sqhumanoinst (Key-Only)
(Serial, fragments: ALL)
Lower Index Filter:
informix.intpro.sqhumanoinst = informix.huminst.sqhumanoinst
Other Join Filters: informix.intpro.sqhumanoinst =
informix.huminst.sqhumanoinst
NESTED LOOP JOIN
6) informix.hum: INDEX PATH
(1) Index Name: informix. 101_10
Index Keys: sqhumano (Serial, fragments: ALL)
Lower Index Filter:
informix.huminst.sqhumanoinst = informix.hum.sqhumano
ON-Filters:informix.huminst.sqhumanoinst =
informix.hum.sqhumano
NESTED LOOP JOIN(LEFT OUTER JOIN)
7) informix.inst: INDEX PATH
(1) Index Name: informix. 205_902
Index Keys: sqhumanoinst (Serial, fragments: ALL)
Lower Index Filter: informix.huminst.sqhumanoinst =
informix.inst.sqhumanoinst
ON-Filters:informix.huminst.sqhumanoinst =
informix.inst.sqhumanoinst
NESTED LOOP JOIN(LEFT OUTER JOIN) |
