Automatic Indexing: Non-Equality Predicates Part III (“Who Can I Be Now”)

In previous posts, I discussed how Automatic Indexing supported the creation of indexes based on equality predicates only.

One of the most significant improvements with Automatic Indexing introduced with Oracle Database 21c is that non-equality predicates are now supported.

Previously, if I created the following 10 million row table and ran an SQL query based on a non-equality predicate that only returned 9 rows:

SQL> create table ziggy (id number, code number, name varchar2(42));

Table created.

SQL> insert into ziggy select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level <= 10000000;

10000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY');

PL/SQL procedure successfully completed.

SQL> select * from ziggy where id between 42 and 50;

9 rows selected.

Execution Plan

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |    10 |   230 |  6173   (6)| 00:00:01 |
|   1 |  PX COORDINATOR              |          |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000 |    10 |   230 |  6173   (6)| 00:00:01 |
|   3 |    PX BLOCK ITERATOR         |          |    10 |   230 |  6173   (6)| 00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| ZIGGY    |    10 |   230 |  6173   (6)| 00:00:01 |
-----------------------------------------------------------------------------------------

It didn’t matter how often I ran the query, Automatic Indexing would never created the necessary Automatic Index on the ID column.

 

However, run the same query now on a 21c database and Automatic Indexing will generate the following index:

INDEX DETAILS

-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
--------------------------------------------------------------------
| Owner | Table | Index                | Key | Type   | Properties |
--------------------------------------------------------------------
| BOWIE | ZIGGY | SYS_AI_8102kh14m1mf8 | ID  | B-TREE | NONE       |
--------------------------------------------------------------------
-------------------------------------------------------------------------------

SQL> select index_name, auto, visibility, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='ZIGGY';

INDEX_NAME                AUT VISIBILIT STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------- --- --------- -------- ---------- ----------- -----------------
SYS_AI_8102kh14m1mf8      YES VISIBLE   VALID      10000000       23780             38451

 

If I re-run the equivalent query:

SQL> select * from ziggy where id between 42 and 50;

Execution Plan
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |    10 |   230 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ZIGGY                |    10 |   230 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_8102kh14m1mf8 |    10 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
       2 - access("ID">=42 AND "ID"<=50)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        963  bytes sent via SQL*Net to client
         52  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

 

Automatic Indexing has kicked in and significantly improved the performance of this query for me.

Automatic Indexing may still have a number of restrictions, but by supporting non-equality predicates, it has now removed its most significant restriction…