Index not being accessed ... why?

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...

dick scherrer · Posted: Thu Oct 07, 2010 9:36 pm

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.

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..

dick scherrer · Posted: Thu Oct 07, 2010 11:15 pm

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. . .

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?

Craq Giegerich · Posted: Fri Oct 08, 2010 12:30 am

Are the DB2 statistics for the table & indexes current.

dick scherrer · Posted: Fri Oct 08, 2010 1:00 am

Hello,

ovreddy · Posted: Fri Oct 08, 2010 1:34 am

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

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!

GuyC · Posted: Fri Oct 08, 2010 3:29 pm

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.

dick scherrer · Posted: Fri Oct 08, 2010 7:39 pm

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. . .

GuyC · Posted: Fri Oct 08, 2010 7:50 pm

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.

dick scherrer · Posted: Fri Oct 08, 2010 8:55 pm

Hi Guy,