jump to navigation

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

Posted by Richard Foote in 21c New Features, Automatic Indexing, Non-Equality Predicates, Oracle Indexes.
trackback

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…

Comments»

1. Automatic Indexes: Scenarios Where Automatic Indexes NOT Created Part I (“Always Crashing In The Same Car”) | Richard Foote's Oracle Blog - April 26, 2022

[…] has increased the number of scenarios in which it will now create automatic indexes, such as with non-equality predicates and JSON […]

Like


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: