Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2001

    Unanswered: Explain question

    I am now doing SQL tunning, although there will have several millions of recrods after put into production, we do have ten records right now, even I have built a index, oracle does not use it coz of smaller size of the table, oracle prefers to use table scan.

    Is there any option on explain to specify the size of table even thought the table size right now is very small.

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    If I understood you well, in test environment you work with a small table (10 records), but in production it is expected that this table will have millions of records.

    Optimizer will choose the best approach to execute a query. I *think* that it is not possible to specify future table size. However, you could use a HINT to order Oracle to use an index, although optimizer might decide differently. For example,

    SELECT /*+ index (e idx_emp_empno) */ e.empno, e.ename
    FROM emp e;

    will tell Oracle: "Don't do full table scan. Use index instead."

  3. #3
    Join Date
    May 2004
    Dominican Republic
    The optimizer makes it choices based on what statistics the table/indexes has, therefore, if you are collecting statistics for these tables, you will always end up with full scan if the tables are small, unless of course you hint the sql as litlefoot said.

    Try setting estimated statistics for these tables from production and use
    dbms_stats.set_table_stats to set these. You could also export the statistics from production to test if you will.

Posting Permissions

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