Why Oracle does not walk the index

CREATE TABLE TB2 as SELECT * from EMP;
ALTER TABLE TB2 modify EMPNO number (4) not null;
Turn To Line 20W

Create INDEX Idxtb21 on TB2 (empno);

Select Index_name from dba_indexes where table_name= ' TB2 ';--Verify that INDEX is set up

Set autotrace on;


Select distinct empno from tb2;--Walk index

Select distinct ename from TB2 where empno=7934;--not gone index


Select distinct ename from TB2 where empno=7934;--hint, also does not walk index


Create INDEX idxtb22 on TB2 (ename);--Does it have anything to do with ename? (It should be OK to say, build it)


SelectEname from TB2 where empno=7934;--still not go index

Rebuild Index

Alter index IDXTB21 rebuild;

1.alter index XXX rebuild [online];
Whether to add online, depends on your system requirements. Because rebuild blocks all DML operations when online is not added。

2.rebuild is not "delete index and then create". Rebuild not go to the FTS for sorting,
Instead, it iterates through the old index and constructs the structure in the temporary segment.Move to the new index.

"Delete the index and then create it" is the worst method.

SelectEname from TB2 where empno=7934;--still not go index

I don't understand, is it just because my optimizer is a CBO?
or is it because my watch is more repetitive because it is turned up? (This possibility is very large)

Take a look at the online article , or do not know why, the article is as follows

Oracle Optimizer does not walk index reason

The SQL Optimizer introduces the rule-based optimizer. Always use an index. Always start with the driver table (the table at the far right of the FROM clause). Full-table scanning is only possible if unavoidable. Any index can be based on the cost of the optimizer. Statistics required for tables and indexes Analyze table customer compute statistics; Analyze table Customer Estimate statistics sample of the rows;. Table to set the degree of parallelism, table partitioning
The optimizer mode rule mode. Always ignore CBO and statistics based on rule choose mode. Oracle chooses the rule or first_rows or all_rows first_rows mode, depending on the situation. Returning records at the fastest rate based on cost will cause the overall query speed to fall or consume more resources, preferring index scanning, suitable for OLTP system all_rows mode. Based on cost, ensure the overall query time is shortest, tend to parallel full-table scan for example: Select last_name from the customer order by last_name; When using First_rows, quickly return records, but large I/O volume, with All_rows, The return record is slow but uses less resources.
The purpose of this article: 1, to say a optimizer of Oracle and some related knowledge. 2. Answer why sometimes a field of a table has an index, and when observing some SQL execution plan, it is found that the index does not follow the problem. 3, if you have doubts about the two modes of first_rows and all_rows, you can also look at this article.
Let's go:
Before Oracle executes a SQL, it first parses the execution plan of the statement and then executes it on the execution plan. The execution plan of the parsing statement is done by the optimizer (Optimizer). In different cases, a SQL may have multiple execution plans, but at some point there must be only one execution plan that is optimal and spends the least time. I believe that you will use PL/SQL Developer, Toad and other tools to see the execution plan of a statement, but you may have the rule, Choose, first rows, all rows of these items have doubts, because I was the same, Then I wondered why the different items were chosen, and the execution plan changed.
1, optimize the way of optimization
Oracle's optimizer has a total of two optimizations, namely rule-based Optimization (rule-based optimization, referred to as RBO) and cost-based optimization (cost-based optimization, referred to as CBO). A, Rbo mode: The optimizer follows a number of rules that are scheduled within Oracle when parsing SQL statements. For example, we often go to the index when one of the columns in a WHERE clause has an index. B, the CBO way: according to the word meaning, it is to see the price of the statement (cost), where the cost is mainly CPU and memory. When deciding whether to use this method, the optimizer mainly refers to the statistics of tables and indexes. Statistics give information such as the size of the table, the number of rows, the length of each row, and so on. These statistics are not in the library at first, it is you do analyze after the appearance, a lot of time out of the statistics will cause the optimizer to make a wrong execution plan, because we should update this information in a timely manner. In Oracle8 and later versions, the Oracle column is recommended in the CBO way.
We have to understand that it is not necessarily the index is excellent, such as a table only two rows of data, a single IO can complete the full table of the retrieval, and at this time to walk the index requires two Io, when the table to do a full table scan is the best.
2. Optimization mode of optimizer (Optermizer modes)
The optimization model includes Rule,choose,first rows,all rows in four ways, which we have mentioned above. I would like to explain:
Rule: Needless to say, go for a rule-based approach.
Choolse: This is what we should be viewing, which is the way Oracle is used by default. Refers to when a table or index has statistics, then walk the CBO way, if the table or index does not have statistical information, the table is not particularly small, and the corresponding column index, then go index, Walk Rbo way.
First rows: It is similar to the Choose approach, unlike when a table has statistics, it will be the quickest way to return the top lines of the query, reducing the overall response time.
All rows: That is what we call cost, and when a table has statistics, it will return all rows of the table in the quickest way, increasing the throughput of the query as a whole. There is no statistical information to go based on the rules of the way.
3, how to set the choice of which optimization mode
A, instance level
We can set Optimizer_mode=rule, Optimizer_mode=choose, Optimizer_mode=first_rows, Optimizer_mode=all_ in the Init.ora file. Rows to choose 3 of the four ways, if you do not set the Optimizer_mode parameter is the default choose this way.
B, Sessions level
Set by sql> ALTER SESSION set optimizer_mode=;
C, statement level
These need to use hint, for example: Sql> SELECT A.userid, 2 b.name, 3 B.depart_name 4 from Tf_f_yhda A, 5 Tf_f_depart B 6 WHERE a.userid=b.u Serid;
4, why sometimes a table of a field is clearly indexed, when observing some of the language of the execution plan does not go index it? How to solve it?
A, do not go to the index for a number of reasons ♀ you at the instance level is the all_rows way to ♀ your table statistics (the most probable cause) ♀ your table is very small, as mentioned above, the Oracle optimizer does not think it is worth going to the index. B, the solution ♀ can modify the Init.ora in the Optimizer_mode this parameter, change it to rule or choose, restart the database. You can also use the hint mentioned in 4.

Add: Do not take the cause of the index, even add hint not go index, it may be because you want to go the index of this column is nullable, although this column has no null value. (change field to NOT null)

Note: Other reasons for not walking 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.

Such as:

The Where condition adds a handler function to the field will not use the index of the column select * from EMP where TO_CHAR (hire_date, ' yyyymmdd ') = ' 20080411 ' (not used) SELECT * from EMP where hire_date = To_char (' 20080411 ', ' YYYYMMDD ') (used)

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 a character type, Oracle generates an implicit type conversion, the converted statement is similar to the select * from T where To_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 walks 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'? ' Percent semicolon in front

8, not in, not exist.

Why Oracle does not walk the index