Optimization of ORACLESQL statements

Oraclesql optimization of the statement :

1. Select the most efficient table name order: the table with the lowest number of record bars should be selected as the base table!

Because the Oracle parser is processed in a right-to-left order. When Oracle processes multiple tables, They are connected by sorting and merging. The table with the lowest number of records should be selected as the base table, after which you can filter out unnecessary records of subsequent tables to maximize execution time and improve execution efficiency.

1. The connection order in the WHERE clause. Oracle parser takes a down-to-bottom where clause, so that connections between tables should 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.

1. Using table aliases (alias): When the associated table is relatively long, it is efficient to save the time that the Oracle parser parses the table name by using the table alias and prefixing the column name with the alias prefix.

1. Avoid having the HAVING clause as much as possible, because having has to retrieve all the records before filtering the result set, which also requires a sort and grand total operation . You can replace the HAVING keyword by using a WHERE clause. Filtering the number of records that are now queried by the WHERE clause can save Oracle a lot of effort and overhead.

1. > and < Operators

It is generally not necessary to adjust, but if you can write >= and <= first.

such as: A table has 1 million records, a numeric field A, 300,000 records of the a=0,30 Records of the A=1,39 million records of a=2,1 Records of the a=3. There is a big difference between performing a>2 and a>=3, because Oracle finds the index of records for 2 and then compares them, while A>=3 Oracle locates the records index of =3 directly.

6. When total number of rows, count (*), COUNT (1) rate is basically the same, but index retrieval through indexes is the fastest count (EMPNO).

1. Reduce the number of times the database is accessed. Oracle also performs a lot of work when executing every SQL statement: Parsing SQL statements, binding variables, reading data, and so on, thus minimizing the number of accesses to the database can reduce Oracle's workload.

Cases:

Inefficient and requires access to the database two times:

Select*from Oe_order_lines_all Ool

WHERE ool.header_id=35718;

SELECT *from Oe_order_lines_all ool

WHERE ool.header_id=38516;

Efficient! Access only one database at a time:

SELECT *from Oe_order_lines_all Ool1,oe_order_lines_all ool2

WHERE ool1.header_id=38516

andool2.header_id=35718;

1. Use the Decode function to avoid re-sending the same table that scans the same record or repeats the connection.

Cases:

Low Efficiency :

SelectCount (*), SUM (EMP. SAL)

Fromemp

WHERE Emp.deptno = 20

and EMP. ename like ' smith% ';

SelectCount (*), SUM (EMP. SAL)

Fromemp

WHERE Emp.deptno = 30

and EMP. ename like ' smith% ';

-- You can use DECODE function to get the same result efficiently

SelectCount (DECODE (emp.deptno,20, ' X ', NULL)) D20_count,

COUNT (DECODE (emp.deptno,30, ' X ', NULL)) D30_count,

SUM (DECODE (emp.deptno,20,sal,null)) D20_sal,

SUM (DECODE (emp.deptno,30,sal,null)) d30_sal

From EMP WHERE EMP. ename like ' smith% ';

1. Note: To minimize the number of accesses to a table, it is common in subquery statements.

Cases:

Low Efficiency :

UPDATE EMP_C1 X

SET x.sal= (Selectmax (y.sal) from EMP Y),

X.hiredate = (Selectmax (y.hiredate) from EMP Y)

WHERE X.deptno = 20;

Efficient :

UPDATE EMP_C1 X

SET (x.sal, x.hiredate) = (Selectmax (y.sal), MAX (y.hiredate) from EMP Y) WHERE x.deptno = 20;

1. ' EXISTS ' and ' notexists ' usage: When dealing with large amounts of data, it is much more efficient to use ' EXISTS ' instead of ' in ' and ' not ' for ' in ' processing in SQL statements;

Cases:

Low Efficiency :

Select*from Oe_order_lines_all Ool

WHERE ool.ship_from_org_id=86

and ool.header_id in (SELECT ooh.header_id fromoe_order_headers_all ooh);

Efficient :

Select*from Oe_order_lines_all Ool

WHERE ool.ship_from_org_id=86

Andexists (Select1fromoe_order_headers_all ooh WHERE ool.org_id = ooh.org_id andool.header_id = ooh.header_id);

Note: Use table joins instead of ' EXISTS ' when the tables used are connected to each other; because tables connect faster and more efficiently;

1. Using indexes to improve efficiency

An index is a conceptual part of a table that is used to improve the efficiency of retrieving data. In fact, 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 uniqueness validation of the primary key (primary key).

In addition to those long or long raw data types, you can index almost any column. 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

Note: it is necessary to periodically refactor the index.

1. Avoid using calculations on indexed columns in the ' WHERE ' clause.

Similarly, avoid using ' is NULL ' or ' was not null ' on the index column , not or ' <> ' or '! = ' : Because the index can only tell you what exists in the table, and cannot tell you what does not exist in the table, the index cannot index null values. When Oracle encounters ' not ', it stops performing an index search scan and performs a full table scan.

1. A like statement with a wildcard character (%).

Because the wildcard character (%) appears at the beginning of the search, the Orcale parser cannot skip '% ' using the index and can only perform a full table search, so the search is much less efficient. '% ' after the search term, Oracle can use the index of the search term to scan the data using the index, the efficiency will be greatly improved.

The '% ' following the search term is the index search scan:

The '% ' in front of the search term is the index search full table scan:

Reprinted from: http://9966064.blog.51cto.com/9956064/1620080

Optimization of ORACLESQL statements