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.add a comment
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…
Presenting At The “Oracle Horizon Breakfast Series” Throughout May in Canberra April 13, 2021
Posted by Richard Foote in 21c New Features, Database Security, JSON, Multitenant, Oracle Horizons Breakfast Series.add a comment
If you’re lucky enough to be based close to sunny Canberra, Australia, please join me in cooperation with Oracle Corporation for the “Oracle Horizons Breakfast Series” I’m running throughout May 2021 at the Canberra Hyatt Hotel.
Enjoy breakfast while I discuss some exciting Oracle Database related topics:
5 May : Innovate with Oracle Database 21c
12 May: Multitenant the Future of Oracle Database
19 May: Hybrid Cloud Security for your Database
26 May: Developing Faster With JSON in Oracle Database
Registration is free but due to COVID requirements, numbers are very limited.
For full details of presentations and to register for these events, please visit:
Richard Foote's Oracle Blog 