Regarding Partial Index usage in the query

callkris_cit · New User Joined: 22 Apr 2006 Posts: 44

In the below query in the table GK5B.STUDNT, ROLL_NO, NAME, MARK all present in the INDEX. So its fully using the index.
But in the GK5B.TOWNDET table only ROLL_NO and STATE present in the index, but the TOWN is not present in the index. ( So its partially using the index)

When i use EXPLAIN parameter for the below query

Query Q Collect. Progname Pl M Ac M I T Table
Number Bl (COLLID) (Packg) No T Ty Co O No Owner Table Name
* * * * * * * * * * * *
--------- -- -------- -------- -- - -- -- - -- -------- -------------
110504003 1 ADBL ADBMAIN 1 0 I 1 Y 1 GGDD STUDNT
110504003 1 ADBL ADBMAIN 2 1 I 2 N 2 GGDD TOWNDET

I heard from somebody, the query performance will be decreased because of one of the query partially using the index. Is it true?

Please give some answers.

SELECT A.ROLL_NO,
A.NAME,
A.MARK,
B.TOWN
FROM GK5B.STUDNT A,
GK5B.TOWNDET B
WHERE A.NAME = ?
AND A.ROLL_NO = ?
AND A.ROLL_NO = B.ROLL_NO
AND B.STATE = 'TN'

GuyC · Posted: Thu May 05, 2011 12:05 pm

short : the indexes are fully used. It's just isn't enough to get all the data you ask.

long:
mark and town are not used in the where clause,
so for finding the rows
STUDNT(ROLL_NO, NAME, MARK) is used matching columns=2
TOWNDET(ROLL_NO,STATE) is used matching columns = 2

because MARK is the only extra column of STUDNT you need, and it is in the index. an extra access to the data of STUDNT is not needed. it is "INDEX-ONLY"
TOWN is not in the index, so access to TOWNDET is not "INDEX-ONLY".

This is an extra i/o, but not that it would be a huge overhead.
adding TOWN to the index will:
- make the query index-only
- Possibly change functionality (if it is used as a unique index)
- increase the length of the index thus needing more pages, thus possibly add extra i/o's.

as an extra : it is a bit silly (and cost a tiny bit extra) to select roll_no and name. You already know those.

callkris_cit · New User Joined: 22 Apr 2006 Posts: 44

Suppose in the Mentioned query if i used 0=1 (I.e if i forced the query to use tablespace), whether it will increase the performance.

GuyC · Posted: Fri May 06, 2011 12:08 pm

not likely