Oracle index creation and SQL optimization tutorial, oraclesql

Oracle index creation and SQL optimization tutorial, oraclesql

Database index:

The index has a single column index compound index.

If a field in a table has primary key constraints and uniqueness constraints, Oracle automatically recommends a unique index on the corresponding constraint column. The database index mainly improves the access speed.

Construction Principles:

1. The index should be frequently created on columns frequently used in the Where clause. If a large table often uses a field for query, and the number of retrieved rows is less than 5% of the total number of rows. You should consider.

2. Indexes should be created for the fields connected to the two tables. If Order By is often performed on a field in a table, it is also indexed.

3. Indexes should not be built on small tables.

Advantages and disadvantages:

1. indexing mainly improves the data query speed. When DML is performed, the index is updated. Therefore, the more indexes, the slower the DML, the more indexes it needs to maintain. Therefore, you need to weigh the need to create an index and DML.

Create an index:

Single Index: Create Index
 
  
On
  
   
(Column_Name); composite Index: Create Index I _deptno_job on emp (deptno, job);-> Create an Index in the deptno and job columns of the emp table. Select * from emp where deptno = 66 and job = 'sals'-> take the index. Select * from emp where deptno = 66 OR job = 'sals'-> scan the entire table. Select * from emp where deptno = 66-> without indexing. Select * from emp where job = 'sals'-> to scan the entire table without indexing.
  
 

If the where clause contains the OR operator OR the Job column is referenced separately (the column following the index column), the full table scan will not be performed.

SQL optimization:

When an Oracle database obtains an SQL statement, it analyzes the statement based on the query optimizer and generates a query execution plan based on the analysis results.

That is to say, the database is a query plan executed, not an SQL statement.

The query optimizer includes rule-based-optimizer (rule-Based Query optimizer) and Cost-based-optimizer (Cost-Based Query optimizer ).

The rule-based Query Optimizer disappears in version 10g.

For rule query, the last query is full table scan. CBO makes the final selection based on the statistics.

1. First execute From-> Where-> Group By-> Order

2. Execute the From statement From the right to the left. Therefore, you must place the table with the least number of records on the right. Why?

3. For the Where clause, the execution sequence is from backward to forward. Therefore, the condition for filtering the maximum number of records must be written at the end of the Where clause. for connections between multiple tables, before writing.

This is because most non-repeated items can be removed during connection.

4. Avoid using () During ORACLE parsing''Convert to all column names in sequence. This task is completed by querying the data dictionary, which means it takes more time.

5. Index failure:

① Not Null/Null if an index is created for a column, Select * from emp where depto is not null/is null. The index is invalid.

② Do not use a function in the index column. SELECT Col FROM tbl WHERE substr (name, 1, 3) = 'abc'

Or SELECT Col FROM tbl WHERE name LIKE '% ABC %' and SELECT Col FROM tbl WHERE name LIKE 'abc % 'will use the index.

③ If SELECT Col FROM tbl WHERE col/10> 10 cannot be calculated on the index column, the index will become invalid and should be changed

SELECT Col FROM tbl WHERE col> 10*10

④ Do NOT use NOT (! =, <>) For example: SELECT Col FROM tbl WHERE col! = 10

SELECT Col FROM tbl WHERE col> 10 OR col <10.

6. replace OR with UNION (applicable to index columns)

Union: appends the result sets of two queries, without causing column changes. Because it is an append operation, the number of columns in the two result sets must be related,

And the data type of the corresponding column should be equivalent. Union returns two result sets, and eliminates repeated items in the two result sets. If no elimination is performed, use unoin all.

In general, replacing OR in the WHERE clause with UNION will produce better results. using OR for index columns will scan the entire table. note that the preceding rules are only valid for multiple index columns.

If a column is not indexed, the query efficiency may be reduced because you did not select OR. In the following example, both the LOC_ID and REGION have indexes.

Efficient:

SELECT LOC_ID, LOC_DESC, region from location where LOC_ID = 10 union select LOC_ID, LOC_DESC, region from location where region = "MELBOURNE" inefficiency: SELECT LOC_ID, LOC_DESC, region from location where LOC_ID = 10 or region = "MELBOURNE"

If you insist on using OR, you need to write the index columns with the least records at the beginning.

7. Replace IN with EXISTS and not exists instead of not in.

In many basic table-based queries, to meet one condition, you often need to join another table. in this case, using EXISTS (or not exists) usually improves the query efficiency.

IN a subquery, the not in Clause executes an internal sorting and merging. IN either case, not in is the most inefficient (because it executes a full table traversal for the table IN the subquery ).

To avoid the use of not in, we can rewrite it into an Outer join (Outer Joins) or not exists.

Example:

Efficient: SELECT * from emp (basic table) where empno> 0 and exists (SELECT 'x' from dept where dept. DEPTNO = EMP. deptno and loc = 'melb') Inefficiency: SELECT * from emp (basic table) where empno> 0 and deptno in (select deptno from dept where loc = 'melb ')