ORACLE SQL Performance Optimization Series (12)

oracle| Performance | optimization
39. Always use the first column of the index

If the index is based on multiple columns, the optimizer chooses to use the index only if its first column (leading column) is referenced by the WHERE clause.



Translator by:

This is also a simple and important rule. See the following examples.



Sql> CREATE TABLE Multiindexusage (inda number, indb number, descr varchar2 (10));

Table created.

Sql> CREATE index Multindex on multiindexusage (INDA,INDB);

Index created.

Sql> Set Autotrace traceonly



Sql> SELECT * from multiindexusage where Inda = 1;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (by INDEX ROWID) of ' multiindexusage '

2 1 INDEX (RANGE SCAN) of ' Multindex ' (non-unique)



Sql> SELECT * from multiindexusage where indb = 1;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (full) ' Multiindexusage '



Obviously, when only the second column of the index is referenced, the optimizer uses a full table scan and ignores the index





Oracle Internal Operations

Oracle takes an internal operation when executing a query. The following table shows several important internal operations.

ORACLE Clause

Internal operation

ORDER BY

SORT ORDER BY

UNION

Union-all

Minus

Minus

INTERSECT

INTERSECT

Distinct,minus,intersect,union

SORT UNIQUE

Min,max,count

SORT AGGREGATE

GROUP by

SORT GROUP by

RowNum

Count or Count Stopkey

Queries involving joins

SORT Join,merge join,nested LOOPS

CONNECT by

CONNECT by





41. Replace union with Union-all (if possible)



When the SQL statement requires a union of two query result sets, the two result sets are merged in a union-all manner, and then sorted before outputting the final result.

If you use UNION ALL instead of union, this sort is not necessary. Efficiency will be improved accordingly.



Example:

Low efficiency:

SELECT Acct_num, Balance_amt

From Debit_transactions

WHERE tran_date = ' 31-dec-95 '

UNION

SELECT Acct_num, Balance_amt

From Debit_transactions

WHERE tran_date = ' 31-dec-95 '

Efficient:

SELECT Acct_num, Balance_amt

From Debit_transactions

WHERE tran_date = ' 31-dec-95 '

UNION All

SELECT Acct_num, Balance_amt

From Debit_transactions

WHERE tran_date = ' 31-dec-95 '



Translator by:

It should be noted that UNION all outputs the same record in the two result sets repeatedly. So you guys are still

The feasibility of using union all to analyze business requirements.

The UNION will sort the result set, which will use the memory of the Sort_area_size block. For this

The optimization of block memory is also very important. The following SQL can be used to query for sorted consumption



Select substr (name,1,25) "Sort area Name",

substr (value,1,15) "Value"

From V$sysstat

Where name like ' sort% '



42. Tips for use (hints)

For table access, you can use two kinds of hints.

Full and ROWID



Full hint tells Oracle to access the specified table using a table-wide scan.

For example:

SELECT/*+ Full (EMP)/*

From EMP

WHERE EMPNO = 7893;



ROWID hint tells Oracle to access the table using the action of table access by ROWID.



Typically, you need to use table access by ROWID, especially when accessing large tables, where you need to know the ROIWD value or use the index.

If a large table is not set to the cache (CACHED) table and you want its data at the end of the query is still stay

In the SGA, you can use the cache hint to tell the optimizer to keep the data in the SGA. Typically, cache hint is used with full hint.

For example:

SELECT/*+ Full (worker) CACHE (worker)/*

From WORK;



Index hint tells Oracle to use an indexed scan method. You don't have to specify the name of the index

For example:

SELECT/*+ INDEX (lodging) */Lodging

From lodging

WHERE MANAGER = ' BILL GATES ';



The above query should also use the index without using hint, however, if the index has too many duplicate values and your optimizer is a CBO, the optimizer may ignore the index. In this case, you can force Oracle to use the index hint.



ORACLE hints also includes all_rows, First_rows, Rule,use_nl, Use_merge, Use_hash, and so on.



Translator by:

Using hint indicates that we are dissatisfied with the default execution path of the Oracle Optimizer and require manual modification.

This is a very skilled job. I recommend hint optimizations for only a specific, small number of SQL.

Be confident in Oracle's optimizer (especially CBO)