Forum Stats

  • 3,758,442 Users
  • 2,251,389 Discussions
  • 7,870,190 Comments

Discussions

Oracle stopped using index after table got bigger

Wojtus-J
Wojtus-J Member Posts: 18
edited May 13, 2013 3:14PM in General Database Discussions
I faced a problem, when the optimizer stopped using index while joining two tables.

My DB version is:
11.2.0.3.0 64 bit Enterprise

Currently I am joining two tables (T1 and T2) within MERGE statement on condition like:
T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND T2.DATE_COL = TO_DATE(...)
it's an inner join and all the rows take part in it.

There is an index on T2 on all the above columns. When there is few thousand of rows in both tables the plan is nice, it says it is using NESTED LOOPS with Index, and all the above column are listed in Access Predicates, whereas OPTION is RANGE SCAN - as far as I know it is very good.

The problem came up when those tables grew up to around 25 millions of rows. The plan changed into HASH JOIN where both tables are FULL-Scanned. I have tried different hints (INDEX, USE_NL_WITH_INDEX, OPT_ESTIMATE) but I couldn't make the optimizer to use the index as before. Index is valid, I have gathered the statistics for both tables, and it still stays the same.

Does it mean that HASH JOIN is a better way with such a big tables, or am I doing something wrong?

AD:
I am unable to provide all the details including the query or table structure, If my question lack of important details please let me know and I will try to explain it better.
Tagged:

Answers

  • DK2010
    DK2010 Member Posts: 1,542 Silver Trophy
    Hi,

    You can check this link

    http://richardfoote.wordpress.com/2010/02/16/how-does-an-execution-plan-suddenly-change-when-the-statistics-remain-the-same-in-limbo/
  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    Wojtus-J wrote:
    I faced a problem, when the optimizer stopped using index while joining two tables.
    How did you identify this is a problem?
    Wojtus-J wrote:

    My DB version is:
    11.2.0.3.0 64 bit Enterprise

    Currently I am joining two tables (T1 and T2) within MERGE statement on condition like:
    T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND T2.DATE_COL = TO_DATE(...)
    it's an inner join and all the rows take part in it.

    There is an index on T2 on all the above columns. When there is few thousand of rows in both tables the plan is nice, it says it is using NESTED LOOPS with Index, and all the above column are listed in Access Predicates, whereas OPTION is RANGE SCAN - as far as I know it is very good.
    Sometimes they are good, sometimes they are bad. If they were always the best option, no other option would exist :)

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968
    http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:37453890985588
    Wojtus-J wrote:

    The problem came up when those tables grew up to around 25 millions of rows. The plan changed into HASH JOIN where both tables are FULL-Scanned. I have tried different hints (INDEX, USE_NL_WITH_INDEX, OPT_ESTIMATE) but I couldn't make the optimizer to use the index as before. Index is valid, I have gathered the statistics for both tables, and it still stays the same.

    Does it mean that HASH JOIN is a better way with such a big tables, or am I doing something wrong?
    Not necessarily better, but I would wager based on what you have outlined that yes, in this case the hash joins will be a more efficient execution plan. You would want to confirm that though. You can test the execution of the query with and without hints to get it to do what you need.

    Cheers,
  • Three key elements of your use case are these:
    >
    it's an inner join and all the rows take part in it.

    There is an index on T2 on all the above columns.

    The problem came up when those tables grew up to around 25 millions of rows.
    >
    If ALL rows in T2 are needed Oracle needs to get them from the index alone or from both the table and the index.

    Are ALL columns from T2 used in the query in that index? If not them Oracle still needs to access the T2 table and you said ALL rows are needed. At some point (some number of rows) it is more efficient to just do multi-block reads of the table to get the rows rather than first going through the index.

    Are the table and index stats up to date.

    Even if all columns ARE in the T2 index Oracle might think it is more efficient to just full scan the table.

    You also didn't provide the plan to show which of the tables for the hash join and nested loop joins is the OUTER table and which is the INNER table. If the wrong table is being used for the OUTER table that can make a big difference.
  • Alvaro
    Alvaro Member Posts: 709
    edited May 13, 2013 1:03PM
    1. Check out the execution plan.

    2. Check out statistics of the index.

    3. Check out if you have non -default values for CBO affecting parameters such as: OPTIMIZER_INDEX_CACHING and optimizer_index_cost_adj

    4. After a certain size, it maybe actually cheaper for the CBO to do a full table-scan over an index probe.

    Since the segment keeps growing,it may be that the optimizer now prefers to do a full table scan than an index scan. The reason for this is that full table scans are multiblock reads, and index scans are single reads. If you have a high value of DB_FILE_MULTIBLOCK_READ_COUNT, CBO will be more likely to choose full table scans over indexes for large volumes of data.

    5. You can force the optimizer to use the index through an INDEX(xxx) hint and see if it's actually better, or force a rule based optimizer plan to see if your problem is actually regarding CBO estimating costs with the RULE hint.
  • jgarry
    jgarry Member Posts: 13,842
    This is why there are several ways to lock down execution plans. There are so many variables, including Oracle trying to dynamically evolve plans, that it becomes common for edge cases to be crossed.

    Please see the thread about [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]how to ask a tuning question to see how to figure out what the best plan is, and see the Oracle[url https://blogs.oracle.com/optimizer/tags/sql_plan_management] optimizer blog or numerous other sites for how to handle plan management. (Google: oracle plan management 11g)
  • marksmithusa
    marksmithusa Member Posts: 509
    Make sure your statistics are up-to-date (for the table as well as associated indexes).

    Sometimes, the sample size can result in odd explain plans being generated. If you're unsure, compute the statistics for both tables and indexes (use a sample size of 100%).
This discussion has been closed.