index usage or not

[randy]

Hello people,
I need some understanding on these 2 questions.

Question 1:
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
column

select emp_name, order_num from emp_table
WHERE
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?

Question2:
If create an index on the status coulmn
and I have status != in my where clause, would the index be used or not

[tamilselvan]

!= will not induce index look up.

[chrisrlong]

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.

- Chris

[stmontgo]

as noted the optimizer may not consider
indexes where != is employed.

as an alternative consider the following

where col1 != 'A'

instead try

where (col1 > 'A' or col1 < 'A')

steev

[jmodic]

Originally posted by chrisrlong
As Tamilselvan noted, a != will never use an index.

Well, to be precise, I would add: "... unless there is a bitmap index on that column".

Although this remark is probably not very relevant for the scope of the original question.

[chrisrlong]

Good catch, as always.

- Chris