CODEWITHSUNDEEP
sqljoinindexingdb2varchar
Clarkey
Clarkey

Reputation: 1581

DB2 varchar index join

For DB2...

table1.a is varchar(30) and has an index on it. table2.b is varchar(45) and has an index on it.

Will table1.a = table2.b use the index on table1, table2, or both?

Although it would seem obvious that it should use both indexes, I'm asking because I believe on sybase, this would only use one of the indexes (I'm not sure which one).

I fully intend to test this out, but I am unable to presently.

Upvotes: 1

Views: 591

Answers (2)

PatrickP61
PatrickP61

Reputation: 76

Use EXPLAIN on your query to determine what indexes it is using. For more information see this link:

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2z10.doc.perf/db2z_captureexplaininfo.htm

Upvotes: 1

Adam Musch
Adam Musch

Reputation: 13583

It's been my experience on DB2 for Z that it won't, as data types must be identical -- the data type, precision, scale, and nullability of a column must match for joins to use them.

Upvotes: 1

Related Questions