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
 

 

Composite Index - How Queries Work for Multiple Predicate

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

Active User


Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur

PostPosted: Mon Feb 01, 2010 8:44 pm    Post subject: Composite Index - How Queries Work for Multiple Predicate
Reply with quote

Hi All,

I was reading Gabrielle Wiorkowski book on DB2 Performance.
In Composite Index section,
It is writtent - A Matching index scan can be done on all columns of a composite index if equal predicates are used in the where clause
Code:

WHERE SN = 'S1'
AND     PN = 'P1'
AND     JN = 'J4'

The Column SN,PN & JN are the primary keys and has a composite index defined on those.

But in earlier section it has been said,
in Case a Query is
Code:

WHERE SN = 'S1'
AND     PN = 'P1'

In this case there are two index one with SN and other with PN


So in this case a matching scan will be done for SN and all the rows will be fecthed. Then MAtching Scan will be done for PN. Now RIDs for both the set will be anded and value will be obtained.

So basically each predicate is evaluated separately and then RIDs are Anded or ORed.

This should happen for Composite Index also. RID for each of SN,PN & JN should be obtained separately and then Anded. And while fetching the RIDs SN will be a matching scan, while the other two should be non matching scan.

Please let me know, If I am clear and also if there is some gap in my understanding.
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Feb 01, 2010 9:26 pm    Post subject:
Reply with quote

Why should it happen for composite index also?
There is absolutely no reason for it.

When looking for saurabh39 in a telephone directory you go straigth to SAU. You don't go looking for all names with 'S' in first position, then all names with 'A' in second position and then start anding.
Back to top
View user's profile Send private message
saurabh39
Warnings : 1

Active User


Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur

PostPosted: Mon Feb 01, 2010 9:58 pm    Post subject: Reply to: Composite Index - How Queries Work for Multiple Pr
Reply with quote

Hi GuyC

Thanks for Quick Reply.

Please correct me, if my interpretation is wrong -

Suppose I have 3 columns with Value SAU,RABH & 39.

Case 1 -
I have 2 index defined on first two column.In this case a matching search will be doen to get SAU and then RABH will also be found with matching index search, but for 39 it will be table space scan since no index is defined.

Case 2 -
I have a composite index defined on the three columns.
As per your statement since we have indexed column, DB2 will directly search for SAURABH39, rather than searching thrice as in first instance.

Please let me know, If my understanding is correct.

Thanks,
Tushar
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Feb 02, 2010 2:20 pm    Post subject:
Reply with quote

Case 2 is correct.
Case 1 depends on which accesspathDB2 chooses and that depends on the cardinality and clusterratio.

1st possibility) A normal 1-matching column indexscan :
- DB2 positions himself on Index (SAU) and fetches all rows until (SAV)
- in each row DB2 will compare for RABH and 39
- when qualify then return row to program

2nd possibility) multi index intersection
- all RIDs of SAU are read out of index 1
- all RIDs of Bath are read out of index 2
- RIDs sorted are ANDed
- for only mutual RIDs the row is fetched and compared to 39
- when qualify then return row to program


There is no seperate accesspath for each criteria, DB2 tries to choose the best accesspath.

Only Multi-IX Accesspath and star joins try something similar.
Others are straightforward : choose the best index and go with that.
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 Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts Converting multiple VB files to FB fi... Viswanath Reddy JCL & VSAM 6 Mon Aug 08, 2016 11:49 pm
No new posts Execute multiple DB2 Load commands in... faizm DB2 4 Wed Aug 03, 2016 12:53 pm
This topic is locked: you cannot edit posts or make replies. JCL MULTIPLE STEP EXECUTION QUERY Susanta JCL & VSAM 18 Sat Jul 30, 2016 1:17 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