oracle| Performance | optimization | statement 1. Choose the appropriate Oracle Optimizer
Oracle's optimizer total of 3 kinds A, rule (rule-based) B, cost (based on costs) C, CHOOSE (optional) set the default optimizer to pass various declarations of optimizer_mode parameters in the Init.ora file, such as Rule,cost,choose,all_rows,first_rows. You will of course overwrite it at the SQL sentence level or at the session level. in order to use the cost-based optimizer (CBO, cost-based Optimizer), you must frequently run the Analyze command to increase the accuracy of object statistics in the database (objects statistics). if the optimizer mode of the database is set to selectivity (CHOOSE), the actual optimizer pattern will be related to whether the Analyze command has been run. If the table has been analyze, the optimizer pattern will automatically become a CBO, whereas the database will use the rule-form optimizer. by Default, Oracle employs the Choose Optimizer, and in order to avoid unnecessary full table scans (scan), you must avoid using the Choose Optimizer as much as possible, using a rule-based or cost-based optimizer.
2. How to Access table
ORACLE takes two ways to access records in a table: A, full table scan A full table scan is a sequential access to each record in the table. Oracle optimizes full table scans in a way that reads multiple data blocks (database block) at a time. B, access to the table through rowID You can use ROWID access mode to improve the efficiency of the Access table, ROWID contains the physical location information recorded in the table. Oracle uses indexes (index) to achieve the link between the data and the physical location (ROWID) where the data resides. Often indexes provide a quick way to access rowid, so queries based on indexed columns can improve performance.
3. Sharing SQL statements
In order not to parse the same SQL statement repeatedly, Oracle stores the SQL statements in memory after the first parsing. This memory in the shared buffer pool, which is located in the system global Zone's SGA (System global Area), can be shared by all database users. Therefore, when you execute an SQL statement (sometimes referred to as a cursor), if it is exactly the same as the previous executed statement, Oracle can quickly get the statements that have been parsed and the best execution path. This feature of Oracle greatly improves the execution performance of SQL and saves memory usage. Unfortunately, Oracle only provides high-speed buffering (cache buffering) for simple tables, and this feature does not apply to multiple table join queries. The database administrator must set the appropriate parameters for the area in the Init.ora, and the larger the memory area, the more statements can be retained and the greater the likelihood of being shared. when you submit an SQL statement to Oracle, Oracle first looks for the same statement in this block of memory. It should be noted here that Oracle is a strict match between the two, to achieve sharing, the SQL statement must be exactly the same (including spaces, line wraps, etc.). The database administrator must set the appropriate parameters for the area in the Init.ora, and the larger the memory area, the more statements can be retained and the greater the likelihood of being shared. the shared statement must meet three criteria: A, character-level comparisons: The statements that are currently executed and the statements in the shared pool must be identical. the objects referred to in B, two must be exactly the same: C, two SQL statements must use the same name of the binding variable (bind variables).
4. Select the most efficient table name order (valid only in the Rule-based optimizer)
The Oracle parser processes the table names in the FROM clause in Right-to-left order, so the last table in the FROM clause (the underlying table driving tables) is processed first. In the case where multiple tables are included in the FROM clause, you must select the table with the least number of records as the underlying table. When Oracle processes multiple tables, it uses sorting and merging to connect them. First, scan the first table (the last table in the FROM clause) and dispatch the records, then scan the second table (the last second table in the FROM clause), and finally merge all the records retrieved from the second table with the appropriate records in the first table. If you have more than 3 table join queries, you need to select the Crosstab table (intersection table) as the underlying table, which is the table referenced by the other tables.
connection order in the 5.WHERE clause
Oracle parses the WHERE clause in a bottom-up order, according to which the connection between the tables must be written before the other where conditions, and the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.
avoid the use of ' * ' in the 6.SELECT clause
When you want to list all columns in the SELECT clause, it is a convenient way to use the dynamic SQL column reference ' * '. Unfortunately, this is a very inefficient approach. In fact, Oracle converts ' * ' to all column names in the parsing process, which is done by querying the data dictionary, which means more time will be spent.
7. Reduce the number of accesses to the database
When executing every SQL statement, Oracle does a lot of work internally: Parsing SQL statements, estimating index utilization, binding variables, reading chunks, and so on. Thus, reducing the number of accesses to the database can actually reduce Oracle's workload.
8. Use the Decode function to reduce processing time
Use the Decode function to avoid repeatedly scanning the same record or repeating the same table.
9. Simple integration, no associated database access
If you have a few simple database query statements, you can integrate them into a single query (even if there is no relationship between them) .
10. Delete duplicate records 11. Replace Delete with truncate
When you delete records in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered. If you do not commit a transaction, Oracle restores the data to the state it was in before it was deleted (or, to be exact, the condition before the delete command was executed). when using truncate, the rollback segment no longer holds any recoverable information. When the command is running, the data cannot be recovered. As a result, few resources are invoked and execution time is short.
12. Use of commit as much as possible
Whenever possible, use a commit in the program as much as possible, so that the performance of the program is improved and the requirements are reduced by the resources released by the commit Resources released by commit: A, the information used to recover data on the rollback segment. B, the lock obtained by the program statement. C, the space in the redo log buffer. D, Oracle is managing the internal cost of the 3 resources mentioned above.
13. Counting the number of record bars
Contrary to general opinion, COUNT (*) is slightly faster than count (1), although the count of indexed columns is still the fastest if indexed retrieval is possible. such as COUNT (EMPNO)
14. Replace the HAVING clause with the WHERE clause
Avoid the HAVING clause, which will filter the result set only after all records have been retrieved. This processing requires sorting, totals, and so on. If you can limit the number of records through the WHERE clause, you can reduce the overhead.
15. Reduce the query to the table
In SQL statements that contain subqueries, you should pay special attention to reducing the query to the table.
16. Improve SQL efficiency through internal functions. 17. Use table alias (alias)
When you are connecting multiple tables in an SQL statement, use the alias of the table and prefix the alias with each column. This reduces the time to parse and reduces the syntax errors caused by column ambiguity.
18. Replace in with exists
In many base table based queries, a join to another table is often required to satisfy one condition. In this case, using EXISTS (or not EXISTS) usually increases the efficiency of the query.
19. Use NOT exists instead of in
In a subquery, the NOT IN clause performs an internal sort and merge. In either case, not in is the least efficient (because it performs a full table traversal of the table in the subquery). In order to avoid using not in, we can rewrite it as an outer join (Outer joins) or not EXISTS.
20. Replace exists with table connection
In general, table joins are more efficient than exists
21. Replace distinct with exists
Avoid using DISTINCT in the SELECT clause when submitting a query that contains a one-to-many table of information, such as a department table and an employee table. You can generally consider replacing with exist
WHERE Clause
Try to avoid operations on the database objects referenced the WHERE clause.
| Given Query |
Alternative |
SELECT ename, HireDate, Sal
From EMP
WHERE SUBSTR (ename,1,3) = ' SCO '; |
SELECT ename, HireDate, Sal
From EMP
WHERE ename like ' sco% '; |
VARIABLE name VARCHAR2 (20)
EXEC name: = ' SCOTT '
SELECT ename, HireDate, Sal
From EMP
WHERE ename = NVL (: Name, ename); |
VARIABLE name VARCHAR2 (20)
EXEC name: = ' SCOTT '
SELECT ename, HireDate, Sal
From EMP
WHERE ename like NVL (: Name, '% '); |
SELECT ename, HireDate, Sal
From EMP
WHERE TRUNC (hiredate) = TRUNC (sysdate); |
SELECT ename, HireDate, Sal
From EMP
WHERE hiredate BETWEEN TRUNC (sysdate)
and TRUNC (sysdate) +. 99999; |
SELECT ename, HireDate, Sal
From EMP
WHERE ename | | empno = ' SCOTT7788 '; |
SELECT ename, HireDate, Sal
From EMP
WHERE ename = ' SCOTT
and empno = 7788; |
SELECT ename, HireDate, Sal
From EMP
WHERE Sal + 3000 < 5000; |
SELECT ename, HireDate, Sal
From EMP
WHERE Sal <; |
SELECT ename, HireDate, Sal
From EMP
WHERE Sal != 0; |
SELECT ename, HireDate, Sal
From EMP
WHERE sal > 0; |
Having Clause
The Having clause filters selected rows have been fetched. Using a WHERE clause helps reduce overheads in sorting, summing, etc. Having clauses should is used when columns with summary operations applied to them are by the restricted.
| Given Query |
Alternative |
SELECT D.dname, AVG (e.sal)
From EMP E, Dept D
WHERE E.deptno = D.deptno
GROUP by D.dname
having dname!= ' Reseaech '
and dname!= ' SALES '; |
SELECT D.dname, AVG (e.sal)
From EMP E, Dept D
WHERE E.deptno = D.deptno
and dname!= ' Reseaech '
and dname!= ' SALES '
GROUP by D.dname; |
Combined Subqueries
Minimize the number of table lookups (subquery blocks) in queries, particularly if your statements include subquery SE Lects or multicolumn UPDATEs.
| Separate subqueries |
Combined subqueries |
SELECT ename
From EMP
WHERE sal = (SELECT MAX (SAL)
From lookup)
and comm = (SELECT MAX (comm)
From lookup); |
SELECT ename
From EMP
WHERE (Sal,comm) = (SELECT MAX (SAL),
MAX (Comm)
From lookup); |
EXISTS, not in, Table joinsConsider the alternatives EXISTS, in and table joins when doing multiple table joins. None of these are consistently faster; It depends on your data.
SELECT ename
From EMP E
WHERE EXISTS (SELECT ' X '
From Dept
WHERE Deptno = E.deptno
and dname = ' ACCOUNTING '); |
SELECT ename
From EMP E
WHERE deptno in (SELECT deptno
From Dept
WHERE Deptno = E.deptno
and dname = ' ACCOUNTING '); |
SELECT ename
From Dept D, EMP E
WHERE E.deptno = D.deptno
and d.dname = ' ACCOUNTING '; |
DISTINCTAvoid joins that require DISTINCT qualifier on the SELECT list in queries which are used to determine information at t He owner end of a one-to-many relationship. The DISTINCT operator causes Oracle to fetch all rows satisfying the table join and then sort and filter out duplicate Val UEs. EXISTS is a faster alternative because the Oracle optimizer realizes when the subquery has been satisfied, once No need to proceed further and the next matching row can be fetched.
| Given Query |
Alternative |
SELECT DISTINCT D.deptno, D.dname
From Dept D,
EMP E
WHERE D.deptno = E.deptno; |
SELECT D.deptno, D.dname
From Dept D
WHERE EXISTS (SELECT ' X '
From EMP E
WHERE E.deptno = D.deptno); |
UNION AllConsider whether a UNION all would suffice in place of a UNION. The UNION clause forces all rows returned by each portion of the UNION to being sorted and merged and duplicates to be filtered before the ' the ' is returned. A UNION All simply returns all rows including duplicates and does not have to perform any sort, merge or filter. If your tables are mutually exclusive (include no duplicate records), or you don ' t care if duplicates are returned, the UN ION all are much more efficient.
| UNION |
UNION All |
SELECT Acct, Balance
From debit
WHERE trandate = ' 31-dec-95 '
UNION
SELECT Acct, Balance
From credit
WHERE trandate = ' 31-dec-95 '; |
SELECT Acct, Balance
From debit
WHERE trandate = ' 31-dec-95 '
UNION All
SELECT Acct, Balance
From credit
WHERE trandate = ' 31-dec-95 '; |
DECODEConsider using DECODE to avoid has to scan the same rows repetitively or join the same table repetitively. Note, DECODE isn't necessarily faster as it depends on your data and the complexity of the resulting query. Also, using DECODE requires you to change your code when new values are the field.
SELECT COUNT (*)
From EMP
WHERE status = ' Y '
and ename like ' smith% ';
----------
SELECT COUNT (*)
From EMP
WHERE status = ' N '
and ename like ' smith% '; |
SELECT COUNT (DECODE(status, ' Y ', ' X ', NULL)) Y_count,
COUNT (DECODE(status, ' N ', ', ' X ', NULL)) N_count
From EMP
WHERE ename like ' smith% '; |
Anti joins
An anti-join are used to return rows from a table, that's are present in another table. It might be used for example between DEPT and EMP to return only those rows in DEPT that didn ' t join to anything in emp;
SELECT *
From Dept
WHERE Deptno not in (SELECT deptno from EMP); |
SELECT dept.*
From Dept, EMP
WHERE Dept.deptno = Emp.deptno (+)
and EMP. ROWID is NULL; |
SELECT *
From Dept
Where not EXISTS (SELECT NULL from emp WHERE emp.deptno = Dept.deptno); |
Full Outer joins
normally, an outer join the table A to table B would return every the record in table A, and if it had a mate in table B, t Hat would be returned as. Every row in table A would is output, but some rows of table B might not appear to the result set. A full outer join would return ebery row in table A, as as and as every row in table B. The syntax for a full outer join are new in Oracle 9i, but it are a syntactic convenience, it is possible to produce full OU TER joins sets using conventional SQL.
Update emp Set deptno = 9 where deptno = 10;
commit;
| Conventional SQL |
New Syntax |
select empno, ename, Dept.deptno, Dname
from EMP, dept
WHERE Emp.deptno (+) = Dept.deptno
UNION all
SELECT empno, ename, Emp.deptno, NULL
from EMP, dept
WHERE Emp.dept No = Dept.deptno (+)
and Dept.deptno is NULL
Order by 1,2,3,4; EMPNO ename DEPTNO dname
--------------------------------------------
7369 SMITH in
7499 all EN Sales
7521 WARD sales
7566 JONES,
7654 MARTIN sales
7698 BLAKE Sales br>7782 CLARK 9
7788 SCOTT
7839 KING 9
7844 TURNER SALES
7876 ADAMS br>7900 JAMES SALES
7902 FORD
7934 MILLER 9
ACCOUNTING
OPERATIONS td> |
SELECT empno, ename,
NVL (Dept.deptno,emp.deptno) Deptno, dname
From EMP full OUTER JOIN dept on
(Emp.deptno = Dept.deptno)
Order BY 1,2,3,4;
EMPNO ename DEPTNO dname
---------- ---------- ---------- --------------
7369 SMITH
7499 ALLEN SALES
7521 WARD SALES
7566 JONES
7654 MARTIN SALES
7698 BLAKE SALES
7782 CLARK 9
7788 SCOTT
7839 KING 9
7844 TURNER SALES
7876 ADAMS
7900 JAMES SALES
7902 FORD
7934 MILLER 9
Ten ACCOUNTING
OPERATIONS |
