Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2004
    Posts
    3

    Unanswered: Optimizer is not using the index

    Hi
    I'm using Oracle 9.2 and I have three tables with 1Mio. Tupels each. Statistics were collected with DBMS_STATS.

    I join the three tables like SELECT * FROM r,s,t WHERE r.a=s.a and s.d=t.d.
    On each Column involved in the join is an index, but the optimizer always uses a hash join and never the indexes. Even if I set OPTIMIZER_INDEX_COST_ADJ to 1, OPTIMIZER_INDEX_CACHING to 100 and PGA_AGGREGATE_TARGET to 10MB. (I know, not very sensible.)

    With hints to use the index or with the rulebased optimizer the Query is 10 times faster.
    Is there a possibility that the optimizer uses the index without hints?

    Thanks for your help
    Tom

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Is it possible that your indexes have "skewed" data ??? ie,
    100 rows with index of STATE...
    Say 90 rows are in state 'NY' ...
    Oracle will not use this ...

    You may need to analyze you indexes for the data they contain ...
    If they are skewed, you may need histograms...

    HTH
    Gregg

  3. #3
    Join Date
    Oct 2004
    Posts
    3
    Thanks for your answer Gregg.

    No, the index makes sense. Each entry of the index appears approx. 10 times.
    And with rulebased optimizer (nested loop with index) the query is very fast.

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    what optimizer mode are you running? CHOOSE? If so, try FIRST_ROWS (either hint, alter sess, or change init.ora)
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    please trace your query and post the TKPROF output
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Dont forget that if your query is joining all the rows in all the tables then oracle sometimes thinks its more efficient not to go in on the index and go for the full table scans instead. If you had some criteria which restricted the rows returned to say just 15% (rough figure) of the full table then it is much more likely to use the index.

    It is also more likely to use full table scans if you have large values for db_file_multiblock_read_count.

    Another thing which might help is to run Dbms_Stats.Gather_System_Stats if you havent already as it gives oracle a much more realistic value for the scattered/sequential IO reads.

    Alan

  7. #7
    Join Date
    Oct 2004
    Posts
    3
    @shoblock:
    If I use FIRST_ROWS the execution plan is different (the indexes are used), but I want to speed up the whole query.

    @The_Duck:
    The File is in the attachement, because I had problems with the spaces between the numbers.
    Last edited by mattmtho; 10-14-04 at 14:47.

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    If you want to optimize your query for getting all the results, you can use ALL_ROWS instead of FIRST_ROWS, it also forces to use the Cost-Based Optimizer.

    With hints to use the index or with the rulebased optimizer the Query is 10 times faster.
    Don't use the RBO : from 9i, Oracle strongly recommends the CBO.

    Now, I would recommend that you either use HINTS to force the optimizer's choice (why wouldn't you ? They exist just for that reason, don't they ?), either check for skewed data just as gbrabham said, and maybe use histograms.

    It may not be the best way, but when I'm sure of the best way to get the result (in most cases at least), I personnally use hints to help the CBO.

    Regards,

    RBARAER

  9. #9
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Sorry, posted twice...
    Last edited by RBARAER; 10-14-04 at 14:47.

  10. #10
    Join Date
    Apr 2004
    Posts
    246
    "Is there a possibility that the optimizer uses the index without hints?"
    The cost based optimizer can use either FIRST_ROWS or ALL_ROWS. CHOOSE will always, by definition, use ALL_ROWS. ALL_ROWS is meant for best-throughput - it's geared toward datawarehouses, and tends to use full table scans or full index scans. FIRST_ROWS is used for best first response - it's more for OLTP systems, and generally prefers indexes. So, FIRST_ROWS is "the optimizer" (so is ALL_ROWS). You want indexes without hints, change the optimizer mode at the database level to FIRST_ROWS (if that makes it use the index - I don't understand "FIRST_ROWS the execution plan is different ...but I want to speed up the whole query."). Warning: doing that will most likely make something else run worse, so just use hints - either a hint to specify the indexes, or the FIRST_ROWS hint.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    what if the join selects ALL the rows from ALL the tables?

    are you saying that you have these three indexes against these columns?
    index on r: (r.a)
    index on s: (s.a, s.d)
    index on t: (t.d)


    what if you run this?
    PHP Code:
    select r.as.as.dt.
    from
     simple
    .rsimple.ssimple.
    where r
    .a=s.and s.d=t.d
    Last edited by The_Duck; 10-14-04 at 17:33.
    - 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
  •