1

I execute following two queries on an oracle database:

select * from qsn join plc on plc.fk_qsn = qsn.id where rownum < 48507

which returns result in less than a second and with following execution plan

enter image description here

and

select * from qsn join plc on plc.fk_qsn = qsn.id where rownum < 48508

which takes more than 10 seconds to execute and uses following execution plan

enter image description here

Can anyone help me understand the reason why this strange behavior happens?

2 Answers 2

2

Why query plan changes by number of rows?

If you are going to select 80% rows of the table then full table scan is most efficient and If you are going to select less than 10% rows from the table then reading an index followed by a table access by rowid may be more efficient than a full table scan. Optimizer makes decision according to the statistics of the table on whether to do full table scan or index scan.

SQL> explain plan for select * from hr.employees where employee_id=100;

Explained.

SQL> select * from table(dbms_xplan.display());                  

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1833546154

--------------------------------------------------------------------------------
-------------

| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU
)| Time     |

--------------------------------------------------------------------------------
-------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |     1 |    69 |     1   (0
)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0
)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN     | EMP_EMP_ID_PK |     1 |       |     0   (0
)| 00:00:01 |

--------------------------------------------------------------------------------
-------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=100)

14 rows selected.


SQL> explain plan for select * from hr.employees where employee_id<=206;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   107 |  7383 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("EMPLOYEE_ID"<=206)

13 rows selected.

There are all together 107 rows in EMPLOYEES table of HR schema. When I tried to select all employees which means all rows from employees table optimizer chose full table scan and when I tried to select a specific record it chose table scan by index rowid.

Obviously table scan by index rowid is faster than full table scan.

In your first query the optimizer chose table scan by index rowid which is faster than full table scan which has chose by optimizer in your second query.

Another thing that makes these queries different than each other is physical joining algorithm used by the optimizer, namely Nested Loop Join and Hash Join in your case.

In a nested loops join, we have two tables a driving table and a secondary table. The rows are usually accessed from a driving table index range scan, and the driving table result set is then nested within a probe of the second table, normally using an index range scan method.

In a hash join, the Oracle database does a full-scan of the driving table, builds a RAM hash table, and then probes for matching rows in the other table.

When we dont have optimal schema statistics(column histogram) the optimizer makes an incorrect guess about the cardinality(number of rows returned) of a result set so sometimes optimizer may use nested loop join instead hash join and vice-versa.

For details Database SQL Tuning Guide

1

Full table scans can be much faster over a certain percentage (2-4) of the table size. It appears you have found the edge case where the optimizer switches to full table scanning.

You can use hints to try to force the original plan. You may want to try a "FIRST_ROWS(1000)" hint as a starting point.

WARNING: Trying to outguess the optimizer can cause significantly slower queries. Always test the queries to determine performance. Retest to see if they are faster with data already loaded in cache.

1
  • Also remember that the optimiser will change the plan as the table grows and shrinks so using a hint MAY prevent the plan from changing later on when it actually needs to. Try to use other ways to get the plan you want where possible but be sure to document why the hint is there if you still need to use it. (Your support staff will thank you many months after you've forgotten all about it) Mar 29, 2016 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.