Oracle 19c Automatic Indexing: Invisible/Valid Automatic Indexes (Bowie Rare) August 31, 2021
Posted by Richard Foote in 19c, 19c New Features, Attribute Clustering, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Clustering Factor, Exadata, Index Access Path, Index statistics, Invisible Indexes, Invisible/Valid Indexes, Oracle, Oracle Cloud, Oracle Cost Based Optimizer, Oracle Indexes, Oracle Statistics, Oracle19c, Unusable Indexes.1 comment so far
In my previous post, I discussed how newly created Automatic Indexes can have one of three statuses, depending the selectivity and effectiveness of the associated Automatic Index.
Indexes that improve performance sufficiently are created as Visible/Valid indexes and can be subsequently considered by the CBO. Indexes that are woeful and have no chance of improving performance are created as Invisible/Unusable indexes. Indexes considered potentially suitable but ultimately don’t sufficiently improve performance, are created as Invisible/Valid indexes.
Automatic Indexes are created as Visible/Valid indexes when shown to improve performance (by the _AUTO_INDEX_IMPROVEMENT_THRESHOLD parameter). But as I rarely came across Invisible/Valid Automatic Indexes (except for when Automatic Indexing is set to “Report Only” mode), I was curious to determine approximately at what point were such indexes created by the Automatic Indexing process.
To investigate things, I created a table with columns that contain data with various levels of selectivity, some of which should fall inside and outside the range of viability of any associated index, based on the cost of the associated Full Table Scan.
The following table has 32 columns of interest, each with a slight variation of distinct values giving small differences in overall column selectivity:
SQL> create table bowie_stuff1 (id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, code21 number, code22 number, code23 number, code24 number, code25 number, code26 number, code27 number, code28 number, code29 number, code30 number, code31 number, code32 number, name varchar2(42));
Table created.
SQL> insert into bowie_stuff1
select rownum,
mod(rownum, 900)+1,
mod(rownum, 1000)+1,
mod(rownum, 1100)+1,
mod(rownum, 1200)+1,
mod(rownum, 1300)+1,
mod(rownum, 1400)+1,
mod(rownum, 1500)+1,
mod(rownum, 1600)+1,
mod(rownum, 1700)+1,
mod(rownum, 1800)+1,
mod(rownum, 1900)+1,
mod(rownum, 2000)+1,
mod(rownum, 2100)+1,
mod(rownum, 2200)+1,
mod(rownum, 2300)+1,
mod(rownum, 2400)+1,
mod(rownum, 2500)+1,
mod(rownum, 2600)+1,
mod(rownum, 2700)+1,
mod(rownum, 2800)+1,
mod(rownum, 2900)+1,
mod(rownum, 3000)+1,
mod(rownum, 3100)+1,
mod(rownum, 3200)+1,
mod(rownum, 3300)+1,
mod(rownum, 3400)+1,
mod(rownum, 3500)+1,
mod(rownum, 3600)+1,
mod(rownum, 3700)+1,
mod(rownum, 3800)+1,
mod(rownum, 3900)+1,
mod(rownum, 4000)+1,
'THE RISE AND FALL OF ZIGGY STARDUST'
from dual connect by level >=10000000;
10000000 rows created.
SQL> commit;
Commit complete.
As always, it’s important that statistics be collected for Automatic Indexing to function properly:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_STUFF1', estimate_percent=>null); PL/SQL procedure successfully completed.
So on a 10M row table, I have 32 columns with the number of distinct values varying by only 100 values per column (or by a selectivity of just 0.001%):
SQL> select column_name, num_distinct, density, histogram from dba_tab_columns where table_name='BOWIE_STUFF1' order by num_distinct; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM ------------ ------------ ---------- --------------- NAME 1 .00000005 FREQUENCY CODE1 900 .001111 HYBRID CODE2 1000 .001 HYBRID CODE3 1100 .000909 HYBRID CODE4 1200 .000833 HYBRID CODE5 1300 .000769 HYBRID CODE6 1400 .000714 HYBRID CODE7 1500 .000667 HYBRID CODE8 1600 .000625 HYBRID CODE9 1700 .000588 HYBRID CODE10 1800 .000556 HYBRID CODE11 1900 .000526 HYBRID CODE12 2000 .0005 HYBRID CODE13 2100 .000476 HYBRID CODE14 2200 .000455 HYBRID CODE15 2300 .000435 HYBRID CODE16 2400 .000417 HYBRID CODE17 2500 .0004 HYBRID CODE18 2600 .000385 HYBRID CODE19 2700 .00037 HYBRID CODE20 2800 .000357 HYBRID CODE21 2900 .000345 HYBRID CODE22 3000 .000333 HYBRID CODE23 3100 .000323 HYBRID CODE24 3200 .000312 HYBRID CODE25 3300 .000303 HYBRID CODE26 3400 .000294 HYBRID CODE27 3500 .000286 HYBRID CODE28 3600 .000278 HYBRID CODE29 3700 .00027 HYBRID CODE30 3800 .000263 HYBRID CODE31 3900 .000256 HYBRID CODE32 4000 .00025 HYBRID ID 10000000 0 HYBRID
I’ll next run the below queries (based on a simple equality predicate on each column) several times each in batches of 8 queries, so as to not swamp the Automatic Indexing process with potential new index requests (the ramifications of which I’ll discuss in another future post):
SQL> select * from bowie_stuff1 where code1=42; SQL> select * from bowie_stuff1 where code2=42; SQL> select * from bowie_stuff1 where code3=42; SQL> select * from bowie_stuff1 where code4=42; SQL> select * from bowie_stuff1 where code5=42; ... SQL> select * from bowie_stuff1 where code31=42; SQL> select * from bowie_stuff1 where code32=42;
If we now look at the statuses of the Automatic Indexes subsequently created:
SQL> select i.index_name, c.column_name, i.auto, i.constraint_index, i.visibility, i.status, i.num_rows, i.leaf_blocks, i.clustering_factor from user_indexes i, user_ind_columns c where i.index_name=c.index_name and i.table_name='BOWIE_STUFF1' order by visibility, status; INDEX_NAME COLUMN_NAME AUT CON VISIBILIT STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ---------------------- ------------ --- --- --------- -------- ---------- ----------- ----------------- SYS_AI_5rw9j3d8pc422 CODE5 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_48q3j752csn1p CODE4 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_9sgharttf3yr7 CODE3 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_8n92acdfbuh65 CODE2 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_brgtfgngu3cj9 CODE1 YES NO INVISIBLE UNUSABLE 10000000 21702 4272987 SYS_AI_1tu5u4012mkzu CODE11 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_34b6zwgtm86rr CODE12 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_gd0ccvdwwb4mk CODE13 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_7k7wh28n3nczy CODE14 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_67k2zjp09w101 CODE15 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_5fa6k6fm0k6wg CODE10 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_4624ju6bxsv57 CODE9 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_bstrdkkxqtj4f CODE8 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_39xqjjar239zq CODE7 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_6h0adp60faytk CODE6 YES NO INVISIBLE VALID 10000000 15364 10000000 SYS_AI_5u0bqdgcx52vh CODE16 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_0hzmhsraqkcgr CODE22 YES NO INVISIBLE VALID 10000000 15366 10000000 SYS_AI_4x716k4mdn040 CODE21 YES NO INVISIBLE VALID 10000000 15366 10000000 SYS_AI_6wsuwr7p6drsu CODE20 YES NO INVISIBLE VALID 10000000 15366 10000000 SYS_AI_b424tdjx82rwy CODE19 YES NO INVISIBLE VALID 10000000 15366 10000000 SYS_AI_3a2y07fqkzv8x CODE18 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_8dp0b3z0vxzyg CODE17 YES NO INVISIBLE VALID 10000000 15365 10000000 SYS_AI_d95hnqayd7t08 CODE23 YES NO VISIBLE VALID 10000000 15366 10000000 SYS_AI_fry4zrxqtpyzg CODE24 YES NO VISIBLE VALID 10000000 15366 10000000 SYS_AI_920asb69q1r0m CODE25 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_026pa8880hnm2 CODE31 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_96xhzrguz2qpy CODE32 YES NO VISIBLE VALID 10000000 15368 10000000 SYS_AI_3dq93cc7uxruu CODE29 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_5nbz41xny8fvc CODE28 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_fz4q9bhydu2qt CODE27 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_0kwczzg3k3pfw CODE26 YES NO VISIBLE VALID 10000000 15367 10000000 SYS_AI_4qd5tsab7fnwx CODE30 YES NO VISIBLE VALID 10000000 15367 10000000
We can see we indeed have the 3 statuses of Automatic Indexes captured:
Columns with a selectivity equal or worse to that of COL5 with 1300 distinct values are created as Invisible/Unusable indexes. Returning 10M/1300 rows or a cardinality of approx. 7,693 or more rows is just too expensive for such indexes on this table to be viable. This represents a selectivity of approx. 0.077%.
Note how the index statistics for these Invisible/Unusable indexes are not accurate. They all have an estimated LEAF_BLOCKS of 21702 and a CLUSTERING_FACTOR of 4272987. However, we can see from the other indexes which are physically created that these are not correct and are substantially off the mark with the actual LEAF_BLOCKS being around 15364 and the CLUSTERING_FACTOR actually much worse at around 10000000.
Again worthy of a future post to discuss how Automatic Indexing processing has to make (potentially inaccurate) guesstimates for these statistics in its analysis of index viability when such indexes don’t yet physically exist.
Columns with a selectivity equal or better to that of COL23 which has 3100 distinct values are created as Visible/Valid indexes. Returning 10M/3100 rows or a cardinality of approx. 3226 or less rows is cheap enough for such indexes on this table to be viable. This represents a selectivity of approx. 0.032%.
So in this specific example, only those columns between 1400 and 3000 distinct values meet the “borderline” criteria in which the Automatic Indexing process creates Invisible/Valid indexes. This represents a very very narrow selectivity range of only approx. 0.045% in which such Invisible/Valid indexes are created. Or for this specific example, only those columns that return approx. between 3,333 and 7,143 rows from the 10M row table.
Now the actual numbers and total range of selectivities for which Invisible/Valid Automatic Indexes are created of course depends on all sorts of factors, such as the size/cost of FTS of the table and not least the clustering of the associated data (which I’ve blogged about ad nauseam).
The point I want to make is that the range of viability for such Invisible/Valid indexes is relatively narrow and the occurrences of such indexes relatively rare in your databases. As such, the vast majority of Automatic Indexes are likely to be either Visible/Valid or Invisible/Unusable indexes.
It’s important to recognised this when you encounter such Invisible/Valid Automatic Indexes (outside of “REPORT ONLY” implementations), as it’s an indication that such an index is a borderline case that is currently NOT considered by the CBO (because of it being Invisible).
However, this Invisible/Valid Automatic Index status should really change to either of the other two more common statuses in the near future.
I’ll expand on this point in a future post…
Oracle 19c Automatic Indexing: The 3 Possible States Of Newly Created Automatic Indexes (“Don’t Sit Down”) August 24, 2021
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, CBO, Clustering Factor, Exadata, Invisible Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle Indexes, Oracle Statistics.2 comments
As 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 it as unexpected behaviour as this wasn’t documented and seemed an odd outcome, one which I had only expected to find when Automatic Indexing was set in “REPORT ONLY” mode.
After further research and discussions with folks within Oracle, Automatic Indexes created in this state is indeed entirely expected, albeit in relatively rare scenarios. So I thought I’ll discuss the 3 possible states in which an Automatic Index can be created and explore things further in future blog posts.
The follow demo illustrates the 3 different states in which Automatic Indexes can be created.
I start by creating a table with 3 columns of note:
- CODE1 which is highly selective and very likely to be used by the CBO if indexed
- CODE2 which is relatively selective BUT likely NOT quite enough so to be used by the CBO if indexed
- CODE3 which is very unselective and almost certainly won’t be used by the CBO if indexed
SQL> create table david_bowie (id number, code1 number, code2 number, code3 number, name varchar2(42)); Table created. SQL> insert into david_bowie select rownum, mod(rownum, 1000000)+1, mod(rownum, 5000)+1, mod(rownum, 100)+1, 'THE RISE AND FALL OF ZIGGY STARDUST' from dual connect by level >=10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'DAVID_BOWIE'); PL/SQL procedure successfully completed.
Note that in an Autonomous Database, these columns will all now have histograms (as previously discussed):
SQL> select column_name, num_distinct, density, histogram from dba_tab_columns where table_name='DAVID_BOWIE'; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM -------------------- ------------ ---------- --------------- ID 9705425 0 HYBRID CODE1 971092 .000001 HYBRID CODE2 4835 .000052 HYBRID CODE3 100 .00000005 FREQUENCY NAME 1 4.9460E-08 FREQUENCY
I’ll now run the following simple queries a number of times, using predicates on each of the 3 columns:
SQL> select * from david_bowie where code1=42;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 540 | 1076 (9) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 10 | 540 | 1076 (9) | 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE1"=42)
filter("CODE1"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
83297 consistent gets
83285 physical reads
0 redo size
783 bytes sent via SQL*Net to client
362 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select * from david_bowie where code2=42;
2000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2068 | 109K | 1083 (10) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 2068 | 109K | 1083 (10) | 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE2"=42)
filter("CODE2"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
83297 consistent gets
83285 physical reads
0 redo size
32433 bytes sent via SQL*Net to client
362 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
SQL> select * from david_bowie where code3=42;
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K | 5273K | 1090 (10) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 100K | 5273K | 1090 (10) | 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE3"=42)
filter("CODE3"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
83297 consistent gets
83285 physical reads
0 redo size
1984026 bytes sent via SQL*Net to client
571 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
Obviously with no indexes in place, they all currently use a FTS.
If we wait though until the next Automatic Indexing reporting period and look at the next Automatic Indexing report:
SQL> select dbms_auto_index.report_last_activity() from dual; SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 3 Indexes created (visible / invisible) : 2 (1 / 1) Space used (visible / invisible) : 276.82 MB (142.61 MB / 134.22 MB) Indexes dropped : 0 SQL statements verified : 2 SQL statements improved (improvement factor) : 1 (83301.1x) SQL plan baselines created : 0 Overall improvement factor : 2x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 -------------------------------------------------------------------------------
We notice Automatic Indexing stated there were 3 index candidates, but has created 2 new indexes, one VISIBLE and one INVISIBLE.
Further down the report:
INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: ------------------------------------------------------------------------------- ---------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | ---------------------------------------------------------------------------- | BOWIE | DAVID_BOWIE | SYS_AI_48d67aycauayj | CODE1 | B-TREE | NONE | | BOWIE | DAVID_BOWIE | SYS_AI_cpw2p477wk6us | CODE2 | B-TREE | NONE | ---------------------------------------------------------------------------- -------------------------------------------------------------------------------
We see that one index was created on the CODE1 column and the other on the CODE2 column (note: in the current 19.12.0.1.0 version of the Transaction Processing Autonomous Database, the * to denote invisible indexes above is no longer present).
No index is listed as being created on the very unselective CODE3 column.
If we continue down the report:
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : 6vp85adas9tq3
SQL Text : select * from david_bowie where code1=42
Improvement Factor : 83301.1x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 246874 1248
CPU Time (s): 139026 694
Buffer Gets: 749710 13
Optimizer Cost: 1076 13
Disk Reads: 749568 2
Direct Writes: 0 0
Rows Processed: 90 10
Executions: 9 1
PLANS SECTION
--------------------------------------------------------------------------------
-------------
- Original
-----------------------------
Plan Hash Value : 1390211489
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1076 | |
| 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 10 | 540 | 1076 | 00:00:01 |
-----------------------------------------------------------------------------------
Notes
-----
- dop = 1
- px_in_memory_imc = no
- px_in_memory = no
- With Auto Indexes
-----------------------------
Plan Hash Value : 3510800558
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 540 | 13 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID_BOWIE | 10 | 540 | 13 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_48d67aycauayj | 10 | | 3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE1"=42)
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
We see that the Visible Index was actually created on the CODE1 column, thanks to the perceived 83301.1x performance improvement.
If we look at the status of all indexes now on our table:
SQL> select i.index_name, c.column_name, i.auto, i.constraint_index, i.visibility, i.compression, i.status, i.num_rows, i.leaf_blocks, i.clustering_factor from user_indexes i, user_ind_columns c where i.index_name=c.index_name and i.table_name='DAVID_BOWIE'; INDEX_NAME COLUMN_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ---------------------- ----------- --- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_48d67aycauayj CODE1 YES NO VISIBLE ADVANCED LOW VALID 10000000 16891 10000000 SYS_AI_cpw2p477wk6us CODE2 YES NO INVISIBLE ADVANCED LOW VALID 10000000 15369 10000000 SYS_AI_c8bkc2z4bxrzp CODE3 YES NO INVISIBLE ADVANCED LOW UNUSABLE 10000000 20346 4173285
We see indexes with 3 different statuses:
- CODE1 index is VISIBLE/VALID
- CODE2 index is INVISIBLE/VALID
- CODE3 index is INVISIBLE/UNUSABLE
The logic appears to be as follows:
If an index will demonstrably improve performance sufficiently, then the index is created as a VISIBLE and VALID index and can be subsequently used by the CBO.
If an index is demonstrably awful and has very little chance of ever being used by the CBO, it’s left INVISIBLE and put in an UNUSABLE state. It therefore takes up no space and will eventually be dropped. It will likely never be required, so no loss then if it doesn’t physically exist.
Interestingly, if an index is somewhat “borderline”, currently not efficient enough to be used by the CBO, but close enough perhaps that maybe things might change in the future to warrant such as index, then it is physically created as VALID but is not readily available to the CBO and remains in an INVISIBLE state. This index won’t have to be rebuilt in the future if indeed things change subsequently to enough to warrant future index usage.
It should of be noted that little of this is clearly documented and that it’s subject to change without notice. One of the key points of Automatic Indexing is that we can off-hand all this to Oracle and let Oracle worry about things. That said, it might be useful to understand why you might end up with indexes in different statuses and the subsequent impact this might make.
If we re-run the first query based on the CODE1 predicate:
SQL> select * from david_bowie where code1=42;
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3510800558
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 540 | 14 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID_BOWIE | 10 | 540 | 14 (0) | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_48d67aycauayj | 10 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE1"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
1151 bytes sent via SQL*Net to client
362 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
The CBO will indeed use the newly created Automatic Index.
But if we re-run either of the other 2 queries based on the CODE2 and CODE3 predicates:
SQL> select * from david_bowie where code2=42;
2000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1390211489
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2068 | 109K | 1083 (10) | 00:00:01 |
| * 1 | TABLE ACCESS STORAGE FULL | DAVID_BOWIE | 2068 | 109K | 1083 (10) | 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE2"=42)
filter("CODE2"=42)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
83297 consistent gets
83285 physical reads
0 redo size
32433 bytes sent via SQL*Net to client
362 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
The CBO will not use an index as no VISIBLE/VALID indexes exist on these columns.
In future blog posts I’ll explore what is meant by “borderline” and what can subsequently happen to any such INVISIBLE/VALID Automatic Indexes…
Richard Foote's Oracle Blog 