|
View previous topic :: View next topic
|
| Author |
Message |
kanha
New User
.jpg)
Joined: 05 Nov 2005
Posts: 28
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
kanha
New User
Joined: 05 Nov 2005
Posts: 28
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
|
|
