DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How To Force Oracle To Use Index On The Update Statement???

  1. #1
    Join Date
    Oct 2003
    Posts
    312

    How To Force Oracle To Use Index On The Update Statement???

    if oracle doesn't pick up the index on the update statement, what else can you do to force Oracle to use index beside the hint????
    thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    give it some good stats - have you done that. There is always the chance that using an index is not the best idea

  3. #3
    Join Date
    Oct 2003
    Posts
    312
    I have

    I run into the issue refer to my other threads, I have two instances exactly the same on structure with slighttly difference on data. the other database oracle pick up the index fine and this instance doesn't pick the index. I use oracle hint on other code but for update statement you can't use hint from what I understand.

    any ideas???

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Where do you get the idea that you can only use hints for SELECT statements? You can use hints for UPDATE and DELETE statements (and one hint for INSERTs even!) as well as well.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Oct 2003
    Posts
    312
    I am not clear on this, could someone please explain to me. If I use Oracle hint and it will pick up the index, how come the cost is much higher than the cost if oracle do FTS???? I read about first_rows hint and all_rows hint and I am not too clear, how come first_rows sometime worst than all_rows cost????

    thanks

  6. #6
    Join Date
    Jun 2000
    Posts
    295
    Comparing cost of two queries make no sense.

  7. #7
    Join Date
    Oct 2003
    Posts
    312
    could you please explain???? I am trying to lean

    thanks

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by learning_bee
    I have two instances exactly the same on structure with slightly difference on data.
    It's not just differences in the data that matter. Other issues that Oracle considers are ...

    * The order of the data (as judged by the clustering factor on the index)
    * What the statistics say about the index and the table, as opposed to what the actual data is.
    * Values of initialization parameters.
    * System statistics, if you have gathered them.

    A good place to start learning would be to read the Oracle Performance Guide from http://tahiti.oracle.com, and the Concepts Guide.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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