Oracle Database performance is determined by the SQL statement execution efficiency

What determines the performance of Oracle databases? The performance of Oracle databases is ultimately determined by the efficiency of SQL statement execution. It can be seen that the performance improvement of Oracle databases is inseparable from the optimization of SQL statements.

The execution of the application is ultimately attributed to the execution of SQL statements in the database, which consumes 70% to 90% of database resources. Therefore, the execution efficiency of SQL statements determines the performance of the ORACLE database. Many Programmers think that query optimization is a task of the DBMS database management system. It has little to do with the SQL statements compiled by the programmers. This is wrong. A good query plan can often increase the program performance by dozens of times. In addition, SQL statements are independent of the program design logic. Compared with the optimization of the program source code, the optimization of SQL statements has a low cost of time and risk.

The main ways to optimize SQL are:

A. Create a valid index. Create an index on a column that is frequently connected but not specified as a foreign key; or an index on a column that is frequently sorted or grouped, that is, performing the group by or order by operation; you can create searches for columns with different values that are frequently used in conditional expressions. Do not create indexes for columns with fewer values. If there are multiple columns to be sorted, compound index can be created on these columns ).

To reduce the I/O competition, the index should be created in an index space that is not on the same disk as the user tablespace. Indexes can be divided into partition indexes, full indexes, unique indexes, bitmap indexes, and Other types. Before creating an index, you should measure the selectivity of this index, the index selectivity refers to the ratio of the number of different values in the index column to the number of records in the table.

B. you can create a cluster index for a column with a large number of duplicate values and frequent range queries such as between, >,<>=, <=) or columns using order by and group;

C. You must frequently access multiple columns at the same time. If each column contains duplicate values, you can create a composite index.

D. Optimize the expression. Use the range index whenever possible when you can use the range query, instead of using the "like" keyword, because the wildcard matching supported by the "LIKE" keyword is particularly time-consuming.

F. Use Oracle optimizer and row-level manager to adjust and optimize SQL statements.

Oracle user syntax Modification

Three methods for Oracle Authentication

Oracle Default User Password Problems

Common Oracle cursor attributes

Show you the Oracle explicit cursor