ORACLE SQL Performance Optimization series (ix)

oracle| Performance | optimization
27. Selection of the base table



The underlying table (driving table) refers to the table that is first accessed (typically accessed as a whole table scan). Depending on the optimizer, the selection of the underlying table in the SQL statement is different.

If you are using a CBO (cost BASED OPTIMIZER), the optimizer checks the physical size of each table in the SQL statement, the status of the index, and then chooses the lowest-cost execution path.

If you use Rbo (Rule BASED OPTIMIZER) and all join conditions have an index corresponding, in this case the underlying table is the table listed in the FROM clause in the last.

Example:

SELECT A.name, B.manager

From WORKER A,

Lodging B

WHERE a.lodging = b.loding;

Because there is an index on the loding column of the lodging table and there is no comparison index in the worker table, the worker table is used as the underlying table in the query.



28. Multiple Equal index

When the execution path of an SQL statement can use multiple indexes that are distributed across multiple tables, Oracle uses multiple indexes at the same time and merges their records at run time, retrieving records that are valid only for all indexes.

When Oracle chooses the execution path, the uniqueness index is higher than the non-unique index. However, this rule only

Indexed columns and constants are more effective when they are in the WHERE clause. If the indexed columns are compared to the index classes of other tables. This seed sentence is very low in the optimizer.

If two indexes of the same level are referenced in different tables, the order of the tables in the FROM clause determines which will be the first to be used. The index of the last table in the FROM clause will have the highest precedence.

If two indexes in the same table that want to be of the same rank are referenced, the first referenced index in the WHERE clause will have the highest precedence.

Example:

There is a DEPTNO index on the Emp_cat, and a non unique index is also available.

SELECT ename,

From EMP

WHERE Dept_no = 20

and Emp_cat = ' A ';

Here, the DEPTNO index is retrieved first and then merged with the records retrieved by 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. Equality comparison and range comparison

When there are indexed columns in the WHERE clause, Oracle cannot merge them, and Oracle will use a range comparison.



Example:

There is a DEPTNO index on the Emp_cat, and a non unique index is also available.

SELECT ename

From EMP

WHERE DEPTNO > 20

and Emp_cat = ' A ';



Only the Emp_cat index is used here, and then all records are compared to the deptno condition. The execution path is as follows:

TABLE ACCESS by ROWID on EMP

INDEX RANGE SCAN on Cat_idx



30. Ambiguous index level



When Oracle cannot determine the level or height of an index, the optimizer will use only one index, which is listed first in the WHERE clause.

Example:

There is a DEPTNO index on the Emp_cat, and a non unique index is also available.



SELECT ename

From EMP

WHERE DEPTNO > 20

and Emp_cat > ' A ';



Here, Oracle uses only the DEPT_NO index. The execution path is as follows:



TABLE ACCESS by ROWID on EMP

INDEX RANGE SCAN on Dept_idx



Translator by:

Let's try the following:

Sql> Select Index_name, uniqueness 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 does a range comparison, the rank is lower than the non-uniqueness index!