Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Partial Index Scan and IN predicate

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vs_mnr_it

New User


Joined: 25 Feb 2005
Posts: 3

PostPosted: Fri Jul 11, 2008 12:35 pm    Post subject: Partial Index Scan and IN predicate
Reply with quote

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?
Back to top
View user's profile Send private message

neeharika

New User


Joined: 23 May 2008
Posts: 14
Location: Hyderabad

PostPosted: Fri Jul 11, 2008 1:47 pm    Post subject: Reply to: Partial Index Scan and IN predicate
Reply with quote

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?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Fri Jul 11, 2008 1:49 pm    Post subject:
Reply with quote

it is either an index scan or a table scan, but you do not scan a table using an index.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Jul 11, 2008 2:13 pm    Post subject:
Reply with quote

Quote:


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



No way ... this will be an index scan ... if you run an EXPLAIN on this query it will give INDEX_ONLY as N ie it has to fetch data from data pages too .... ACCESS_TYPE will be I .... MATCHCOLS will be 0 bcos col1 is not included in the WHERE clause as Index has been created with col1 as the first entry ... indexes are arranged as B trees ... so it has to start from root of the tree onto col1 and then go to col2 ...if col1 is not there then it has to start from leaf pages and traverse right ....

Now if IN is included the accesstype will change to N ..ie Index scan with an IN clause and MATCHCOLS will be 4 ... the greater the MATCHCOLS the better the query is ....
Back to top
View user's profile Send private message
neeharika

New User


Joined: 23 May 2008
Posts: 14
Location: Hyderabad

PostPosted: Fri Jul 11, 2008 2:51 pm    Post subject: Reply to: Partial Index Scan and IN predicate
Reply with quote

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...
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Jul 11, 2008 3:17 pm    Post subject:
Reply with quote

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 ?
Back to top
View user's profile Send private message
neeharika

New User


Joined: 23 May 2008
Posts: 14
Location: Hyderabad

PostPosted: Fri Jul 11, 2008 4:07 pm    Post subject: Reply to: Partial Index Scan and IN predicate
Reply with quote

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...
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Fri Jul 11, 2008 7:46 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Jul 11, 2008 7:59 pm    Post subject:
Reply with quote

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 ...
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Fri Jul 11, 2008 8:07 pm    Post subject:
Reply with quote

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,..
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Jul 11, 2008 9:09 pm    Post subject:
Reply with quote

You are welcome Suresh icon_biggrin.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts setting new limit Key values for inde... srilata83 DB2 1 Fri Feb 10, 2017 9:24 pm
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts CICS TS 5.3 migr after DFHCSDUP UPGRA... Kyle Carroll CICS 6 Wed Aug 31, 2016 10:58 pm
No new posts Several errors during building altern... Andi1982 JCL & VSAM 11 Wed Jul 06, 2016 7:39 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us