ORACLE's parser processes the table names in the FROM clause FROM right to left, and the table written at the end of the from clause (basic table-driven table) will be processed first. When there are multiple tables in the from clause, the table with the least number of records must be selected as the basic table. If more than three tables join the query, you need to select the intersection table as the basic table, and the basic table refers to the table referenced by other tables.
(the connection order in the where clause. ;
ORACLE parses the WHERE clause in bottom-up order. According to this principle, the connection between tables must be written before other WHERE conditions, and those conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.
(3) Avoid using' *' in the select clause:
During parsing, ORACLE will convert' *' into all column names in turn. This work is done by consulting the data dictionary, which means more time is needed.
(4) Reduce the number of visits to the database:
ORACLE has done a lot of work: parsing SQL statements, estimating index utilization, binding variables, reading data blocks and so on.
(5) Resetting the ARRAYSIZE parameters in SQL*Plus, SQL*Forms and Pro*C can increase the amount of data retrieved in each database access, and the recommended value is 200.
(6) Use the decoding function to reduce the processing time:
Use the DECODE function to avoid scanning the same records repeatedly or connecting the same tables.
(7) Simple integration and irrelevant database access:
If you have several simple database query statements, you can integrate them into one query (even if they are not related).
(8) Delete duplicate records:
Example of the most effective way to delete duplicate records (because ROWID is used):
Delete WHERE E. ROWID & gt from EMP E (select the minimum value (X.ROWID).
FROM EMP X where x.emp _ no = e.emp _ no);
(9) Replace deletion with truncation:
Under normal circumstances, when deleting records in the table, the rollback segment is used to store information that can be recovered. If the transaction is not committed, ORACLE will restore the data to the state before deletion (specifically, to the state before the deletion command is executed). When using TRUNCATE, the rollback segment no longer stores any recoverable information. When the command runs, the data cannot be recovered. Therefore, few resources are called and the execution time will be short.
(10) Use submit whenever possible:
Whenever possible, use COMMIT as much as possible in the program, so that the performance of the program will be improved and the demand will be reduced because of the resources released by COMMIT:
Commit released resources:
A. information used to recover data on the rollback segment.
B. Lock acquired by program statement
C. Redo log buffer space
D.ORACLE manages the internal expenses in the above three resources.
Replace the HAVING clause with the Where clause:
Avoid using the HAVING clause, which only filters the result set after all records are retrieved. This process requires operations such as sorting and total. If you can limit the number of records through the WHERE clause, you can reduce the overhead in this regard. (Non-Oracle Bone Inscriptions) Among the three conditional clauses on, where and HAVING, on is executed first, followed by where. Having Finally, because on filters unqualified records before making statistics, it can reduce the data to be processed in intermediate operations, which should be the fastest, and where should be faster than having, because it filters data before summing and uses on when connecting two tables, so when using a table, leave where to compare with having. In the case of single-table query statistics, if the conditions to be filtered do not involve the fields to be calculated, their results are the same, but where can use rushmore technology, but having can't, and the latter is slower. If the field to be calculated is involved, it means that the value of this field is uncertain before calculation. According to the workflow written in the last article, the action time of where is completed before calculation, and having only takes effect after calculation. In multi-table join query, on works earlier than where. The system first combines multiple tables into a temporary table according to the connection conditions between tables, and then filters by where, then calculates, and then filters by have after calculation. It can be seen that in order to correctly screen conditions, we must first understand when this condition should work, and then decide where to put it.
(12) Reduce the query of tables:
In SQL statements with subqueries, special attention should be paid to reducing queries on tables. Example:
Select TAB_NAME from the table, where (TAB_NAME, DB_VER) = (SELECT
TAB_NAME, DB_VER FROM TAB_COLUMNS where VERSION = 604)
(13) Improve SQL efficiency through internal functions.
Complex SQL often sacrifices execution efficiency. It is very meaningful to master the above methods of using functions to solve problems in practical work.
(14) Use the alias of the table:
When connecting multiple tables in an SQL statement, use the alias of the table and use the alias as the prefix of each column. In this way, parsing time can be reduced, and grammatical errors caused by column ambiguity can be reduced.
(15) Replace IN with EXISTS and Replace NOT IN with NOT EXISTS.
In many queries based on basic tables, it is usually necessary to join another table in order to satisfy one condition. In this case, using EXISTS (or NOT EXISTS) usually improves the efficiency of the query. In the subquery, the NOT IN clause will perform internal sorting and merging. In either case, NOT IN is the least efficient (because it performs a full table traversal on the tables in the subquery). To avoid using NOT IN, we can rewrite it as external connection or NOT EXISTS.
Example:
(efficient) SELECT * FROM EMP (basic table) where EMPNO >；; 0 and exists (select "x" from department, where department number = employee. DEPTNO and LOC = 'MELB')
(inefficient) SELECT * FROM EMP (basic table) where EMPNO >；; 0 and deptnoin (select deptno from deptno where loc =' melb')
(16) Identifying "inefficient execution" SQL statements:
Although various graphical tools about SQL optimization emerge one after another at present, it is always the best way to write your own SQL tools to solve problems:
SELECT execution, disk read, buffer fetch,
ROUND((BUFFER _ GETS-DISK _ READS)/BUFFER _ GETS，2) Hit_radio，
ROUND (disk read/execute, 2) Reads_per_run,
SQL _ text
From V$SQLAREA
Execution place & gt0
And BUFFER _ GETS & gt0.
And (buffer _ gets-disk _ reads)/buffer _ gets <; 0.8
Ordered by 4 DESC;
(17) Using indexes to improve efficiency;
Index is a conceptual part of table, which is used to improve the efficiency of data retrieval. ORACLE uses a complex self-balancing B-tree structure. Generally speaking, querying data through an index is faster than scanning the whole table. When ORACLE finds the best path to execute queries and update statements, ORACLE optimizer will use indexes. It can also improve efficiency when connecting multiple tables. Another advantage of using an index is that it provides uniqueness verification of the primary key. For those LONG or LONG RAW data types, almost all columns can be indexed. In general, using indexes in large tables is particularly effective. Of course, you will also find that when scanning small tables, using indexes can also improve efficiency. Although the use of indexes can improve the query efficiency, we should also pay attention to its cost. Indexes need space to store and need to be maintained regularly. Every time a record is added or deleted in a table or an index column is modified, the index itself is modified. This means that inserting, deleting and updating each record will cost 4 to 5 times of disk I/O.. Because indexes need extra storage space and processing, those unnecessary indexes will reduce query response time. It is necessary to rebuild the index regularly.
Change index & ltINDEXNAME & gt rebuild & lt tablespace name & gt
(18) Replace DISTINCT with EXISTS:
Avoid using DISTINCT in the SELECT clause when submitting a query that contains one-to-many tables of information, such as department tables and employee tables. Generally, you can consider using EXIST instead. EXIST makes the query faster, because the RDBMS core module will return the results as soon as the conditions of the subquery are met. Example:
(inefficient):
Select different department numbers and names from department D and employee E.
Where, department number = department number
(efficient):
SELECT DEPT_NO, dept _ name from dept d where existing (select "x").
FROM EMP E where e.dept _ no = d.dept _ no);
(19) sql statement capitalization; Because oracle always parses sql statements first and converts lowercase letters into uppercase letters before execution.
(20) Use the connector "+"to connect strings as little as possible in Java code!
(2 1) Avoid using NOT on indexed columns. Usually,
We should avoid using NOT, NOT index columns, which has the same effect as using functions on index columns. When "ORACLE" encounters "NOT", it will stop using indexes and perform a full table scan instead.
(22) Avoid using calculations on indexed columns.
In the WHERE clause, if the indexed column is part of a function, the optimizer will use a full table scan instead of an index.
For example:
Inefficient:
SELECT…FROM DEPT WHERE SAL * 12 & gt； 25000;
Efficient:
SELECT … FROM department SAL & gt25000/ 12;
(23) use > = replace >
Efficient:
SELECT * FROM EMP WHERE DEPTNO & gt=4
Inefficient:
SELECT * FROM EMP WHERE DEPTNO & gt III
The difference between them is that the former DBMS will jump directly to the first record with DEPT equal to 4, while the latter will first locate the record with DEPTNO=3 equal to 3, and then scan forward to the first record with DEPT greater than 3.
(24) replace OR with UNION (applicable to index columns)
Generally speaking, it is very effective to replace the OR in the WHERE clause with UNION. Using or will cause a full table scan on indexed columns. Note that the above rules are only valid for multiple index columns. If a column has no index, the query efficiency may be reduced because you have not selected or. In the following example, both LOC_ID and REGION have indexes.
Efficient:
Select location identifier, location DESC, and area.
Slave position
Where LOC_ID = 10.
alliance
Select location identifier, location DESC, and area.
Slave position
Where REGION = "Melbourne"
Inefficient:
Select location identifier, location DESC, and area.
Slave position
Where LOC_ID = 10 or REGION = "MELBOURNE "
If you insist on using OR, you need to return the index column with the least records first.
(25) Replace or with.
This is a simple and easy-to-remember rule, but the actual implementation effect needs to be tested. Under ORACLE8i, the execution paths of the two seem to be the same.
Inefficient:
Select … from the location of LOC_ID = 10 or LOC_ID = 20 or LOC_ID = 30.
High efficiency (rate)
SELECT…FROM LOCATION WHERE LOC _ IN IN( 10，20，30)；
(26) Avoid using IS NULL and IS NOT NULL on index columns.
Avoid using any columns that can be empty in the index, and ORACLE will not be able to use the index. For a single-column index, if the column contains null values, the record will not exist in the index. For a composite index, if each column is empty, the record will not exist in the index. If at least one column is not empty, the record will exist in the index. For example, if a unique index is established on columns A and B of a table, and there is a record A in the table, if the value of B is (123, null), then ORACLE will not accept the next record (insert) with the same value of A and B (123, null). But if all the index columns are empty, ORACLE will think that the whole key value is empty, and empty does not mean empty. Therefore, you can insert 1000 records with the same key value. Of course, because there are no null values in the index column, comparing the null values of the index column in the WHERE clause will cause ORACLE to disable the index.
Inefficient: (invalid index)
Department WHERE DEPT _ CODE is not empty;
High efficiency: (effective index)
WHERE DEPT _ CODE & gt=0;
(27) Always use the first column of the index:
If the index is built on more than one column, the optimizer will choose to use the index only when the where clause refers to its first leading column. This is also a simple and important rule. When only the second column of the index is referenced, the optimizer uses a full table scan and ignores the index.
(28) Replace UNION with UNION-ALL (if possible):
When an SQL statement needs two UNION query result sets, the two result sets will be merged in the way of UNION-ALL, then sorted, and finally the results will be output. If UNION ALL is used instead of UNION, sorting is unnecessary and efficiency will be improved. It should be noted that UNION ALL will repeatedly output the same record in both result sets. Therefore, you should analyze the feasibility of using UNION ALL from the perspective of business requirements. UNION will sort the result set, and this operation will use the memory SORT_AREA_SIZE. It is also very important to optimize this memory. The following SQL can be used to query the consumption of sorting.
Inefficient:
Select ACCT _ quantity, balance _ amount.
Debit _ Transaction
Where TRAN date =' 365438+ 19951Feb. 0'
alliance
Select ACCT _ quantity, balance _ amount.
Debit _ Transaction
Where TRAN date =' 365438+ 19951Feb. 0'
Efficient:
Select ACCT _ quantity, balance _ amount.
Debit _ Transaction
Where TRAN date =' 365438+ 19951Feb. 0'
Joint ownership
Select ACCT _ quantity, balance _ amount.
Debit _ Transaction
Where TRAN date =' 365438+ 19951Feb. 0'
(29) change ORDER to WHERE:
The ORDER BY clause uses indexes only under two strict conditions.
All columns in ORDER BY must be contained in the same index and keep the order in the index.
All columns in ORDER BY must be defined as non-empty.
The index used in the WHERE clause and the index used in the ORDER BY clause cannot be collocated.
For example:
Table DEPT contains the following columns:
DEPT_CODE primary key is not empty.
DESC department is not empty.
The department type is empty.
Inefficient: (index not used)
Select the department code from the department order by department type.
Efficient: (using indexes)
Select the department code from the department, where the department type & gt0.
(30) Avoid changing the types of index columns;
When comparing data of different data types, ORACLE automatically performs simple type conversion on columns.
Suppose EMPNO is an index column of numeric type.
SELECT…FROM EMP WHERE EMPNO = ' 123 '
In fact, after ORACLE type conversion, this statement is converted to:
SELECT…FROM EMP WHERE EMPNO = TO _ NUMBER(' 123 ')
Fortunately, type conversion does not occur on indexed columns, and the purpose of indexing has not changed.
Now, suppose EMP_TYPE is an index column of character type.
SELECT … FROM EMP, where EMP_TYPE = 123.
ORACLE converts this statement into:
SELECT…FROM EMP where _ NUMBER(EMP _ TYPE)= 123
The index will not be used due to internal type conversion! In order to avoid implicit type conversion of your SQL by ORACLE, it is best to express the type conversion explicitly. Note that when comparing characters with numeric values, ORACLE will give priority to converting numeric types to character types.
(3 1) WHERE clause to pay attention to:
The WHERE clause in some SELECT statements does not use indexes. Here are some examples.
In the following example, (1)'! =' Index will not be used. Remember, an index can only tell you what exists in the table, not what doesn't exist in the table. (2)' ||' is a character concatenation function. As with other functions, indexing is disabled. (3)'+'is a mathematical function. Just like other mathematical functions, indexes are disabled. (4) The same index columns cannot be compared with each other, which will
(32) A. If the retrieved data exceeds 30% of the records in the table, there will be no obvious efficiency improvement by using the index.
B. In some cases, using indexes may be slower than full table scanning, but they are of the same order of magnitude. Usually using an index is several times or even thousands of times faster than scanning a full table!
(33) Avoid using operations that consume resources:
SQL statements with distinct, union, Minus, intersect and order by will start the SQL engine.
Perform the sorting function that consumes resources. DISTINCT needs to be sorted once, while others need to be sorted at least twice. Generally, SQL statements with Union, Minus and Intersect can be rewritten in other ways. If the SORT_AREA_SIZE of your database is well allocated, Union, Minus and Intersect can also be considered, after all, it is very readable.
(34) Optimize grouping by:
In order to improve the efficiency of GROUP BY statement, we can filter out unnecessary records before GROUP BY. The following two queries return the same results, but the second query is obviously much faster.
Inefficient:
Select a job, AVG (sal)
From electromagnetic pulse
Group work
Have a job = "President"
Or JOB = 'MANAGER'
Efficient:
Select a job, AVG (sal)
From electromagnetic pulse
Where JOB = "president"
Or JOB = 'MANAGER'
Group work