how good is the information from EXPLAIN?

[Michael TEpperis]

Hello,

doing some investigations with the EXPLAIN statement
I wonder about the quality from the plan_table.

for different kind of selects I expected that
an index with two columns is used in a join.
the table ERRORCODE has an index on the columns
SYSTEMCODE, ERRORCODE.

the results in the plantable gives 1 in MATCHCOLS
in both of the following queries.

1)
.....
FROM
COMPANY a
JOIN
RECEIPT b
ON
a.COMPANY_ID = b.COMPANY_ID
JOIN
ERRORCODE c
ON
a.SYSCODE = c.SYSTEMCODE
AND a.ERROR_CODE = c.ERRORCODE

2)
.....
FROM
COMPANY a
, RECEIPT b
, ERRORCODE c
WHERE
a.COMPANY_ID = b.COMPANY_ID
AND a.SYSCODE = c.SYSTEMCODE
AND a.ERROR_CODE = c.ERRORCODE

any ideas? what do I miss?

tia
michael tepperis-von der ohe

[Philip Sherman]

I see this happen all the time when the column definitions for
ERROR_CODE and ERRORCODE are not the same. Frequently one is numeric
while the other is character.

It's always helpful to include table definitions when asking this type
of question.

Phil Sherman
Michael TEpperis wrote:

Hello,

doing some investigations with the EXPLAIN statement
I wonder about the quality from the plan_table.

for different kind of selects I expected that
an index with two columns is used in a join.
the table ERRORCODE has an index on the columns
SYSTEMCODE, ERRORCODE.

the results in the plantable gives 1 in MATCHCOLS
in both of the following queries.

1)
....
FROM
COMPANY a
JOIN
RECEIPT b
ON
a.COMPANY_ID = b.COMPANY_ID
JOIN
ERRORCODE c
ON
a.SYSCODE = c.SYSTEMCODE
AND a.ERROR_CODE = c.ERRORCODE

2)
....
FROM
COMPANY a
, RECEIPT b
, ERRORCODE c
WHERE
a.COMPANY_ID = b.COMPANY_ID
AND a.SYSCODE = c.SYSTEMCODE
AND a.ERROR_CODE = c.ERRORCODE

any ideas? what do I miss?

tia
michael tepperis-von der ohe