Oracle index creation and SQL optimization tutorial, oraclesql

Oracle index creation and SQL optimization tutorial, oraclesql

Oracle index creation and SQL optimization tutorial

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. No index left

Select * from emp where deptno = 66-> take the index.

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 () in the SELECT clause to convert ''into all column names in sequence during ORACLE parsing. This is done by querying the data dictionary,

This means it will take 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 functions in index Columns

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 should

Change to: SELECT Col FROM tbl WHERE col> 10 OR col <10.

6. Use UNION to replace OR (applicable to index columns) union: append the result sets of two queries, which will not cause 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 columns should be equivalent. Union

Two result sets are returned, and duplicate items in the two result sets are eliminated. If no elimination is performed, use unoin all.

In general, replacing OR in the WHERE clause with UNION will produce better results. Using OR in the index column will cause a full table scan. Note,

The above 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, LOC_ID

And REGION.

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,

In this case, EXISTS (or not exists) is usually used to improve the query efficiency. In subqueries, NOT

The 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 tables 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 ')