Oracle SQL Optimization One (common method)

1, table access mode optimization: a) Common table first "index Lookup index Scan" to avoid full table scan

In most scenarios, it is much more efficient to scan through index Lookup indexes than full table scan (FTS). When writing SQL, in order to ensure that the query can use the index, you need to avoid the following scenario:

• Is null and is not NULL

Null is not indexed in Oracle, and if there is "null" in a column of data, do not create an index on that column, even if it is created, it does not improve query performance.

In SQL statements, the SQL optimizer that uses is null and is not null,oracle are not allowed to walk the index.

• Leading wildcard characters

When you use like, a leading wildcard character appears, such as

ORACLE11 Code SELECT *
From T_sys_conf_midtoresult t
WHERE t.targettable like '%mdsp_rpt% '

At this point, the index on the targettable does not take effect.

Wildcard characters include '% ' and ' _ '.

• Not

Not causes the query index to not take effect.

There are several forms of not:

Not (fee = 0)

Fee <> 0

Fee! = 0

If the index must be used on the field, modify the following expression:

Fee > 0 and Fee < 0

• Try to put the expression on the right side of the = sign

Using an expression on a field causes the index to not take effect. For example:

ORACLE11 Code SELECT *
From T_mdsp_rpt_product_revenue t
WHERE to_char (t.dtime, ' yyyymmdd ') = ' 20140701 '

As a general recommendation, put the expression to the right of the = sign, and do not add any expressions to the left field name. For example:

ORACLE11 Code SELECT *
From T_mdsp_rpt_product_revenue t
WHERE t.dtime = to_date (' 20140701 ', ' YYYYMMDD ')

• Avoid implicit conversions of data types

In SQL statements, the common implicit conversions are as follows:

For comparison of character and numeric values, Oracle converts the character type implicitly into numerical form.

For character and date comparisons, Oracle converts the character type implicitly to date;

Implicit conversions can cause an index to fail if it occurs on a field. Principle same as "Add Expression on field"

• Note: If you avoid the above situation, still do not go index, not easy to use the hint way to force the index, the need for specific analysis according to the actual situation.

b) partition table must be retrieved through the partition key partition, not full table scan

In the interpretation plan, the scan for partitioned tables cannot be "partition range all".

In the Where condition, be sure to include conditional filtering on the partition key columns, as well as avoid adding expressions and implicit conversions on fields

• For list partitions

General use = judgment, for example:

Oracle11 code WHERE dtime = ' 20140701 '

• For range partitions

General use of the scope of judgment, such as between ... and ... or >, <, >=, <=, etc.

c) Indexes on partitioned tables generally use local indexes

The principles to be aware of are consistent with indexes on ordinary tables.

When querying a partitioned table, Oracle first finds the partition that needs to be queried by partitioning the partition key, and uses the local index for retrieval within the partition.

2. Table connection mode optimization a) NESTED LOOPS (nested loop NL) optimization, creating an index on the Internal Table Association field

The most common table connection method, the connection principle is as follows:

Get the first data from the external table (driver table), go to the internal table to query the match, if matching to the output.

Take the second data match until the external table data is traversed again.

Under the CBO, Oracle automatically chooses a table with a smaller amount of data (or a subquery result set) as an internal table.

Create indexes on internal tables to increase efficiency for each retrieval.

b) Hash Join

In most scenarios, the Hash join is the most efficient connection.

Generally used for large tables and small tables of the connection, when the small table data can all put into memory, the most efficient.

c) Sort merge joins (sort merge Join (SMJ))

It will inevitably accompany the sequencing operation, less efficient, after the Oracle 10g version of the basic does not appear, if it appears, you need to confirm whether the sub-query is used in the unnecessary sorting.

d) Cartesian join (Cartesian connection)

This is not to say more, it must be avoided.

3. Optimization of operation mode

Avoid unnecessary sort and sort group by, in general SQL only when the final output results are sorted, internal subqueries are not sorted.