Oracle multi-Table query optimization

From: http://hi.baidu.com/freeperson/blog/item/2f9eafd48a693909a08bb70f.html

 

Here we provide optimization of the execution performance, rather than the background database optimizer:

According to various issues in database development performance, some optimization solutions are collected as follows (of course, suchIndexWhen the optimization solution is too simple, it will not be included, hey ):

Execution path: OracleThis function greatly improves SQL Execution performance and saves memory usage: We found that the speed of single table data statistics is completely two concepts than that of Multi-table statistics. the statistics for a single table may only take 0.02 seconds, but the combined statistics for two tables may take dozens of tables. this is becauseOracleOnly for simple tablesHigh-Speed Buffer(Cache buffering), this function does not applyMulti-table join query... The database administrator must set the appropriate parameters for this region in init. ora. When the memory area is larger, more statements can be retained. Of course, the possibility of sharing will be greater.

when you submit an SQL statement to Oracle , Oracle first searches for the same statement in the memory.
it must be noted that Oracle adopts a strict match between the two. To achieve sharing, the SQL statement must be exactly the same
(including spaces, line feed, etc ).
the shared statement must meet three conditions:
. character-level comparison:
the statements currently executed must be identical to those in the sharing pool.
For example:
select * from EMP;
different from each of the following
select * from EMP;
select * from EMP;

B. The objects referred to by the two statements must be identical:

How to access user object names
Jack sal_limit private Synonym
Work_city public Synonym
Plant_detail public Synonym
Jill sal_limit private Synonym
Work_city public Synonym
Plant_detail table owner
Consider whether the following SQL statements can be shared between the two users.
Can SQL be shared?
Select max (sal_cap) from sal_limit; each user cannot have a private synonym-sal_limit. They are different objects.
Select count (*) from work_city where sdesc like 'new % '; two users can access the same object public synonym-work_city
Select a. sdesc, B. Location from work_city A, plant_detail B where a. city_id = B. city_id cannot be used by Jack to access plant_detail through private synonym, while Jill is the table owner and the objects are different.

C. bind variables must be used in the two SQL statements)
For example, the two SQL statements in the first group are the same (which can be shared), while the two statements in the second group are different (even when running, different bind variables are assigned the same value)
A.
Select pin, name from people where pin =: blk1.pin;
Select pin, name from people where pin =: blk1.pin;
B.
Select pin, name from people where pin =: blk1.ot _ ind;
Select pin, name from people where pin =: blk1.ov _ ind;
Focus on 1: select the most efficient table name sequence (only valid in the rule-based Optimizer) Important
Oracle The 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, You must select the table with the least number of records as the base table. when 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

Oracle uses the bottom-up sequence to parse the WHERE clause. Based on this principle, the connections 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.
example:
(inefficient, 156.3 seconds)
select...
from EMP e
where SAL>; 50000
and job = 'manager'
and 25 <(select count (*) from EMP
where Mgr = E. empno);
(efficient, 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. focus on
using the decode function can avoid repeatedly scanning the same records or joining the same table.
example:
select count (*), sum (SAL)
from EMP
where dept_no = 0020
and ename like 'Smith % ';
select count (*), sum (SAL)
from EMP
where dept_no = 0030
and ename like 'Smith % ';
you can use the decode function to efficiently get the same result
select count (decode (dept_no, 0020, 'x', null) d0020_count,
count (decode (dept_no, 0030, 'x', null) d0030_count,
sum (decode (dept_no, 0020, Sal, null) d0020_sal,
sum (decode (dept_no, 0030, Sal, null) d0030_sal
from EMP where ename like 'Smith % ';
similar, the decode function can also be used in group by and order by clauses.

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

InQueryIn SQL statements, pay special attention to reducing the numberQuery.

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-basedQueryTo meet one condition, you often need to join another table. In this case, using exists (or not exists) will usually increaseQueryEfficiency.
Inefficiency:
Select *
From EMP (basic table)
Where empno>; 0
And deptno in (select deptno
From Dept
Where loc = 'melb ')
Efficient:
Select *
From EMP (basic table)
Where empno>; 0
And exists (select 'x'
From Dept
Where Dept. deptno = EMP. deptno
And loc = 'melb ')
(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.Important

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.
For example:
Select...
From EMP
Where dept_no not in (select dept_no
From Dept
Where dept_cat = 'A ');
To improve efficiency, rewrite it:
(Method 1: efficient)
Select ....
From emp a, DEPT B
Where a. dept_no = B. Dept (+)
And B. dept_no is null
And B. dept_cat (+) = 'A'
(Method 2: most efficient)
Select ....
From EMP E
Where not exists (select 'x'
From dept d
Where D. dept_no = E. dept_no
And dept_cat = 'A ');
Of course, the most efficient way is to associate tables. directly link two tables with the fastest speed!
11: Identify 'inefficient execution' SQL statements. Important

Use the following SQL tools to find out inefficient SQL statements:
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;
(Translator's note: although various graphical tools for SQL optimization are emerging, writing your own SQL tools is always the best way to solve the problem)