Partial Index Scan and IN predicate

vs_mnr_it · New User Joined: 25 Feb 2005 Posts: 3

I'm scanning table using INDEX1. This INDEX1 is composed of Col1, Col2, Col3 and Col4. In the present scenario, I'm populating only Col2, Col3 and Col4 to do a SELECT.

Select Row1, Row2.....
From table1
Where
Col2 = :xCol2 and
Col3 = :xCol3 and
Col4 = :xCol4 ;

This is taking lot of time (obvious..). Now, to improve the performance, I'm planning to use IN predicate (with just three values) for Col1 along with other three columns. New query will be

Select Row1, Row2.....
From table1
Where
Col2 = :xCol2 and
Col3 = :xCol3 and
Col4 = :xCol4 and
Col1 IN (Val1, Val2, Val3);

Will this improve the performance?

neeharika · New User Joined: 23 May 2008 Posts: 14 Location: Hyderabad

Iam new to this…But I feel like you can have the advantage of index scan if the columns you are selecting are part of the index

Query is not clear:-
Select Row1, Row2.....
From table1
Where
Col2 = :xCol2 and
Col3 = :xCol3 and
Col4 = :xCol4 ;

If you are writing a query like

Select * from table1
Where
Col2 =:xCol2 and
Col3 =:xCol3 and
Col4 =:xCol4;

A table scan will be done even if there is an index defined on the columns col1, col2, col3 and col4

Can you be clearer like how many columns are there in the table? And what are you trying to select?

dbzTHEdinosauer · Posted: Fri Jul 11, 2008 1:49 pm

it is either an index scan or a table scan, but you do not scan a table using an index.

ashimer · Posted: Fri Jul 11, 2008 2:13 pm

neeharika · New User Joined: 23 May 2008 Posts: 14 Location: Hyderabad

Hi Ashimer,

Iam not talking about the query he has written.
/*
Select * from table1
Where
Col2 =:xCol2 and
Col3 =:xCol3 and
Col4 =:xCol4;

A table scan will be done even if there is an index defined on the columns col1, col2, col3 and col4
*/
But I have explained by an example that if we do a Select *, a table scan will be done and you can see the same in the Visual Explain...
There you can even see the optimised access path...

ashimer · Posted: Fri Jul 11, 2008 3:17 pm

No Neeharika, y shud DB2 do an table space scan when you have cols from your index in your where clause ... does it have to check each and every row for the condition to get satisfied ?? no right ???

Here Select * means you need to get all the cols ... now as i said indexes are arranged as a B tree ... and here you have the pointers to the actual data or data pages ... Db2 traverses thru the tree till it finds a matching index specified in the WHERE clause and then use the pointer to the data ... now y is a table scan required over here ... wht is making you think that the o/p of visual explain is giving a table scan ?

neeharika · New User Joined: 23 May 2008 Posts: 14 Location: Hyderabad

Ashimer,

I missed out completely that there is a where clause here...

When you don't have a where clause and doing a simple "select * from table" it will scan the entire table...

Thanks for correcting me...

Suresh Ponnusamy · Posted: Fri Jul 11, 2008 7:46 pm

Hi
-------------------------------------------------------------------------------------
Select Row1, Row2.....
From table1
Where
Col2 = :xCol2 and
Col3 = :xCol3 and
Col4 = :xCol4 ;

This is taking lot of time (obvious..). Now, to improve the performance, I'm planning to use IN predicate (with just three values) for Col1 along with other three columns. New query will be

Select Row1, Row2.....
From table1
Where
Col2 = :xCol2 and
Col3 = :xCol3 and
Col4 = :xCol4 and
Col1 IN (Val1, Val2, Val3);
-------------------------------------------------------------------------------------

There is no difference between the first query and second query. DB2 automatically avoid the IN clause since the same 3 coulmns are given in the WHERE Clause.

You can try the below one.

If Col1 is alphanumeric use > Spaces

Select Row1, Row2.....
From table1
Where
Col1 > ' ' and
Col2 = :xCol2 and
Col3 = :xCol3 and
Col4 = :xCol4

If Col1 is alphanumeric use > Spaces

Select Row1, Row2.....
From table1
Where
Col1 > 0 and
Col2 = :xCol2 and
Col3 = :xCol3 and
Col4 = :xCol4

Let us know if this improves your query performance.

ashimer · Posted: Fri Jul 11, 2008 7:59 pm

Suresh,

There is difference b/w the 2 queries ... the first one is a non-matching index scan while the 2nd is a matching index scan ... DB2 will not avoid IN clause ...

Suresh Ponnusamy · Posted: Fri Jul 11, 2008 8:07 pm

Oops.. I am sorry.. I thought he has not used Col1 in the query..

Yes you are right..

But I would suggest if the Columns order in WHERE Clause is as the column in the INDEX, that will give better performance.

If the Index is defined as the below order

Col1, Col2, Col3, Col4 then WHERE clause should be in the same order.

Thanks Ashimer for correcting me,..

ashimer · Posted: Fri Jul 11, 2008 9:09 pm

You are welcome Suresh