Optimization of SQL statements in Oracle Learning discourse

Optimization of SQL statements in Oracle Learning discourse

① when querying with a SELECT statement, do not use "*" instead of all column names, as this will present an analytic dynamic problem for the Oracle system. The Oracle system translates "*" into all column names of the table by querying the data dictionary, which naturally consumes system time.

② in a subquery, the [NOT] in clause performs an internal sort and merge, in which case [not] is the least-efficient, because he performs a full-table traversal of the table in the subquery. To improve efficiency, we can change it to an outer join, not EXISTS, or EXISTS clause.

The ③ driver table refers to the table that was first accessed (usually accessed as a full table scan). In general, the table following the from is the driver table, so we should put the table with the available indexes behind the from when we join the table, which can improve the efficiency of the query.

④ The primary purpose of creating primary keys and unique indexes, in addition to the integrity and consistency of data, also has the effect of increasing query speed.

⑤ you can consider creating an index for a table that queries 2%~4% only from the total number of rows. The following are the basic principles of creating an index:

(1) based on the query keyword, the rows in the table are randomly sorted.

(2) A table containing a relatively small number of columns.

(3) Most of the queries in the table contain relatively simple whrer clauses.

(4) For tables that are often based on query keywords, and the rows in the table conform to a uniform distribution.

(5) The cache hit ratio is low and operating system permissions are not required.

⑥ Avoid full table scan, Oracle uses full table scan in the following cases:

(1) The table you are querying is not indexed.

(2) All rows need to be returned.

(3) A statement with like and use "%" is a full table scan.

(4) There is a conditional restriction on the primary column of the index, but using a function, Oracle uses a full table scan.

(5) with IS NULL, is non null or! = Equal words also cause a full table scan.

This article is from the "11544066" blog, please be sure to keep this source http://11554066.blog.51cto.com/11544066/1900237

Optimization of SQL statements in Oracle Learning discourse