Oracle indexing and SQL optimization

Database index:

The index has a single-column index, and the composite index says that if a field of a table has a PRIMARY KEY constraint and a uniqueness constraint, Oracle automatically suggests a unique index on the corresponding constraint column. Database indexes are primarily made to improve access speed.

Construction principles:

1, the index should often be built in the WHERE clause is often used on the column. If a large table is frequently queried with a field, and the number of rows retrieved is less than 5% of the total table rows. It should be considered.

2. For two table-connected fields, index should be established. If an order by is often made in a field on a table, it is also indexed.

3, the index should not be built on the small table.

Advantages and Disadvantages
1, the index is mainly to improve the data query speed. When DML is in progress, the index is updated. Therefore, the more indexes, the slower the DML, and the need to maintain the index. Therefore, there is a tradeoff between creating indexes and DML.

To create an index:
Single index:

Create Index < Index - Name>on<table_name>(column_name);

Composite Index:

Create IndexI_deptno_job onEMP (deptno,job); —>index The DEPTNO, job column of the EMP table. Select *  fromEmpwhereDeptno= the  andJob='sals'  -Walk the index. Select *  fromEmpwhereDeptno= the ORJob='sals'  -A full table scan will be performed. Do not go indexSelect *  fromEmpwhereDeptno= the  -Walk the index. Select *  fromEmpwhereJob='sals'  -Do a full table scan, do not go index.

If there is an OR operator in the WHERE clause or a separate reference to the job column (the following column of the index column), the index will not go, and a full table scan will be performed.

SQL Optimizations:

When the Oracle database gets the SQL statement, it parses the statement against the query optimizer and generates a query execution plan based on the results of the analysis. In other words, the database is the query plan that executes, not the SQL statement. The query optimizer has the Rule-based-optimizer (rule-based query optimizer) and the Cost-based-optimizer (cost-based query optimizer). Where the rule-based query optimizer disappears in the 10g version. For a rule query, the last query is a full table scan. The CBO will make the final choice based on the statistical information.

1. First execute from->where->group By->order by

2. The execution FROM clause is executed from right to left. Therefore, you must select the table with the fewest number of record bars on the right. What is this for?

3. The condition that the where sentence is executed from the back forward, so that the maximum number of records can be filtered, must be written at the end of the WHERE clause, and the connection between the multiple tables is written before. Because of this connection, you can remove most of the items that are not duplicates.

4. Avoid using in the SELECT clause (*) Oracle will convert ' * ' to all column names in the process of parsing, which is done by querying the data dictionary, which means more time is spent

5, the index failure condition:
①not Null/null If a column is indexed when making a select * from EMP where depto are not null/is Null. The index will be invalidated.

② do not use functions on index columns, SELECT Col from TBL WHERE substr (name, 1, 3) = ' ABC '
Or select col from the TBL where name like '%abc% ' and select col from tbl where name like ' abc% ' will use the index.

③ cannot be computed on an index column select Col from TBL WHERE COL/10 > 10 will invalidate the index and should be changed to
SELECT col from tbl WHERE Col > 10 * 10

Do not use NOT (! =, <>) on the ④ index column as: SELECT col from TBL where Col! = 10
Should be changed to: SELECT col from tbl WHERE col > Ten OR col < 10.

6. Replace or with union (for indexed columns)

Union: Is the result set of two queries appended together, it does not cause the column to change. Because it is an append operation, the number of columns that require two result sets should be relevant, and the data type of the corresponding column should be equivalent. Union returns two result sets, eliminating two duplicates of the result set. If not eliminated, use Unoin all.

In general, replacing or in a WHERE clause with Union will have a good effect. Using or on an indexed column causes a full table scan. Note that the above rules are valid only for multiple indexed columns. If a column is not indexed, the query efficiency may be reduced because you did not select or. In the following example, indexes are built on both loc_id and region.

Efficient:

SELECT loc_id, Loc_desc, region  from  Location WHERE = Ten UNION SELECT loc_id, Loc_desc, region  from  Location WHERE = "MELBOURNE"

Low efficiency:

SELECT loc_id, Loc_desc, region  from  Location WHERE = Ten OR = "MELBOURNE"

If you persist in using or, you need to return the least logged index column to the front.

7. Replace in with exists instead of not exists instead of in

In many base-table-based queries, it is often necessary to join another table in order to satisfy one condition. In this case, using EXISTS (or not EXISTS) will usually improve the efficiency of the query. In a subquery, the NOT IN clause performs an internal sort and merge. In either case, not in is the least effective (because it performs a full table traversal of the table in the subquery). To avoid using not, we can change it to an outer join (Outer Joins) or not EXISTS.

Example:

Efficient: SELECT * from EMP (base table) where EMPNO > 0 and EXISTS (select ' X ' from DEPT where DEPT. DEPTNO = EMP. DEPTNO and LOC = ' Melb ')

Inefficient: SELECT * from EMP (base table) where EMPNO > 0 and DEPTNO in (SELECT DEPTNO from DEPT WHERE LOC = ' Melb ')

This article transferred from: http://www.cnblogs.com/tianmingt/articles/4444885.html

Oracle indexing and SQL optimization