Oracle 19c Automatic Indexing: Poor Data Clustering With Autonomous Databases Part I (Don’t Look Down) August 6, 2020
Posted by Richard Foote in 19c, 19c New Features, Attribute Clustering, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, Clustering Factor, Full Table Scans, Index Rebuild, Index statistics, Oracle, Oracle Cloud, Oracle Cost Based Optimizer, Oracle Indexes, Oracle19c, Performance Tuning.trackback
I’ve discussed many times the importance of data clustering in relation to the efficiency of indexes. With respect to 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 Autonomous Database Transaction Processing Cloud environment.
I 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 nickcave (id number, code number, name varchar2(42)); Table created. SQL> insert into nickcave select rownum, mod(rownum, 100), 'Nick Cave and the Bad Seeds' from dual connect by level <= 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'NICKCAVE'); 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 nickcave where code=42; 100000 rows selected. 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| NICKCAVE | 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:
SQL> select dbms_auto_index.report_last_activity() report from dual;
The Automatic Indexing report makes no mention of Automatic Indexes on the NICKCAVE table…
If we look to see if any indexes have actually been created:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='NICKCAVE'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR -------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_dh8pumfww3f4r YES NO INVISIBLE DISABLED UNUSABLE 10000000 20346 4158302 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='NICKCAVE' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION -------------------- -------------------- --------------- SYS_AI_dh8pumfww3f4r CODE 1
We can see that yes, an Automatic Index (SYS_AI_dh8pumfww3f4r) has been created on the CODE column of the NICKCAVE 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 nickcave where code=24;
SQL> select * from nickcave where code=42;
SQL> select * from nickcave where code=13;
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;
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='NICKCAVE'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR -------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_dh8pumfww3f4r YES NO INVISIBLE DISABLED UNUSABLE 10000000 20346 4158302
Basically, the index remains ineffective because with a Clustering Factor of 4158302, 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…
[…] my previous post, I discussed a scenario in which Oracle Automatic Indexing refused to create a VALID index, because […]
LikeLike
[…] Part I we looked at a scenario where an index was deemed to be too inefficient for Automatic Indexing to […]
LikeLike
[…] Part I we looked at a scenario where an index was deemed to be too inefficient for Automatic Indexing to […]
LikeLike
[…] the Automatic Indexing process can leave a new Automatic Index in UNUSABLE / INVISIBLE state (as previously discussed), which can be subsequently […]
LikeLike