jump to navigation

Merry Christmas and a Happy, Covid-Free New Year!! (“The Little Drummer Boy/Peace On Earth”) December 24, 2021

Posted by Richard Foote in Christmas, Oracle Indexes.
add a comment

Well, 2021 was a bit of a struggle wasn’t it !! I’m hopeful that 2022 might just be a tad better (but I’m not overly confident to be honest).

I would like to take this opportunity to wish all my readers and Oracle friends a very Merry Christmas and a most happy, peaceful, prosperous and Covid free New Year.

I’m going back to my traditional gift this year, the classic David Bowie and Bing Crosby Christmas duet as featured on the Bing Crosby Christmas Special in 1977, back when life was just that little bit less stressful…

 

Enjoy 🙂

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.
2 comments

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…

Automatic Indexing: 3 Possible States Of Newly Created Automatic Indexes Part II (“Because You’re Young”) December 20, 2021

Posted by Richard Foote in 21c New Features, Automatic Indexing.
add a comment

In a previous post, I discussed how there can be three different states of newly created Automatic Indexes, based on the predicate selectivities of the SQLs that generated the indexes.

In this post, I’m going to highlight new behaviour I’ve noticed with Oracle 21c that results in the three different states of newly created Automatic Indexes based on the number of times of new SQL statements are executed.

To demonstrate, I’m going to create 3 identical tables and populate them with identical data:

SQL> create table bowie1 (id number, code number, name varchar2(42));
SQL> create table bowie2 (id number, code number, name varchar2(42));
SQL> create table bowie3 (id number, code number, name varchar2(42));

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

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE1');
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE2');
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE3');

I then run the following queries in the following manner.

I run this query based on a CODE equality predicate on the BOWIE1 table just one time only:

select * from bowie1 where code=42;

I run the same query on the BOWIE2 table a few times (definitely more than once, say 5 times):

select * from bowie2 where code=42;

I run the following queries on the BOWIE3 table multiple times (say 10 or more times):

select * from bowie3 where code=42;
select * from bowie3 where code=24;
select * from bowie3 where code=4242;
select * from bowie3 where code=4444;

 

I then await the Automatic Indexing process and examine the resultant generated Automatic Indexes:

SQL> select table_name, index_name, auto, visibility, status, num_rows, leaf_blocks
from user_indexes where table_name in ('BOWIE1','BOWIE2', 'BOWIE3');

TABLE_NAME INDEX_NAME           AUT VISIBILIT STATUS     NUM_ROWS LEAF_BLOCKS
---------- -------------------- --- --------- -------- ---------- -----------
BOWIE1     SYS_AI_1c6k1qpybh694 YES INVISIBLE UNUSABLE   10000000       23058
BOWIE2     SYS_AI_8v1sh0j56g4hs YES INVISIBLE VALID      10000000       16891
BOWIE3     SYS_AI_g6tta1zrmak4d YES VISIBLE   VALID      10000000       16891

 

We notice we have each of our possible 3 Automatic Index states.

The Automatic Index on the BOWIE1 table with a query that was “only executed once” has an INVISIBLE/UNUSABLE index. Where previously Automatic Indexing would have simply generated a VISIBLE/VALID index even on a query that was only executed once, in more current releases, the logic seems to be if a query is very very infrequently executed, it will not be enough on it’s own to generate a usable Automatic Index.

The Automatic Index on the BOWIE2 table with a single query that was only executed a “very few number of times” (5 in this example) has an INVISIBLE/VALID index. Where previously again Automatic Indexing would have simply generated a VISIBLE/VALID index, in more current releases, the logic seems to be if a query is executed only a few times, it’s close to being useful, but not quite to generate a Visible index. Having it as a Visible index is perhaps something to consider in the near future, if the useful load increases sufficiently.

The Automatic Index on the BOWIE3 table with a query that runs “many times” and/or with multiple queries on the column has a VISIBLE/VALID index. The logic seems to be if the index is used sufficiently to justify its creation, the index is actually created in a state in which it can be generally subsequently used.

So there are a number of possible scenarios now in which an Automatic Index is created in this “in-between” INVISIBLE/VALID state.

It would perhaps be a nice addition if we had the ability to alter the Visibility state of an Automatic Index. But I guess the whole idea behind Automatic Indexing is that we shouldn’t need to worry about such things…