A simple example looks at Oracle's "selfless" robustness

The power of Oracle is that he can always help you choose the right execution plan , even if you give it the wrong instructions.

Experiment:

1. Create a test table :

Collect statistics:

To create a B-Tree index:

2. Execute select ID from Tbl_plan; view its execution plan :

Because the B-tree index is created, it is normal to get the value of the ID from the index without a full table scan, but the full table scan is used here.

Even with hint, this still uses a full table scan:

This is because this is a B-tree index and does not store null values, although this table does not have null values. If a record is queried directly from a B-tree index that may contain a null value, the null value is not stored in the index, and some records are omitted, so the query results are wrong, so Oracle does not choose to use an index scan, but a full table scan .

3. Set the ID field to non-null :

This is the equivalent of a unique index.

Re-execute Select ID from Tbl_plan;:

Oracle chooses an index fast full scan, because ID is an indexed field and getting values directly from the index is the quickest way.

If you are using the Hint method:

this uses an index full scan, as well as a fast full scan of the index, but at least hint plays a role because obtaining a value from a non-empty index is a reliable way, so Oracle allows the use of hint .

Summary :

As you can see from the simple example above, Oracle will always help you choose the right execution plan, even if you give the wrong information to oracle,oracle this "selfless" spirit is worth learning, if our application is strong enough, it would be better.

A simple example looks at Oracle's "selfless" robustness