Quote Originally Posted by PAVB View Post
Shall we understand there are about 100 rows that match condition (id_even='ATC') out of 150,000 rows in the table?

If this is the case an index on id_even should be used - provided you have gathered performance statistics on table and index. Did you "analyzed" tablle and index? how old are those stats?

When no stats are present Oracle assumes normal distribution then FTS is most likely - build a non-unique index on ID_EVEN, gather starts, try again.
PAVB,

No, sorry if I am not clear with my post.

On the table client_sim, there are only 100 rows on the columns ID_EVEN out of 40 million rows. On the column JOB_STATUS, it had the three values from the where clause and they are around 150,000 rows on two values: 'ACTIVE', NACTIVE' and around 20,000 rows on values: 'RUNNING'

I did build index in ID_EVEN and JOB_STATUS seperately and I also update stat using DBMS_STATS.GATHER_TABLE_STATS, but for some reason the index never been used unless I force the hint on the index.

Thoughts?