Avoid unnecessary full-table scans with appropriate indexes

Most of the SQL optimization problems in the Oracle database can be solved by increasing or decreasing the index, but this is by no means all. When the target SQL statement is querying only a subset of the data in the target table, creating an appropriate index avoids having to use a full table scan for querying this small amount of data without an index, which reduces the resource consumption of the target SQL statement and also shortens the execution time.

Create a test table and create an ordinary single-key-value B-Tree index:

 as Select  from dba_objects; Table created. SQL> CREATE index idx_t1 on T1 (object_id); index created.

emptying the cache data, we look at the next SQL query execution plan and resource consumption:

Sql>alter system flush Buffer_cache; System altered. SQL>SetTiming Onsql>Setautotrace Traceonlysql>Select* fromT1whereobject_id is NULL; no rows selectedelapsed: xx:00.11 Execution Plan----------------------------------------------------------Plan Hash Value:3617692013--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |--------------------------------------------------------------------------|0|      SELECT STATEMENT | | A|2484|291(1)|xx:xx:Geneva|| *1|TABLE ACCESS full| T1 | A|2484|291(1)|xx:xx:Geneva|--------------------------------------------------------------------------predicate information (identified by Operation ID):---------------------------------------------------1-Filter ("object_id"is NULL) Note-----   -DynamicSampling used for  ThisStatement (level=2) Statistics----------------------------------------------------------308Recursive calls0db block gets 1151  consistent gets   1038  physical reads   0  redo size 1183Bytes sent via sql*Net to client405Bytes received via Sql*net fromClient1Sql*net roundtrips to/ fromClient0sorts (memory)0sorts (disk)0Rows processed

As you can see from the above query, the SQL statement query goes through a full table scan with 1151 consistent reads and 1038 physical reads.

Why not go to the index?

Originally for the normal single-key B-Tree index, the null value is not indexed, so even if there is a one-button value B-Tree index idx_t1 on object_id, it is not used when executing the above query. Is there a way for the above SQL to go through the index? It is not difficult to answer this question if you understand the structure and principles of the B-tree index in the Oracle database.

Just create a composite B-tree index here.

Delete the original IDX_T1 index, recreate a composite B-tree index IDX_T1 with the same name, whose leading column is still object_id, but the second column is a constant 0, the principle used here is---although the null value is not indexed for the single-key B-Tree index, for the composite B-Tree index, The null value is in the index.

sql> DROP Index Idx_t1;index dropped. SQL> CREATE index idx_t1 on T1 (object_id,0); index created

re-execute the same SQL query:

Sql>alter system flush Buffer_cache; System altered. Elapsed:xx:xx:00.11SQL>SQL>Select* fromT1whereobject_id is NULL; no rows selectedelapsed: xx:00.01 Execution Plan----------------------------------------------------------Plan Hash Value:50753647--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |--------------------------------------------------------------------------------------|0|        SELECT STATEMENT | | A|2484| the(0)|xx:xx: Geneva||1| TABLE ACCESS by INDEX rowid| T1 | A|2484| the(0)|xx:xx: Geneva|| *2|INDEX RANGE SCAN| Idx_t1 |4314| | One(0)|xx:xx: on|--------------------------------------------------------------------------------------predicate information (identified by Operation ID):---------------------------------------------------2-Access ("object_id"is NULL) Note-----   -DynamicSampling used for  ThisStatement (level=2) Statistics----------------------------------------------------------0Recursive calls0db block gets2 consistent gets2 Physical reads0Redo Size1183Bytes sent via sql*Net to client405Bytes received via Sql*net fromClient1Sql*net roundtrips to/ fromClient0sorts (memory)0sorts (disk)0Rows processed

Create a composite index after re-execution you can see Oracle this query uses the B-Tree composite index IDX_T1, the data from the full table scan into an index range scan, data time-consuming, consistency reading and physical reading have also declined significantly.
By creating an appropriate index to avoid unnecessary full table scans, the resource consumption of the target SQL statement is greatly reduced, resulting in a significant reduction in SQL execution time.

--Excerpt from Tri Hua Oracle-based SQL optimization

Avoid unnecessary full-table scans with appropriate indexes