Using the index of the myth of the Five: the magical value of NULL

The myth of indexing using indexes: The magical use of null values
It is not because a completely empty entry is not logged to the index, it is determined not to use null values, on the contrary, sometimes a reasonable use of Oracle's NULL value will bring our query several times or even dozens of times times more efficient.

For example, add a table with a field that is "processing time", if a transaction is not processed, the column is empty, and in most cases the transaction is always 10% or less of the total number of records, while the record waiting to be processed (the "Processing Time" column is empty) is always the majority of records, then the Wait time "This column is indexed, and there is always a small number of records in the index, and the way we want to access it is when we access all of the records in the table (that is, 10% or more), we want to retrieve it through a full table scan; When we want to access the transactions that have been processed (that is, 5% or less), we want to access them through the index, because the number of records in the index is very small, see the following example:

Sql> CREATE TABLE TT as SELECT * from Sys.dba_objects;



Table created



Executed in 0.601 seconds



Sql> ALTER TABLE TT Add (t int);



Table Altered



Executed in 0.061 seconds



Sql> Select COUNT (*) from TT;



COUNT (*)

----------

6131c



Executed in 0.01 seconds



Sql> UPDATE TT set t=1 where owner= ' DEMO ';



Ten rows updated



Executed in 0.03 seconds



Sql> COMMIT;



Commit Complete



Executed in 0 seconds



Sql> Select COUNT (*) from TT where owner= ' DEMO ';



COUNT (*)

----------

Number of 10―――――――――――――― already processed



Executed in 0.08 seconds

S

Sql> Select COUNT (*) from TT;



COUNT (*)

----------

Total number of 6131―――――――――――――― records





Executed in 0.01 seconds



The following query, because it accesses most of the records in the table (the number of records that are processed, that is, more than 10%), can be seen, using a full table scan as we would like to:



Select object_name from TT where T is null;



Plan_table_output

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id |  Operation | Name | Rows | Bytes | Cost |

--------------------------------------------------------------------

| 0 |             SELECT STATEMENT |       |       |       | |

|* 1 | TABLE ACCESS Full |       TT |       |       | |

--------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter ("TT".) T "is NULL)

Note:rule Based Optimization



Rows selected



Executed in 0.05 seconds



The following query, because you want to access a small number of records in a table, we want to visit by index:

Select object_name from TT where T=1;



Plan_table_output

--------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id |  Operation | Name | Rows | Bytes | Cost |

--------------------------------------------------------------------

| 0 |             SELECT STATEMENT |       |       |       | |

|* 1 | TABLE ACCESS Full |       TT |       |       | |

--------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter ("TT".) T "=1)

Note:rule Based Optimization



Rows selected



Executed in 0.06 seconds

Note that the index is not used as we would like it to be, but a full table scan is used, and here is a conclusion:

Once the index is established, it is important to update the statistics regularly if you want to use the index reasonably under the CBO.



Below we analyze the index to see what effect:

Sql> Analyze index TT_IDX validate structure;



Index analyzed



Executed in 0 seconds



Sql> select Lf_rows from Index_stats;



Lf_rows

----------

A total of 10 lines in the 10―――――――――― index



Executed in 0.05 seconds



sql> exec dbms_stats.gather_index_stats (' DEMO ', ' tt_idx ');



Pl/sql procedure successfully completed



Executed in 0.03 seconds



Sql> SELECT Distinct_keys from User_indexes;



Distinct_keys

-------------

1―――――――――― only one key value



Executed in 0.05 seconds



Sql> SELECT * from TT where T is null;



6121 rows have been selected.





Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (full) ' TT '









Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

485 consistent gets

0 physical Reads

0 Redo Size

355012 Bytes sent via sql*net to client

4991 bytes received via sql*net from client

410 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

6121 rows processed



Sql> SELECT * from TT where t=5;



No rows selected





Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (by INDEX ROWID) of ' TT '

2 1 INDEX (RANGE SCAN) of ' Tt_idx ' (non-unique)









Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

1 consistent gets

0 physical Reads

0 Redo Size

964 Bytes sent via sql*net to client

372 bytes received via sql*net from client

1 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

0 rows processed



Sql> SELECT * from TT where t=1;



10 rows have been selected.





Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (by INDEX ROWID) of ' TT '

2 1 INDEX (RANGE SCAN) of ' Tt_idx ' (non-unique)









Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

4 consistent gets

0 physical Reads

0 Redo Size

1639 bytes sent via sql*net to client

503 Bytes received via sql*net from client

2 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Ten rows processed



sql> Update TT set t=2 where t=1;



10 rows have been updated.





Execution Plan

----------------------------------------------------------

0 UPDATE STATEMENT Optimizer=choose

1 0 UPDATE of ' TT '

2 1 INDEX (RANGE SCAN) of ' Tt_idx ' (non-unique)









Statistics

----------------------------------------------------------

0 Recursive calls

DB Block gets

1 consistent gets

0 physical Reads

3216 Redo Size

616 Bytes sent via sql*net to client

527 bytes received via sql*net from client

3 sql*net roundtrips To/from Client

2 Sorts (memory)

0 Sorts (disk)

Ten rows processed



Sql> Set Autotrace traceonly

sql> Update TT set t=3 where T is null;



6121 rows updated.





Execution Plan

----------------------------------------------------------

0 UPDATE STATEMENT Optimizer=choose

1 0 UPDATE of ' TT '

2 1 TABLE ACCESS (full) ' TT '









Statistics

----------------------------------------------------------

0 Recursive calls

18683 db block gets

Gets consistent

0 physical Reads

2583556 Redo Size

618 bytes sent via sql*net to client

Bytes received via sql*net from client

3 sql*net roundtrips To/from Client

1 Sorts (memory)

0 Sorts (disk)

6121 rows processed



Sql>