Oracle SQL statement optimization, oraclesql

Oracle SQL statement optimization, oraclesql

(1) select the most efficient table name sequence (only valid in the rule-based Optimizer ):
The Oracle parser processes the table names in the FROM clause in the order FROM right to left. The table written in the FROM clause (basic table driving table) will be processed first, when the FROM clause contains multiple tables, You must select the table with the least number of records as the base table. If more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables.

(2) join order in the WHERE clause:
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.

(3) Avoid using '*' in the SELECT clause '*':
During the parsing process, Oracle converts '*' into all column names in sequence. This is done by querying the data dictionary, which means it takes more time.

(4) use the DECODE function to reduce processing time:
You can use the DECODE function to avoid repeated scan of the same record or join the same table.

(5) simple integration with no associated database access:
If you have several simple database query statements, you can integrate them into a single query (even if there is no relationship between them ).

(6) replace DELETE with TRUNCATE:
When deleting records in a table, a rollback segment is 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 a command is run, data cannot be recovered. Therefore, a small amount of resources are called and the execution time is short. (TRUNCATE is only applicable to deleting the entire table, and TRUNCATE is DDL rather than DML ).

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

(8) Replace with> =>:
Efficient: SELECT * from emp where deptno> = 4 inefficient: SELECT * from emp where deptno> 3
The difference between the two lies in that the former DBMS will directly jump to the first record with DEPT equal to 4, while the latter will first locate the record with DEPTNO = 3 and scan forward to the record with the first DEPT greater than 3.


(9) SQL statements are written in uppercase, because Oracle always parses SQL statements first, converts lowercase letters to uppercase and then executes them.

(10) replace 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.