Oracle Database 19c Automatic Indexing: Invisible Indexes Oddity (Wild Eyed Boy From Freecloud) February 3, 2021
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Automatic Table Statistics, Autonomous Database, Autonomous Transaction Processing, CBO, Clustering Factor, Exadata, Histograms, Invisible Indexes, Oracle, Oracle Cloud, Oracle General, Oracle Indexes, Oracle Statistics, Oracle19c.trackback
There have been a couple of “oddities” in relation to both Oracle Autonomous Databases and Automatic Indexing behaviour that I’ve seen frequently enough now (on Oracle 19.5.0.0.0) to make it worth a quick blog article.
The following is a simple test case that highlights both these issues. I’ll begin with a basic table, that has the key column CODE with a selectivity that would likely make it too expensive to be accessed via an associated index.
SQL> create table pink_floyd (id number, code number, create_date date, name varchar2(42)); Table created. SQL> insert into pink_floyd select rownum, ceil(dbms_random.value(0, 5000)), sysdate-mod(rownum, 50000)+1, 'Dark Side of the Moon' from dual connect by level <=10000000; 10000000 rows created. SQL> commit; Commit complete.
Importantly, I’ll next collect statistics on this table using all the default attributes, including allowing Oracle to decide the merits of any column histogram:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'PINK_FLOYD'); PL/SQL procedure successfully completed.
Note I’ve yet to run a single query against this table. And yet, if we look at the details of each of these columns:
SQL> select column_name, num_distinct, density, histogram from dba_tab_columns where table_name='PINK_FLOYD'; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM -------------------- ------------ ---------- --------------- ID 9705425 0 HYBRID CODE 4835 .00005 HYBRID CREATE_DATE 50357 .00002 HYBRID NAME 1 4.9639E-08 FREQUENCY
All the columns have a histogram !! This despite the columns not meeting either criteria normally required for a histogram, that the column be used in a SQL predicate AND for the column to have an uneven distribution of values.
None of these columns have yet to be used in a filtering predicate and none of these columns have a uneven distribution of values, even the CODE column as highlighted by looking at the minimum and maximum number of occurrences:
SQL> select min(code_count), max(code_count) from (select count(*) code_count from pink_floyd group by code); MIN(CODE_COUNT) MAX(CODE_COUNT) --------------- --------------- 1845 2163
So it’s very odd for these histograms to be present.
If we run the following query with a filtering predicate based on the CODE column:
SQL> select * from pink_floyd where code=42; 2012 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1152280033 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2068 | 82720 | 844 (11) | 00:00:01 | |* 1 | TABLE ACCESS STORAGE FULL | PINK_FLOYD | 2068 | 82720 | 844 (11) | 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - storage("CODE"=42) filter("CODE"=42) Note ----- - automatic DOP: Computed Degree of Parallelism is 1 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 63655 consistent gets 63645 physical reads 0 redo size 38575 bytes sent via SQL*Net to client 360 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2012 rows processed
The CBO currently has no choice but to use a FTS with no index currently present. But what will Automatic Indexing make of things? If we look at the next automatic indexing report:
SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 2 Indexes created (visible / invisible) : 1 (0 / 1) Space used (visible / invisible) : 134.22 MB (0 B / 134.22 MB) Indexes dropped : 0 SQL statements verified : 1 SQL statements improved : 0 SQL plan baselines created : 0 Overall improvement factor : 0x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: *: invisible ------------------------------------------------------------------------------- ---------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | ---------------------------------------------------------------------------- | BOWIE | PINK_FLOYD | * SYS_AI_dp2t0j12zux49 | CODE | B-TREE | NONE | ---------------------------------------------------------------------------- -------------------------------------------------------------------------------
We notice that Oracle has created an Automatic Index, but it’s an INVISIBLE index !!
If we look at the details of this Automatic Index:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='PINK_FLOYD'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_dp2t0j12zux49 YES NO INVISIBLE ADVANCED LOW VALID 10000000 15369 9845256
The index is in an INVISIBLE/VALID state, not the usual INVISIBLE/UNUSABLE state for an index for which Automatic Indexing decides an index is not efficient enough to be implement.
This is NOT expected behaviour.
Usually INVISIBLE/VALID indexes are created when Automatic Indexing is in “REPORT ONLY” mode, although I have come across this scenario when statistics are stale or missing. But in this case, Automatic Indexing is in “IMPLEMENT” mode and the table has recently collected statistics, albeit with odd histograms present (hence why I think these issues to be related).
If we run the same query again:
SQL> select * from pink_floyd where code=42; 2012 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1152280033 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2068 | 82720 | 844 (11) | 00:00:01 | |* 1 | TABLE ACCESS STORAGE FULL | PINK_FLOYD | 2068 | 82720 | 844 (11) | 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - storage("CODE"=42) filter("CODE"=42) Note ----- - automatic DOP: Computed Degree of Parallelism is 1 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 63655 consistent gets 63645 physical reads 0 redo size 38575 bytes sent via SQL*Net to client 360 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2012 rows processed
The CBO has again no option but to use the FTS as Invisible indexes can not be considered by the CBO. However, it’s important to note that such an index would not be used by the CBO anyways as it would be deemed too expensive to use than the current FTS.
If you’re relying on Automatic Indexing and have it in Implement mode, I would recommend checking for any indexes in this INVISIBLE/VALID state as they’re an indication that something has very likely gone wrong…
[…] a recent post on Invisible Automatic Indexes, I was puzzled by a couple of “oddities” in relation to some behaviour in the Oracle […]
LikeLike
[…] I discussed way back in February 2021 (doesn’t time fly!!), I discussed some oddity cases in which Automatic Indexes were being created in an Invisible/Valid state. At the time, I described […]
LikeLike