Regarding index usage 1

[reachmano]

Have a question regarding index usage
Example of the scenario :
Table A
Column AA Varchar(15) ----> indexed
Column AB decimal(15,2)

Table B
Column BA Char(5)----> indexed
Column BB Decimal (10,2)----> indexed

Will the following join use the index ?
where condition : Column AA = Column BA
and Column AB = Column BB
or will it ignore becuase one is a varchar and the other is a char ?
Also one is a decimal(15,2) and another (10,2) ?

Thanks in advance.

 

[sathyarams]

I think both of them will be used ...

The best way to confirm is to use a Explain Tools to know the right access path ... There are cases(eg, the cardinality of the column) where the optimizer may decide not to use the index ..

Cheers
Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[reachmano]

Thanks for your reply.

But is there any rule that if there is a mismatch in length or different types like(VarChar and Char), the index won't be used ???

 

[sathyarams]

Have not heard anything of this sort ...

Cheers
Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[jimcleve]

Column type matching is required in order for hash joins to take place - I don't think it's necessary for merge and nested loop join.

 

[sathyarams]

Jim .. Thanks for the additional point
Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[gregsimpson]

reachmano,

I think your question relates to Stage 1 and Stage 2 of the data acess and may well have been the case in previous versions of DB2.

STAGE 1 - Evaluated at the time the data rows are retrieved (sargable). There is a performance advantage to using Stage 1 predicates because fewer rows are passed to Stage 2 via the Data Manager

STAGE 2 - Evaluated after data retrieval (non-sargable) via the RDS (Relational Data Services) which is more expensive than the Data Manager

I believe in current versions od DB2 the scenario you mention is now at STAGE 1 evaluation, so you should be okay.

Cheers
Greg