Shulou(Shulou.com)06/01 Report--

This paper introduces several situations in which indexes are not used. The database version of this experiment is 11.2.0.4.

Case 1:

We are using a B* tree index, and the first column of the index is not used in the predicate.

If this is the case, you can assume that there is a table T with an index on T (xmemy). To make the following query: select * from t where yearly 5. At this point, the optimizer does not intend to use the index on T (xmemy) because the X column is not involved in the predicate. In this case, if you use an index, you may have to look at each index entry, and the optimizer usually prefers to do a full table scan of the T table.

Zx@ORCL > create table t as select rownum x _ r _ r _ num _ 1 y _ r _ num _ 2 z from dual connect by level

Select count (*) from t; COUNT (*)-99999zx@ORCL > create index idx_t on t (XMagi y); Index created.zx@ORCL > exec dbms_stats.gather_table_stats (user,'T',cascade= > true); PL/SQL procedure successfully completed.zx@ORCL > set autotrace traceonly explain--where conditions use y=5zx@ORCL > select * from t where yard5 Execution Plan---Plan hash value: 1601196873 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- | 0 | SELECT STATEMENT | | 1 | 15 | 80 (2) | 00:00:01 | | * 1 | TABLE ACCESS FULL | T | 1 | 15 | 80 (2) | 00:00:01 |-- -Predicate Information (identified by operation id):-1-filter ("Y" = 5)-- where condition uses x=5zx@ORCL > select * from t where xchang5 Execution Plan---Plan hash value: 1594971208 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 15 | 3 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 15 | 3 (0) | 00:00:01 | | * 2 | INDEX RANGE SCAN | IDX_T | 1 | | 2 (0) | 00:00:01 |-Predicate Information (identified by operation id):- -2-access ("X" = 5)

However, this does not completely rule out the use of indexes. If the query is select XQuery y from t where yearly 5, the optimizer will notice that it does not have to scan the table fully to get X or Y (both x and y are in the index), and it would be more appropriate to do a fast, comprehensive scan of the index itself, because the index is generally much smaller than the underlying table. Also note that only CBO can use this access path.

Zx@ORCL > select XBI y from t where YP5 Execution Plan---Plan hash value: 2497 555198 Muhami- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 10 | 81 (2) | 00:00:01 | | * 1 | INDEX FAST FULL SCAN | IDX_T | 1 | 10 | 81 (2) | 00:00:01 |- -- Predicate Information (identified by operation id):-1-filter ("Y" = 5)

In another case, CBO will also use the index on T (XMagol y), which is the index skip scan. Jump scanning (skip scan) works well when and only if the top column of the index (x in the above example) has only a few different values, and the optimizer knows this. For example, consider an index on (GEMDER,EMPNO), where the GENDER values are M and F, and EMPNO is unique. For the following query:

Select * from t where empno=5

Consider using the index on T to satisfy this query with a skip scan, which means that conceptually the query will be handled as follows:

Select * from t where GENDER='M' and empno=5

Union all

Select * from t where GENDER='F' and empno=5

It jumps through the index, thinking that it is two indexes: one corresponding value M and the other corresponding value F.

Zx@ORCL > create table T1 as select decode (mod (rownum,2), 0) gender,all_objects.* from all_objects;Table created.zx@ORCL > create index idx_t1 on T1 (gender,object_id); Index created.zx@ORCL > exec dbms_stats.gather_table_stats (user,'T1',cascade= > true); PL/SQL procedure successfully completed.zx@ORCL > set autotrace traceonly explainzx@ORCL > select * from T1 where object_id=42 Execution Plan---Plan hash value: 4072187533 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 100 | 4 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 100 | 4 (0) | 00:00:01 | | * 2 | INDEX SKIP SCAN | IDX_T1 | 1 | | 3 (0) | 00:00:01 |-Predicate Information (identified by operation id):- -2-access ("OBJECT_ID" = 42) filter ("OBJECT_ID" = 42)

The INDEX SKIP SCAN step tells Oralce to skip through the index, query for changes in the GENDER value, read down the tree from there, and then query OBJECT_id=42 in each virtual index under consideration. If you significantly increase the available value of GENDER, it is as follows:

Zx@ORCL > alter table T1 modify GENDER varchar2 (2); Table altered.zx@ORCL > update T1 set gender= (chr (mod (rownum,1024); 84656 rows updated.zx@ORCL > commit;Commit complete.zx@ORCL > exec dbms_stats.gather_table_stats (user,'T1',cascade= > true); PL/SQL procedure successfully completed.zx@ORCL > set autotrace traceonly explainzx@ORCL > select * from T1 where object_id=42 Execution Plan---Plan hash value: 1601196873 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- | 0 | SELECT STATEMENT | 1 | 101 | 344 (1) | 00:00:05 | | * 1 | TABLE ACCESS FULL | T1 | 1 | 101 | 344 (1) | 00:00:05 |-- -Predicate Information (identified by operation id):-- 1-filter ("OBJECT_ID" = 42)

Case 2:

The select count (*) from t query (or similar query) is used, and there is a B* tree index on table T. However, instead of counting index entries, the optimizer is fully scanning the table (although the index is smaller than the table). In this case, the index may be based on a column that allows null values. Since no index entries are created for rows whose index keys are entirely null, the number of rows in the index may not be the number of rows in the table. The optimizer made the right choice here, if not, if it uses the index to count the number of rows, it may get the wrong answer.

Zx@ORCL > desc t; Name Null? Type- -X NUMBER Y NUMBER Z CHAR (23) zx@ORCL > select count (*) from t Execution Plan---Plan hash value: 2966233522 Murray- -| Id | Operation | Name | Rows | Cost (% CPU) | Time |-| 0 | SELECT STATEMENT | | 1 | 153 (1) | 00:00:02 | 1 | SORT AGGREGATE | | 1 | 2 | TABLE ACCESS FULL | T | 99999 | 153 (1) | 00:00:02 |-- -zx@ORCL > alter table t modify y not null Table altered.zx@ORCL > desc t Name Null? Type- -X NUMBER Y NOT NULL NUMBER Z CHAR (23) zx@ORCL > select count (*) from t Execution Plan---Plan hash value: 2371838348 Murray- | | Id | Operation | Name | Rows | Cost (% CPU) | Time |-| 0 | SELECT STATEMENT | | | 1 | 80 (0) | 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 2 | INDEX FAST FULL SCAN | IDX_T | 99999 | 80 (0) | 00:00:01 |- -

Situation 3:

For a column with an index, make the following query:

Select * from t where function (indexed_column) = value

Only to find that the index on indexed_colum is not used. The reason is that functions are used on this column. If the value of indexed_column is indexed instead of the value of function (indexed_column). This index cannot be used here. If you prefer, you can index the function separately.

Zx@ORCL > select * from t where mod (xmem999) = 1 Execution Plan---Plan hash value: 1601196873 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- | 0 | SELECT STATEMENT | | 1000 | 34000 | 153 (1) | 00:00:02 | | * 1 | TABLE ACCESS FULL | T | 1000 | 34000 | 153 (1) | 00:00:02 |- -Predicate Information (identified by operation id):-- 1-filter (MOD ("X") 999) = 1) zx@ORCL > create index idx_t_f on t (mod (xmem999)) Index created.zx@ORCL > exec dbms_stats.gather_table_stats (USER,'T',cascade= > true); PL/SQL procedure successfully completed.zx@ORCL > select * from t where mod (xmem999) = 1 Execution Plan---Plan hash value: 4125918735 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 3800 | 3800 | 102 (0) | 00:00:02 | | 1 | TABLE ACCESS BY INDEX ROWID | T | 3800 | 3800 | 00:00:02 | | * 2 | INDEX RANGE SCAN | IDX_T_F | 100 | | 1 (0) | 00:00:01 |-Predicate Information (identified by operation id):- -2-access (MOD ("X") 999) = 1)

Situation 4:

Considering the following situation, a character money has been indexed. This column contains only data. If you use the following syntax to query:

Select * from t where indexed_colum=5

Note that the number 5 in the query is the constant 5 (not a string), so the index on INDEXED_COLUMN is not used at this time. This is because the previous query is equivalent to the following query:

Select * from t where to_number (indexed_column) = 5

We implicitly apply a function to this column, as described in case 3, which prohibits the use of this index.

Zx@ORCL > create table T2 (x char (1) constraint t2_pk primary key, y date); Table created.zx@ORCL > insert into T2 values ('5row created.zx@ORCL author sysdate); 1 row created.zx@ORCL > commit;Commit complete.zx@ORCL > exec dbms_stats.gather_table_stats (USER,'T2',cascade= > true); PL/SQL procedure successfully completed.zx@ORCL > explain plan for select * from t2 where xbirthday 5 / explored.zxexamples ORCL > select * from table (dbms_xplan.display) PLAN_TABLE_OUTPUT- -Plan hash value: 1513984157 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0) | 00:00:01 | | * 1 | TABLE ACCESS FULL | T2 | 1 | 12 | 3 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-1-filter (TO_NUMBER ("X") = 5) Note--dynamic sampling used for this statement (level=2)

As you can see, it fully scans the table; and even if we give the following hint to the query:

Zx@ORCL > explain plan for select / * + index (T2 t2_pk) * / * from T2 where Xero5 * * explored.zxroomORCL > select * from table (dbms_xplan.display) PLAN_TABLE_OUTPUT- -Plan hash value: 3365102699 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 10 | 2 (0) | 00:00:01 | | * 2 | INDEX FULL SCAN | T2_PK | 1 | | | 1 (0) | 00:00:01 |-Predicate Information (identified by operation id):- -2-filter (TO_NUMBER ("X") = 5)

The index is used here, but instead of performing a unique scan (UNIQUE SCAN) of the index as we thought, a full scan (FULL SCAN) is completed. The reason can be seen from the last line of output: filter (TO_NUMBER ("X") = 5). An implicit function is applied to this database column. The string stored in X must be converted to a number before it can be compared with the value 5. It is not possible to convert 5 to a string here, because our NLS (National language support) setting controls the specific form when 5 is converted to a string (which is uncertain, different NLS settings will have different controls), so strings should be converted into data. This is the same (because the application is also a function), it is not possible to use the index to quickly find this row. If you just perform a string-to-string comparison:

Zx@ORCL > explain plan for select * from T2 where x stories 5 alternate explored.zx examples ORCL > select * from table (dbms_xplan.display) PLAN_TABLE_OUTPUT- -Plan hash value: 3897349516 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 12 | 1 (0) | 00:00:01 | | * 2 | INDEX UNIQUE SCAN | T2_PK | 1 | | 1 (0) | 00:00:01 |-Predicate Information (identified by operation id):- -2-access ("X" ='5') 14 rows selected.

As expected, this will get the INDEX UNIQUE SCAN we expect, and you can see that there are no functions applied here. Be sure to avoid implicit conversion as much as possible.

There is also a frequent question about the date, if you make the following query:

Select * from t where trunc (date_col) = trunc (sysdate)

It is also found that the query does not use an index on DATE_COL, and to solve this problem, you can index trunc (date_col) or query it using the interval comparison operator (which may be easier). Let's look at an example of using the greater than or less than operator for a date. The following conditions can be recognized:

Trunc (date_col) = trunc (sysdate)

The conditions are the same as the following:

Date_col > = trunc (sysdate) and date_colcreate table T3 (xMagney null,primary key (x)) as select rownum xMagneName y from all_objects;Table created.zx@ORCL > exec dbms_stats.gather_table_stats (USER,'T3',cascade= > true); PL/SQL procedure successfully completed.zx@ORCL > set autotrace traceonly explain-run a query zx@ORCL > select count (y) from T3 where x with relatively few queries

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.