index not used
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: index not used

  1. #1
    Join Date
    May 2000
    Location
    dallas,tx,usa
    Posts
    32

    index not used

    This one baffles me.


    I have two databases on one the index I like is getting used on the other it uses a different index.

    Query-

    Select ...
    from ...
    where ...
    and record_date > sysdate - 10
    order by upper(name)



    I have a FBI on name and another index on record_date.
    In one database
    (lets call it Database1) the query uses the date index
    (on the other Database2) it uses the FBI.
    Infact it seems to like the FBI on the second database most of the time ; even when the query changes to have a different filter condition that is indexed.


    The dataset is identical
    Both database were analyzed.
    Both have same init parameters
    Database 1 is on 9.2.0.1
    Database 2 on 9.2.0.5


    Any help is greatly appreciated
    thx
    Last edited by rnv; 03-11-2005 at 12:59 PM.

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Does both the database have the same set of records?
    anandkl

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi,
    Sorry i missed the your comment
    "The dataset is identical"

    regards
    anandkl
    anandkl

  4. #4
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Try searching metalink for changes to the CBO between the two versions you are using.
    Assistance is Futile...

  5. #5
    Join Date
    May 2000
    Location
    dallas,tx,usa
    Posts
    32

    bug in 9.2.0.5

    Note:272202.1


    ALTER SESSION SET "_SORT_ELIMINATION_COST_RATIO"=5.

    -- The above statement directs the CBO to consider a plan with ORDER BY sort elimination as long as the plan is no more expensive than 5 times the cost of the best known plan (that uses sort).

    -- The parameter can be set to any non-negative integer.

    Thanks folks

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