|
View previous topic :: View next topic
|
| Author |
Message |
saurabh39
Warnings : 1
Active User
.jpg)
Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur
|
|
|
|
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 |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
saurabh39
Warnings : 1
Active User
Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
|
|
