20 database operation optimization experience and 20 databases summarized from the actual project

20 database operation optimization experience and 20 databases summarized from the actual project

1. Use indexes to improve efficiency:
An index is a conceptual part of a table to improve data retrieval efficiency. 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 advantage of using an index is that it provides uniqueness verification for the primary key .. For those LONG or long raw data types, You Can index almost all columns. generally, using indexes in large tables is particularly effective. of course, you will also find that using indexes to scan small tables can also improve efficiency. 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. Regular INDEX reconstruction is necessary: alter index <INDEXNAME> REBUILD <TABLESPACENAME>

 From 2 to 5, the full table scan is not applicable to indexes:

2. is null and IS NOT NULL

Null cannot be used as an index. Any column containing null values will not be included in the index. Even if there are multiple columns in the index, as long as one of these columns contains null, this column will be excluded from the index. That is to say, if a column has a null value, even if the column is indexed, the performance will not be improved. Any statement optimizer that uses is null or is not null in the where clause cannot use indexes.

3. Joined Columns

For joined columns, the optimizer does not use indexes even if the last joined value is a static value. Select * from employss where first_name | ''| last_name = 'beill cliton'; the system optimizer does not use indexes created based on last_name.

When the following SQL statement is used, the Oracle system can use an index created based on last_name. * ** Where first_name = 'beill' and last_name = 'cliton ';

4. Avoid using computation on index columns.
If the index column is part of the function, the optimizer will use full table scan without using the index:
Inefficient: SELECT... From dept where sal * 12> 25000;
Efficient: SELECT... From dept where sal> 25000/12;

5. NOT

... Where not (status = 'valid ')

Select * from employee where salary <3000 or salary> 3000;

Although the results of these two queries are the same, the second query scheme is faster than the first query scheme. The second query allows Oracle to use indexes for salary columns, while the first query does not.

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

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

8. Avoid '*' 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.

9. Reduce the number of database accesses:
ORACLE has performed a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks. If you have several simple database query statements, you can integrate them into a query (even if there is no relationship between them)

10. 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 the command is run, the data cannot be restored. therefore, few resources are called and the execution time is short.

11. Try to use COMMIT as much as possible:
As long as possible, use COMMIT as much as possible in the program, so that the program's performance is improved, and the demand will also be reduced by the resources released by COMMIT:
Resources released by COMMIT:
A. Information used to restore data on the rollback segment.
B. Locks obtained by Program Statements
C. Space in redo log buffer
D. ORACLE manages the internal costs of the above three types of resources

12. 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. (in non-oracle) where on, where, and having can be added, on is the first statement to execute, where is the second clause, and having is the last clause, because on filters out records that do not meet the conditions before making statistics, it can reduce the data to be processed by intermediate operations. It is reasonable to say that the speed is the fastest, where should also be faster than having, because it performs sum only after filtering data, and on is used only when two tables are joined, so in a table, then we can compare where with having. In the case of single-Table query statistics, if the filter condition does not involve fields to be calculated, the results will be the same, but the where technology can be used, having cannot be used. In terms of speed, the latter must be slow. If a calculated field is involved, the value of this field is uncertain before calculation, according to the workflow written in the previous article, the where function time is completed before computing, and having works only after computing. In this case, the results are different. In multi-table join queries, on takes effect earlier than where. The system first combines multiple tables into a temporary table based on the join conditions between tables, then filters them by where, then computes them, and then filters them by having after calculation. It can be seen that to filter a condition to play a correct role, you must first understand when the condition should take effect, and then decide to put it there.

13. Improve SQL efficiency through internal functions .:
Complex SQL statements tend to sacrifice execution efficiency. It is very meaningful to grasp the above methods to solve problems by using functions.

14. Use the table Alias (Alias ):
When an SQL statement connects multiple tables, 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.

15. Replace IN with EXISTS and not exists instead of not in:
In many basic table-based queries, to meet one condition, you often need to join another table. in this case, using EXISTS (or not exists) usually improves the query efficiency. IN a subquery, the not in Clause executes an internal sorting and merging. IN either case, not in is the most inefficient (because it executes a full table traversal for the table IN the subquery ). to avoid the use of not in, we can rewrite it into an Outer join (Outer Joins) or not exists.
(Efficient) SELECT * from emp (basic table) where empno> 0 and exists (SELECT 'x' from dept where dept. DEPTNO = EMP. deptno and loc = melb ')
(Inefficient) SELECT * from emp (basic table) where empno> 0 and deptno in (select deptno from dept where loc = melb ')

16. Replace DISTINCT with EXISTS:
When you submit a query that contains one-to-many table information (such as the Department table and employee table), avoid using DISTINCT in the SELECT clause. in general, you can consider replacing it with EXIST, and EXISTS makes the query more rapid, because the RDBMS core module will immediately return the result after the subquery conditions are met:
(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 );

17. Always use the first column of the index:
If an index is created on multiple columns, the optimizer selects this index only when its first column (leading column) is referenced by the where clause. this is also a simple and important rule. When only the second column of the index is referenced, the optimizer uses the full table scan and ignores the index.

18. replace order by with WHERE:
The order by clause only uses indexes under two strict conditions: all columns in order by must be included in the same index and maintained in the ORDER of sorting in the index. & all columns in order by must be defined as non-empty.
The index used BY the WHERE clause and the index used in the order by clause cannot be tied together.
The DEPT table contains the following columns:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
Inefficiency: (indexes are not used)
SELECT DEPT_CODE from dept order by DEPT_TYPE
Efficiency: (using indexes)
SELECT DEPT_CODE from dept where DEPT_TYPE> 0

19. Avoid resource-consuming operations:
SQL statements with DISTINCT, UNION, MINUS, INTERSECT, and order by enable the SQL engine to execute resource-consuming sorting (SORT. DISTINCT requires a sorting operation, while other operations require at least two sorting operations. generally, SQL statements with UNION, MINUS, and INTERSECT can be rewritten in other ways. if your database's SORT_AREA_SIZE is well configured, you can also consider using UNION, MINUS, and INTERSECT. After all, they are highly readable.

Optimize group:
To improve the efficiency of group by statements, you can filter out unnecessary records before group.
Inefficiency:
Select job, AVG (SAL) from emp group by job having job = 'President'
Efficient:
Select job, AVG (SAL) from emp where job = 'President 'GROUP by JOB