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
