In-depth understanding of Oracle indexes (22): synergy between indexes and null is mutually beneficial

(I) basis of cooperation

(1)/* All null Columns cannot be stored in B * tree */hr @ orcl> Create Table T (X number, y number ); HR @ orcl> Create index idx_t on T (x, y); HR @ orcl> insert into T values (1, 1); HR @ orcl> insert into T values (1, null); HR @ orcl> insert into T values (null, 1); HR @ orcl> insert into T values (null, null); HR @ orcl> commit; HR @ orcl> exec dbms_stats.gather_table_stats (ownname => User, tabname => 'T'); HR @ orcl> exec dbms_stats.gather_index_stats (ownname => User, indname => 'idx _ t');/* four rows are inserted, and only three rows are stored in the Index */hr @ orcl> select index_name, num_rows from user_indexes where index_name = 'idx _ T '; index_name num_rows ---------------------------- ---------- idx_t 3 (2)/* the query uses an index only when at least one column in the index key is defined as not null */-- Take the index HR @ orcl> drop table t purge; HR @ orcl> Create Table T (X number, y number not null); HR @ orcl> create unique index idx_t on T (x, y ); HR @ orcl> insert into T values (1, 1); HR @ orcl> insert into T values (null, 1); HR @ orcl> exec dbms_stats.gather_table_stats (ownname => User, tabname => 'T'); HR @ orcl> exec dbms_stats.gather_index_stats (ownname => User, indname => 'idx _ t '); HR @ orcl> set autot trace exphr @ orcl> select * from t where X is null; Execution Plan ------------------------------------------------------ plan hash value: 2296882198 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | bytes | 0 | SELECT statement | 1 | 5 | 1 (0) | 00:00:01 | * 1 | index range scan | idx_t | 1 | 5 | 1 (0) | 00:00:01 | identified predicate information (identified by Operation ID ): ----------------------------------------------- 1-access ("X" is null) -- do not go to index HR @ orcl> drop table t purge; HR @ orcl> Create Table T (X number, y number ); HR @ orcl> create unique index idx_t on T (x, y); HR @ orcl> insert into T values (1, 1); HR @ orcl> insert into T values (1, null); HR @ orcl> insert into T values (null, 1); HR @ orcl> insert into T values (null, null); HR @ orcl> commit; HR @ orcl> exec dbms_stats.gather_table_stats (ownname => User, tabname => 'T'); HR @ orcl> exec dbms_stats.gather_index_stats (ownname => User, indname => 'idx _ t'); HR @ orcl> set autot trace exphr @ orcl> select * from t where X is null; Execution Plan into plan hash value: 1601196873 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | bytes | 0 | SELECT statement | 2 | 8 | 3 (0) | 00:00:01 | * 1 | table access full | T | 2 | 8 | 3 (0) | 00:00:01 | identified predicate information (identified by Operation ID ): --------------------------------------------------- 1-filter ("X" is null)

(Ii) mutual benefit


Scenario 1

A table, a skewed column, and only two values are allowed.

② Requirements

● Index access to a few rows
● Scan the entire table to access most rows
● Saving index maintenance costs and reducing index storage overhead

③ Solution

There are 2:
● Use NULL for most rows and any value you want for a few rows
● Using function indexes and only indexing the non-null return values of functions is a good use of functions.

④ Example

The T and status columns of a ticket table only have two statuses: Processing (1) and not processing (0). Among them, the unprocessed columns account for 10% and the processed table accounts for 90%.
The following example shows how to query unprocessed items.

HR @ orcl> drop table t purge; HR @ orcl> Create Table T (X number, y number); HR @ orcl> Create index idx_t on T (decode (x, 0, 0, null); HR @ orcl> insert into T values (0, 1); HR @ orcl> edwrote file afiedt. buf 1 begin 2 for I in 1 .. 1000 3 Loop 4 insert into T values (1, I); 5 end loop; 6 commit; 7 * end; 8/PL/SQL procedure successfully completed. HR @ orcl> exec dbms_stats.gather_table_stats (ownname => User, tabname => 'T'); HR @ orcl> exec dbms_stats.gather_index_stats (ownname => User, indname => 'idx _ t');/* The index stores only one row, saving the index storage space */hr @ orcl> select index_name, num_rows from user_indexes where index_name = 'idx _ T '; index_name num_rows ---------------------------- ---------- idx_t 1hr @ orcl> set autot trace exp/* CBO select index */hr @ orcl> select * from t where decode (x, 0, 0, null) = 0; Execution Plan -------------------------------------------------------- plan hash value: 1594971208 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | period | 0 | SELECT statement | 1 | 7 | 2 (0) | 00:00:01 | 1 | table access by index rowid | T | 1 | 7 | 2 (0) | 00:00:01 | * 2 | index range scan | idx_t | 1 | 1 (0) | 00:00:01 | identified predicate information (identified by Operation ID ): ------------------------------------------------- 2-access (decode ("X", 0, null) = 0)

By David Lin

2013-06-10

Good luck

Happy Dragon Boat Festival