-
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
-
!= 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.
- Chris
-
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
I'm stmontgo and I approve of this message
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Good catch, as always.
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|