How do I write high-performance SQL statements?

How do I write high-performance SQL statements?

(1) 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 a right-to-left order, and the FROM clause is written in the final table (the underlying table, driving tables) will be processed first, and in the case where the FROM clause contains more than one table, you must select the table with the fewest number of record bars as the underlying table. If you have more than 3 tables connected to the query, you need to select the crosstab (intersection table) as the underlying table, which refers to the table that is referenced by the other table.

(2) The 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.

(3) Avoid using ' * ' in the SELECT clause:

In the process of parsing, ORACLE converts ' * ' to all column names, which is done by querying the data dictionary, which means more time is spent

(4) Reduce the number of access to the database:

Oracle has done a lot of work internally: Parsing SQL statements, estimating index utilization, binding variables, reading chunks, and so on;

(5) Reset the ArraySize parameter in Sql*plus, sql*forms and pro*c to increase the amount of data retrieved per database access, with a recommended value of 200

(6) Use the Decode function to reduce processing time:

Use the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table.

(7) Integration of simple, unrelated database access:

If you have a few simple database query statements, you can integrate them into a single query (even if they are not related)

(8) Delete duplicate records:

The most efficient method of deleting duplicate records (because of the use of rowID) Example:

DELETE from EMP E WHERE e.rowid > (SELECT MIN (X.ROWID)

From EMP X WHERE x.emp_no = e.emp_no);

(9) Replace Delete with truncate:

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)

(10) Use commit as much as possible:

Whenever possible, use commit as many of the programs as possible, so that the performance of the program is improved and the requirements are reduced by the resources freed by the commit:

Resources Freed by Commit:

A. Information for recovering data on a rollback segment.

B. Locks acquired by program statements

C. Redo space in the log buffer

D. Oracle manages internal spending on 3 of these resources

(11) Replace the HAVING clause with a WHERE clause:

Avoid having a HAVING clause that filters 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 this overhead. (in non-Oracle) on, where, having

These three can be added to the conditional clause, on is the first execution, where the second, having the last, because on is the non-conforming record filter before the statistics, it can reduce the intermediate operation to process the data, it should be said that the speed is the fastest, where should be faster than having the , because it filters the data before the sum is used when the two tables are joined, so in the case of a table there is a comparison between where and having. In the case of this single-table query statistics, if the conditions to be filtered do not involve the fields to be calculated, then they will be the same result, but where you can use the Rushmore technology, and have not, at the speed of the latter slow if you want to relate to the calculated field, it means that before the calculation, The value of this field is indeterminate, according to the workflow of the previous write, where the action time is done before the calculation, and having is calculated after the function, so in this case, the results will be different. On a multi-table join query, on has an earlier effect than where. The system first synthesizes a temporary table based on the conditions of the joins between the tables, then the where is filtered, then calculated, and then filtered by having. Thus, to filter the conditions to play the right role, first of all to understand when this condition should play a role, and then decided to put it there

(12) Reduce the query on the table:

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

Select Tab_name from TABLES 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. The ability to master the above application function to solve the problem is very meaningful in practical work.

(14) using the alias of the table:

When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the alias to each column. This reduces the time to parse and reduces the syntax errors caused by column ambiguity.

(15) Replace in with exists with not exists instead of in:

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. 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.

Example:

(efficient) SELECT * from EMP (base table) where EMPNO > 0 and EXISTS (select ' X ' from DEPT where DEPT. DEPTNO = EMP. DEPTNO and LOC = ' Melb ')

(Low efficiency) SELECT * from EMP (base table) where EMPNO > 0 and DEPTNO in (SELECT DEPTNO from DEPT where LOC = ' Melb ')

(16) Identify the SQL statement for ' inefficient execution ':

Although there are many graphical tools for SQL optimization, it is always a good idea to write your own SQL tools to solve the problem:

SELECT executions, disk_reads, Buffer_gets,

ROUND ((buffer_gets-disk_reads)/bu

ffer_gets,2) Hit_radio,

ROUND (disk_reads/executions,2) Reads_per_run,

Sql_text

From V$sqlarea

WHERE executions>0

and buffer_gets > 0

and (Buffer_gets-disk_reads)/buffer_gets < 0.8

ORDER by 4 DESC;

(17) Use Index to improve efficiency:

An index is a conceptual part of a table used to improve the efficiency of retrieving data, and Oracle uses a complex self-balancing b-tree structure. In general, querying data through an index is faster than a full table scan. When Oracle finds the best path to execute queries and UPDATE statements, the Oracle Optimizer uses the index. Also, using indexes when joining multiple tables can improve efficiency. Another advantage of using an index is that it provides the uniqueness of the primary key (primary key) Validation: Those long or long raw data types, you can index almost all the columns. In general, using indexes in large tables is particularly effective. Of course, you will also find that using indexes can also improve efficiency when scanning small tables. Although the use of indexes can improve the efficiency of query, but we must also pay attention to its cost. Indexes require space to store, and they need to be maintained regularly, and the index itself is modified whenever a record is added to a table or the index column is modified. This means that each record's insert, DELETE, and update will pay more than 4, 5 disk I/O. Because indexes require additional storage space and processing, those unnecessary indexes can slow query response time. It is necessary to periodically refactor the index.:

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

(18) 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. It is generally possible to consider replacing with exist, EXISTS makes the query faster because the RDBMS core module will return the results immediately after the conditions of the subquery have been met. Example:

(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);

SQL statements are capitalized, because Oracle always parses the SQL statements first, converting lowercase letters to uppercase and then executing

(20) Use the connector "+" connection string sparingly in Java code!

(21) Avoid using not on indexed columns typically,

We want to avoid using not on indexed columns, and not to have the same effect as using functions on indexed columns. When Oracle "encounters" not, he stops using the index instead of performing a full-table scan.

How do I write high-performance SQL statements?