By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,347 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

how good is the information from EXPLAIN?

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.