SQL optimization in Oracle

I. Use of the SQL language
1. in operator
The advantages of SQL in write are easier to write and easy to understand, which is more suitable for modern software development style.
But SQL performance with in is always lower, and 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.

Recommended scenario: Try not to use the in operator in a business-intensive SQL

2. not in operator
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

3.<> operator (not equal to)
The non-equal operator is never indexed, so processing it only results in a full table scan.

Recommended scenario: Replace with other operations of the same function, such as
A<>0 change to a>0 or a<0
A<> "instead of a>"

4. isnull 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 of the same function instead, such as
A is not NULL changed to a>0 or a> ', and so on.
The field is not allowed to be empty, and a default value is used instead of a null value, such as an application where the Status field is not allowed to be empty, and the default is the request.
Bitmap indexing (partitioned tables cannot be built, bitmap indexes are more difficult to control, such as field values too many indexes can degrade performance, multi-person update operation will increase block lock phenomenon)

5.>And<Operator (greater than or less than operator)
The greater-than or less-than operator generally does not need to be adjusted, because it has an index to find the index, but in some cases it can be optimized,
If a table has 1 million records, a numeric field of a, 300,000 records the a=0,30 Records of the a=1,39 Records of the 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.

6. likeOperator
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.
Queries such as like '%5400% ' do not reference the index, and the type ' x5400% ' references the scope index.
A practical example:
Use the user identification number in the YW_YHJBQK table after the business number to query the business number YY_BH like '%5400% ' this condition will produce a full table scan,
If you change to yy_bh like ' x5400% ' OR yy_bh like ' b5400% ', you can use the index of YY_BH to make two-range queries, and the performance will be greatly improved.

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

8. No need for large amounts of dataUpper ()and lower

Two. Impact of SQL Writing

1. Effect of the same function on the same performance (using Oracle's Shared SQL program)
    as a programmer in a SQL write: Select * from Zl_yhjbqk
     B Programmers Write: SELECT * from DLYX.ZL_YHJBQK (prefixed with the table owner)
    C programmer writes: SELECT * from Dlyx. ZLYHJBQK (uppercase table name)
    D Programmers write: Select * from Dlyx. Zlyhjbqk (more spaces in the middle)
    above four SQL results and execution time are the same after the Oracle analysis, but from the Oracle shared memory SGA, you can derive oracle for each SQL Will analyze it once, and take up shared memory, if the SQL string and format are written exactly the same, Oracle will only parse once, shared memory will only leave a single analysis results, this can not only reduce the time to analyze SQL, but also reduce the duplication of shared memory information, Oracle can also accurately count the frequency of SQL execution.

The conditional order after 2.WHERE affects

The conditional order after the A.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 or less '
The above two SQL DY_DJ (voltage level) and XH_BZ (PIN household sign) Two fields are not indexed, so the time to execute is full table scan,
The first SQL DY_DJ = ' 1KV below ' condition has a ratio of 99% in the Recordset, while the xh_bz=1 ratio is only 0.5%, and 99% records are compared Dy_dj and xh_bz at the time of the first SQL.
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

B. Impact of query table order
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 will automatically link the small table and then the large table)

Three. Utilization of SQL statement indexes

1. Optimization of the operator (see section above)
2. Some optimizations for the condition fields:
A. Fields that use 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)
B. A field that has 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.
C. A field operation that includes multiple tables in the condition cannot be indexed, such as:
YS_DF&GT;CX_DF, unable to optimize
qc_bh| | Kh_bh= ' 5400250000 ', optimized processing: qc_bh= ' 5400 ' and kh_bh= ' 250000 '
Four. Apply Oracle's hint (hint) processing: Prompt processing is used in cases where the SQL analysis execution path generated by Oracle is unsatisfactory. It can be a hint to SQL in the following ways
1. Target-related tips:
Cost (optimized by costs)
Rule (optimized by rules)
CHOOSE (default) (Oracle automatically selects cost or rules for optimization)
All_rows (all rows are returned as soon as possible)
First_rows (first row of data returned as soon as possible)
2. Tips for executing the method:
USE_NL (union with Nested Loops method)
Use_merge (Federated using the MERGE join method)
Use_hash (union with HASH join)
3. Index hints:
Index (table index) (query using the hint's list of tables)
4. Other advanced tips (such as parallel processing, etc.)
Oracle's hints feature is a relatively strong feature and is a complex application, and hints are only a recommendation to Oracle, and sometimes Oracle may not follow the prompts for cost considerations.
Based on practical application, developers are generally not recommended to apply Oracle hints because the performance of each database and server is not the same, it is likely that one place performance is improved, but another place is down,
Oracle has matured in SQL execution analysis,
If the parse execution path is not the first should be in the database structure (mainly index), the server's current performance (shared memory, disk file fragmentation), database objects (tables, indexes) statistics are correct these aspects of analysis

SQL optimization in Oracle