4. Select the most efficient table order (which is only valid in rule-based optimers)
Oracle's parser handles the table name in the FROM clause in order from right to left, so the FROM clause writes in the final table (base table driving table) will be processed. In the FROM clause contains multiple In the case of the table, you must select the table for the number of records as a base table. When Oracle handles multiple tables, you will connect to them, first, first, scan the first table (last in the FROM clause That table) and sequence the record, then scan the second table (the last second table in the FROM clause), finally record all records detected from the second table with the first table suitable record merge.
E.g:
Table Tab1 16, 384 records
Table Tab2 1 record
Select Tab2 as a base table (best way)
Select count (*) from tab1, tab2 execution time 0.96 seconds
Select Tab2 as a basic table (poor method)
SELECT Count (*) from Tab2, Tab1 execution time 26.09 seconds
If there are more than 3 table connection queries, you need to select a crosstum (INTERSECTION TABLE) as a base table, and the crosstab refers to the table referenced by the other tables.
E.g:
The EMP table describes the intersection of the Location table and the Category table.
SELECT *
From location L,
Category C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 and 2000
And E.cat_no = c.cat_no
And e.locn = l.locn
More efficient than the following SQL
SELECT *
From EMP E,
Location L,
Category C
Where e.cat_no = c.cat_no
And e.locn = l.locn
And E.EMP_NO BETWEEN 1000 and 2000
5. The connection order in the WHERE clause.
Oracle parses the WHERE clause from the bottom, according to this principle, the connection between the tables must be written before other WHERE conditions, and the conditions that can be filtered out must be written at the end of the WHERE clause.
E.g:
(Inefficient, execution time 156.3 seconds)
SELECT ...
From EMP E
Where sal> 5000
And job = 'manager'
And 25 <(Select Count (*) from EMP
WHERE MGR = E.EMPNO);
(Efficient, execution time is 10.6 seconds)
SELECT ...
From EMP E
WHERE 25 <(Select Count (*) from EMP
Where mgr = e.empno)
And Sal> 50000
And Job = 'Manager';
6. Avoid using '*' in the SELECT clause
When you want to list all Column in the SELECT clause, use dynamic SQL columns to reference '*' is a convenient way. Unfortunately, this is a very inefficient method. In fact, Oracle is in parsing In the middle, the '*' will be converted into all column names, which is done by querying the data dictionary, which means more time.
7. Reduce the number of access to the database
When performing each SQL statement, Oracle has performed a lot of work inside: parsing the SQL statement, estimating the utilization, binding variables, reading data blocks, etc. Reduce the workload of Oracle.
E.g,
There are three ways to retrieve employee numbers equal to 0342 or 0291.
Method 1 (Minimum Efficiency)
SELECT EMP_NAME, SALARY, GRADE
From EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME, SALARY, GRADE
From EMP
WHERE EMP_NO = 291; Method 2 (Low Efficiency)
Declare
Cursor C1 (E_NO Number) IS
SELECT EMP_NAME, SALARY, GRADE
From EMP
WHERE EMP_NO = E_NO;
Begin
Open C1 (342);
Fetch C1 Into ..., .., ..;
... ..
Open C1 (291);
Fetch C1 Into ..., .., ..;
CLOSE C1;
END;
Method 3 (Efficiency)
Select a.emp_name, A.salary, A.Grade,
B.emp_name, b.salary, b.grade
From EMP A, EMP B
WHERE a.emp_no = 342
And B.EMP_NO = 291;
note:
Reset the Arraysize parameter in SQL * Plus, SQL * Forms, and Pro * C, you can increase the amount of retrieval amount of each database access, the recommended value is 200
(Excerpt from 9cbs)
