A simple example shows Oracle's "selfless" robustness, and the example oracle is selfless.

A simple example shows Oracle's "selfless" robustness, and the example oracle is selfless.

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:

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:

This uses full index scanning, which is not as good as fast and full index scanning, but at least HINT plays a role because Obtaining values from non-null indexes is a reliable method, therefore, 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.

A small oracle problem-Let's briefly talk about oracle instances and routines and their connection

The two concepts of oracle can be viewed here:
Instance: hi.baidu.com/..d.html
The routine is the process: hi.baidu.com/...9.html
A database instance is a combination of a group of oracle background processes/threads and shared memory areas.
Therefore, routines are part of oracle instances ..

Shutdown is used to close the database.
Databases and instances are one-to-one. You can consider them as shutting down instances.

A simple oracle statement,

Oracle SqlPlus
Enter an SQL statement.
If the SELECT statement ends with a semicolon or/, You can execute it.

If the input is a stored procedure statement, the statement cannot be run after the semicolon is entered.
It must be input /.
The following is a simple example:

SQL> set serveroutput on
SQL> BEGIN
2 dbms_output.put_line ('Hello World ');
3 END;
4/
Hello World
PL/SQL procedure successfully completed

If you see Row 3, enter one. In fact, all the code has been entered.
But not executed
It must be in Row 3 and enter one/for execution.