ORACLE SQL Performance Optimization series (14) End of article

oracle| Performance | optimization
46. Connect multiple Scans

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."





(End of full text)

Black_snail

Ligang1000@hotmail.com

4/sep/2003