Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    bangalore
    Posts
    14

    Unanswered: Index not being used...

    Hi,

    Having a problem with oracle 9i. We have a table which is indexed on status. If the query has Status ='A', then it is taking the index and repsonding in no time. But if i query with Status <> 'U' oracle is going for a full table scan. Does oracle ignores index if the 'not equal to' is used in the query?


    Murali
    Murali Mohan Rao. M

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    No, It is not like that.

    It depends on the OPTIMIZER_MODE. If it is RULE then it will definetely use the index on the table regardless of the no. of rows returned by that query.

    But if you have OPTIMIZER_MODE set to cost based (ALL_ROWS or FIRST_ROWS) AND You have statistics available in the database then optimizer will first look at to the statistics and calculate the costs of your query. If it turns out 14-20% ( not sure of exact figure) of the total rows then It will use the Index to retrieve the rows. Else it performs FTS (Full table Scan).

    HTH
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Jan 2004
    Location
    bangalore
    Posts
    14
    Hi,

    Actually forgot to mention about the OPTIMIZER_MODE. It is set to RULE only.... Even then it was not taking the index set.


    Murali.
    Murali Mohan Rao. M

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Is there any STORED OUTLINE created for this SQL statemnet?

    If htis is the case then Regardless of your OPTIMIZER_MODE, it will use the statistics stored with that outline and use that.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Jan 2004
    Location
    bangalore
    Posts
    14
    Hi,

    Thanx for the info....

    Murali
    Murali Mohan Rao. M

  6. #6
    Join Date
    Jan 2004
    Location
    Issy les Moulineaux, France
    Posts
    24
    I don't think index will be used with <>,

    you can try the condtion : status < 'U' or status > 'U'
    And if it doesn't work, you can split the query in two parts, with a union all:

    select ....
    from ....
    where status < 'U'
    union all
    select ...
    from ...
    where status > 'U'

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    try NOT IN ('U')
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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