Oracle 19c database Automatic Indexing lab setup session 1

 Hi All,

In this post i am sharing about Oracle 19C database automatic indexing sample lab exercise 1 .

the efficiency of Automatic Indexes including their usage within Oracle’s Autonomous Database environments, data clustering is just as important.

The following demo was run on an Oracle 19c database within the Oracle ATP database Cloud environment.

will begin by creating a simple table that has the key column CODE, in which data is populated in a manner where the data is very poorly clustered:

SQL> create table racsinfo (id number, code number, name varchar2(42));

 

Table created.

 

SQL> insert into racsinfo select rownum, mod(rownum, 100), 'racs info and the dba Seeds'

     from dual connect by level <= 1000000;

 

1000000 rows created.

 

SQL> commit;

 

Commit complete.

 

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'racsinfo');

 

PL/SQL procedure successfully completed.

So we have 100 evenly distributed distinct CODE values but they’re all distributed throughout the table.

The following SQL statement is basically returning just 1% of the data and is executed a number of times:

SQL> select * from racsinfo where code=43;

Execution Plan

 -----------------------------------------------------------------------------------------

| Id  | Operation                    | Name     | Rows    | Bytes | Cost (%CPU)| Time    |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |          |     100K|  3613K|  9125   (5)| 00:00:01|

|   1 |  PX COORDINATOR              |          |         |       |            |         |

|   2 |   PX SEND QC (RANDOM)        | :TQ10000 |     100K|  3613K|  9125   (5)| 00:00:01|

|   3 |    PX BLOCK ITERATOR         |          |     100K|  3613K|  9125   (5)| 00:00:01|

|*  4 |     TABLE ACCESS STORAGE FULL| racsinfo |     100K|  3613K|  9125   (5)| 00:00:01|

------------------------------------------------------------------------------------------

Without an index, the CBO currently has no choice but to use a Full Table Scan to access the table. So we wait for the next Automatic Index process to kick in:

Without an index, the CBO currently has no choice but to use a Full Table Scan to access the table. So we wait for the next Automatic Index process to kick in:

select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor     from user_indexes;

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor 

     from user_indexes where table_name='RACSINFO';

 

INDEX_NAME           AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR

-------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------

SYS_AI_76gfredfww3f4r YES NO  INVISIBLE DISABLED      UNUSABLE   1000000       20346           8758302

 

SQL> select index_name, column_name, column_position from user_ind_columns where table_name='racsinfo'

     order by index_name, column_position;

 

INDEX_NAME           COLUMN_NAME          COLUMN_POSITION

-------------------- -------------------- ---------------

SYS_AI_76gfredfww3f4r CODE                               1

 

We can see that yes, an Automatic Index (SYS_AI_76gfredfww3f4r) has been created on the CODE column of the racsinfo table BUT it remains in an INVISIBLE, UNUSABLE state.

So Automatic Indexing considered an index on CODE, created it in an INVISIBLE, USABLE state but when testing it, failed in that it found it to be less efficient than the current FTS and so reverted the Automatic Index back to an UNUSABLE index.

Therefore, if we run a bunch of other similar SQL statements such as the following:

SQL> select * from racsinfo where code=21;

SQL> select * from racsinfo where code=22;

SQL> select * from racsinfo where code=43;

 

They all use the FTS as again, the CBO has no choice with no VALID index on the CODE column available.

If we keep checking the Automatic Indexing report:

SQL> select dbms_auto_index.report_last_activity() report from dual;

SQL> select dbms_auto_index.report_last_activity() report from dual;

 

There’s still no mention of an index on the CODE column. The existing Automatic Index remains in an UNUSABLE state:

SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='racsinfo';

 

INDEX_NAME           AUT CON VISIBILIT COMPRESSION   STATUS     NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR

-------------------- --- --- --------- ------------- -------- ---------- ----------- -----------------

SYS_AI_76gfredfww3f4r YES NO  INVISIBLE DISABLED      UNUSABLE   10000000       20346           8758302

 

Basically, the index remains ineffective because with a Clustering Factor of 8758302, it’s just too inefficient to return the 1% (100000 rows) of the table.

Even in an Autonomous Database environment, nothing will automatically change with this scenario.

In my next post, we’ll look at how we can improve the performance of this query and get an Automatic Index to actually kick in with a USABLE index…

Thanks,

Srini