select * from emp where job_type<>'ANALYST';
I have similar query in my project. I have been told to find other way to remove not equal to, because index is not used if you do so.
I have no other filter condition, can I do some work around to fool optimizer?
Advertisement
Answer
I have been told to find other way to remove not equal to,
is generally bad advice. Queries should be written as per the function required. That said, it might be the case that ANALYST occupies the vast majority of the data in your table, and thus you could do something like:
select * from emp where job_type < 'ANALYST' or job_type > 'ANALYST'
which could be transformed internally to a union all on the appropriate index by the optimizer. Or if you knew in advance the list of distinct values, you could have something like
select * from emp where job_type in (...)
or if they were in a parent table, something like
select * from emp where job_type in ( select job_type from valid_job_types here job_type != 'ANALYST' )
on the assumption that EMP is large and VALID_JOB_TYPES is small.
But I would push back and ask for a justification on the request, because using an index is not always the best thing to do