Oracle SQL Performance Optimization series (14)

46.Connect multiple scans

If you compare a column with a limited set of values, the Optimizer may perform multiple scans and merge 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 selecting the execution path, the Optimizer may scan the index range on LODGING $ MANAGER for each condition. the returned ROWID is used to ACCESS records of the lodging table (by table access by rowid ). the last two sets of records are combined into a single set in the form of 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)

Press:

This section and 37thThere seems to be a conflict in the Section.

 

 

47.CBOUse more selective Indexes

The Cost-Based Optimizer (CBO, Cost-Based Optimizer) determines whether the index can be used to improve efficiency.

If the index is highly selective, that is to say, for each index key value that does not repeat, it only corresponds to a small number of records.

For example, there are 100 records in the table, and 80 of them are non-duplicate index key values. the selectivity of this index is 80/100 = 0.8. the higher the selectivity, the fewer records retrieved by the index key value.

If the selection of indexes is low, a large number of index range queries and ROWID queries are required to retrieve data.

Operation. It may be less efficient than full table scan.

Press:

See:

A.If the retrieved data volume exceeds 30%Number of records in the table.Using indexes will not significantly improve the efficiency.

B.Under specific circumstances,Using indexes may be slower than scanning the entire table,But this is on the same order of magnitude.

Difference.In general,Using indexes is several times or even several thousand times more than scanning the entire table!

 

 

48.Avoid resource-consuming operations

SQL statements with DISTINCT, UNION, MINUS, INTERSECT, and order by will start the SQL engine.

Execute the resource-consuming sorting (SORT) function. DISTINCT requires a sorting operation, and the other requires at least two sorting operations.

For example, for a UNION query, where each query has a group by clause, group by triggers NESTED sorting. In this way, each query needs to be sorted once, then, when UNION is executed, another sort unique operation is executed and can only be executed after the preceding embedded sorting is completed. the depth of embedded sorting will greatly affect the query efficiency.

Generally, SQL statements with UNION, MINUS, and INTERSECT can be rewritten in other ways.

Press:

If your database's SORT_AREA_SIZEWell deployed,Use UNION, MINUS, INTERSECTIt can also be considered,After all, they are highly readable.

 

 

49.Optimize GROUP

To improve the efficiency of the group by statement, you can filter out unnecessary records before group by. The following two queries return the same results, but the second query is much faster.

 

Inefficiency:

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

Press:

This section and 14Same section.Skipped.

 

50.Date of use

When the date is used, note that if there are more than five decimal places added to the date, this date will enter 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-9300:00: 00'

 

Press:

Although this section and SQLPerformance Optimization does not matter,However, the author's skill is evident.

51.Use an explicit cursor (CURSORs)

If an implicit cursor is used, two operations will be performed. record retrieval is performed for the first time, and the exception TOO contains ROWS is checked for the second time. The explicit cursor does not perform the second operation.

 

52.Optimize EXPORTAnd IMPORT

Using a large BUFFER (such as 10 MB, 10,240,000) can speed up EXPORT and IMPORT.

ORACLE will try to get the memory size you specified, even if the memory is not satisfied, no error will be reported. This value must be at least the same as the maximum column in the table, otherwise the column value will be truncated.

Press:

Yes,Add BUFFERWill greatly improve the EXPORT, IMPORT.(Once met a CASE,Add BUFFERAfter, IMPORT/EXPORT10 fasterTimes !)

The author may make a mistake:"This value must be at least the same as the maximum column in the table,Otherwise, the column value is truncated."

The largest column may be the maximum record size.

About EXPORT/IMPORTCSDNThere are some summative posts in the Forum,For exampleParameter, COMMITParameters,For more information, see.

 

53.Separate tables and Indexes

Always create your table and index in different TABLESPACES (TABLESPACES ). never store objects that do not belong to the internal oracle system in the system tablespace. at the same time, make sure that the data table space and the index tablespace are placed on different hard disks.

 

Press:

"At the same time,Make sure that the data table space and index tablespace are placed on different hard disks ."It may be changed to the following:At the same time,Make sure that the data table space and index tablespace are placed on the hard disk controlled by Different Hard Disk control cards ."