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
 

 

Query regarding accessing only one index

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

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Tue Sep 27, 2011 1:15 pm    Post subject: Query regarding accessing only one index
Reply with quote

Hi all,

In our installation I see even though columns in where is present in multiple index, queries are only using index & then fetching data pages.

For example:

SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL4=:H AND COL5 = :H

INDEX1:
COL4 ASC

INDEX2
COL5 ASC

In situations like this, any one index is getting used & then it's fetching data pages.

What might be the possible reason? Same is the situation for clustered indexes.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 27, 2011 9:07 pm    Post subject:
Reply with quote

Hello,

Quote:
queries are only using index & then fetching data pages.
Quote:
What might be the possible reason?

The reason is because that is the way it is intended to work. Why might you want to query to use something other than an index?
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Tue Sep 27, 2011 9:45 pm    Post subject: Reply to: Query regarding accessing only one index
Reply with quote

My apologies!

In the above query, COL4 & COL5 are both in where clause & are present in 2 different indexes(index1 & index2); the query should access both the index ideally before fetching other columns from data pages.

But in our installation, it is accessing only 1 index & then it is going to the data pages even for the column which is present in another index???

Below is the explain.

ACCESS NAME: INDEX1 and ACCESS TYPE = I

Hope I am clear this time.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 27, 2011 11:12 pm    Post subject:
Reply with quote

Hello,

Quote:
the query should access both the index ideally before fetching other columns from data pages.
Why do you believe this? Why shoule reslurces be wasted referring to a second index when the first index solves the query?

What you propose would only waste extra resources.

What if there were 6 index entries that would locate the rows. Do you believe that all 6 index pages should be "hit" instead of only 1 and then getting the rest of the columns from the data page?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Wed Sep 28, 2011 1:06 pm    Post subject:
Reply with quote

There is such a thing as Multiple index scan accesspath : plan_table.accesstype = M,MX,MI,MU
In this case you would like M,MI

It is a rather expensive access path and involves several steps
1) collects all the RIDs of the qualifying rows in index1 (col4 = :h) and sort them
2) collect all the RIDs of the qualifying rows in index2 (col5 = :h) and sort them
3) join those two list keeping the intersection
4) retrieve all rows with the remaining RIDs

Only when it involves large quantities and low filtering of col4, col5 this kind of accesspath is interesting.
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts setting new limit Key values for inde... srilata83 DB2 1 Fri Feb 10, 2017 9:24 pm


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