Shulou(Shulou.com)06/01 Report--

Experience sharing

Optimization of ORACLESQL statements:

Choose the most efficient order of table names: you should choose the table with the least number of records as the base table!

Because the processing order of the Oracle parser is from right to left. When ORACLE processes multiple tables, it joins them by sorting and merging them. The table with the least number of records should be selected as the base table, and the unnecessary records of the tables behind the sorting join can be filtered out as much as possible, so as to speed up the execution time and improve the execution efficiency.

The join order in the WHERE clause. Oracle Parser uses the bottom-up WHERE clause, so the joins 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.

Using table aliases (Alias): when there are a large number of associated tables, it can effectively save time for Oracle parsers to parse table names by using table aliases and prefixing Column names with aliases.

Avoid using the HAVING clause as much as possible, because HAVING needs to filter the result set after all the records have been retrieved, and this process also requires sorting and totals. You can replace the HAVING keyword by using the WHERE clause. Filtering the number of current query records through the WHERE clause can save a lot of ORACLE workload and overhead.

> and

There is a big difference between = and 2 and A > = 3, because when A > 2, ORACLE will first find the record index of 2 and then compare it, while A > = 3 ORACLE will directly find the record index of = 3.

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

Reduce the number of times you access the database. When each SQL statement is executed, a lot of work will be done inside ORACLE: parsing SQL statements, binding variables, reading data, etc., so the workload of ORACLE can be reduced by minimizing the number of visits to the database.

Example:

Inefficient, you need to access the database twice:

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! The database was accessed only once:

SELECT * FROM oe_order_lines_all ool1,oe_order_lines_all ool2

WHERE ool1.header_id=38516

ANDool2.header_id=35718

Use the DECODE function to avoid re-scanning the same records or repeatedly joining the same tables.

Example:

Inefficient:

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 the 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%'

Note: to minimize the number of visits to the table, it is common in subquery statements.

Example:

Inefficient:

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

EXISTS' and NOTEXISTS' usage: when dealing with large amounts of data, it is much more efficient to use 'EXISTS' instead of' IN' and 'NOTEXISTS'' instead of 'NOT IN'' in SQL statements.

Example:

Inefficient:

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 AND ool.header_id = ooh.header_id)

Note: when the tables used can be connected to each other, try to use table joins instead of 'EXISTS';' because table joins are faster and more efficient

Use indexes to improve efficiency

Index is a conceptual part of a table, which is used to improve the efficiency of data retrieval. 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 way to execute queries and Update statements, the ORACLE optimizer uses indexes. Similarly, using indexes when joining multiple tables can also improve efficiency. Another advantage of using an index is that it provides uniqueness verification of the primary key (primary key).

You can index almost all columns except those LONG or LONG RAW data types. 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 query efficiency, we must also pay attention to its cost. The index needs space to store and needs to be maintained regularly. Whenever a record is added or decreased in the table or the index column is modified, the index itself will be modified. This means that the INSERT, DELETE, and UPDATE of each record will pay an extra 4 or 5 times for the disk. Because indexes require additional storage space and processing, unnecessary indexes will slow down the query response time.

Note: it is necessary to reconstruct the index regularly.

Avoid using calculations on index columns in the 'WHERE' clause.

By the same token, avoid using the sign'IS NULL' or'IS NOT NULL',NOT'or'OR'! ='on the index column: because the index can only tell you what exists in the table, but not what does not exist in the table, that is, the index cannot index null values. When ORACLE encounters' NOT', it stops performing an index search scan and performs a full table scan.

Like statement with wildcard (%).

Because the wildcard (%) appears at the beginning of the search, the Orcale parser cannot skip the'% 'index and can only search the full table, which makes the search much less efficient.' % 'after searching the term, ORACLE can use the index of the search term and scan the data using the index, which will greatly improve the efficiency.

'%' is followed by an index search scan:

'%' is preceded by an index search full table scan:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.