I need some understanding on these 2 questions.
Assuming the only column indexed in this query below is
emp_id. Should I leave it unchanged or I need
to create an index on SERVICE and the STATUS
select emp_name, order_num from emp_table
emp_id = '112dd2' AND
service = 'running' AND
status != 'A';
NOTE: Status has a check constraint and it can be to be only A or B?
If create an index on the status coulmn
and I have status != in my where clause, would the index be used or not
!= will not induce index look up.
Considering you are selecting from emp_table, it is reasonable to assume that emp_id is the PK, thereby making the index on the field unique. given that, there is absolutely no reason to change the index or add another for the purposes of the statement provided.
Note that if status can only be A or B, then predicate should be AND status = 'B' - always query by the positive where possible. As Tamilselvan noted, a != will never use an index.
as noted the optimizer may not consider
indexes where != is employed.
as an alternative consider the following
where col1 != 'A'
where (col1 > 'A' or col1 < 'A')
I'm stmontgo and I approve of this message
Well, to be precise, I would add: "... unless there is a bitmap index on that column".
Originally posted by chrisrlong
As Tamilselvan noted, a != will never use an index.
Although this remark is probably not very relevant for the scope of the original question.
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Good catch, as always.
Click Here to Expand Forum to Full Width