index usage or not
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: index usage or not

  1. #1
    Join Date
    Mar 2001
    Posts
    82

    Exclamation

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    != will not induce index look up.

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Good catch, as always.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width