34 PL/SQL Optimizations

34 PL/SQL Optimizations  

We should not only write SQL statements, but also write SQL statements with excellent performance. The following sections will help the author learn, extract, and summarize some materials to share with you! (1) select the most efficient table name sequence (only valid in the rule-based Optimizer ): Oracle The parser processes the table names in the from clause in the order from right to left. The from clause is written in the final table (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 there are more than three table join queries, You need to select an intersection table as the base table, which is 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) Reduce the number of visits to the database: Oracle has performed a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks; (5) re-set the arraysize parameter in SQL * Plus, SQL * forms, and Pro * C to increase the retrieval data volume for each database access. The recommended value is 200. (6) 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. (7) 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) (8) delete duplicate records: The most efficient way to delete duplicate records (because rowid is used) is as follows: 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 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, The rollback segment no longer stores any information that can be recovered. After the command is run, the data cannot be recovered. Therefore, a small amount of resources are called and the execution time is short: Truncate is applicable only when the entire table is deleted, and truncate is DDL rather than DML) (10) 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 (11) replace having clause with the WHERE clause: Dodge Without having clause, having filters the result set only after all records are retrieved. This processing requires operations such as sorting and total. 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 clauses can be added, on is the first to execute, where is the second, having is the last, because on is the first When a qualified record is filtered for statistics, it can reduce the data to be processed by intermediate operations. It is reasonable to say that the speed is the fastest, and where should be faster than having, because it filters data In addition, on is used only when two tables are joined. Therefore, when a table is joined, where and having are compared. In the case of query statistics for this single table, if the condition to be filtered does not exist When it comes to fields to be calculated, the results are the same, but the Where can use the Rushmore technology, and having cannot. The latter must be slow in terms of speed. If it involves computing words Segment, it indicates that 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 calculation, having is started after computation. So in this case, the two results will be 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, the where clause is used for filtering, and then computing. After calculation, the having clause is used for filtering. It can be seen that to filter a condition to play a correct role, we must first understand when the condition should be And then decide to put it there. (12) Reduce table queries: In SQL statements containing subqueries, pay special attention to reducing the number of queries to 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 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 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. (15) Replace in with exists and not exists instead of not in: In In many basic table-based queries, to meet one condition, you often need to join another table. In this case, use exists (or not Exists) usually improves the query efficiency. In subqueries, 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 using not in, we can rewrite it into an outer join (outer joins) or not Exists. Example: (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) Identifying 'inefficient execution' SQL statements: Although a variety of graphical tools for SQL optimization are emerging, writing your own SQL tools is always the best way to solve the problem: Select executions, disk_reads, buffer_gets, Round (BUFFER_GETS-DISK_READS)/buffer_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) using indexes to improve efficiency: Suo Introduction is a conceptual part of a table to improve the efficiency of data retrieval. 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 will use indexes, which can also improve efficiency when connecting multiple tables. Another advantage of using an index is that it provides uniqueness verification for the primary key .. 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 can also improve efficiency when scanning small tables. Although the index can improve the query efficiency, we must pay attention to its cost. indexes require space for storage and regular maintenance. Whenever a record is added or removed from a table or the index column is modified, The index itself will also be modified. This means that the insert, delete, and update operations for each record will pay four or five more disk I/O operations. 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> (18) 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 return the result immediately after the subquery conditions are 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 ); (19) SQL statements are written in upper case, because Oracle always parses SQL statements first, converts lowercase letters to uppercase letters, and then executes (20) try to use the connector "+" to connect strings in Java code! (21) Avoid using not in index columns, We should avoid using not in the index column, not will have the same impact as using the function in the index column. when Oracle Encounters "not", it stops using indexes and performs full table scanning. (22) Avoid using computation on index columns. In the WHERE clause, if the index column is part of the function, the optimizer will use full table scan without using the index. Example: Inefficiency: Select... From dept where Sal * 12> 25000; Efficient: Select... From dept where SAL> 25000/12; (23) Replace with> => Efficient: Select * from EMP where deptno> = 4 Inefficiency: Select * from EMP where deptno> 3 The difference between the two lies in that the former DBMS will jump directly to the first record whose DEPT is equal to 4, while the latter will first locate the record whose deptno is = 3 and scan forward to the record whose first DEPT is greater than 3. (24) replace or with Union (applicable to index columns) Connect In general, replacing or in the WHERE clause with union will produce better results. Using or in the index column will cause a full table scan. Note, The above rules are only valid for multiple index columns. If a column is not indexed, the query efficiency may be reduced because you did not select or. In the following example, loc_id And region. Efficient: Select loc_id, loc_desc, Region From location Where loc_id = 10 Union Select loc_id, loc_desc, Region From location Where region = "Melbourne" Inefficiency: Select loc_id, loc_desc, Region From location Where loc_id = 10 or region = "Melbourne" If you insist on using or, you need to write the index columns with the least records at the beginning. (25) use in to replace or This is a simple and easy-to-remember rule, but the actual execution results must be tested. in Oracle8i, the execution paths of the two seem to be the same. Inefficiency: Select .... From location where loc_id = 10 or loc_id = 20 or loc_id = 30 Efficient Select... From location where loc_in in (10, 20, 30 ); (26) Avoid using is null and is not null in the index column. Dodge Oracle will not be able to use any column that can be empty in the index. This record does not exist in the index if the column contains null values for a single column index. For a composite index, if each column is empty, the record does not exist in the index. if at least one column is not empty, the record exists in the index. For example: If the unique index is created in column A and column B of the table, and there is a record in the table, the value of A and B is (123, null ), Oracle will not accept the next record with the same a, B value (123, null) (insert ). However, if all index columns are empty, Oracle considers the entire key value to be null, but null is not equal to null. therefore, you can insert 1000 records with the same key value. Of course, they are empty! Because the null value does not exist in the index column, the Null Value Comparison of the index column in The WHERE clause will disable oracle. Inefficiency: (index failure) Select... From department where dept_code is not null; Efficient: (index valid) Select... From department where dept_code> = 0; (27) 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. (28) replace union with Union-all (if possible ): When When an SQL statement needs to union two query result sets, these two result sets are merged in the form of union-all and sorted before the final result is output. If Union all is used to replace union, sorting is unnecessary. The efficiency will be improved. Note that Union all The two result sets will be repeatedly output with the same records. Therefore, you still need to analyze the feasibility of using Union all from the business needs. Union Sort the result set. This operation uses sort_area_size memory, which is also very important for memory optimization. The following SQL can be used to query the consumption of sorting Inefficiency: Select acct_num, balance_amt From debit_transactions Where tran_date = '31-DEC-95' Union Select acct_num, balance_amt From debit_transactions Where tran_date = '31-DEC-95' Efficient: Select acct_num, balance_amt From debit_transactions Where tran_date = '31-DEC-95' Union all Select acct_num, balance_amt From debit_transactions Where tran_date = '31-DEC-95' (29) 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 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. For example: 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 (30) Avoid changing the index column type .: Oracle automatically converts columns to different types of data. Assume that empno is a numeric index column. Select... From EMP where empno = '20140901' In fact, after Oracle type conversion, the statement is converted: Select... From EMP where empno = to_number ('123 ') Fortunately, the type conversion does not occur on the index column, and the purpose of the index is not changed. Assume that emp_type is a character-type index column. Select... From EMP where emp_type = 123 This statement is converted: Select... From EMP whereto_number (emp_type) = 123 This index will not be used because of internal type conversion! To avoid implicit type conversion for your SQL statements, it is best to explicitly convert the type conversion. Note that when comparing the character and value, Oracle will first convert the value type to the character type. (31) WHERE clause to be careful: The where clause in some select statements does not use indexes. Here are some examples. In In the following example, (1 )'! = 'No index will be used. Remember, indexes only tell you what exists in the table, but not what does not exist in the table. (2) '|' Is a character concatenation function. Like other functions, the index is disabled. (3) '+' is a mathematical function. Like other mathematical functions, the index is disabled. (4) The same index Columns cannot be compared with each other, which enables full table scan. (32) A. If the retrieved data volume exceeds 30% of the number of records in the table, using indexes will not significantly improve the efficiency. B. in certain cases, using indexes may be slower than full table scanning, but this is an order of magnitude difference. in general, using an index is several times or even several thousand times more than a full table scan! (33) Avoid resource-consuming operations: SQL statements with distinct, union, minus, intersect, and order by will start the SQL engine. Executive The resource-consuming sorting (SORT) function of the row. Distinct requires a sorting operation, while the other requires at least two sorting operations. Generally, with union, minus Intersect SQL statements can be rewritten in other ways. If your database's sort_area_size is well configured, use Union, Minus and Intersect can also be considered. After all, they are highly readable. (34) Optimize group: To improve the efficiency of the Group by statement, you can filter out unnecessary records before group by. The following two queries return the same results, but the second query is much faster. Inefficiency: Select job, AVG (SAL) From EMP Group job Having job = 'President' Or job = 'manager' Efficient: Select job, AVG (SAL) From EMP Where job = 'President' Or job = 'manager' Group job