A simple example shows Oracle's "selfless" robustness

A simple example shows Oracle's "selfless" robustness

Oracle's strength lies in its ability to always help you choose the right execution plan, even if you give it wrong instructions.

Lab:

1. Create a test table:

Collect statistics:

Create a B-Tree Index: <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4KPHA + pgltzybzcm9 "http://www.2cto.com/uploadfile/Collfiles/20141031/2014103109132018.png" alt = "\">

2. Execute select id from tbl_plan; to view its execution plan:

Because a B-tree index is created, the id value can be obtained from the index without full table scan. However, full table scan is used here.

Even if HINT is used, full table scan is still used:

The reason is that this is a B-tree index and does not store NULL values, although this table does not have NULL values. If you directly query records from a B-tree index that may contain NULL values, the NULL value is not stored in the index, and some records are missing, the query results will be wrong, therefore, Oracle does not choose to use index scanning, but instead performs full table scanning..

3. Set the id field to non-empty:

This is equivalent to a unique index.

Then execute select id from tbl_plan ;:

Oracle chooses fast and full index scanning. Because id is an index field, it is the fastest way to obtain the value directly from the index.

If the HINT method is used:

Oracle allows the use of HINT.

Summary:

From the simple example above, Oracle will always help you select the correct execution plan. Even if you give the wrong information to Oracle, Oracle's "selfless" spirit deserves our learning, if our application system is robust enough, it would be better.