|
View previous topic :: View next topic
|
| Author |
Message |
jerryte
Active User
Joined: 29 Oct 2010
Posts: 202
Location: Toronto, ON, Canada
|
|
|
|
I have created an sql to be run once a day for a report. It will extract data from 5 tables using an inner join. Is there a way I can tell db2 which table to do a scan on?
Table A has 33M rows
Table B has 10M rows
Tables are joined using a single column. An index exists on both tables for the column.
The sql has extra predicates for each table so that it can't use index only. It will have to read a table page to satisfy it. Once the predicate for table A is satisfied the number of rows selected will be about 50K. This will mean a small subset of rows are needed from table B.
Table A is a logical child of table B. Therefore every row on A has a match on B.
An explain of my sql shows that db2 will do a tablespace scan on B and then join the results to table A. The predicate is coded in such a way that once this is done it will then end up selecting every row on table A anyway. Thus it will end up reading every page of both tables.
If I can tell db2 to scan table A it can then use the index to read only the matching rows on table B.
I looked in the sql reference guide but I couldn't see any syntax for this. Any suggestions? I can't add an index as this would impact the performance of the online programs. |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
put ( or 0=1) on the extra criteria of table B
Verify :
- there is an index on B for the join key (which probably is there, because B is child of A).
- as many as possible fields of the index are specified in the join condition
-Statistics have run on both tables |
|
| Back to top |
|
|
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
| Back to top |
|
|
|
|
