ORACLE-two-Table connection index failure, oracle-index failure

ORACLE-two-Table connection index failure, oracle-index failure

Symptom

Recently, the customer has received feedback that a report data cannot be queried.

Handling process

After the involved SQL is dug out, no SQL exception is found, and no optimization space is found. F5 view the Execution Plan and the two table indexes are not effective, however, the two tables are taken out separately to query the index, so it is initially considered that the index is invalid.

The goal of Oracle indexes is to avoid full table scans and improve query efficiency, but sometimes it is counterproductive. For example, if a table contains millions of data records and an index is added to a field, the query performance is not improved. This may be caused by oracle index failure. Oracle indexes have some restrictions. If you violate these index restrictions, oracle will perform a full table scan even if you have added an index, the query performance will not be better than without an index, but may be worse because of the system overhead of the database to maintain the index. The following are the seven restrictions that can invalidate Oracle indexes.

1. No WHERE clause(Excluded)

2.Index ColumnUse is null and IS NOT NULL(Excluded)

SELECT... FROM emp WHERE comm is null; index of comm column will be invalid

3. In the WHERE clauseIndexUse Functions(Excluded)

If function-based indexes are not used, the optimizer will ignore these indexes when using functions for columns with indexes in the where clause. For example:

Select * from staff where trunc (birthdate) = '01-MAY-82 ';

However, if you apply a function to a condition, the index takes effect. If you change the preceding statement to the following statement, you can search through the index.

Select * from staff where birthdate <(to_date ('01-MAY-82 ') + 0.9999 );

Note:Oracle still uses indexes for MIN and MAX functions.

4.IndexUse LIKE '% t' for fuzzy search(Excluded)

5. In the WHERE clauseIndexUse operations not equal(Excluded)

Operations not equal to: <> ,! =, NOT colum> = ?, NOT colum <=?

This restriction can be replaced by OR, for example, colum <> 0 ==> colum> 0 OR colum <0

6. equal to and range indexes will not be used together(Excluded)

SELECT emp_id, emp_m, salary_q... FROM emp WHERE job = 'manager' AND deptno> 10

Both job and deptno are non-unique indexes. In this case, oracle does not merge indexes, but uses only the first index.

7. Unmatched Data Types(Excluded)

Dept_id is a varchar2 field with an index. However, the following statement performs a full table scan.

Select * from dept where dept_id = 900198;

This is because oracle will automatically convert the where clause to to_number (dept_id) = 900198, which is equivalent to using functions, thus limiting the use of indexes. The correct syntax is as follows:

Select * from dept where dept_id = '201312 ';

After a large amount of data queries, the cause of index failure has not been found, so we try to solve the problem of index failure.

1. Try re-indexing (invalid)

2. try to create a new table and Index(Invalid)

3. the HINTS mechanism is used to forcibly execute the index, which is effective. In the original statement, the HINTS index can be used to query the result. The result takes 10 S and the total cost is 86, which is 2 more than the original statement. Although the results can be queried, the total consumption is not very good, and the consumption of connection queries increases abnormally. Therefore, we will continue to study other solutions.

/* + Index (table name index name )*/

Note: after the table is renamed in the query statement, the hint table name uses the renamed table name.

4. Perform table analysis again to query the results,It takes 15 S and the total cost is 86.

Analyze table name compute statistics;

ANALYZE table name compute statistics for all indexed columns;

Analyze table name compute statistics for table for all indexes for all indexed columns;

Extended: Last table analysis time query

SELECT t. TABLE_NAME, t. TABLESPACE_NAME, last_analyzed
FROM all_tables t -- user_tables t
Order by t. LAST_ANALYZED desc
Note: Do not analyze tables that are frequently used by the system or when the system is busy.

Here, we can query and give the customer an explanation, but I don't know which method is better than 3 or 4, or which of the following bosses can provide more suggestions to my sister.