Automatic indexing - AUTO_INDEX_TABLE

 
One of the more common questions regarding the Automatic Indexing (AI) are the areas of concern around having large and expensive automatic index build operations suddenly Occurring in one database and the impact this may have on overall performance.
 
Additionally, the questions around scenarios where very large automatic indexes are suddenly being build, but then get cancelled because they couldn’t get completed in the default (3600 second) allocated time, only for them to be attempted to be built again and for this cycle to be forever ongoing.
 
Sometimes we may not necessarily want to have indexes built on specific tables, perhaps they are massive, and we want to control when and how indexes on such tables are built. Perhaps because we are satisfied such table are already indexed very well. 
 
The impact of overall database performance of the AI Task creating large indexes is reduced, by Oracle only allowing one index to be created serially at any given time.
 
To address all these concerns Oracle has now introduced a new configuration (available in Oracle 21c and above) option within the DBMS_AUTO_INDEX.CONFIGURE procedure, AUTO_INDEX_TABLE. This now allow us to explicitly state which table to either included / exclude from the AI process. Where as in the prior version of Oracle we had the ability to state the included / exclude at schema level and not at table level.
 
demo@ATP21C> select *
  2  from dba_auto_index_config
  3  where parameter_name ='AUTO_INDEX_TABLE';
 
PARAMETER_NAME       PARAMETER_VALUE    LAST_MODIFIED   MODIF
-------------------- ------------------ --------------- -----
AUTO_INDEX_TABLE                       
 
To add the EMP table to an exclusion list of tables then
 
demo@ATP21C> exec dbms_auto_index.configure('AUTO_INDEX_TABLE','ai_demo.EMP',false);
 
PL/SQL procedure successfully completed.
 
To add the DEPT table to an inclusion list of tables then
 
demo@ATP21C> exec dbms_auto_index.configure('AUTO_INDEX_TABLE','ai_demo.DEPT',true);
 
PL/SQL procedure successfully completed.
 
To view the current AI settings
 
demo@ATP21C> select *
  2  from dba_auto_index_config
  3  where parameter_name ='AUTO_INDEX_TABLE';
 
PARAMETER_NAME       PARAMETER_VALUE                                                   LAST_MODIFIED                  MODIF
-------------------- ----------------------------------------------------------------- ------------------------------ -----
AUTO_INDEX_TABLE     table IN ("AI_DEMO"."DEPT") AND table NOT IN ("AI_DEMO"."EMP")    18-AUG-22 02.01.16.000000 PM   DEMO
 
Finally, to remove all tables from both exclusion or inclusion table list, then
 
demo@ATP21C> exec dbms_auto_index.configure('AUTO_INDEX_TABLE',null);
 
PL/SQL procedure successfully completed.
 
demo@ATP21C> select *
  2  from dba_auto_index_config
  3  where parameter_name ='AUTO_INDEX_TABLE';
 
PARAMETER_NAME       PARAMETER_VALUE    LAST_MODIFIED                  MODIF
-------------------- ------------------ ------------------------------ -----
AUTO_INDEX_TABLE                        18-AUG-22 02.01.34.000000 PM   DEMO
 
 
This means we can now more safely deploy AI, by determining explicitly which tables to include or exclude.
 
To support large table that can take potentially longer to build than the default 3600 seconds for the AI task to complete, we can also change the MAX_RUN_TIME of the AI tasks as follows.
 
demo@ATP21C> col dbid noprint
demo@ATP21C> select *
  2  from dba_autotask_settings
  3  where task_name ='Auto Index Task';
 
   TASK_ID TASK_NAME          INTERVAL MAX_RUN_TIME ENABL
---------- ----------------- --------- ------------ -----
         3 Auto Index Task         900         3600 TRUE
 
demo@ATP21C> exec dbms_auto_task_admin.modify_autotask_setting('Auto Index Task','MAX RUN TIME',7200);
 
PL/SQL procedure successfully completed.
 
demo@ATP21C> select *
  2  from dba_autotask_settings
  3  where task_name ='Auto Index Task';
                                             
   TASK_ID TASK_NAME          INTERVAL MAX_RUN_TIME ENABL
---------- ----------------- --------- ------------ -----
         3 Auto Index Task         900         7200 TRUE
                                             
demo@ATP21C>
 

Automatic indexing - indexing JSON - Part III

In the previous blogpost on how JSON expression can now be automatically indexed, there was an outstanding issue with the associated CBO immediately after the creation of automatic index.
 
ai_demo@ATP21C> set autotrace traceonly exp statistics
ai_demo@ATP21C> select * from demo032 where json_value(my_json_data,'$.OBJECT_NEW_ID') = '55';
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2321837570
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |   100K|    31M|  9154   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO032              |   100K|    31M|  9154   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_fwkyzzbd70x9s | 40000 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(JSON_VALUE("MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.OBJECT_NEW_ID'
              RETURNING VARCHAR2(4000) ERROR ON ERROR NULL ON EMPTY)='55')
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     478790  consistent gets
     478784  physical reads
          0  redo size
        960  bytes sent via SQL*Net to client
        433  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
 
If we look at the statistics of the execution plan, we notice both the numbers consistent gets (478790) and physical reads (478784), remain very high and identical to the pervious full table scan.
 
The first issue is with the estimated cardinality in the execution plan. The plan got a cost of 9154 but most importantly an estimated cardinality of 100K rows, but actuals were only one row returned. The estimates are way off and so therefore are the associated cost. This could potentially result in an inefficient plan and with index not being used by the CBO.
 
Basically, although autotrace suggests the newly created automatic index is being used, in fact the previous Full Table Scan plan is still being invoked.  (Note: this of course is one of the dangers of the autotrace plan, in that it might not display the actual plan being invoked).
 
 
So what’s going on there? It was due to one of the new feature introduced in Oracle 12c Release 2 – fine grained cursor invalidations – the ability to defer the invalidation of depended SQL cursor when an index is created or modified.
 
When an AI was created in Oracle 21c, the current SQL cursors are not invalidated (to reduce the overhead of having to potentially reparse of large number of current SQL cursors). However, this means the current inefficient sql statement will keep their existing sub-optimal execution plan post the creation of the newly created automatic indexes, until the existing sql cursors are aged out.
 
At which point, the new CBO plan using the AI will actually be invoked.
 
ai_demo@ATP21C> set autotrace traceonly exp statistics
ai_demo@ATP21C> select * from demo032 t11 where json_value(t11.my_json_data,'$.OBJECT_NEW_ID') = '55';
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2321837570
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     1 |   339 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO032              |     1 |   339 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_fwkyzzbd70x9s |     1 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(JSON_VALUE("MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.OBJECT_NEW_ID'
              RETURNING VARCHAR2(4000) ERROR ON ERROR NULL ON EMPTY)='55')
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        960  bytes sent via SQL*Net to client
        433  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
 
So just be aware that in Oracle database 21c, that your beautifully created AI may not actually get used a desired period of time.

Automatic indexing - indexing JSON - Part II

In the previous blogpost we found two issues on the execution plan generated after the automatic indexing (AI) were created.
 
ai_demo@ATP21C> set autotrace traceonly exp statistics
ai_demo@ATP21C> select * from demo032 where json_value(my_json_data,'$.OBJECT_NEW_ID') = '55';
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2321837570
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |   100K|    31M|  9154   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO032              |   100K|    31M|  9154   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_fwkyzzbd70x9s | 40000 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(JSON_VALUE("MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.OBJECT_NEW_ID'
              RETURNING VARCHAR2(4000) ERROR ON ERROR NULL ON EMPTY)='55')
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     478790  consistent gets
     478784  physical reads
          0  redo size
        960  bytes sent via SQL*Net to client
        433  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
 
The first issue is with the estimated cardinality in the execution plan. The plan got a cost of 9154 but most importantly an estimated cardinality of 100K rows, but actuals were only one row returned. The estimates are way off and so therefore are the associated cost. This could potentially result in an inefficient plan and with index not being used by the CBO.
 
The estimate of 100K rows comes from 1% of number of rows (10M rows) in the table. The issue here is CBO has no idea about what the expected cardinality might be, as the output from JSON expression is effectively a black box. Oracle generates a virtual hidden column for this purpose, to capture the column statistics that gives the CBO an accurate idea on the selectivity of expression-based predicates.
 
But it we look at the column statistics after the generation of the automatic indexes
 
ai_demo@ATP21C> select column_name,virtual_column,hidden_column,num_nulls,density,num_distinct
  2  from user_tab_cols
  3  where table_name = 'DEMO032';
 
COLUMN_NAME     VIRTU HIDDE  NUM_NULLS    DENSITY NUM_DISTINCT
--------------- ----- ----- ---------- ---------- ------------
ID              NO    NO             0 1.0304E-07      9705425
CREATED_DT      NO    NO             0 .333333333            3
MY_JSON_DATA    NO    NO             0          0            0
SYS_NC00005$    YES   YES
SYS_NC00004$    YES   YES
 
 
We notice that two virtual columns generated for the JSON based expression have no statistics. This is because we have yet to collect new statistics since the creation of automatic indexes. As a result, the CBO still has no idea on how many rows are estimated to come from the JSON based expression and so has to resort to the 1% estimate.
 
The good news with Oracle autonomous database environments, is that Oracle has the High Frequency Statistics collection capability, which will automatically collect these missing statistics after a small (configurable) period of time. 
 
So, if we wait approximately 15 mins and checkout the column statistics again.
 
ai_demo@ATP21C> exec dbms_output.put_line( dbms_stats.get_prefs('AUTO_TASK_STATUS') );
ON
 
PL/SQL procedure successfully completed.
ai_demo@ATP21C> select column_name,virtual_column,hidden_column,num_nulls,density,num_distinct
  2  from all_tab_cols
  3  where table_name = 'DEMO032';
 
COLUMN_NAME     VIR HID  NUM_NULLS    DENSITY NUM_DISTINCT
--------------- --- --- ---------- ---------- ------------
ID              NO  NO           0 1.0304E-07      9705425
CREATED_DT      NO  NO           0 .333333333            3
MY_JSON_DATA    NO  NO           0          0            0
SYS_NC00005$    YES YES          0          0      9705425
SYS_NC00004$    YES YES          0          0      9973333
 
 
If we re-run the query again,
 
ai_demo@ATP21C> set autotrace traceonly exp statistics
ai_demo@ATP21C> select * from demo032 where json_value(my_json_data,'$.OBJECT_NEW_ID') = '55';
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2321837570
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     1 |   339 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO032              |     1 |   339 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_fwkyzzbd70x9s |     1 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(JSON_VALUE("MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.OBJECT_NEW_ID'
              RETURNING VARCHAR2(4000) ERROR ON ERROR NULL ON EMPTY)='55')
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     478790  consistent gets
     478784  physical reads
          0  redo size
        960  bytes sent via SQL*Net to client
        433  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
 
We can see that the CBO has now correctly estimated that just 1 rows to be returned and the associated CBO cost has reduced to just 4 as a result.
 
So if you create a function based index, make sure the generated virtual column (whether created automatically or manually generated before the associated index) has the necessary statistics.
 

Automatic indexing - indexing JSON - Part I

One of many potential capabilities of Oracle database 21c was the ability of auto indexing (AI) of JSON expressions. Will start by creating a simple table that uses the new JSON datatype (introduced in Oracle 21c – the following example was run from Oracle Autonomous database cloud service having Oracle 21c(21.3) ) with some JSON documents loaded into it.
 
ai_demo@ATP21C> create table demo032
  2  nologging as
  3  select rownum as id,
  4    a.created as created_dt,
  5    json_object(
  6    'OWNER' value a.OWNER ,
  7    'OBJECT_NAME' value a.OBJECT_NAME ,
  8    'SUBOBJECT_NAME' value a.SUBOBJECT_NAME ,
  9    'OBJECT_ID' value a.OBJECT_ID ,
 10    'OBJECT_NEW_ID' value rownum ,
 11    'DATA_OBJECT_ID' value a.DATA_OBJECT_ID ,
 12    'OBJECT_TYPE' value a.OBJECT_TYPE ,
 13    'CREATED' value a.CREATED ,
 14    'LAST_DDL_TIME' value a.LAST_DDL_TIME ,
 15    'TIMESTAMP' value a.TIMESTAMP ,
 16    'STATUS' value a.STATUS returning JSON ) my_json_data
 17  from stage a, stage b
 18  where rownum <= 10000000 ;
 
Table created.
 
Then I run a few SQL statements with different JSON expression-based predicates, like this
 
ai_demo@ATP21C> set autotrace traceonly exp statistics
ai_demo@ATP21C> select * from demo032 where json_value(my_json_data,'$.OBJECT_NEW_ID') = '55';
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3377958815
 
-------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |   100K|    31M|   130K  (1)| 00:00:06 |
|*  1 |  TABLE ACCESS STORAGE FULL| DEMO032 |   100K|    31M|   130K  (1)| 00:00:06 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage(JSON_VALUE("MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.OBJECT_NEW_ID' RETURNING VARCHAR2(4000) NULL ON ERROR)='55')
       filter(JSON_VALUE("MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.OBJECT_NEW_ID' RETURNING VARCHAR2(4000) NULL ON ERROR)='55')
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     478790  consistent gets
     478784  physical reads
          0  redo size
        960  bytes sent via SQL*Net to client
        433  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
ai_demo@ATP21C> select * from demo032 where json_value(my_json_data,'$.OBJECT_NEW_ID' returning number) = 55;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3377958815
 
-------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |   100K|    31M|   130K  (1)| 00:00:06 |
|*  1 |  TABLE ACCESS STORAGE FULL| DEMO032 |   100K|    31M|   130K  (1)| 00:00:06 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage(JSON_VALUE("MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.OBJECT_NEW_ID' RETURNING NUMBER NULL ON ERROR)=55)
       filter(JSON_VALUE("MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$.OBJECT_NEW_ID' RETURNING NUMBER NULL ON ERROR)=55)
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     478790  consistent gets
     478784  physical reads
          0  redo size
        960  bytes sent via SQL*Net to client
        433  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
ai_demo@ATP21C> select * from demo032 t1 where t1.my_json_data.OBJECT_NEW_ID.number() = 55;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3377958815
 
-------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |   100K|    31M|   130K  (1)| 00:00:06 |
|*  1 |  TABLE ACCESS STORAGE FULL| DEMO032 |   100K|    31M|   130K  (1)| 00:00:06 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage(JSON_VALUE("T1"."MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT
              OSON , '$.OBJECT_NEW_ID.number()' RETURNING NUMBER NULL ON ERROR)=55)
       filter(JSON_VALUE("T1"."MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT
              OSON , '$.OBJECT_NEW_ID.number()' RETURNING NUMBER NULL ON ERROR)=55)
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     478790  consistent gets
     478784  physical reads
          0  redo size
        960  bytes sent via SQL*Net to client
        433  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
ai_demo@ATP21C> set autotrace off
 
they all return just one row, but most currently use full table scan with no indexes present.
 
So, what does AI make of things?
 
The first thing to note is that running the AI report generates the following error.
 
ai_demo@ATP21C> set echo on linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000
ai_demo@ATP21C> col report for a180
ai_demo@ATP21C> select dbms_auto_index.report_activity(systimestamp -1/24, systimestamp,'TEXT','ALL','ALL') report from dual;
ERROR:
ORA-30954: char 0 is invalid in json_value(MY_JSON_DATA, '$.OBJECT_NEW_ID' returni
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 107
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 8686
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 8686
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9236
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 89
ORA-06512: at line 1
 
 
 
no rows selected
 
If we look at the indexes now present in the table
 
 
ai_demo@ATP21C> select table_name,index_name,index_type,indexing,auto,
  2          status,visibility
  3  from all_indexes
  4  where table_name = 'DEMO032'
  5  and owner ='AI_DEMO';
 
TABLE_NAME INDEX_NAME                INDEX_TYPE                  INDEXIN AUT STATUS   VISIBILIT
---------- ------------------------- --------------------------- ------- --- -------- ---------
DEMO032    SYS_AI_fwkyzzbd70x9s      FUNCTION-BASED NORMAL       FULL    YES VALID    VISIBLE 
DEMO032    SYS_AI_17jynda4v9sxu      FUNCTION-BASED NORMAL       FULL    YES VALID    VISIBLE 
 
ai_demo@ATP21C> select table_name,index_name,column_name,column_position
  2  from user_ind_columns
  3  where table_name = 'DEMO032'
  4  order by 1,2;
 
TABLE_NAME INDEX_NAME                COLUMN_NAME     COLUMN_POSITION
---------- ------------------------- --------------- ---------------
DEMO032    SYS_AI_17jynda4v9sxu      SYS_NC00005$                  1
DEMO032    SYS_AI_fwkyzzbd70x9s      SYS_NC00004$                  1
 
ai_demo@ATP21C> col column_expression for a60
ai_demo@ATP21C> select index_name,column_expression
  2  from user_ind_expressions
  3  where table_name = 'DEMO032';
 
INDEX_NAME                COLUMN_EXPRESSION
------------------------- ------------------------------------------------------------
SYS_AI_17jynda4v9sxu      JSON_VALUE("MY_JSON_DATA" FORMAT OSON , '$.OBJECT_NEW_ID' RE
                          TURNING NUMBER ERROR ON ERROR NULL ON EMPTY)
 
SYS_AI_fwkyzzbd70x9s      JSON_VALUE("MY_JSON_DATA" FORMAT OSON , '$.OBJECT_NEW_ID' RE
                          TURNING VARCHAR2(4000) ERROR ON ERROR NULL ON EMPTY)
 
 
We can see that AI has indeed created two new automatic indexes, one on the varchar2 expression and other on numeric expression. If we re-run the sql’s we notice few important points, note the following was run after the automatic indexes were created.
 
ai_demo@ATP21C> set autotrace traceonly exp
ai_demo@ATP21C> select * from demo032 where json_value(my_json_data,'$.OBJECT_NEW_ID') = '55';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2321837570
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |   100K|    31M|  9154   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO032              |   100K|    31M|  9154   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_fwkyzzbd70x9s | 40000 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(JSON_VALUE("MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.OBJECT_NEW_ID'
              RETURNING VARCHAR2(4000) ERROR ON ERROR NULL ON EMPTY)='55')
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
ai_demo@ATP21C> select * from demo032 where json_value(my_json_data,'$.OBJECT_NEW_ID' returning number) = 55;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2305263965
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |   100K|    31M|  1920   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO032              |   100K|    31M|  1920   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_17jynda4v9sxu | 40000 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(JSON_VALUE("MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.OBJECT_NEW_ID'
              RETURNING NUMBER ERROR ON ERROR NULL ON EMPTY)=55)
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
ai_demo@ATP21C> select * from demo032 t1 where t1.my_json_data.OBJECT_NEW_ID.number() = 55;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2305263965
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |   100K|    31M|  1920   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEMO032              |   100K|    31M|  1920   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_AI_17jynda4v9sxu | 40000 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(JSON_VALUE("MY_JSON_DATA" /*+ LOB_BY_VALUE */  FORMAT OSON , '$.OBJECT_NEW_ID'
              RETURNING NUMBER ERROR ON ERROR NULL ON EMPTY)=55)
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1
 
ai_demo@ATP21C> set autotrace off
 
 
the first point to note is that the CBO now uses the newly created AI, as only one row is returned. In the next few blogposts we will see about the anomalies from the above plan.