db file sequential read

[dbbyleo]

Are the indexes NGUSRFLD6_IND and NGUSRFLD2_IND function based?

Yes, there are, but they are also compound (correct my term usage) indexes. The indexes are based on the NGPROJGUID field and uses UPPER function on the NGUSRFLD6 and NGUSRFLD2 field, respectively.

After looking over this whole thread, I realized I was over looking something... something I should have asked about cuz I don't understand why its happening....

In my first post, I said response time is poor of this one query. Trace shows that there's a lot of db file seq read waits. Looking back now, I see it's using the NGTAG_PK index (as shown in the row execution) and it is having to read many many blocks to return just 31 rows.

After that, I said I realized that the indexes on the (base) table being queried did not have stats, so I got stats for the NGTAGS_PK index. I reran the query and it had significant improvement. WHAT I DIDN'T REALIZE was that it NOW using a different index - NGUSRFLD2_IND.

Question 1: When I analyzed only the NGTAGS_PK index, which improved response time, did the stats tell Oracle that NGTAGS_PK was not the best index to use, so instead it used NGUSRFLD2_IND?

Then I went ahead and got stats for the other indexes (of the same table) missing stats. When I reran the query, it is slow again. And again, WHAT I DIDN'T REALIZE was that it NOW using a different index - NGUSRFLD6_IND.

Question 2: Why did it even bother using NGUSRFLD6_IND when it was not even part of the query?? NGUSRFLD6_IND does have the NGPROJGUID indexed (and so did NGTAGS_PK), is this why Oracle would even remotely think to use this index?

It seems that for this qeury, the best index to use is NGUSRFLD2_IND and it makes sense (now) - It indexes by NGPROJGUID and UPPER("NGUSRFLD2").

Question 3: Why doesn't it use NGUSRFLD2_IND all the time, even after I got stats for all the indexes of the table??