27. Choice of the basic table
The Basic Table refers to a table that is first visited (usually accessed in a full mete-scan). The choice of the base table in the SQL statement is different from the difference in the optimizer.
If you are using CBO (COST Based Optimizer), the optimizer checks the physical size, index status of each table in the SQL statement, and then select the lowest execution path.
If you use RBO (Rule Based Optimizer, and all connection conditions have indexes, in this case, the basic table is the last table listed in the finals in the FROM clause.
Example:
SELECT A.NAME, B.Manager
From worker a,
Lodging B
WHERE a.lodging = b.loding;
Since there is an index on the Loding list of the Lodging table, the WORKER table will be used as the base table in the query in the Worker table.
28. Multiple equality indexes
When the execution path of the SQL statement can use multiple indexes distributed on multiple tables, Oracle will simultaneously use multiple indexes and merge their records at runtime, retrieved only records that are valid only to all indexes.
When Oracle selects the execution path, the level of unique index is higher than that of the unique index. However, this rule is only
When WHERE clauses are more valid. If the index class of the index column is compared to the index class of other tables. This clause is very low in the optimizer.
If two indexes in different tables think of the same level will be referenced, the order in the FROM clause will determine which will be used first. The final table in the FROM clause will have the highest priority.
If two indexes of the same level in the same table will be referenced, the most referenced index in the WHERE clause will have the highest priority.
Example:
There is a non-unique index on deptno, and EMP_CAT also has a non-unique index.
Select ename,
From EMP
WHERE DEPT_NO = 20
And Emp_cat = 'a';
Here, the DEPTNO index will be first retrieved, and then the records are merged with the EMP_CAT index. The execution path is as follows:
Table Access by RowID on EMP
And-Equal
Index Range Scan on DePT_IDX
Index Range Scan on Cat_IDX
29. Comparison and scope of equation
When there is an index column in the WHERE clause, Oracle cannot merge them, Oracle will use the range comparison.
Example:
There is a non-unique index on deptno, and EMP_CAT also has a non-unique index.
SELECT ENAME
From EMP
WHERE Deptno> 20
And Emp_cat = 'a';
Only the EMP_CAT index is used here, and then all records compare it to the DEPTNO condition. The execution path is as follows:
Table Access by RowID on EMP
Index Range Scan on Cat_IDX
30. Unclear index level
When Oracle is unable to determine the level of high and low differences, the optimizer will use only one index, which is listed in the WHERE clause.
Example:
There is a non-unique index on deptno, and EMP_CAT also has a non-unique index.
SELECT ENAME
From EMP
WHERE Deptno> 20
AND EMP_CAT> 'a';
Here, Oracle only uses a DEPT_NO index. The execution path is as follows:
Table Access by RowID on EMP
Index Range Scan on DePT_IDX
Translator presses:
Let's try the following situations:
SQL> SELECT INDEX_NAME, UNIQENESS from user_indexes where table_name = 'EMP'
INDEX_NAME UniQuenes
------------------------------------- Empno unique
EMPTYPE NONUNIQUE
SQL> Select * from Emp where Empno> = 2 and EMP_TYPE = 'A';
No rows selected
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 Table Access (by index rowid) of 'EMP'
2 1 Index (Range Scan) of 'EMPTYPE' (Non-Unique)
Although EMPNO is a unique index, because it is, the scope comparison is lower than the equality of the non-unique index!
