Shulou(Shulou.com)06/01 Report--

Optimization of SQL sentences in Oracle Learning articles

When ① uses the select statement to query, do not replace all column names with "*", because this writing will have a dynamic problem of parsing for the Oracle system. The Oracle system converts "*" to all the 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, and in either case, [NOT] IN is the least efficient because it performs a full table traversal on the tables in the subquery. To improve efficiency, we can rewrite it as an outer join, NOT EXISTS, or EXISTS clause.

A ③-driven table is the table that is accessed first (usually in the form of a full table scan). Generally speaking, the table immediately after the FROM is the driven table, so when we join the table, we should put the table with available index after the FROM, which can improve the query efficiency.

The main purpose of ④ to create primary keys and unique indexes is not only to improve the integrity and consistency of data, but also to improve the speed of query.

⑤ can consider creating an index for tables that query only 2% or 4% of the total number of rows. Here are the basic principles for creating an index:

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

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

(3) most 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 are evenly distributed.

(5) the cache hit rate is low and does not require operating system permissions.

⑥ avoids full table scanning of large tables. Oracle uses full table scans in the following cases:

(1) the queried table does not have an index.

(2) all rows need to be returned.

(3) using the statement "%" with like is a full table scan.

(4) if there are conditional restrictions on the main column of the index, but if a function is used, Oracle uses a full table scan.

(5) with is null, is non null or! Words such as = also cause a full table scan.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.