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.
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)
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.