46. Connect multiple scans
If you compare a column and a limited value, the optimizer may perform multiple scans and merge the results.
Example:
SELECT *
From lodging
WHERE Manager in ('Bill Gates', 'Ken Muller');
Optimizer may convert it into the following form
SELECT *
From lodging
Where manager = 'BILL GATES'
Or Manager = 'Ken Muller';
When the execution path is selected, the optimizer may use the index range scan on the Lodging $ Manager. Return RouiD to access the Lodging table record (by the Table Access By RowID). The last two groups recorded in connection ( The form of concatenation is combined into a single collection.
Explain Plan:
Select Statement Optimizer = choose
Concateation
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 presses:
This section and Section 37 seem to have contradictory.
47. Use more selective indexing under CBO
Cost-based optimizer (CBO, COST-based Optimizer) determines whether the use of indexes can improve efficiency.
If the index has a high selectivity, it is to say that there is a small number of records for each non-repetitive index key.
For example, there are 100 records in the table, and 80 non-repetitive index key values. The selectivity of this index is 80/100 = 0.8. The higher the selectivity, the less recorded by the index key value.
If the selectivity of the index is very low, retrieval data requires a large number of index range query operations and RowID access tables.
Operation. Perhaps lower efficiency than full measuring.
Translator presses:
See: See:
a. If the number of records exceeds 30% of the amount of data records. Use the index will have no significant efficiency.
b. In certain cases, use the index may be slower than the full table, but this is the same order
Difference. And usually, using the index ratio of the full table to scan a few times or thousands of times!
48. Avoid using a resource-consuming operation
SQL statement with Distinct, Union, Minus, INTERSECT, ORDER BY will start SQL engine
Execute the sort function of the resource. DistINCT requires a sorting operation, while others need to perform two sorting.
For example, a Union query, each query comes with a Group By clause, and Group By will trigger embedd sort; this, each query needs to be sorted, and then a unique order when executing Union, Sort unique) Operation is performed and it can only begin execution after the end of embedding sorting. The depth of embedded sorting will greatly affect the efficiency of the query.
Typically, both the SQL statement with UNION, Minus, and INTERSECT can be rewritten in other ways.
Translator presses:
If your database's sort_AREA_SIZE is well fixed, use union, minus, and intersect can also be considered, after all, their readability is very
49. Optimize Group by
Improve the efficiency of Group By statements, you can filter out the unwanted records before the group BY. The following two queries return the same result but the second obvious is much better.
Inefficient:
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 presses:
This section is the same as 14 sections. Skeptable.
50. Date of use
When the date of use is, it is necessary to pay attention to the date on the date of more than 5 decimals, this date will come next day!
E.g:
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 presses:
Although this section and SQL performance have no relationship, the author's skill can be seen
51. Use explicit cursors (CURSORS)
With implicit cursors, two operations will be performed. The first retrieval record, the second check of the Exception. The explicit cursor does not perform the second operation.
52. Optimize Export and Import
Use larger buffers (such as 10MB, 10, 240,000) to increase the speed of Export and Import.
Oracle will get the memory size you specify as much as possible, even if the memory is not satisfied, it will not report error. This value is at least equivalent to the largest column in the table, otherwise the value will be truncated.
Translator presses:
It is certain that adding buffer will greatly increase the efficiency of Export, Import. (Once in a case, after increasing buffer, Import / Export is 10 times!)
Authors may have made a mistake: "This value is at least equivalent to the largest column in the table, otherwise the value will be truncated."
The largest column may mean the maximum record size.
About Export / Import Optimization, there are some summary posts in the 9CBS forum, such as the Buffer parameters, commit parameters, etc., please check.
53. Separation tables and indexes
Always build your tables and indexes in different tablespaces. Never store objects that do not belong to the Oracle internal system into the System table space. At the same time, ensure that data tablespace and index table space are placed in different Hard disk.
Translator presses:
"At the same time, make sure that data table space and index table space are set to different hard drives." Must be changed to be more accurate ", ensuring that data tablespace and index table space are controlled on hard drives controlled with different hard disk control cards."
(Full text)
Black_snail
Ligang1000@hotmail.com
4 / SEP / 2003
