Automatic indexing - Not supported - Part II

In the pervious post, we discussed how AI process does not create auto indexing in the scenario where minimum or maximum of a column is required.
 
Another scenario when an AI is not created is when we hit the issues associated with the missing index on a foreign key constraint.
 
Deleting a record from the parent table without index on the associate foreign key column of the child table, we hit a number of issues including having to perform a full table scan on the child tables and the associated locking problems.
 
To illustrate, we will first create a parent table.
 
ai_demo@ATP21C> create table demo035p
  2  nologging
  3  as
  4  select rownum as id , a.*
  5  from stage a
  6  where rownum <= 10000;
 
Table created.
 
And then a larger child table, with no index on the associated foreign key constraint.
 
ai_demo@ATP21C> create table demo035c
  2  nologging
  3  as
  4  select rownum as pid ,
  5     mod(rownum,1000)+1000 as cid ,
  6     a.*
  7  from stage a, stage b, stage c
  8  where rownum <= 10000000 ;
 
Table created.
 
ai_demo@ATP21C> alter table demo035p
  2  add constraint pk_demo035p
  3  primary key(id);
 
Table altered.
 
ai_demo@ATP21C> alter table demo035c
  2  add constraint fk_demo035c
  3  foreign key(cid)
  4  references demo035p;
 
Table altered.
 
If we decide to delete rows from the parent table
 
ai_demo@ATP21C> set autotrace traceonly exp statistics
ai_demo@ATP21C> delete from demo035p where id = 55;
 
1 row deleted.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 4215583649
 
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |             |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  DELETE            | DEMO035P    |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| PK_DEMO035P |     1 |     4 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ID"=55)
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
   - PDML disabled because child referential constraints are present
 
 
Statistics
----------------------------------------------------------
         55  recursive calls
         12  db block gets
     164019  consistent gets
          6  physical reads
       2596  redo size
        340  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
We notice that even thought we delete one row from the parent table, we perform a large number of consistent gets due to a necessary full table scan on the child table, as Oracle is forced to check the table for any possible FK violations. Without an index on child table foreign key column, Oracle has no choice but to perform the relative expensive full table scan.
 
Additionally, if we have an existing transaction on the child table
 
ai_demo@ATP21C> @sid
 
INST_ SESSION_ID
----- ----------
1          54092
 
ai_demo@ATP21C> insert into demo035c(pid,cid) values(10000001,1055);
 
1 row created.
 
 
And then in another session attempt to delete a row from the parent table (from a different session)
 
ai_demo@ATP21C> @sid
 
INST_ SESSION_ID
----- ----------
1          12038
 
ai_demo@ATP21C> delete from demo035p where id = 56;
 
that delete statement goes into a block state, due to uncommitted transaction in the child table, which can lead to further locking issues in other sessions.
 
ai_demo@ATP21C> select l.object_id,u.object_name,l.session_id,l.locked_mode
  2  from v$locked_object l,
  3      user_objects u
  4  where l.object_id = u.object_id;
 
 OBJECT_ID OBJECT_NAM SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
    136548 DEMO035P        12038           3
    136548 DEMO035P        54092           3
    136549 DEMO035C        54092           3
 
ai_demo@ATP21C> select sid,blocking_session_status, blocking_session,event
  2  from gv$session
  3  where sid = 12038;
 
       SID BLOCKING_SE BLOCKING_SESSION EVENT
---------- ----------- ---------------- -----------------------------------
     12038 VALID                  54092 enq: TX - row lock contention
 
 
The full table scan on the child table and those associated locks can all be avoided by having an index on the FK constraint column, as these indexes can then be used to effectively police the constraint during such delete operation.
 
So what does AI does in this scenario? Currently it does nothing. In the Oracle database 21c(21.3) currently we can see an index in unusable/invisible
 
ai_demo@ATP21C> select index_name,index_type,indexing,auto,
  2         status,visibility
  3  from all_indexes
  4  where table_name in ('DEMO035P','DEMO035C')
  5  and owner ='AI_DEMO';
 
INDEX_NAME                INDEX_TYPE INDEXIN AUT STATUS   VISIBILIT
------------------------- ---------- ------- --- -------- ---------
SYS_AI_7zysyhnb663y9      NORMAL     FULL    YES UNUSABLE INVISIBLE
PK_DEMO035P               NORMAL     FULL    NO  VALID    VISIBLE  
 
ai_demo@ATP21C> select table_name,index_name,column_name,column_position
  2  from all_ind_columns
  3  where table_name in ('DEMO035P','DEMO035C')
  4  order by 1,2;
 
TABLE_NAME INDEX_NAME                COLUMN_NAME     COLUMN_POSITION
---------- ------------------------- --------------- ---------------
DEMO035C   SYS_AI_7zysyhnb663y9      CID                           1
DEMO035P   PK_DEMO035P               ID                            1
 
So you may need to manually create such an index on the FK constraint to improve the performance and eliminate the locking issues.
 
ai_demo@ATP21C> create index idx_demo035c on demo035c(cid);
 
Index created.
 
ai_demo@ATP21C> set autotrace traceonly exp statistics
ai_demo@ATP21C> delete from demo035p where id = 56;
 
1 row deleted.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 4215583649
 
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |             |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  DELETE            | DEMO035P    |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| PK_DEMO035P |     1 |     4 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ID"=56)
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
   - PDML disabled because child referential constraints are present
 
 
Statistics
----------------------------------------------------------
         22  recursive calls
          7  db block gets
         26  consistent gets
          6  physical reads
       1276  redo size
        340  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
ai_demo@ATP21C> set autotrace off
 
 
consistent gets were dropped massively as Oracle can now use the index to avoid the FTS search on the child table. The associated locking issues are eliminated as well.
 
 

Automatic indexing - Not supported - Part I

As discussed previously latest version of Oracle (21c (21.3)) Autonomous database running on Oracle cloud has increased the number of scenarios in which it will now create automatic indexes (AI), such as with non-equality predicates and JSON expressions.
 
However even with the latest version of Oracle database, there are still several scenarios where an automatic index will not be created, even though an index might prove beneficial.
 
One such scenario is when the min/max of a column is required.
 
Oracle can effectively use an index to determine either the min or max value of a column by just visiting the first or the last leaf block of an index. The index full scan (min/max) access path can be used explicitly for this purpose.
 
We will create a simple table like this
 
ai_demo@ATP21C> create table demo033
  2  nologging as
  3  select rownum as id, a.*
  4  from stage a, stage b
  5  where rownum <= 10000000 ;
 
Table created.
 
 Then we will run the following queries few time that return the min value of the ID column.
 
ai_demo@ATP21C> set autotrace traceonly exp statistics
ai_demo@ATP21C> select min(id) from demo033 ;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 4169885461
 
--------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |     1 |     6 | 45385   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE            |         |     1 |     6 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| DEMO033 |    10M|    57M| 45385   (1)| 00:00:02 |
--------------------------------------------------------------------------------------
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     166628  consistent gets
     166622  physical reads
          0  redo size
        376  bytes sent via SQL*Net to client
         48  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
 
 
Currently the CBO has no choice but to use a full table scan as there is currently no index on the ID column. So what does AI make of things?
 
Nothing, currently AI will not create index in this scenario, no matter how many times we execute these queries. If we look at the index on the table after a significant period of time running these queries.
 
ai_demo@ATP21C> select dbms_auto_index.report_activity(systimestamp -1/24, systimestamp,'TEXT','ALL','ALL') report from dual;
 
REPORT
-------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 11-AUG-2022 09:31:11
 Activity end                 : 11-AUG-2022 10:31:11
 Executions completed         : 4
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------
 
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates            : 0
 Indexes created             : 0
 Space used                  : 0 B
 Indexes dropped             : 0
 SQL statements verified     : 0
 SQL statements improved     : 0
 SQL plan baselines created  : 0
 Overall improvement factor  : 1x
-------------------------------------------------------------------------------
 
ai_demo@ATP21C> select table_name,index_name,index_type,indexing,auto,
  2          status,visibility
  3  from all_indexes
  4  where table_name = 'DEMO033'
  5  and owner ='AI_DEMO';
 
no rows selected
 
ai_demo@ATP21C> select table_name,index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name = 'DEMO033'
  4  order by 1,2;
 
no rows selected
 
no automatic index, to improve the performance of these queries, we currently have to manually create the associated index.
 
ai_demo@ATP21C> create index demo033_idx1 on demo033(id);
 
Index created.
 
If we now re-run these queries and look at the execution plan
 
ai_demo@ATP21C> set autotrace traceonly exp statistics
ai_demo@ATP21C> select min(id) from demo033 ;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3449641509
 
-------------------------------------------------------------------------------------------
| Id  | Operation                  | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |              |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |              |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| DEMO033_IDX1 |     1 |     6 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        376  bytes sent via SQL*Net to client
         48  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 

We can see that the CBO is now indeed using the index to return the min/max value with a vastly reduced number of consistent gets (down to 3 from the previous 166K).