SQL statement optimization and skills

I. SQL Introduction

1. SQL Introduction

Structured Query Language (structuredquery language) was first developed for its Relational Database Management System (system R) by the St. Joseph Research Laboratory of IBM. Its predecessor was the square language. The SQL language is simple, powerful, and easy to learn. Therefore, since its launch in 1981, the SQL language has been widely used. Today, whether it is large database management systems such as Oracle, Sybase, DB2, Informix, and SQL server, or commonly used database development systems on PCs such as Visual Foxpro and PowerBuilder, SQL is supported as the query language.

2. Three Components of SQL language

Data Definition Language (DDL): Create, drop, alter, and other statements.

Data Operation Language (DML): Data Query Language (dql), such as select statements and data update languages, such as insert, update, and delete statements. Data Control Language (DCL): Grant, revoke, commit, rollback, and other statements. Ii. SQL skills 1.The Oracle parser processes the table names in the from clause in the order from right to left. If the from clause contains multiple tables, you must select a table with the least number of records as the base table.

For example:Table 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

2.If you have more than three table join queries, You need to selectIntersection table)As a base table, cross tabulation refers to 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

3. oracle uses the bottom-up sequence to parse the WHERE clause. According to this principle, the join 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, execution time: 156.3 seconds)

SELECT … FROM EMP E WHERE SAL> 50000 AND    JOB = ‘MANAGER’AND   25 < (SELECT COUNT(*) FROM EMP             WHERE MGR=E.EMPNO);

(Efficient, execution time: 10.6 seconds)

SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROMEMP             WHERE MGR=E.EMPNO)AND    SAL > 50000AND    JOB = ‘MANAGER’;

4. Avoid '*' in the select clause '*'. When listing all columns in the select clause, it is convenient to reference '*' using dynamic SQL columns. unfortunately, this is a very inefficient method. in fact, Oracle converts '*' into all column names in sequence during parsing. This is done by querying the data dictionary, which means it takes more time. 5. Use Oracle built-in functions to improve efficiency. If you use the decode function to reduce the processing time, you can avoid repeatedly scanning the same records or joining the same table. 6. Replace Delete with truncate.

When deleting records in a table, rollbacksegments are usually used to store information that can be recovered. if you do not have a commit transaction, Oracle will recover the data to the State before the deletion (which is precisely the State before the deletion command is executed). When truncate is used, rollback segments no longer store any recoverable information. after the command is run, the data cannot be restored. therefore, few resources are called and the execution time is short. 7. Use commit as much as possible. As long as possible, use commit as much as possible in the program, so that the program performance is improved, and the demand will also be reduced because of the resources released by commit. Note: When Using commit, you must note the integrity of the transaction (commit cannot be used in recycling ). 8. Replace the having clause with the WHERE clause.

Avoid using the having clause. Having filters the result set only after all records are retrieved. this process requires sorting, total, and other operations. if the WHERE clause can be used to limit the number of records, this overhead can be reduced. The conditions in having are generally used to compare some set functions, such as Count (). In addition, the general conditions should be written in the WHERE clause. 9. Use the table alias (alias ). When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each column. in this way, the parsing time can be reduced and the syntax errors caused by column ambiguity can be reduced. column ambiguity means that different SQL tables have the same column name. When this column appears in an SQL statement, the SQL parser cannot determine the attribute of this column. 10. Replace exists with table join. 11. Replace distinct with exists. Exists makes the query faster, because the core module of RDBMS will return immediately after the subquery conditions are met. 12. Use indexes to improve efficiency.

An index is a conceptual part of a table to improve the efficiency of data retrieval. in fact, Oracle uses a complex self-balancing B-tree structure. data Query by index is usually faster than full table scan. when Oracle finds the optimal path for executing the query and update statements, the Oracle optimizer uses the index. using indexes when joining multiple tables can also improve efficiency. another benefit of using an index is that it provides uniqueness verification for the primary key (primarykey.

Although the index can improve the query efficiency, we must pay attention to its cost. the index requires space for storage and regular maintenance. The index itself is also modified whenever a record is increased or decreased in the table or the index column is modified. this means that the insert, delete, and update operations for each record will pay four or five more disk I/O. because indexes require additional storage space and processing, unnecessary indexes will slow the query response time.

13. Use> = to replace>. The difference between the two is, the former DBMS will jump directly to the first record whose DEPT is equal to 4, while the latter will first locate the record whose deptno is = 3 and scan forward to the record whose first DEPT is greater than 3 (> = 4 and> 3 ). 14. replace or with Union (applicable to index columns ). In general, replacing or in the WHERE clause with union will produce better results. using or for index columns will scan the entire table. note that the preceding rules are only valid for multiple index columns. if a column is not indexed, the query efficiency may be reduced because you did not select or. 15. replace or with in. This is a simple and easy-to-remember rule, but the actual execution results must be tested. in Oracle8i, the execution paths of the two seem to be the same. 16. Avoid using is null and is not null in the index column.

To avoid using any columns that can be empty in the index, Oracle will not be able to use this index. this record does not exist in the index if the column contains a null value. for a composite index, if each column is empty, this record does not exist in the index. if at least one column is not empty, the record is stored in the index.

The null value does not exist in the index column. Therefore, if you compare the null value of the index column in The WHERE clause, Oracle will disable the index.

17. In contrast to the general idea, count (*) is slightly faster than count (1). Of course, if you can search by index, the Count of index columns is still the fastest. For example, count (empno ). 18. For composite indexes, the query column must use the leading column.

Create unique index pk_ename on dept (dname, Loc)

Select * from Dept t where T. Loc = 'xxx'; (no index is used ).

Select * from Dept t where T. dname = ''xxx; (use index ).

19. The comparison value is consistent with the data type of the index column. (If they are inconsistent, there is a hermit conversion)

20. The query column order is the same as that of the index column.

Create unique index pk_ename on dept (dname, Loc)

Select * from Dept where loc = 'boston 'and dname = 'sales' implicit conversion --> select * from Dept where dname = 'sales' and loc = 'boston'

21. Avoid indexing columns.

Create index ind_empno on EMP (empno)

Select * from EMP t where to_char (T. empno) = '000000'; no index is used.

22. Avoid null when comparing values. Full table scan.

23. Replace the exists clause with multi-table join.

24. replace not in with not exists.

25. Use as few subqueries as possible.

26. Put the condition with the maximum number of records to the end;

Iii. indexing principles.