About Oracle's multi-table query optimization

The following article reproduced from: http://www.cnblogs.com/200911/archive/2012/07/26/2610018.html, thank you

This feature greatly improves the performance of SQL execution and saves memory usage: we find that the statistics of single-table data are two concepts more than the speed of multi-table statistics. Single-table statistics can be as long as 0.02 seconds, but 2 tables may have dozens of tables. This is because ORACLE caching is only available for simple tables (cache buffering), which does not apply to multi-table connection queries . The database administrator must set the appropriate parameters for this region in Init.ora, and when the memory area is larger, more statements can be kept, and the likelihood of sharing is greater.

When you submit an SQL statement to Oracle ,Oracle will first look for the same statement in this block of memory. It is important to note thatORACLE has a strict match for both, and the SQL statements must be identical (including spaces, line breaks, and so on) to achieve sharing. A shared statement must meet three conditions:

A. Character-level comparison: The statements that are currently executed and those in the shared pool must be identical.

For example: SELECT * from EMP;

And each of the following is different SELECT * from EMP;

Select * from EMP; SELECT * from EMP;

B. the object that the two statement refers to must be exactly the same:

   User Object Name                         How to access 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 these two users. Whether SQL can share the reason select Max (sal_cap) from Sal_limit; Not every user has a private synonym-sal_limit, they are different objects select COUNT (*) from work_city where Sdesc like ' new% '; Can two users 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.ci TY_ID cannot have user jack access plant_detail through private synonym and Jill is the owner of the table, with different objects.

C.binding variable with the same name must be used in two SQL statements (bind variables)For example: The first group of two SQL statements are the same (can be shared), and two statements in the second group are different (even at run time, the same values are assigned to different binding variables) 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 order (valid only in the rule-based optimizer)Focus on ORACLEParser processes the table names in the FROM clause in a right-to-left order, so the table that is written in the FROM clause (the underlying table driving tables) is processed first. In cases in which the FROM clause contains more than one table, you must select the table with the lowest number of record bars as the underlying table. WhenORACLEWhen working with multiple tables, they are concatenated using sort and merge. First, scan the first table (the last table in the FROM clause) and order the records, 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 from the first table. Example: Table TAB1 16,384 Records table TAB2 1 records Select TAB2 as the base table (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 there are more than 3 tables connected to the query, you need to select the Crosstab table (intersection table) as the underlying table, which refers to the table that is referenced by the other table. 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, and E.cat_no = c.cat_no and E.LOCN = L.L OCN will be 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 and 2000

Focus on the connection order in the 2:where clause . Focus on

From:qq kisses ' s blog:http://qqhack8.blog.163.com

ORACLE uses a bottom-up sequential parsing where clause, according to which the connection between tables must be written before other where conditions, and those 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 < (SELECT COUNT (*) from EMP WHERE mgr=e.empno); ( High efficiency, execution time 10.6 seconds) SELECT ... From EMP E where < (SELECT COUNT (*) from EMP where mgr=e.empno) and SAL >; 50000 and JOB = ' MANAGER ';

focus on avoiding the use of ' * ' in the 3:select clause. Focus on

When you want to list all columns in the SELECT clause, it is a convenient way to refer to ' * ' Using a dynamic SQL column. Unfortunately, this is a very inefficient approach. As a matter of factORACLEIn the process of parsing, the ' * ' will be converted to all column names in turn, which is done byEnquiryThe data dictionary is done, which means more time is spent. 7. Reduce the number of database accesses when executing each SQL statement,ORACLEA lot of work has been done internally: Parsing SQL statements, estimating index utilization, binding variables, reading blocks, and so on. This shows that by reducing the number of accesses to the database, you can actually reduceORACLEThe workload. For example, there are three ways to retrieve employees with an employee number equal to 0342 or 0291.Method 1 (minimum effect)SELECT Emp_name, SALARY, GRADE from EMP WHERE emp_no = 342; SELECT Emp_name, SALARY, GRADE from EMP WHERE emp_no = 291;Method 2 (sub-inefficiency)DECLARE CURSOR C1 (e_no number) is a 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 (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: The ArraySize parameter is reset in Sql*plus, Sql*forms, and pro*c to increase the amount of data retrieved per database access, with a recommended value of 200.

focus on 4: use the decode function to reduce processing time . Focus on using the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table. For example: SELECT COUNT (*), SUM (SAL) from the EMP WHERE dept_no = 0020 and ename like ' smith% '; SELECT COUNT (*), SUM (SAL) from the EMP WHERE dept_no = 0030 and ename like ' smith% '; You can use the DECODE function to get the same result efficiently 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% '; Similarly, the Decode function can also be used in the group BY and ORDER BY clauses.

Focus on 5: Delete duplicate records . Focus on

The most efficient method of 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: Replace Delete with truncate . Focus on

When you delete a record in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered. If you do not have a COMMIT transaction,ORACLE restores the data to the state it was before it was deleted (exactly before the delete command was executed) and when the truncate is applied, the rollback segment no longer holds any recoverable information. When the command runs, The data cannot be restored. So very few resources are invoked and execution times are short. (Translator Press: truncate only in Delete full table applies, truncate is DDL is not DML)

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

Whenever possible, use commit in your program as much as possible, so that the performance of the program is improved and the requirements are reduced by the resources freed by the commit: the resources freed by commit: a. The information that is used to recover data on the rollback segment.        B. Lock C obtained by the program statement.       Redo Space D in the log buffer. ORACLE manages the internal costs of the above 3 resources (translator presses: The integrity of the transaction must be noted in the use of commit, the efficiency of reality and the integrity of the transaction are often not available in both fish and paws)

focus on 8: Reduce the query on the table. Focus on

In the SQL statement that contains the subquery, pay particular attention to reducing the query on the table.

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 TA B_name,db_ver) from tab_columns WHERE VERSION = 604) update multiple column examples: inefficient: 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 = 002 0;

Focus on 9: Replace in with exists. Focus on

In many base-table-based queries, it is often necessary to join another table in order to satisfy one condition. In this case, using EXISTS (or not EXISTS) will usually improve the efficiency of the query. Inefficient: SELECT * from EMP (base table) WHERE EMPNO >; 0 and DEPTNO in (select DEPTNO from DEPT where LOC = ' melb ') Efficient: SELECT * from EMP (base table) WHERE EMPNO >; 0 and EXISTS (SELECT ' X ' from DEPT WHERE DEPT. DEPTNO = EMP. DEPTNO and LOC = ' Melb ') (translator by: Relatively speaking, replacing not with not exists will increase efficiency more significantly, as noted in the next section)

Focus on 10: Replace not in with NOT exists . Focus on

In a subquery, the NOT IN clause performs an internal sort and merge.   In either case, not in is the least effective (because it performs a full table traversal of the table in the subquery). To avoid using not, we can change it to an outer join (Outer Joins) or not EXISTS. For example: SELECT ... From the EMP where Dept_no not in (SELECT dept_no from DEPT where dept_cat= ' A '); To improve efficiency. Rewritten as: (Method one: Efficient) SELECT .... From EMP a,dept B WHERE a.dept_no = b.dept (+) and B.dept_no are NULL and b.dept_cat (+) = ' A ' (method two: most efficient) SELECT .... From the 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 have a table associated with it. Direct two-table relationship the speed of couplets is the fastest! Focus on 11: Identify the SQL statement for ' inefficient execution ' . Focus on

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 >; buffer_gets 0 and (Buffer_gets-disk_reads)/buffer_gets < 0.8 ORDER by 4 DESC;

About Oracle's multi-table query optimization