Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1281
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: 1281
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 ejck thru rexx to multiple jobs insid... Susanta All Other Mainframe Topics 5 Tue Sep 19, 2017 1:39 pm
No new posts PLI - printing from multiple programs Pedro PL/I & Assembler 8 Fri Sep 08, 2017 6:36 am
No new posts Confusion b/w index and subscript Deepak kumar25 Mainframe Interview Questions 7 Thu Aug 31, 2017 6:50 am
No new posts Repeat part of record multiple times Learncoholic DFSORT/ICETOOL 4 Tue Aug 29, 2017 11:33 am
No new posts Match two files using multiple keys a... santoshks1987 SYNCSORT 11 Fri Aug 18, 2017 10:50 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us