Summary of reasons why Oracle Execution plan does not go index

In Oracle database operations, why is it that sometimes a field in a table is indexed, and when the execution plan of some language is observed, it does not go through the index? How to solve it? In this article we mainly introduce this part of the content, then let us come together to understand.
One
There are several reasons why the index does not go:
The way you use the instance level is all_rows.
Statistics for your table (most likely cause)
Your watch is small, as mentioned above, and Oracle's optimizer doesn't think it's worth the index.

Workaround:
You can modify the Optimizer_mode parameter in Init.ora, change it to rule or choose, and restart the database. You can also use the hint mentioned in 4.

Other reasons to not walk the index:
1. Set up a composite index, but the query predicate does not use the first column of the combined index, there is an index SKIP scan concept here.

2. Index on table column containing null value, when using SELECT COUNT (*) from table does not use index.

3. Indexes are not used when functions are used on indexed columns, and only function indexes can be established if you must use an index.

4. The index is not used when the indexed column is implicitly type-converted.
such as: SELECT * from t where Indexed_column = 5, while the Indexed_column column is indexed but the type is character type, Oracle generates an implicit type conversion, the converted statement is similar to the select * from T where T O_number (Indexed_column) = 5, the situation is similar to CASE3 when the index is not gone. Date conversion also has a similar problem, such as: SELECT * from T where trunc (date_col) = Trunc (sysdate) where Date_col is the index column, so that the write does not go index, can be rewritten as a select * from T where date _col >= trunc (sysdate) and Date_col <
Trunc (sysdate+1), this query will go through the index.

5, not all cases using the index will speed up the query, full scan table is sometimes faster, especially when the data volume of the query for the entire table is large, because full scan table is a multi-block read, When the Oracle optimizer does not choose to use an index without immediately forcing it, it is sufficient to prove that using an index does query faster when you use a forced index.

6. <>

7, like '%DD ' percent semicolon in front.

Oracle database cannot use index for reason targeting

Oracle database sometimes can not use the index phenomenon, there are many reasons for this phenomenon, how to locate it? This article we mainly introduce this part of the content


First, we want to determine what optimization mode the database is running in, and the corresponding parameters are: Optimizer_mode. You can run the show in SVRMGRL.
Parameter Optimizer_mode "to view. The default setting since ORACLE V7 should be "choose", which is if you select the CBO for the parsed table query
, otherwise select RBO. If the parameter is set to "rule", the RBO is chosen regardless of whether the table is parsed, unless forced in the statement by hint.


Second, check to see if the first column of the indexed column or composite index appears in the WHERE clause of the PL/SQL statement, which is the "Execution plan".


Third, see which type of connection is used. ORACLE's Common Sort Merge join (SMJ), Hash join (HJ), and
Nested Loop Join (NL). When two tables are connected and an index is built on the target column of the inner table, only Nested Loop can effectively use that index. Smj
Even if there are indexes on related columns, the data sorting process can be avoided at most because of the existence of the index. The existence of indexes has little effect on data query speed because of the need to do HASH operation.


Finally, see if the connection order allows the associated index to be used. Assuming that the table EMP has an index on the Deptno column, there is no index on the column Deptno of Table dept, where
The statement has a emp.deptno=dept.deptno condition. In the NL connection, the EMP as the appearance, first be accessed, due to the connection mechanism, the appearance of data access is a full table scan,
The index on the Emp.deptno is obviously not used, up to the top.


The System data dictionary table or view is used. Because the System data dictionary table has not been parsed, it can lead to a very poor "execution plan."


Six, there is a potential data type conversion. If you compare character data with numeric data, ORACLE automatically uses To_number () for character types
function, which causes the sixth phenomenon to occur.


VII, do you want to collect enough statistics for the tables and related indexes? It is better to analyze the tables and indexes regularly, and the SQL statements can be used to "analyze
Table XXXX Compute statistics for all indexes; ". ORACLE Palm.


The index column is not highly selective. We assume a typical case, there is a table emp, there are 1 million rows of data, but where the Emp.deptno column, the data has only 4 different values, such as 10
, 20, 30, 40. Although there are many EMP data rows, the values of the columns in the ORACLE default determination table are evenly distributed across all data rows, meaning each DEPTNO value has 25
The data line corresponds to it. Assuming that the SQL search condition deptno=10, using the index on the DEPTNO column for data search efficiency, is often not higher than the full table scan, ORACLE is not in the 4
The average distribution among the deptno values, of which 990,000 rows corresponds to a value of 10, 5000 rows corresponds to a value of 20, 3000 rows corresponds to a value of 30, and 2000 rows correspond to a value of 40
。 In this data distribution pattern, when searching for other deptno values other than 10, there is no doubt that if the index can be applied, then the efficiency will be much higher. We can use a separate analysis of the indexed column, or use the
The Analyze statement creates a histogram of the column and collects enough statistics on the column to enable ORACLE to index the higher-selectivity values of the search.

Ninth, whether the indexed column value can be null (
NULL). If the index column value can be null, the operation in the SQL statement that needs to return a NULL value will not be used for the index, such as COUNT (*
), but with a full table scan. This is because the stored value in the index cannot be full empty.

Tenth, see if it is useful to parallel queries (PQO). The index will not be used for parallel queries. If we want to use a
Table Ind_col1 "Select/*+ INDEX (A ind_col1) */* from A WHERE COL1 =
XXX; " Note that the comment must follow the Select, and the "+" in the comment should follow the comment start character "/*" or "--", otherwise the hint is considered a general comment,
The execution of a PL/SQL statement does not have any effect.
One is the EXPLAIN TABLE method.

The user must first in their own mode (schema
), the Plan_table table is established, and each step of the execution plan is recorded in the table, and the SQL script for the table is created under ${oracle_home}/rdbms/admin/
Utlxplan.sql Open Sql*plus, enter "SET AUTOTRACE on" and run the SQL statement you want to debug. After the query results are given,
ORACLE will display the corresponding execution plan, including the optimizer type, execution cost, connection method, connection order, data search path, if we are unsure of the specific SQL that needs to be tracked
Statements, such as when an app has been in use for some time, the response slows down suddenly. We can then take advantage of another powerful tool provided by ORACLE, TKPROF, to track the execution of an application throughout the process.


We have to first in the system view v$session, according to the USERID or machine, the corresponding SID and serial# can be detected. With the Sys
or other user who has executed the Dbms_system package to connect to the database, execute "execute dbms_system." Set_sql_trace_in_session
(SID, serial#, TRUE); ". Then run the application, at the server side, the database parameter "User_dump_dest" indicates the directory, will generate
ORA__XXXX.TRC file, where xxxx is the operating system process number for the tracked application. After the application finishes executing, the file is parsed with the command tkprof.


command example: "Tkprof tracefile outputfile Explain=userid/password". In the operating system
Under ORACLE user, type "tkprof", with detailed command help. After parsing the output file outputfile, there are "execution plans" for each PL/SQL statement,
Important information such as CPU occupancy, physical reads, logical reads, execution time, and so on. Based on the output file information, we can quickly find out which PL/SQL statements in the application are the crux of the problem.


The reason why the Oracle database cannot use the index is described here, and I hope this introduction will help you.

Two
Oracle
Index use (TIPS)

Although indexes are not always faster than full-table scans, many times we want Oracle to use indexes to execute some SQL, which we can do with index
Hints to force SQL to use index.

The format of Index hints is as follows:

/*+ index (table [index
[index] ...] ) */

Select/*+ Index (t i_t) */* from t where
Username= ' Eygle ';


When the field in the where condition is similar to column1 is null or column1 is not
Null is, even if the Column1 has an index on it would not be used

For example where Column1 = ' AAA ' is an index that can be used


The following conditions are not used in the index
column1| | Column2= ' aaabbb '
Substr (column1,1) = ' AAA '

column1| | ' B ' = ' Aaab '
Column3 + 1 >: A

Like
The first character in the string is not indexed if it is '% '
Column1 like ' aaa% ' is possible.
Column1 like '%aaa% ' can't be used


<> does not use the index
Sometimes appropriate measures can be taken to rewrite the index that can be used
Example: Column1
There are 3 values (' A ', ' B ', ' C '), three values are distributed as ' A ' 10%, ' B ' 80%, ' C ' 10%, then column1 <> ' B '
Can be rewritten as column1 in (' A ', ' C ')

Although the in notation is simpler than exists, exists generally performs much higher than in

With in or with exists time
When the set of in is relatively small, or use exists can not use the high-selectivity index, use in good, otherwise you will use exists

Example: SELECT COUNT (*) from Person_info where XB in (select xb_id from Dic_sex);

Select COUNT (*) from N_acntbasic a where shbxdjm =:a and exists (select 1
From Person_info where pid=a.pid and ...);

Select * from Person_info where
zjhm=3101. person_info full table scan will be
Select * from Person_info where ZJHM
= ' 3101 ... ' to use the index

Assume that the DT field of the test table is of type date and that the DT is indexed.

If you want to check the data for ' 20041010 ' Day. The following method does not use the index
Select * FROM Test where
To_char (DT, ' yyyymmdd ') = ' 20041010 ';
While select * FROM test where DT
>=to_date (' 20041010 ', ' YYYYMMDD ') and DT < to_date (' 20041010 ', ' yyyymmdd ') +
1 The index will be used.

If you can not use the sort, try to avoid sorting.
Used to sort the case there are
Collection operation. Union, minus
, intersect, etc., note: UNION ALL is unordered.
Order by
Group by
Distinct
Inch
Sometimes it's sort of used.
Do try to sort small amounts of data when you want to sort
, try to make the sort in memory execution, there is the article said that the speed of memory sorting is 10,000 times times the hard disk sort.