Plsql Efficient principle

SQL optimization is a complex task, not simple, but some of the details of writing scripts in peacetime can greatly improve the efficiency of our writing code and improve the quality of our code.

The following rules are part of my experience, part of the network materials, after finishing in my usual work after the use of the verification, special finishing as follows, in case of forgetting, hope to have some help for others:

1, as far as possible with the in operator, basically all in operators can be replaced with exists (my previous blog also mentions in the efficiency problem, relatively low)
The advantages of SQL in write are relatively easy to write and clear, but SQL performance with in is always better than

Lower, the steps taken from Oracle to parse SQL with in is the following differences from SQL without in:
ORACLE attempts to convert it into a connection to multiple tables, and if the conversion is unsuccessful, it executes the subquery in the inside, then queries the outer table record, and if the conversion succeeds, it directly uses the connection method of multiple tables. This shows that using in SQL at least one more conversion process. General SQL can be converted successfully, but for the inclusion of grouping statistics and other aspects of SQL cannot be converted.
When Oracle executes an in subquery, it first executes the subquery, puts the query results into the staging table, and executes the main query. Instead, exist first checks the main query and then runs the subquery until the first match is found. The not exists is slightly more efficient than not. However, in selecting the in or exist operation, it is necessary to consider the size of the master table data.
Recommended scenario: Try not to use the in operator in a business-intensive SQL.
Without the not-in operator, you can use not exists or outer joins + replace
This action is not recommended for strong columns because it does not apply the index of the table.
Recommended scenario: Replace with not EXISTS or (outer join + empty) scheme

2. Do not use "<>" or "! =" operators. Handling of non-equal operators results in a full table scan and can be used with "<" or ">"

Instead of a non-equal operator, the index is never used, so processing it only results in a full table scan.
Recommended scenario: Replace with other operations with the same function, such as:
1) a<>0 change to a>0 or a<0
2) a<> "Change to A>"
Oracle stops using the index to perform a full table scan when the is null or is not NULL appears in the WHERE clause. You can consider setting the index column to not NULL when designing the table. This allows you to replace the null operation with another operation
Is null or is not NULL operation (determines whether the field is empty)
Determining whether a field is empty generally does not apply an index, because the B-tree index is not indexed by a null value.

Recommended Solutions:
Use other operations with the same function instead, such as:
1) A is not NULL changed to A>0 or a> ", etc.
2) The field is not allowed to be empty, and a default value instead of a null value, such as the expansion of the application in the Status field is not allowed to empty, the default is the application.
3) Create a bitmap index (partitioned tables can not be built, bitmap index is difficult to control, such as the field value too many indexes will degrade performance, multi-person update operation will increase block lock phenomenon)
The index is not used when the wildcard "%" or "_" is the first character of a query string
For a connected column ' | | ', the last Connection column index will be invalid. Avoid connections as much as possible, either by separating them or by using functions that do not function on the columns.
If the index is not function-based, the index no longer works when the function is used on the indexed column in the WHERE clause.
Avoid using calculations on indexed columns in the WHERE clause, which will cause the index to fail with a full table scan.
Indexes are invalidated when comparisons are made to columns of different data types.
3, > and < operator (greater than or less than operator)
The greater than or less than the operator generally does not need to adjust, because it has an index will be indexed to find, but in some cases it can be optimized, such as a table has 1 million records, a numeric field A, 300,000 records of 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.
Recommended scenario: Replace ">" with ">=".
4. Union operator
The Union will filter out duplicate records after the table link is made, so the resulting set of results will be sorted after the table is connected, the duplicate records are deleted and the results returned. Most of the actual applications do not produce duplicate records, the most common being the process table and the History table Union. Such as:
SELECT * FROM Gc_dfys
Union
SELECT * FROM Ls_jg_dfys
This SQL takes out the results of two tables at run time, then sorts the duplicate records with the sort space, and finally returns the result set, which may cause the disk to be sorted if the table data volume is large.
Recommended Scenario: Use the union ALL operator instead of union because the union all operation simply merges two results and returns.
SELECT * FROM Gc_dfys
UNION ALL
SELECT * FROM Ls_jg_dfys
4. Like operator
The LIKE operator can apply a wildcard query, where the wildcard combination may reach almost arbitrary queries, but if used poorly it can produce performance problems, such as the "%5400%" query does not reference the index, and the "x5400%" reference to the scope index. A practical example: Use the user identification number behind the business number in the YW_YHJBQK table to query the business number YY_BH like '%5400% ' this condition will result in a full table scan, if changed to yy_bh like ' x5400% ' or yy_bh like ' b5400% ' will benefit The performance of the two-range query with YY_BH Index is certainly greatly improved.
The impact of SQL writing (shared SQL statements can improve operational efficiency)

5, the same function the same performance different SQL effect  
     like a SQL programmer writes for  
    Select * FROM zl_yhjbqk 
    B programmers write for  
    Select * from DLYX.ZL_YHJBQK (prefixed with table owner)  
    C programmers write for  
    Select * from Dlyx. ZLYHJBQK (uppercase table name)  
    D programmers write  
    Select *  from Dlyx. ZLYHJBQK (more space in the middle)  
      above four SQL results and the execution time is the same after Oracle analysis and collation, However, from the Oracle shared memory SGA, it can be concluded that Oracle will analyze each sql  and consume shared memory, and if the SQL string and format are written exactly the same, Oracle will only parse once, Shared memory also leaves only one analysis, which does not   only reduces the time to parse SQL, but also reduces the duplication of shared memory information, and Oracle can accurately count the frequency of SQL execution.  
     Recommendation: The same SQL statements appear in different regions to ensure that the query characters are exactly the same to take advantage of the SGA shared pool, preventing the same SQL statements from being parsed multiple times.  

6. The condition order behind the where affects  
       Oracle Processes multiple query conditions in the WHERE clause from bottom to top. So the table join statement should be written before the other where conditions, the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.  
       The conditional order after the WHERE clause has a direct effect on the query of the large data scale, such as  
    SELECT * from zl_yhjbqk where dy_dj = ' 1KV or less ' and xh_bz=1 
    Select * from Zl_yhjbqk where xh_bz= 1  and DY_DJ = ' 1KV below '  
     above two SQL Dy_dj (voltage level) and XH_BZ (PIN household sign) Two fields are not indexed, So when the execution is full table scan, the first SQL DY_DJ = ' 1KV below ' condition in the recordset ratio is 99%, while the xh_bz=1 ratio is only 0.5%, in the first SQL when the 99% records are DY_DJ and xh_bz   comparison, And in the second SQL when the 0.5% records are DY_DJ and xh_bz comparison, in order to conclude that the second SQL CPU utilization is significantly lower than the first one.  

7. Influence of query Table order
Oracle handles the table names in the FROM clause from right to left, so if you include more than one table in the FROM clause, the table with the fewest records is placed last. (only valid when using RBO optimization)
The order of the list in the table after the from is performed on the SQL performance impact, and Oracle is linked in the order in which the tables appear, without indexes and when Oracle does not have statistical analysis of the tables, because the order of the tables does not intersect with data that consumes server resources. (Note: If the table is statistically analyzed, Oracle automatically links the small table and then the large table).
8. The non-indexed columns in the Order by statement degrade performance and can be processed by adding an index. Strictly controls the use of expressions in an ORDER BY statement
9. When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix each column with it. This can reduce the parsing time

10, multi-use internal functions to improve SQL efficiency SQL Statement Index Utilization
11, some optimization of the condition field
Fields with function processing cannot take advantage of indexes
Such as:
substr (hbs_bh,1,4) = ' 5400 ', optimized processing: HBS_BH like ' 5,400% '
Trunc (SK_RQ) =trunc (sysdate), optimized processing: Sk_rq>=trunc (sysdate) and Sk_rq<trunc (sysdate+1)
12. Fields that have an explicit or implicit operation cannot be indexed
Such as:

SS_DF+20&GT;50, optimized processing: ss_df>30

' X ' | | Hbs_bh> ' X5400021452 ', optimized for handling:hbs_bh> ' 5400021542 '

Sk_rq+5=sysdate, optimized processing: sk_rq=sysdate-5

hbs_bh=5401002554, optimized processing: hbs_bh= ' 5401002554 ', note: This condition implicitly to_number conversion for HBS_BH, because the Hbs_bh field is a character type.
cannot be indexed when a field operation with multiple tables is included in the condition
YS_DF&GT;CX_DF, unable to optimize
qc_bh| | Kh_bh= ' 5400250000 ', optimized processing: qc_bh= ' 5400 ' and kh_bh= ' 250000 '

13. Operations that may cause a full table scan
Use NOT OR "<>" on an indexed column
Use functions or calculations on indexed columns
Not in operation
Wildcard characters in the first character of a query string
Is null or is not NULL
Multi-column index, but its first column is not referenced by a WHERE clause

Plsql Efficient principle