A simple demo example to look at Oracle's "selfless" robustness

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

Experiment:

1. Create a frequently :

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvymlzyww=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70 /gravity/center ">

Collect statistics:

To create a B-Tree index:

2. Run the Select ID from Tbl_plan; View its run schedule :

Because the B-tree index is created, it is normal to be able 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.

assume that you query a record directly from a B-tree index that might include a null value. Null values are not stored in the index. Will leak some records, then the query results will be wrong, so Oracle does not choose to use the index scan, but the full table scan .

3. Set the ID field to non-null :

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvymlzyww=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70 /gravity/center ">

This is the equivalent of a unique index.

Run the Select ID again from Tbl_plan;:

Oracle chooses an index high-speed full scan, which is the fastest way to get a value directly from the index because the ID is an indexed field.

The assumption is that the hint method is used:

This is an index full scan, not as high-speed full scan, but at least hint played a role. The reason is that getting a value from a non-empty index is a reliable way. Oracle therefore agrees to the use of hint .

Summary :

You can see it from the Simple demo sample above. Oracle will always help you choose the right plan for your operation, even if you give the wrong information to Oracle. Oracle's "Selfless" spirit is worth learning. Let's say that our application system is strong enough, that's better.

A simple demo example to look at Oracle's "selfless" robustness