index not used for negative numbers
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: index not used for negative numbers

  1. #1
    Join Date
    Jul 2010
    Posts
    8

    index not used for negative numbers

    hi all,

    where condition of select query uses negati

    i am searching by negative number on a particular table , in this case select query scans full table
    index is not utilised

    If i search by positive number on same column same table
    index used

    plz reply

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Thanks for sharing the information. What is your question here??

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Jul 2010
    Posts
    8
    Hi all,
    how can i used non unique index on column which already has non unique index
    but it doesnt use

    plz reply

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    how can i used non unique index on column which already has non unique index
    but it doesnt use
    I am sorry, your question is not clear to understand what exactly your issue is. Could you please be more elaborate?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Jul 2010
    Posts
    8
    Hi all

    select query on employee table which has non unique index on status column.

    select * from employee
    where status = -1;

    In above query full table scan happened

    select * from employee
    where status = 1;

    In above query index range scan happened

    plz provide the solution , i want to use
    select * from employee
    where status = -1;

    thx

  6. #6
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    as this is the simple query, based on number of rows returning from the query, optimizer will decide whether to use FTS or Index scan.

    If you want to force the optimizer to use Index always they use Index hint.

    Also, could you please post the result from the following queries?

    desc employee;
    select count(*) from employee where status=1;
    select count(*) from employee where status=-1;

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  7. #7
    Join Date
    Jul 2010
    Posts
    8
    [LEFT]desc employees
    Name Null Type
    EMP_ID NOT NULL NUMBER
    EMP_EMP_CODE NOT NULL VARCHAR2(16 CHAR)
    EMP_PRODUCT_ID NUMBER(5)
    EMP_STATUS VARCHAR2(10 CHAR)
    EMP_DELETED NUMBER
    EMP_CREATED_DATE NOT NULL TIMESTAMP(6)
    EMP_CREATED_BY NOT NULL VARCHAR2(16 CHAR)
    EMP_MODIFIED_DATE TIMESTAMP(6)
    EMP_MODIFIED_BY VARCHAR2(16 CHAR)

    select * from employee
    where status = -1;
    30

    select * from employee
    where status = -1;
    521

  8. #8
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Quote Originally Posted by mitpat121 View Post
    [LEFT]
    select * from employee
    where status = -1;
    30

    select * from employee
    where status = -1;
    521
    different counts with same status ?? please post the actual query that you have executed.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  9. #9
    Join Date
    Jul 2010
    Posts
    8
    Hi
    sorry
    correct display is as follows
    select * from employee
    where status = 1;
    30

    select * from employee
    where status = -1;
    521

  10. #10
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    in either case it should not use Index. However, if you still want to use index by force use index hint.

    Also, gather stats on this table. You might have stale stats on it.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

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