Yes, app_no is the primary key. So I have the index for it.
Also I can not mention the > 5 and <1 as I have some char's also.
Basically bellow are the values which I am expecting should be placed under the "not in". Sorry for not mentioning in the previous post. My sql query is,
"select app_no from table1 a, table2 b where a.app_no = b.app_no and
b.stat_f not in ('0','1','2','3','4','5','6','7','8','A','B','C',' D') "
I would say most of the times I need to check whether stat_f is blank, but some times it may be the other conditions.
table1 has around ten million records and table2 also have around ten million records.
And the number or rows it returns may be between hundred to eight thousand.
We already have index for bgck_stat_f also.
NOT IN, NOT LIKE and NOT BETWEEN predicates pretty much preclude the optimizer from using a matching index scan. Always try to use positively formulated predicates.
If you know the complete domain of the possible b.stat_f values, and the total set is only a couple values more than the list given in your NOT IN predicate, you may chose to use IN (...) instead. This way the optimiser will use the matching index scan approach if you have an appropriate index defined. I know this obscures the business logic somewhat, but if performance is a significant issue, one can possibly live with such compromises.
Of course, if this is THE critical statement to run against the b table, clustering on the stat_f field will further improve your situation.