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.
FROM GK5B.STUDNT A,
WHERE A.NAME = ?
AND A.ROLL_NO = ?
AND A.ROLL_NO = B.ROLL_NO
AND B.STATE = 'TN'
short : the indexes are fully used. It's just isn't enough to get all the data you ask.
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.