If you compare a column to a limited set of values, the optimizer may perform multiple scans and concatenate the results.
Example:
SELECT *
From lodging
WHERE MANAGER in (' Bill GATES ', ' KEN MULLER ');
The optimizer may convert it to the following form
SELECT *
From lodging
WHERE MANAGER = ' BILL GATES '
or MANAGER = ' KEN MULLER ';
When you select the execution path, the optimizer may take an index range scan on Lodging$manager for each condition. The returned ROWID is used to access the records of the lodging table (via table access by ROWID). The last two sets of records are grouped into a single set in the form of a connection (concatenation).
Explain Plan:
SELECT STATEMENT Optimizer=choose
Concatenation
TABLE ACCESS (by INDEX ROWID) of lodging
INDEX (RANGE SCAN) of Lodging$manager (Non-unique)
TABLE ACCESS (by INDEX ROWID) of lodging
INDEX (RANGE SCAN) of Lodging$manager (Non-unique)
Translator by:
There seems to be a contradiction between this and section 37th.
Use more selective indexes under the CBO
The cost-based optimizer (CBO, cost-based Optimizer) determines the selectivity of an index to determine whether an index can be used to improve efficiency.
If the index is highly selective, it means that for each distinct index key value, only a small number of records are corresponding.
For example, there are 100 records in the table, and there are 80 key values that are not repeated. The selectivity of this index is 80/100 = 0.8. The higher the selectivity, the fewer records are retrieved through the index key value.
If the selectivity of the index is very low, retrieving the data requires a large number of index range query operations and the ROWID Access table
Operation. may be less efficient than full table scans.
Translator by:
See the following experience:
A. The number of records in a table that retrieves more than 30% of the data. Using indexes will not improve significantly.
B. Under certain circumstances, using an index may be slower than full table scans, but it is the same order of magnitude
Difference. In general, using an index is a few times or even thousands of times times more than a full table scan!
48. Avoid using resource-consuming operations
SQL statement with Distinct,union,minus,intersect,order by will start the SQL engine
Perform a resource-intensive sort (sort) function. Distinct requires a sort operation, while the other requires at least two times to perform the sort.
For example, a union query in which each query has a GROUP BY clause, and GROUP by triggers an embedded sort (NESTED sort); In this way, each query needs to perform a sort, and then, when the union is executed, another unique sort (sort UNIQUE) operation is executed and it can only begin execution after the previous embedded sort has ended. The depth of the embedded sort can greatly affect the efficiency of the query.
Typically, SQL statements with union, minus, and intersect can be overridden in other ways.
Translator by:
If your database sort_area_size well, use union, minus, intersect can also be considered, after all, their readability is very strong
49. Optimize GROUP BY
Increase the efficiency of the group BY statement by filtering out unwanted records before group by. The following two queries return the same result but the second one is obviously much faster.
Low efficiency:
SELECT JOB, AVG (SAL)
From EMP
GROUP JOB
Having JOB = ' PRESIDENT '
OR JOB = ' MANAGER '
Efficient:
SELECT JOB, AVG (SAL)
From EMP
WHERE JOB = ' PRESIDENT '
OR JOB = ' MANAGER '
GROUP JOB
Translator by:
This section and section 14 are the same. can skip over.
50. Date of Use
When using the date is, be aware that if there are more than 5 decimal places added to the date, this date will go to the next day!
For example:
1.
SELECT to_date (' 01-jan-93 ' +.99999)
From DUAL;
Returns:
' 01-jan-93 23:59:59 '
2.
SELECT to_date (' 01-jan-93 ' +.999999)
From DUAL;
Returns:
' 02-jan-93 00:00:00 '
Translator by:
Although this section is not related to SQL performance tuning, the author's ability to see
51. Use an explicit cursor (CURSORs)
With an implicit cursor, two operations will be performed. The first time to retrieve records, the second check too MANY ROWS this exception. An explicit cursor does not perform a second operation.
52. Optimize Export and Import
Using larger buffer (such as 10MB, 10,240,000) can increase the speed of export and import.
Oracle will maximize the amount of memory you specify, and will not complain even if memory is not satisfied. This value should be at least as large as the largest column in the table, otherwise the column value will be truncated.
Translator by:
To be sure, increasing the buffer will greatly increase the efficiency of export and import. (once encountered a case, increase the buffer, import/export 10 times times faster!)
The author may have made a mistake: "This value must be at least as large as the largest column in the table, otherwise the column values will be truncated."
The largest column may be the largest record size.
On the optimization of Export/import, there are some summary posts in CSDN forum, such as about buffer parameters, commit parameters and so on, please check the details.
53. Separating tables and indexes
Always build your table and index in a different table space (tablespaces). Never store objects that are not part of an Oracle internal system in the system tablespace. Also, make sure that the data table space and index table space are placed on different hard disks.
Translator by:
"Also, make sure that the datasheet space and index table space are placed with different hard disks." It may be more accurate as follows "at the same time, ensure that the datasheet space and index tablespaces are placed on hard drives controlled by different hard disk control cards."
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.