1.
Writing format specification
1-1. Unification of size text and space
1-2, the date format is clear
1-3. Use of BIND variables
1-4. Use of table aliases
1-5. Try to avoid retrieving unnecessary columns when retrieving
1-6. The ORDER by column expressly specifies
1-7. Insert column explicitly specified
1-8. Restrictions on the number of related tables
1-9. Do not use views in subqueries
1-10, the hint of the wording
1-11. Naming specification
2.
Index Application Specification
2-1. In the WHERE clause, try not to use or
2-2, where clause as far as possible without like '%c% ', like '%c '
2-3, WHERE clause try not to use not
2-4, WHERE clause try not to be null, is not NULL
2-5, in where the use of <>,!= carefully
2-6. Supplement for use of is not NULL in the WHERE clause
2-7, try to use exists instead of distinct
2-8, order by use of the attention point
2-9, index column try not to participate in the calculation
2-10. The preceding column in the composite index should be specified in the condition
3.
Other specifications
3-1, column comparison as far as possible to maintain the same type
3-2, try to avoid using sub-query
3-3, sub-query level limit
3-4, try to use not exists instead of In + sub-query
3-5. Replace exists with table connection
3-5. Try not to use the HAVING clause
3-6. Specify the order for the tables in the FROM clause
3-7. Connection order in the WHERE clause
3-8, the use of ROWID
3-9. Use rownum to determine whether the record exists
3-10. How to Write page-turn SQL (next n records obtained)
3-11, the use of the merge
3-12. Use of multiple table inserts
3-13. Restrictions on the use of Dblink
3-14, try to use decode instead of Set function
3-15. Delete duplicate records
3-16, reduce the amount of the table query
3-17, avoid the use of resource-intensive operations
1. Writing format specification
1-1. Unification of size text and space
Database object name (table, index, etc.) and variable full half-width lowercase, other SQL text full half-width capitalization, avoid the blank space, if using spaces only use half-width space, not only to maintain the readability of SQL, and to minimize the SQL parsing time.
Because Oracle's shared SQL mechanism only finds an exact match in the shared pool (character-level comparisons, including whitespace, character case), SQL does not need to be parsed (parsing) to be executed SQL, so try to maintain a uniform style of SQL.
Error: Select E.emp_no from EMP E;
Yes: SELECT e.emp_no from EMP e;
1-2, the date format is clear
If you do not explicitly specify a time format, by default using the time format specified by the Nls_date_format parameter, you may not get the results you want, so you need to explicitly convert and use TO_CHAR or to_date functions.
Error: SELECT e.ename, e.hire_date from emp e WHERE e.hire_date > ' 20100726 '
Pair: SELECT e.ename, To_char (e.hire_date, ' YYYYMMDD ') from emp e WHERE e.hire_date > To_date (' 20100726 ', ' YYYYMMDD ')
1-3. Use of BIND variables
For SQL text with different conditional values for the same condition, the bind variable is used to make it the same SQL, thus reducing the parsing time of Oracle. (commonly used in the Java language as a bind variable)
Error: SELECT e.ename from emp e WHERE e.emp_no = 123;
SELECT e.ename from emp e WHERE e.emp_no = 567;
Yes: SELECT e.ename from emp e WHERE e.emp_no =?;
1-4. Use of table aliases
When you are querying a multi-table association, the column is preceded by a table alias, so that both the definition of the column and the parsing time of the SQL text are reduced.
Error: SELECT ename, dname from EMP, dept WHERE Emp.deptno=dept.deptno and Sal >1000;
Pair: SELECT e.ename, d.dname from EMP E, dept D WHERE E.deptno=d.deptno and E.sal >1000;
1-5. Try to avoid retrieving unnecessary columns when retrieving
When retrieving records, especially when the records are relatively long, try not to use ' * ' instead of all the columns, so as not only to increase processing time (Oracle in the process of parsing, will be ' * ' into all the column names, this work is done by querying the data dictionary, which means that it will take more time, It also increases the amount of I/O, and when the table structure changes, the original column order may change completely and cause unnecessary bugs or modifications.
Error: SELECT * from emp WHERE Eadrs = "Shanghai City";
Yes: Select E.empno e.ename from emp WHERE E.eadrs = "Shanghai City";
1-6. The ORDER by column expressly specifies
Although you can use the ORDER BY clause to specify the columns that you want to sort according to the column number of the column that is retrieved from the SELECT clause, you should try not to use the column number but explicitly specify the sort order from the maintenance of the code.
Error: SELECT e.emp_no, E.ename, e.sal from emp e ORDER by 2;
Yes: SELECT e.emp_no, E.ename, e.sal from emp e ORDER by E.ename;
1-7. Insert column explicitly specified
When you insert all columns on the object table, the SQL syntax allows you to omit the designation of the column, but because the table structure (column additions and deletions) is subject to change, the insertion column is explicitly specified even if full column insertion is required.
Error: INSERT into emp VALUES (' AAA ');
Yes: INSERT into EMP (emp_no, ename) VALUES (' AAA ');
1-8. Restrictions on the number of related tables
In the case of multi-table association queries, the Oracle access path will become more complex as the associated table increases, leading to an unreasonable implementation plan for Oracle, so avoid unnecessary associations.
Error: SELECT ~ from EMP1 E1, EMP2 E2, Emp3 E3; Emp3 for tables that do not need to be associated
Yes: SELECT ~ from EMP1 E1, EMP2 E2;
1-9. Do not use views in subqueries
Using a view in a subquery complicates the access path for Oracle, resulting in an unreasonable implementation plan for Oracle, so try not to use views in subqueries.
CREATE VIEW Emp_v as SELECT e.name, e.sal*12 sal from emp e WHERE E.deptno < 120;
Error: Select T1.dept_no, t2.ename from dept1 T1, (select V.ename, from Emp_v where V.sal <) t2 where t1.dept_no = t2.d Ept_no;
Pair: Select T1.dept_no, t2.ename from dept1 T1, (select V.ename, from emp e WHERE e.salary < (3000/12) and E.dept_no < T2 WHERE t1.dept_no = t2.dept_no;
1-10, the hint of the wording
General SQL does not consider the use of hint sentences, unless the performance test and other test results when the performance is very poor to consider the use. (The original implementation plan will be changed after using hint)
Error: SELECT/*+first_rows*/e.ename from emp e WHERE e.emp_no = ' SCOTT ';
Yes: SELECT e.ename from emp e WHERE e.emp_no = ' SCOTT ';
1-11. Naming specification
Table aliases, when the column alias name, as far as possible according to the original table name and column name abbreviated form, to maintain the readability of SQL.
Wrong: [table name]employee-->[alias]a
Yes: [table name]employee-->[alias]emp 2, index Application Specification
2-1. In the WHERE clause, try not to use or
With or, in some cases (involving the scanning of an index), the retrieval of the index becomes invalid, thereby reducing the performance of the retrieval, in which case you might consider whether to substitute union or in.
RBO (rule-based), or, like the expansion of the Union all, cannot be executed in the case of an external union, CONNECT by clause. The CBO (cost-based), or according to the basis of the combination of the combined cost of the order, in the re-estimation phase, according to the cost of inlist and or can be extended to judge.
Wrong 1:select name from emp WHERE deptno = OR Deptno = 999;
To 1:select name from emp WHERE DEPTNO = 99
UNION All
SELECT name from emp WHERE deptno = 999;
Error 2:~ from emp WHERE deptno = ten or Deptno = or Teptno = 30;
To 2:~ from EMP WHERE deptno in (10,20,30);
2-2, where clause as far as possible without like '%c% ', like '%c '
When you use like '%c% ' on indexed columns, the index is invalidated and performance is degraded.
Wrong: ~ WHERE name like '%c% ';
Wrong: ~ WHERE name like '%c ';
Yes: ~ WHERE name like ' c% ';
2-3, WHERE clause try not to use not
After using not,! =, <> on indexed columns, index retrieval is invalidated and performance degrades.
Example 1:job column most values are null or ' salesman ', and the job column has a b*tree index.
Wrong: ~ WHERE job! = ' salesman '
Yes: ~ WHERE job > ' salesman ' OR job < ' salesman '
Example 2:dname is a text column with a b*tree index, and the cardinality is relatively small (the value is less, such as the gender column, only male and female two values), and is not updated frequently.
Error: ~ WHERE dname is not NULL;
Yes: DROP INDEX btree_dname_idx;
CREATE BITMAP INDEX bitmap_dname_idx on EMP (dname);
SELECT ~ WHERE dname is not NULL;
2-4, WHERE clause try not to be null, is not NULL
The B*tree index of the corresponding retrieved column loses its index function after it has been null, is not NULL, resulting in a significant decrease in performance.
You should also consider whether you really need a null value when using is null. is not NULL in the case of statistical information obtained under the CBO, the retrieval of the index may also be used (referring to the supplement used by is not null). Even if you use is NULL, the is Null,bitmap index is still used.
Wrong: HireDate is listed as a date type, B*tree index is built on the HireDate column, and ' 9999-12-31 ' is a date that is not possible in the system, execute the following SQL
SELECT ~ WHERE hiredate is NULL;
Pair: CREATE INDEX function_hiredate_idx on EMP (NVL (HireDate, to_date (' 9999-12-31 ')));
SELECT ~ WHERE NVL (hiredate, to_date (' 9999-12-31 ')) = To_date (' 9999-12-31 ', ' YYYYMMDD ');
2-5, in where the use of <>,!= carefully
Remember that the index can only tell you what is present in the table, not what does not exist in the table.
In the following example, '! = ' will not use the index.
Do not use the index: SELECT account_name from transaction WHERE amout! = 0;
Use index: SELECT account_name from transaction WHERE amout > 0;
2-6. Supplement for use of is not NULL in the WHERE clause
Using is not NULL on a column with a lower null-value ratio will invalidate the B*tree index, thereby reducing retrieval performance, whereas using the is not Null,b*tree index on a column with a fairly high value of NULL will use a full-table scan to provide high performance. (This is under the condition that the CBO has obtained the statistical information)
Example: Comm is a numeric column with B*tree indexes, and the ratio of null values is quite high (CBO and statistical intelligence has been obtained)
Wrong: ~ WHERE Comm >= 0 OR Comm < 0;
Yes: ~ WHERE comm is not NULL;
2-7, try to use exists instead of distinct
Dept table and EMP Table is a one-to-many relationship, according to the EMP table from the Dept table to take records, this situation to avoid the use of distinct, because all records that meet the criteria after distinct will be retrieved, sorted, duplicate row deletion, thus affecting performance. Instead, the exsits subquery can be used, and the exsits subquery returns without processing the remaining records as soon as one of the qualifying records is present, so it is fast.
Error: SELECT DISTINCT d.dept_code, d.dept_name from Dept D, emp e WHERE e.dept_code = D.dept_code;
Pair: Select D.dept_code, d.dept_name from Dept D where Exsits (select ' X ' from emp e where e.dept_code = D.dept_code);
2-8, order by use of the attention point
If you want the ORDER BY clause to be indexed, you must meet the following two conditions
1), the column order in the ORDER BY clause is fully contained in a composite index in the same order
2), the columns in the order by sentence are all defined in the table as NOT NULL columns
2-9, index column try not to participate in the calculation
When an indexed column in a retrieval condition participates in a calculation or is used as a parameter to a function, the index function of that column is lost, resulting in a dramatic decrease in performance.
It can be used to calculate the result or function value in advance by the method of building function index.
Wrong: ~ WHERE sal*1.1 > 950;
Yes: ~ WHERE sal > 950/1.1;
Wrong: ~ WHERE name | | Type = ' Xxxy ';
To: ~ WHERE name = ' XXX ' and type = ' Y ';
Wrong: ~ WHERE to_char (hiredate, ' YYYYMMDD ') = ' 20100722 ';
Yes: ~ WHERE hiredate = to_date (' 20100722 ', ' YYYYMMDD ');
Error: ~ WHERE SUBSTR (name, 1, 7) = ' captial ';
Yes: ~ WHERE name like ' captial% ';
Error: ~ WHERE TRUNC (trans_date) = TRUNC (sysdate);
Right: ~ WHERE trans_date between TRUNC (Sysdate) and TURNC (sysdate) +. 99999
Note: When you add more than 5 decimal places to a date, the date automatically becomes the date of the next day.
To_date (' 2010-7-22 ') +.99999 & #61664; ' 2010-7-22 23:59:59 '
To_date (' 2010-7-22 ') +.999999 & #61664; ' 2010-7-23 00:00:00 '
2-10. The preceding column in the composite index should be specified in the condition
When you use a composite index, the preceding columns in the composite index are specified in the criteria. If the previous column is not specified in the condition, the index may be used by the skip scan function, but it is important to verify that the index is actually being used.
(Depno, job) a composite index of column order
Yes: ~ WHERE depno = ' job = ' MANAGER ';
Yes: ~ WHERE job = ' MANAGER ' and depno = 20;
Yes: ~ WHERE depno = 20; Use the front part of the index
Yes: ~ WHERE job = ' MANAGER '; In this case, be sure to verify that the index is being used
3. Other specifications
3-1, column comparison as far as possible to maintain the same type
When numeric columns and text columns are compared, the text columns are automatically converted to numeric values, but such processing invalidates index-based retrieval, thereby reducing performance.
Cases:
Right: ~ WHERE Emp_no = 123 (emp_no indexed numeric column)
May not be right: ~ WHERE emp_no = ' 123 ' (emp_no indexed numeric column)
Note) Oracle will do the conversion in actual execution ~ where Emp_no = To_number (' 123 ') and may cause the index not to be used
Right: ~ WHERE Emp_type = ' 123 ' (Emp_tepe indexed text column)
Wrong: ~ WHERE Emp_type = ' 123 ' (Emp_tepe indexed text column)
Note) Oracle will do the conversion of the ~ where To_number (emp_no) = ' 123 ' In actual execution, resulting in invalid index use
3-2, try to avoid using sub-query
The increased degrees of freedom on SQL access paths that do not use subqueries, and the increased processing performance, therefore do not use subqueries in principle by considering whether they can be replaced by the usual combination of processing.
Error: Select E.ename from emp E, (select D1.deptno from dept d1 where D1.deptno = ' A ') d where e.deptno = D.deptno;
Yes: SELECT e.ename from emp E, dept d WHERE E.deptno = d.deptno and D.deptno = ' A ';
3-3, sub-query level limit
Nested subqueries in subqueries can complicate the implementation of Oracle, with the higher the level of subqueries, the more likely it is to execute an imprecise plan. Therefore, try to avoid the use of nested subqueries.
3-4, try to use not exists instead of In + sub-query
Internal sorting, merging, and in order to improve performance can be done by using the not-in calculus in the subquery, instead of the not-in + subquery in the case of exists.
Error: Select E.ename from emp e where E.deptno not in (SELECT D.deptno from dept d where D.deptno = E.deptno and D.dept_cat = ' A ');
Yes: Select E.ename from emp e where not EXISTS (SELECT ' X ' from dept d WHERE D.deptno = e.deptno and D.dept_cat = ' A ');
3-5. Replace distinct with exists
Avoid using DISTINCT in the SELECT clause when submitting a query that contains one-to-many table information, such as a departmental table and an employee table. You can generally consider replacing with exist.
Note) EXISTS makes queries faster because the RDBMS core module returns results immediately after the conditions of the subquery have been met.
Inefficient: SELECT DISTINCT dept_no,dept_name from dept d,emp E
WHERE d.dept_no = e.dept_no;
Efficient: SELECT dept_no,dept_name from Dept D
WHERE EXISTS (SELECT ' X ' from emp E
WHERE e.dept_no = d.dept_no);
3-6. Replace exists with table connection
In general, table joins are more efficient than exists
Note) In the case of Rbo, the former's execution path includes the filter, which uses the nested LOOP
Low efficiency: SELECT ename
From EMP E
WHERE EXISTS (SELECT ' X ' from dept
WHERE dept_no = e.dept_no and Dept_cat = ' A ');
Efficient: SELECT ename from Dept D, EMP E
WHERE e.dept_no = d.dept_no and Dept_cat = ' A ';
3-5. Try not to use the HAVING clause
Having the clause is to select all the records, the selected records are then filtered by the conditions, in contrast with the WHERE clause to filter the selected records better, so when using the HAVING clause to consider whether it can be moved to the WHERE clause implementation.
Wrong:
SELECT E.deptno AVG (e.sal) from emp e GROUP by E.deptno have e.deptno > 10;
Right:
SELECT E.deptno AVG (e.sal) from emp e WHERE e.deptno > Ten GROUP by E.deptno;
3-6, the choice of the base table
The base table (Driving table, also called the driver table) refers to the first table accessed (usually accessed as a full table scan). Depending on the optimizer, the selection of the underlying table in the SQL statement is different.
In CBO mode, the optimizer checks the physical size of each table in the SQL statement, the state of the index, and then chooses the least expensive execution path.
In Rbo mode, when the table combines all the same conditions, right-to-left is combined from the FROM clause, and the rightmost table of the FROM clause is the base table. Because the lower the number of base table records, the better the performance, the binding table is specified in the FROM clause in large to small order by record count.
Note) However, if the condition specifies a change in the number of pieces, it is specified as the number of changes from large to small.
CBO Example: SELECT A.name,b.manager from worker a,lodging B
WHERE a.loding = b.loding;
Because there is an index on the loding column of the lodging table, and there is no comparison index in the worker table, the worker table is used as the underlying table in the query.
Rbo Example:
Wrong: ~ from A,b,c; (Number of records C < b < a)
Yes: ~ from C,b,a; (Number of records C < b < a)
3-7. Connection order in the WHERE clause
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.
Wrong: SELECT ...
From EMP E
WHERE sal > 50000
and job = ' MANAGER '
< (SELECT COUNT (*) from EMP WHERE mgr=e.empno);
Yes: SELECT ...
From EMP E
where < (SELECT COUNT (*) from EMP WHERE mgr=e.empno)
and Sal > 50000
and job = ' MANAGER ';
3-8, the use of ROWID
When using the select result as a condition to delte or update, use the ROWID as a condition to reduce the load.
Note) rowID is the physical record designation for the actual Oracle data block in the database, and retrieval by ROWID is the quickest way to retrieve it.
Error: SELECT e.ename into:emp_ename from emp e WHERE e.emp_no = 123 for UPDATE of E.ename;
UPDATE emp e SET e.ename = ' XXX ' WHERE e.emp_no = 123;
Pair: SELECT e.ename e.rowid into:emp_ename,:emp_rowid from emp e WHERE e.emp_no = 123 for UPDATE of E.ename;
UPDATE emp e SET e.ename = ' XXX ' WHERE ROWID = E.emp_rowid;
3-9. Use rownum to determine whether the record exists
Record presence or not use rownum<=1 to determine the efficiency is higher, because when specifying rownum<=1, as long as a record is found the execution of SQL is ended, so can be constant number of the speed of the implementation.
Error: Select ' x ' from dual where EXISTS (select ' x ' from emp where sal > 100);
Yes: SELECT ' X ' from dual WHERE sal > rownum<=1;
3-10. How to Write page-turn SQL (next n records obtained)
When you remove n records, you can significantly reduce the performance of response by qualifying the result set with rownum in the form clause and then specifying the number of pieces in the WHERE clause.
Error: Select Row_num,empno,name from (select ROWNUM row_num, empno,name where emp) WHERE row_num >= 6 and row_num <= 10;
Yes: Select Row_num,empno,name from (select ROWNUM row_num, empno,name where emp ROWNUM <=) WHERE row_num >= 6;
3-11, the use of the merge
Merge is a new function after Oracle 9i, the corresponding condition of the record exists when the update is performed, does not exist when the insert processing. Using merge simplifies programming effort and complexity.
Wrong: DECLARE
CURSOR Dept_cur is SELECT * from dept FOP UPDATE;
WK Dept_cur%rowtype;
BEGIN
For WK in Dept_cur LOOP
UPDATE dept2 SET dname = wk.dname WHERE deptno = Wk.deptno;
IF Sql%notfound Then
INSERT into Dept2 VALUES (wk.deptno,dname,wk.loc);
END IF
END LOOP;
END;
Right: MERGE into Dept2 D2
USING Dept D
On (D2.deptno = D.deptno)
WHERE matched Then
UPDATE SET d2.dname = d.dname
When NO matched then
INSERT (D2.deptno,d2.dname,d2.loc) VALUES (D.deptno,d.name,d.loc);
3-12. Use of multiple table inserts
The same data source is inserted into a complex number of tables, which requires a multiple separate insert ~ before Oracle 9i
SELECT statement, but after Oracle 9i we can use the Multi-table insert feature, as long as the insert is executed once to insert data into the plural table.
Past:
INSERT into Sales_month_a (month,sales) SELECT month,sum (sales) from sales WHERE prod_id = ' prod_a ' GROUP by month;
INSERT into Sales_month_b (month,sales) SELECT month,sum (sales) from sales WHERE prod_id = ' prod_b ' GROUP by month;
Right now:
INSERT into first
When prod_id = ' prod_a ' to Sales_month_a (month,sales) VALUES (Month,slaes)
When prod_id = ' prod_b ' to Sales_month_b (month,sales) VALUES (Month,slaes)
SELECT Month,sum (sales) from sales GROUP by month;
3-13. Restrictions on the use of Dblink
In a decentralized DB environment, a combination of dblink using tables from different DB can cause severe performance degradation due to the excessive amount of data being transmitted. So try to replace the tables in different DB with other methods.
3-14, try to use decode instead of Set function
To reduce the total or total calculated load in the aggregate function, use decode to minimize processing time. Use the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table.
Error: SELECT COUNT (*), SUM (SAL) from EMP WHERE deptno=10;
SELECT COUNT (*), SUM (SAL) from EMP WHERE deptno=20;
Yes: Select COUNT (DECODE (deptno,10, ' D10 ')) "COUNT-10",
COUNT (DECODE (deptno,20, ' D20 ')) "COUNT-20",
SUM (DECODE (deptno,10,sal,null) "SUM-10",
SUM (DECODE (deptno,20,sal,null) "SUM-20"
from EMP;
3-15. Delete duplicate records
The most efficient way to delete 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);
3-16, reduce the amount of the table query
In the SQL statement that contains the subquery, pay particular attention to reducing the query on the table.
Error: SELECT tab_name
From tables
WHERE tab_name = (SELECT tab_name
From Tab_colums
WHERE Version = 604)
and db_ver= (SELECT db_ver
From Tab_colums
WHERE Version = 604);
Yes: SELECT tab_name
From tables
WHERE (tab_name, db_ver) = (SELECT tab_name, Db_ver)
From Tab_colums
WHERE Version = 604);
3-17, avoid the use of resource-intensive operations
SQL statements with Distinct,union,minus,intersect,order by will start the SQL engine, performing a resource-intensive sort (sort) function. Distinct requires a sort operation, while the others need to perform at least two sorting.
For example, a union query, where each query has a GROUP BY clause, and GROUP by triggers an embedded sort (NESTED sort), so that each query needs to be sorted once and then a unique sort (sort unique) when the union is executed. The operation is executed and it can only begin execution after the previous embedded sort has ended. The depth of the embedded sort can greatly affect the efficiency of the query. Typically, SQL statements with union, minus, and intersect can be overridden in other ways.
syntax for efficient SQL in Oracle
