467,151 Members | 929 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,151 developers. It's quick & easy.

how good is the information from EXPLAIN?

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
  • viewed: 1176
Share:
1 Reply
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.

Similar topics

24 posts views Thread by matty | last post: by
22 posts views Thread by JavaJunkie | last post: by
72 posts views Thread by E. Robert Tisdale | last post: by
7 posts views Thread by TAVOSOFT | last post: by
45 posts views Thread by dolphin | last post: by
3 posts views Thread by Lars Johansen | last post: by
23 posts views Thread by tonytech08 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.