View previous topic :: View next topic
|
Author |
Message |
Nirav721
New User
Joined: 11 Feb 2008 Posts: 53 Location: NJ
|
|
|
|
I have a table that has two indexes ...
First one is on Column 2 of the table
Second one is on Columns 1, 2 , 3 .
Now, when I am declaring my cursor with hold for Select... I have columns 1,2,3 listed in my WHERE clause... but yet, DB2 is deciding to scan the entire table space...
Column 1 has a table partition number ... This table has a total of 14 partitions... but only Partition 13 has data in it .. meaning a value of '13' is inside column 1 for all contracts (column 2) ...
The table has around 3 million rows for partition 13 and nothnig else for other partitions..
Can anyone explain why DB2 is deciding not use the index for Col 1,2,3?
Thank you in advance for your insight... |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Because db2 has determined that the table scan will be more efficient than using the index. If the other 13 partitions were loaded with data and there was a wide range of values, the index would most likely have been chosen. |
|
Back to top |
|
|
Nirav721
New User
Joined: 11 Feb 2008 Posts: 53 Location: NJ
|
|
|
|
Hi ... thank you.. Is there any way to 'force' db2 to go againest the index? Or any other way for the index to be defined (order of the columns inside index??)
The reason why I am asking this is that it is taking considerably long time to look up a single contract using table scan...
Thanks.. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Using an ORDER BY the columns in the index may do what you want.
Suggest you run an "explain" and work with your dba. There may need to be a change in the database definition. . . |
|
Back to top |
|
|
Nirav721
New User
Joined: 11 Feb 2008 Posts: 53 Location: NJ
|
|
|
|
Thanks again for quick reply .. when you say ORDER BY, you mean inside my SELECT statement? If yes, I did try that but it did not help...
I just had my DBA add additional index in order of COL 2, COL 1, and COL 3... ? Is this efficient? I am not sure, but it works... what do you suggest? |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
Are the DB2 statistics for the table & indexes current. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
when you say ORDER BY, you mean inside my SELECT statement? If yes, I did try that but it did not help... |
Yes, inside the SELECT - in the same order as they are named in the key. . .
It may help if you show the different SELECT statements that were tried. . . |
|
Back to top |
|
|
ovreddy
Active User
Joined: 06 Dec 2004 Posts: 211 Location: Keane Inc., Minneapolis USA.
|
|
|
|
Hi,
Do you have additional columns in your WHERE CLAUSE as well. If so put the 3 indexed columns with in bracket that might help.
Example WHERE (col1=val1 and col2=val2 and col3=val3) and (col4=val4 or 0=0);
0=0 forces not to use col4 for searching database. This way we can disqualify the indexed columns from being used in search.
If you are running a batch program then RUNSTATS and rebinding would help.
Thanks,
Reddy |
|
Back to top |
|
|
Nirav721
New User
Joined: 11 Feb 2008 Posts: 53 Location: NJ
|
|
|
|
Thanks everyone .. yes, the stats are all up-to-date. The DBA just blamed other partitions not having data and that was throwing DB2 off track in not using the index.. so adding index starting with non-partition column forces it to use index now...
I find it odd that DB2 is very strict..I am only 'hoping' that once we get to production, it behaves as we think it should..otherwise, long days ahead! |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
with table partitioning, db2 doesn't need an index to determine which partitions to scan.
so if col2=13 is only partition 13 then a select where COL2 = 13 can be a tablescan , but will be a tablescan of only part 13 .
See column page_range of your explain table. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hi Guy,
The problem was that db2 chose to scan the entire table rather than directly reading the rows matching the select. Adding the new key provided direct access rather than the scan.
One concern might be performance impact with this new key. If rows are added once, seldom or never updated, and used often to read data, it should not be a problem. Well, imho. . . |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
just want to make sure that "a complete tablescan" is really what DB2 decides,
Sometimes people misinterprete the explain due to the reason I explained.
a select ... where col1=? and col2 =? and col3=? (direct access) with correct stats , not using an index col1,col2,col3 is highly unlikely.
Maybe it is not col1 = but something like col1 between / col1 > / col1 not in() , maybe it is not stage1 but a stage2 predicate
Without the actual where clause being posted it is highly speculative. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hi Guy,
Quote: |
just want to make sure that "a complete tablescan" is really what DB2 decides, |
I may have read too much into the earlier posts - it was my understanding that originally the entire partition was being scanned (and taking a long time) and adding the new key caused the query to run quickly. |
|
Back to top |
|
|
|