Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2008
    Posts
    45

    Unanswered: Forcing index usage without hint

    Hi, everyone. I'm a newbie at Oracle and I just found out recently that there's a thing called volatile table at DB2 which will force database to use any available index or we could probably say, forcing index scan to be executed whenever possible.

    This way, database will search any available index automatically and use it. I'm wondering if it's possible to do such a thing at Oracle.

    I tried to modify the parameter OPTIMIZER_INDEX_COST_ADJ and db2 multiblock something (I kinda forgot the name), and was able to perform index scanning in some cases (example : all columns have their own index), but I still can't perform it at all cases.

  2. #2
    Join Date
    Aug 2008
    Posts
    4
    The safest way to force index is using the hints in your SQL code because this will override all Oracle's CBO options. In all other situations where you are not forcing the index using the hints, it will depend on the ORacle's CBO calculations and Oracle CBO will choose the least expensive execution path.

    By
    http://www.oraexpert.in

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Ready, Fire, Aim!

    You have better results by using the undocumented parameter
    _make_SQL_fast=true

    You'll hear lots of Urban Legends about Oracle & a few will always be true.

    Please continue to looking for the Silver Bullet, instead of actually RTFM found at:

    http://tahiti.oracle.com
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Why do you want to force an index scan?

    An index scan is not necessarily faster than e.g. a full table scan. But that depends on the query, the joins involved, the number of rows returned and some other things.

    Please show us your table definition, the indexes defined, the query you are running and the execution plan for that query.

    Without that information it will not be possible to actually say if an index scan is indeed faster

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    To back up Shammat's comments: in 99 cases out of 100 (at least) Oracle works best if you:

    1) index all tables appropriately (i.e. create primary and unique keys on all tables, index all foreign keys, and index any other non-unique columns likely to be searched on e.g. person surname

    2) gather optimiser statistics on all tables

    then the Cost Based Optimiser (CBO) will produce excellent plans. There is no sense it forcing Oracle to use an index for every table access. Index hints should only be used in exceptional cases - the less than 1% of cases where for some reason the CBO doesn't do as well as it should. Even then, less prescriptive hints such as FIRST_ROWS should be tried first.

    I don't know whether you have exceptional cases to deal with, but my impression is that you may not, but being new to Oracle think you need to interfere more than than you really do in its internals.

  6. #6
    Join Date
    Aug 2008
    Posts
    45
    I'm just only wondering why DB2 has the function that could force the index usage and Oracle doesn't. Cause, if I were to perform that same operation (the index usage forcing) in DB2 as in Oracle, it means, that I can't, right ??

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by linakichi
    I'm just only wondering why DB2 has the function that could force the index usage and Oracle doesn't. Cause, if I were to perform that same operation (the index usage forcing) in DB2 as in Oracle, it means, that I can't, right ??
    No, you can force an index usage in Oracle.
    But in 99% of the cases (especially for someone who is new to Oracle) this is slower than the execution plan that Oracle has chosen.
    I would be very surprised if that wasn't very similar with DB2

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by linakichi
    I'm just only wondering why DB2 has the function that could force the index usage and Oracle doesn't. Cause, if I were to perform that same operation (the index usage forcing) in DB2 as in Oracle, it means, that I can't, right ??
    You admit to not knowing Oracle well.
    Some may wonder how well you know DB2.

    http://publib.boulder.ibm.com/infoce...n/t0005308.htm

    "consider using" according to the Fine Manual, is quiet different from "force"!

    "To prevent this, you should consider declaring the table as volatile using the ALTER TABLE statement. By declaring the table volatile, the optimizer will consider using index scan rather than table scan."

    It appears the DB2 optomizer is as intelligent as Oracle's & smarter than some database (ab)users.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Aug 2008
    Posts
    45
    Thank you, everyone, for the replies. I forgot to mention that I'm new on both Oracle and DB2, so, sorry for my misunderstandings.

    So, the volatile just considers the index usage, right ? So, evenif there's an available index, it won't be used if the index scan's cost is higher than the cost of table scan, right ??

    Quote Originally Posted by shammat
    No, you can force an index usage in Oracle.
    I just wonder (without considering performance and cost), how Oracle force the index usage without using hints ?? Is there any command that could perform this operation ? Again, I just want to know.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by linakichi
    So, the volatile just considers the index usage, right ?
    I have no idea what you mean with "the volatile",

    So, evenif there's an available index, it won't be used if the index scan's cost is higher than the cost of table scan, right ??
    Exactly. An in most of the cases the optimizer is right.

    I just wonder (without considering performance and cost), how Oracle force the index usage without using hints ?? Is there any command that could perform this operation?
    Oh. I overlooked the "without using hints" part. But I think switching e.g. to the rule base optimizer (which might not be possible at all in newer versions, but I might be mistaken) could give you a way to force the index scan.

    Maybe fiddling with the statistics (locking them, importing them from a system where an index scan is cheaper than a FTS, something like that) could achieve that.

  11. #11
    Join Date
    Aug 2008
    Posts
    45
    Hi, all, I've found an answer (probably) on how to force index usage, even though it probably cost more than table scan

    http://www.geekinterview.com/question_details/20968

    I haven't tried this, but if there's anyone who knows different way to perform such a function, please, let me hear it

  12. #12
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Chr(0)

    If an index exist and you want to force without using "hints" the solution we used in version 8i is:
    Code:
    select * from MyTable
    where index_col > CHR(0);

    PS: Better is to follow all above suggestions and to let optimizer decide.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  13. #13
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by linakichi
    Hi, all, I've found an answer (probably) on how to force index usage, even though it probably cost more than table scan
    Maybe I'm missing something, but this does not make any sense to me...
    I cant't think of any reason why I would want to force Oracle to use the slower execution plan

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, it is possible that the CBO may predict that plan A is slower than plan B when in fact it is not: that's what hints are for (usually). However, to set parameters like optimizer_index_cost_adj just to force use of one index on one query would be the work of a lunatic!

  15. #15
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking bind peek

    Quote Originally Posted by andrewst
    Well, it is possible that the CBO may predict that plan A is slower than plan B when in fact it is not: that's what hints are for (usually). However, to set parameters like optimizer_index_cost_adj just to force use of one index on one query would be the work of a lunatic!
    One of the reasons the CBO may change the execution plan is the 'bind variable lookahead' or 'bind variable peek' where after building an execution plan and before executing the query CBO takes a look into the values of the bind variables and may decide FTS (or some other plan) is better.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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