Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you an analysis of how to carry out null and index. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Today, I encountered a problem during the test. SQL should have taken Index, but didn't leave. I can't even add index hint. The description is as follows:

1. Set up a test table

Create table t1

As

Select object_id, object_name from dba_objects

two。 Create a b-tree index on the object_name column

Create index idx_t1_name on T1 (object_name)

3. If I were select object_name from T1, CBO would choose Index scan. But the strange thing is that it turns out to be full table scan.

SQL > set autotrace trace exp

SQL > select object_name from T1

Execution Plan

Plan hash value: 3617692013

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 50934 | 3282K | 57 (2) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | T1 | 50934 | 3282K | 57 (2) | 00:00:01 |

Note

-

-dynamic sampling used for this statement

[@ more@]

3. Use index hint to forcibly walk Index, but the result is still full table scan. I was surprised. Why can't hint afford to use it? Depressed.

SQL > select / * + index (T1, idx_t1_name) * / object_name from T1

Execution Plan

Plan hash value: 3617692013

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 50934 | 3282K | 57 (2) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | T1 | 50934 | 3282K | 57 (2) | 00:00:01 |

Note

-

-dynamic sampling used for this statement

4. Happened to look at the structure of the following table

SQL > desc T1

Name Null? Type

-

OBJECT_ID NUMBER

OBJECT_NAME VARCHAR2 (128)

The NULL column caught my attention. OBJECT_NAME can be null! If b-tree Index is built on a single column in oracle, null will not be stored in Index (composite index can, as long as the whole Index columns is not null). That is to say, if the object_name of some rows is null, then the value of Index will not discard the rows with object_name as null. What if I let object_name not null?

SQL > alter table T1 modify object_name not null

Table altered.

SQL > desc T1

Name Null? Type

-

OBJECT_ID NUMBER

OBJECT_NAME NOT NULL VARCHAR2 (128)

Try again.

SQL > select object_name from T1

Execution Plan

Plan hash value: 3617692013

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 50934 | 3282K | 57 (2) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | T1 | 50934 | 3282K | 57 (2) | 00:00:01 |

The result is still full table scan: (

Try hint

SQL > select / * + index (T1, idx_t1_name) * / object_name from T1

Execution Plan

Plan hash value: 1352742509

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 50934 | 3282K | 264 (1) | 00:00:04 |

| | 1 | INDEX FULL SCAN | IDX_T1_NAME | 50934 | 3282K | 264 (1) | 00:00:04 |

This time hint worked. This shows that it is not the failure of Hint, but that the conditions for Index are not met at the beginning. It'seems that null is a potential killer and should be carefully guarded against.

Now the forced use of index is ok. But what will affect CBO's judgment of not going to Index? Thinking that statistics might be one of the reasons, I looked at it.

SQL > select index_name, LAST_ANALYZED from user_indexes

INDEX_NAME LAST_ANALYZED

--

IDX_T1_NAME 01-MAR-18

SQL > select table_name, LAST_ANALYZED from user_tables

TABLE_NAME LAST_ANALYZED

--

T1

I saw that the newly built table did not make statistics. So go to analyze table, the result is as follows:

SQL > exec dbms_stats.gather_table_stats ('TEST','T1')

PL/SQL procedure successfully completed.

SQL > select table_name, LAST_ANALYZED from user_tables

TABLE_NAME LAST_ANALYZED

--

T1 01-MAR-18

Let's see if there is any change in the implementation result:

SQL > select object_name from T1

Execution Plan

Plan hash value: 222950081

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 49917 | 1218K | 57 (2) | 00:00:01 |

| | 1 | INDEX FAST FULL SCAN | IDX_T1_NAME | 49917 | 1218K | 57 (2) | 00:00:01 |

This finally follows the old path of Index:) in Index, key value is sorted and stored. Index Fast full scan reads data according to the storage order of block, and can improve the efficiency of multi-block reading at one time (parameter readdb_file_multiblock_read_count), but the returned values are not sorted. And

Index full scan reads the values in Key value order and returns the sorted results. So, to be an order by would be to go Index full scan.

SQL > select object_name from T1 order by object_name

Execution Plan

Plan hash value: 1352742509

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 49917 | 1218K | 249 (1) | 00:00:03 |

| | 1 | INDEX FULL SCAN | IDX_T1_NAME | 49917 | 1218K | 249 (1) | 00:00:03 |

For columns defined as NULL, create a bitmap index to walk through the index

The above is the editor for you to share how to carry out null and index analysis, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.