| View previous topic :: View next topic |
| Author |
Message |
danm
Intermediate
Joined: 29 Jun 2004
Posts: 170
Topics: 73
|
 Posted: Thu Apr 29, 2010 10:05 am Post subject: Access path |
 |
|
| I am joining 3 tables (all of them have index). Explain shows ACCESSTYPE for table1 and table3 are "I", while it is "R" for table2. All tables has a 9 character column (COL1) as the first column in the index. I try different tricks (e.g. for optizime 1 row) and table2 always uses table scan. The only way I can force index scan to be used on table2 is to include "AND Tablen.COL1 between '000000000' and '999999999'" in the where clause (n = 1, 2 or 3). What else can I try? |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Apr 29, 2010 10:47 am Post subject: |
|
|
danm,
Alter table 2 to "Volatile cardinality" and it will force the INDEX scan. By declaring the table volatile, the optimizer will consider using index scan rather than table scan. The access plans that use declared volatile tables will not depend on the existing statistics for that table. check this link
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/dsnagj11/5.7.8.4?
btw if you do want to use a hint , a simpler option is
| Code: |
Index_col > CHAR('0')
|
Kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
