Oracle SQL Efficiency Theory

• Key factors affecting Oracle Efficiency

Original http://hi.baidu.com/odbcconnection/item/f4a56cbf1c8bea422bebe32f

1: select the most efficient table name sequence (only valid in the rule-based Optimizer)
OracleThe parser processes the table names in the from clause in the order from right to left. Therefore, the table written in the from clause (basic table driving table) will be processed first.

When the from clause contains multiple tables, the table with the least number of records must be selected as the base table. Oracle When processing multiple tables, they are connected by sorting and merging. first, scan the first table (the last table in the from clause) and sort the records, and then scan the second table (the last second table in the from clause ), finally, all records retrieved from the second table are merged with the appropriate records in the first table.
Example: tab1 16,384 records
Table tab2 1 record
Select tab2 as the base table (the best method)
Select count (*) from tab1, tab2 execution time 0.96 seconds
Select tab2 as the base table (poor method)
Select count (*) from tab2, tab1 execution time 26.09 seconds
If more than three tables are connected Query You must select an intersection table as the base table. A cross table is the table referenced by other tables.
For example, 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
It will be more efficient than the following SQL statements
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
Focus on 2: the connection sequence in the WHERE clause. Important

OracleUse the bottom-up sequence to parse the WHERE clause. Based on this principle, the join between tables must be written before other where conditions. The conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.
For example:
(Inefficient, execution time: 156.3 seconds)
Select...
From EMP E
Where SAL>; 50000
And job = 'manager'
And 25 <(select count (*) from EMP
Where Mgr = E. empno );
(Efficient, execution time: 10.6 seconds)
Select...
From EMP E
Where 25 <(select count (*) from EMP
Where Mgr = E. empno)
And SAL>; 50000
And job = 'manager ';
Focus on 3: Avoid using '*' in the select clause '*'.Important

When you want to list all columns in the select clause, using dynamic SQL column reference '*' is a convenient method. Unfortunately, this is a very inefficient method. In fact, Oracle During parsing, '*' is converted to all column names in sequence. Query The data dictionary is complete, which means it takes more time.
7. Reduce the number of database accesses
When each SQL statement is executed, Oracle I performed a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks. This shows that reducing the number of database accesses can actually reduce the number of times. Oracle .
For example,
There are three ways to retrieve employees with employee numbers equal to 0342 or 0291.
Method 1 (most inefficient)
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 ...,..,.. ;
Open C1 (291 );
Fetch C1 ...,..,.. ;
Close C1;
End;
Method 3 (efficient)
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:
You can reset the arraysize parameter in SQL * Plus, SQL * forms, and Pro * C to increase the retrieval data volume for each database access. The recommended value is 200.

Focus on 4: Use the decode function to reduce processing time.Important
You can use the decode function to avoid repeated scan of the same record or join the same table.
For example:
Select count (*), sum (SAL)
From EMP
Where deptid = 'c021 ';
Select count (*), sum (SAL)
From EMP
Where deptid is null;
You can use the decode function to efficiently get the same result.
Select count (decode (deptid, 'c021', 'x', null) c021_count,
Count (decode (deptid, 'c022', 'x', null) c022_sal,
Sum (decode (deptid, 'c021', Sal, null) c021_count,
Sum (Deco

Focus on 5: delete duplicate records.Important

The most efficient method for deleting duplicate records (because rowid is used)
Delete from EMP E
Where E. rowid>; (select Min (X. rowid)
From EMP X
Where X. emp_no = E. emp_no );

Focus on 6: replacing Delete with truncate.Important

When you delete a record in a table, a rollback segment is usually used to store information that can be recovered. If you do not have a commit transaction,OracleThe data will be restored to the status before the deletion (accurately, the status before the deletion command is executed)
When truncate is used, the rollback segment no longer stores any recoverable information. after the command is run, the data cannot be restored. therefore, few resources are called and the execution time is short.
(The translator Press: truncate applies only to deleting the entire table, and truncate is DDL rather than DML)

Focus on 7: Use commit as much as possible.Important

If possibleProgramUse commit as much as possible, so that the program performance is improved, and the demand will be reduced by the resources released by commit:
Resources released by commit:
A. Information used to restore data on the rollback segment.
B. Locks obtained by Program Statements
C. Space in redo log Buffer
D.OracleTo manage the internal costs of the above three types of resources
(Translator's note: when using commit, you must pay attention to the integrity of the transaction. In reality, the efficiency and integrity of the transaction are often the same as that of the fish and the bear's paw)

Focus on 8: reduce table queries.Important

in an SQL statement that contains sub-databases query , pay special attention to reducing query .

For example:
Inefficient
Select tab_name
From tables
Where tab_name = (select tab_name
From tab_columns
Where version = 604)
And db_ver = (select db_ver
From tab_columns
Where version = 604)
Efficient
Select tab_name
From tables
Where (tab_name, db_ver)
= (Select tab_name, db_ver)
From tab_columns
Where version = 604)
Example of updating multiple columns:
Inefficiency:
Update EMP
Set emp_cat = (select max (Category) from emp_categories ),
Sal_range = (select max (sal_range) from emp_categories)
Where emp_dept = 0020;
Efficient:
Update EMP
Set (emp_cat, sal_range)
= (Select max (category), max (sal_range)
From emp_categories)
Where emp_dept = 0020;
9: replace in with exists. Important

In many basic table-based queries, to meet one condition, you often need to join another table. In this case, using exists (or not exists) will usually increaseQueryEfficiency.
Inefficiency:
 
(In relative terms, replacing not in with not exists will significantly improve efficiency, which will be pointed out in the next section)
Focus on 10: replace not in with not exists.

 

In a subquery, the not in Clause executes an internal sorting and merging. in either case, not in is the most inefficient (because it executes a full table traversal for the table in the subquery ). to avoid the use of not in, we can rewrite it into an outer join (outer joins) or not exists.
 

 
11: Identify 'inefficient execution' SQL statements.Important

use the following SQL tools to find inefficient SQL:
select executions, disk_reads, buffer_gets,
round (BUFFER_GETS-DISK_READS)/buffer_gets, 2) hit_radio,
round (disk_reads/executions, 2) reads_per_run,
SQL _text
from V $ sqlarea
where executions>; 0
and buffer_gets>; 0
and (BUFFER_GETS-DISK_READS)/buffer_gets <0.8
order by 4 DESC ;