Automatic Indexing 21c: Non-Equality Predicate Anomaly (“Strangers When We Meet”) July 14, 2022
Posted by Richard Foote in 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Exadata X8, Full Table Scans, Index Column Order, Invisible Indexes, Non-Equality Predicates, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle Indexes, Performance Tuning, Richard Foote Training, Richard's Blog.3 comments
I’m currently putting together some Exadata related training for a couple of customers and came across a rather strange anomaly with regard the status of Automatic Indexes, when created in part on unselective, non-equality predicates.
As discussed previously, Oracle Database 21c now allows the creation of Automatic Indexes based on non-equality predicates (previously, Automatic Indexes were only created on equality-based predicates).
But one appears to get rather odd resultant Automatic Indexes in the scenario where the non-equality predicate is not particularly selective but other predicates are highly selective.
To illustrate, I’ll create a basic table that has two columns (ID and CODE) that are both highly selective:
SQL> create table ziggy_new (id number, code number, name varchar2(42)); Table created. SQL> insert into ziggy_new select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level <= 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'ZIGGY_NEW'); PL/SQL procedure successfully completed.
So there are currently no indexes on this table.
I’ll next run the following SQL (and others similar) a number of times:
SQL> select * from ziggy_new where code=42 and id between 1 and 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3165184525
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6738 (2) | 00:00:01 |
| * 1 | TABLE ACCESS STORAGE FULL | ZIGGY_NEW | 1 | 23 | 6738 (2) | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42 AND "ID"<=100000 AND "ID">=1)
filter("CODE"=42 AND "ID"<=100000 AND "ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38605 consistent gets
38600 physical reads
0 redo size
725 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Without any indexes, the CBO currently has no choice but to use a Full Table Scan.
But only 1 row is returned. The first equality predicate on the CODE column is highly selective and on its own would only return 10 rows out of the 10M row table. The second, non-equality range-based predicate on the ID column is nowhere near as selective and offers limited additional filtering.
The CBO stops calculating index related costs after a non-equality predicate column (as subsequent column values could exist anywhere within the preceding range), and so the more effective index here is one based on (CODE, ID) with the non-equality predicate column second, or potentially just on the CODE column only, as the ID range offers minimal filtering benefits.
So what does Automatic Indexing make of things?
If we look at the subsequent Automatic Indexing report:
SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 3 Indexes created (visible / invisible) : 1 (0 / 1) Space used (visible / invisible) : 209.72 MB (0 B / 209.72 MB) Indexes dropped : 0 SQL statements verified : 44 SQL statements improved (improvement factor) : 12 (64.7x) SQL plan baselines created : 0 Overall improvement factor : 1.6x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: ------------------------------------------------------------------------------- ---------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | ---------------------------------------------------------------------------- | BOWIE | ZIGGY_NEW | SYS_AI_75j16xff1ag3j | CODE,ID | B-TREE | NONE | ----------------------------------------------------------------------------
So Automatic Indexing has indeed created an index based on CODE,ID (a common Automatic Indexing trait appears to be to create an index based on all available predicates).
BUT the index is created as an INVISIBLE Index and so can not generally be used by database sessions.
SQL> select index_name, auto, visibility, status, num_rows, leaf_blocks, clustering_factor
from user_indexes where table_name='ZIGGY_NEW';
INDEX_NAME AUT VISIBILIT STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ --- --------- -------- ---------- ----------- -----------------
SYS_AI_75j16xff1ag3j YES INVISIBLE VALID 10000000 25123 10000000
SQL> select index_name, column_name, column_position
from user_ind_columns where table_name='ZIGGY_NEW';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------ ---------------
SYS_AI_75j16xff1ag3j CODE 1
SYS_AI_75j16xff1ag3j ID 2
So re-running the previous SQL statements continues to use a Full Table Scan:
SQL> select * from ziggy_new where code=42 and id between 1 and 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3165184525
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6738 (2) | 00:00:01 |
| * 1 | TABLE ACCESS STORAGE FULL | ZIGGY_NEW | 1 | 23 | 6738 (2) | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=42 AND "ID"<=100000 AND "ID">=1)
filter("CODE"=42 AND "ID"<=100000 AND "ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38605 consistent gets
38600 physical reads
0 redo size
725 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Automatic Indexing appears to only create Invisible indexes when there is an inefficient non-equality predicate present. It won’t create the index as a Visible index, even though it would significantly benefit these SQL statements that caused its creation. And Automatic Indexing won’t create an index on just the highly selective CODE equality predicate, which would also be of much benefit to these SQL statements.
If we now run similar queries, but with much more selective non-equality predicates, such as:
SQL> select * from ziggy_new where code=1 and id between 1 and 10;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3165184525
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6738 (2) | 00:00:01 |
| * 1 | TABLE ACCESS STORAGE FULL | ZIGGY_NEW | 1 | 23 | 6738 (2) | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE"=1 AND "ID"<=10 AND "ID">=1)
filter("CODE"=1 AND "ID"<=10 AND "ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38604 consistent gets
38600 physical reads
0 redo size
503 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Again, with no (Visible) index present, the CBO currently has no choice but to use the Full Table Scan.
But during the next cycle, after Automatic Indexing kicks in again:
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 5
Indexes created (visible / invisible) : 1 (1 / 0)
Space used (visible / invisible) : 209.72 MB (209.72 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 89
SQL statements improved (improvement factor) : 31 (71.9x)
SQL plan baselines created : 0
Overall improvement factor : 1.7x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
----------------------------------------------------------------------------
| BOWIE | ZIGGY_NEW | SYS_AI_75j16xff1ag3j | CODE,ID | B-TREE | NONE |
----------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : d4znwcu4h52ca
SQL Text : select * from ziggy_new where code=42 and id between 1 and 10
Improvement Factor : 38604x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 3398605 68
CPU Time (s): 3166824 68
Buffer Gets: 463250 3
Optimizer Cost: 6738 4
Disk Reads: 463200 0
Direct Writes: 0 0
Rows Processed: 0 0
Executions: 12 1
PLANS SECTION
--------------------------------------------------------------------------------
-------------
- Original
-----------------------------
Plan Hash Value : 3165184525
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6738 | |
| 1 | TABLE ACCESS STORAGE FULL | ZIGGY_NEW | 1 | 23 | 6738 | 00:00:01 |
--------------------------------------------------------------------------------
- With Auto Indexes
-----------------------------
Plan Hash Value : 1514586396
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ZIGGY_NEW | 1 | 23 | 4 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_75j16xff1ag3j | 1 | | 3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("CODE"=42 AND "ID">=1 AND "ID"<=10)
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
But this time, the index on the CODE,ID columns is created as a Visible index.
INDEX_NAME AUT VISIBILIT STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------------ --- --------- -------- ---------- ----------- ----------------- SYS_AI_75j16xff1ag3j YES VISIBLE VALID 10000000 25123 10000000 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='ZIGGY_NEW'; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ ------------ --------------- SYS_AI_75j16xff1ag3j CODE 1 SYS_AI_75j16xff1ag3j ID 2
So this index can be generally used, both by the newer SQLs that generated the now Visible index:
SQL> select * from ziggy_new where code=42 and id between 1 and 10;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1514586396
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ZIGGY_NEW | 1 | 23 | 4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_75j16xff1ag3j | 1 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE"=42 AND "ID">=1 AND "ID"<=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
503 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
And also used by the SQLs with the unselective non-equality predicates, that Automatic Indexing would only create as Invisible indexes:
SQL> select * from ziggy_new where code=42 and id between 1 and 100000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1514586396
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ZIGGY_NEW | 1 | 23 | 4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_75j16xff1ag3j | 1 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE"=42 AND "ID">=1 AND "ID"<=100000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
729 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Automatic Indexing appears to currently not quite do the right thing with SQL statements that have unselective non-equality predicates, by creating such indexes as only Invisible Indexes, inclusive of the unselective columns.
Although an edge case, I would recommend looking through the list of created Automatic Indexes to see if any such Invisible/Valid indexes exists, as it can suggest there are current inefficient SQL statements that could benefit from such indexes being Visible.
Automatic Indexes: Automatically Rebuild Unusable Indexes Part IV (“Nothing Has Changed”) May 31, 2022
Posted by Richard Foote in 19c, 19c New Features, 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, Index Column Order, Index Internals, Local Indexes, Mixing Auto and Manual Indexes, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Oracle19c, Unusable Indexes.1 comment so far
In a previous post, I discussed how Automatic Indexing (AI) does not automatically rebuild a manually built index that is in an Unusable state (but will rebuild an Unusable automatically created index).
The demo I used was a simple one, based on manually created indexes referencing a non-partitioned table.
In this post, I’m going to use a demo based on manually created indexes referencing a partitioned table.
I’ll start by creating a rather basic range-based partitioned table, using the RELEASE_DATE column to partition the data by year:
SQL> CREATE TABLE big_bowie (id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION ALBUMS_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
PARTITION ALBUMS_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
PARTITION ALBUMS_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
PARTITION ALBUMS_2021 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> INSERT INTO big_bowie SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000;
10000000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE');
PL/SQL procedure successfully completed.
I’ll next manually create a couple indexes; a non-partitioned index based on just the ALBUM_ID column and a prefixed locally partitioned index, based on the columns RELEASE_DATE, TOTAL_SALES:
SQL> create index album_id_i on big_bowie(album_id); Index created. SQL> create index release_date_total_sales_i on big_bowie(release_date, total_sales) local; Index created.
If we now re-organise just partition ALBUMS_2017 (without using the ONLINE clause):
SQL> alter table big_bowie move partition albums_2017; Table altered.
This results in the non-partitioned index and the ALBUMS_2017 local index partition becoming Unusable:
SQL> select index_name, status from user_indexes where table_name='BIG_BOWIE';
INDEX_NAME STATUS
------------------------------ --------
ALBUM_ID_I UNUSABLE
RELEASE_DATE_TOTAL_SALES_I N/A
SQL> select index_name, partition_name, status from user_ind_partitions
where index_name='RELEASE_DATE_TOTAL_SALES_I';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ -------------------- --------
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2014 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2015 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2016 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2017 UNUSABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2018 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2019 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2020 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2021 USABLE
Let’s now run a number of queries a number of times. The first series is based on a predicate on just the ALBUM_ID column, such as:
SQL> select * from big_bowie where album_id=42;
2000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1510748290
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 52000 | 7959 (2) | 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 2000 | 52000 | 7959 (2) | 00:00:01 | 1 | 8 |
| * 2 | TABLE ACCESS FULL | BIG_BOWIE | 2000 | 52000 | 7959 (2) | 00:00:01 | 1 | 8 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("ALBUM_ID"=42)
- filter("ALBUM_ID"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
48593 consistent gets
42881 physical reads
0 redo size
44289 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed
We’ll also run a series of queries based on both the RELEASE_DATE column using dates from the unusable index partition and the TOTAL_SALES column, such as:
SQL> select * from big_bowie where release_date='01-JUN-2017' and total_sales=42;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3245457041
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 986 (2) | 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 26 | 986 (2) | 00:00:01 | 4 | 4 |
| * 2 | TABLE ACCESS FULL | BIG_BOWIE | 1 | 26 | 986 (2) | 00:00:01 | 4 | 4 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("TOTAL_SALES"=42 AND "RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
- filter("TOTAL_SALES"=42 AND "RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5573 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Without a valid/usable index, the CBO currently has no choice but to use a Full Table Scan on the first query, and a Full Partition Scan on the partition with the unusable local index.
So what does AI make of things? Does it rebuild the unusable manually created indexes so the associated indexes can be used to improve these queries?
If we wait until the next AI task completes and check out the indexes on the table:
SQL> select index_name, status, partitioned from user_indexes where table_name='BIG_BOWIE';
INDEX_NAME STATUS PAR
------------------------------ -------- ---
RELEASE_DATE_TOTAL_SALES_I N/A YES
ALBUM_ID_I UNUSABLE NO
SYS_AI_aw2825ffpus5s VALID NO
SYS_AI_2hf33fpvnqztw VALID NO
SQL> select index_name, partition_name, status from user_ind_partitions
where index_name='RELEASE_DATE_TOTAL_SALES_I';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ -------------------- --------
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2014 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2015 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2016 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2017 UNUSABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2018 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2019 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2020 USABLE
RELEASE_DATE_TOTAL_SALES_I ALBUMS_2021 USABLE
We notice that AI has created two new non-partitioned automatic indexes, while both the manually created indexes remain in the same unusable state. If we look at the columns associated with these new automatic indexes:
SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BIG_BOWIE'; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ -------------------- --------------- ALBUM_ID_I ALBUM_ID 1 RELEASE_DATE_TOTAL_SALES_I RELEASE_DATE 1 RELEASE_DATE_TOTAL_SALES_I TOTAL_SALES 2 SYS_AI_aw2825ffpus5s ALBUM_ID 1 SYS_AI_aw2825ffpus5s RELEASE_DATE 2 SYS_AI_2hf33fpvnqztw TOTAL_SALES 1 SYS_AI_2hf33fpvnqztw RELEASE_DATE 2
As we can see, AI has logically replaced both unusable indexes.
The manual index based on ALBUM_ID has been replaced with an inferior index based on the ALBUM_ID, RELEASE_DATE columns. Inferior in that the automatic index is both redundant (if only the manual index on ALBUM_ID were rebuilt) and in that it has the logically unnecessary RELEASE_DATE column to inflate the size of the index.
The manual index based on the RELEASE_DATE, TOTAL_SALES columns has been replaced with a redundant automatic index based on the reversed TOTAL_SALES, RELEASE_DATE columns.
Now, AI has indeed automatically addressed the current FTS performance issues associated with these queries by creating these indexes, but a better remedy would have been to rebuild the unusable manual indexes and hence negate the need for these redundant automatic indexes.
But currently (including with version 21.3), AI will NOT rebuild unusable manually created indexes, no matter the scenario, and will instead create additional automatic indexes if it’s viable for it to do so.
A reason why Oracle at times recommends dropping all current manually created secondary indexes before implementing AI (although of course this comes with a range of obvious issues and concerns).
If these manually created indexes didn’t exist, I’ll leave it as an exercise to the discernable reader on what automatic indexes would have been created…
As always, this restriction may change in future releases…
Automatic Indexes: Automatically Rebuild Unusable Indexes Part III (“Waiting For The Man”) May 17, 2022
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, Exadata, Full Table Scans, Manual Indexes, Mixing Auto and Manual Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Unusable Indexes.1 comment so far
I’ve previously discussed how Automatic Indexing (AI) will not only create missing indexes, but will also rebuild unusable indexes, be it a Global or Local index.
However, all my previous examples have been with Automatic Indexes. How does AI handle unusable indexes in which the indexes were manually created?
In my first demo, I’ll start by creating a basic non-partitioned table:
SQL> create table bowie_stuff (id number, album_id number, country_id number, release_date date, total_sales number); Table created. SQL> insert into bowie_stuff select rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800), ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BOWIE_STUFF'); PL/SQL procedure successfully completed.
We next manually create an index on the highly selective TOTAL_SALES column:
SQL> create index bowie_stuff_total_sales_i on bowie_stuff(total_sales); Index created.
Let’s now invalidate the index by re-organising the table without the online clause:
SQL> alter table bowie_stuff move; Table altered. SQL> select index_name, status from user_indexes where table_name='BOWIE_STUFF'; INDEX_NAME STATUS ------------------------------ -------- BOWIE_STUFF_TOTAL_SALES_I UNUSABLE
So the index is now in an UNUSABLE state.
To perk up the interest of AI, I’ll run a number of queries such as the following with a predicate condition on TOTAL_SALES:
select * from bowie_stuff where total_sales=42;
18 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 910563088
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 520 | 7427 (2) | 00:00:01 |
|* 1 | TABLE ACCESS FULL | BOWIE_STUFF | 20 | 520 | 7427 (2) | 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("TOTAL_SALES"=42)
filter("TOTAL_SALES"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
42746 consistent gets
42741 physical reads
0 redo size
1392 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
18 rows processed
Without a valid index, the CBO has no choice but to perform an expensive full table scan.
However, it doesn’t matter how long I wait or how many different queries I run similar to the above, AI currently will never rebuild an unusable index if the index was manually created.
AI will only rebuild unusable automatically created indexes.
I’ve discussed previously how automatic and manually created indexes often don’t gel well together and is one of the key reasons why Oracle recommends dropping all manually created secondary indexes if you wish to implement AI (using the DBMS_AUTO_INDEX.DROP_SECONDARY_INDEXES procedure, which I’ll discuss in a future post).
Things can get a little interesting with AI, if the underlining table is partitioned and you have manually created unusable indexes.
As I’ll discuss in my next post…
Automatic Indexes: Automatically Rebuild Unusable Indexes Part II (“I Wish You Would”) May 11, 2022
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, Local Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, Partitioned Indexes, Partitioning, Performance Tuning, Rebuild Unusable Indexes.1 comment so far
Within a few hours of publishing my last blog piece on how Automatic Indexing (AI) can automatically rebuild indexes that have been placed in an UNUSABLE state, I was asked by a couple of readers a similar question: “Does this also work if just a single partition of an partitioned index becomes unusable”?
My answer to them both is that I’ve provided them the basic framework in the demo to check out the answer to that question for themselves (Note: a fantastic aspect of working with the Oracle Database is that it’s available for free to play around with, including the Autonomous Database environments).
But based on the principle that for every time someone asks a question, there’s probably a 100 others who potentially might be wondering the same thing, thought I’ll quickly whip up a demo to answer this for all.
I’ll begin with the same table format and data as my previous blog:
SQL> CREATE TABLE big_ziggy(id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION ALBUMS_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
PARTITION ALBUMS_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
PARTITION ALBUMS_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> INSERT INTO big_ziggy SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000;
10000000 rows created.
SQL> COMMIT;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_ZIGGY');
PL/SQL procedure successfully completed.
But this time, I’ll run a number of queries similar to the following, that also has a predicate based on the partitioned key (RELEASE_DATE) of the table:
SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3599046327
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1051 (2) | 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 26 | 1051 (2) | 00:00:01 | 3 | 3 |
|* 2 | TABLE ACCESS FULL | BIG_ZIGGY | 1 | 26 | 1051 (2) | 00:00:01 | 3 | 3 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
filter(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5618 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
If we wait for the next AI task to kick in:
DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY() -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 11-MAY-2022 10:55:43 Activity end : 11-MAY-2022 10:56:27 Executions completed : 1 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 0 Indexes created (visible / invisible) : 1 (1 / 0) Space used (visible / invisible) : 192.94 MB (192.94 MB / 0 B) Indexes dropped : 0 SQL statements verified : 6 SQL statements improved (improvement factor) : 3 (6670.1x) SQL plan baselines created : 0 Overall improvement factor : 2x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- | Owner | Table | Index | Key | Type | Properties | --------------------------------------------------------------------------------------------- | BOWIE | BIG_ZIGGY | SYS_AI_6wv99zdbsy8ar | RELEASE_DATE,TOTAL_SALES | B-TREE | LOCAL | --------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------
We can see that AI has indeed automatically created a LOCAL, partitioned index (on columns RELEASE_DATE, TOTAL_SALES) in this scenario, as we have an equality predicate based on the partitioned key (RELEASE_DATE).
Currently, all is well with the index, with all partitions in a USABLE state:
SQL> SELECT index_name, partitioned, auto, visibility, status FROM user_indexes WHERE table_name = 'BIG_ZIGGY';
INDEX_NAME PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_6wv99zdbsy8ar YES YES VISIBLE N/A
SQL> select index_name, partition_name, status from user_ind_partitions where index_name='SYS_AI_6wv99zdbsy8ar';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ -------------------- --------
SYS_AI_6wv99zdbsy8ar ALBUMS_2015 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2016 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2017 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2018 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2019 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2020 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2021 USABLE
SYS_AI_6wv99zdbsy8ar ALBUMS_2022 USABLE
SQL> select index_name, column_name, column_position from user_ind_columns
where index_name='SYS_AI_6wv99zdbsy8ar';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ --------------- ---------------
SYS_AI_6wv99zdbsy8ar RELEASE_DATE 1
SYS_AI_6wv99zdbsy8ar TOTAL_SALES 2
But if we now do an offline reorg of a specific table partition:
SQL> alter table big_ziggy move partition albums_2017; Table altered. SQL> select index_name, partition_name, status from user_ind_partitions where index_name='SYS_AI_6wv99zdbsy8ar'; INDEX_NAME PARTITION_NAME STATUS ------------------------------ -------------------- -------- SYS_AI_6wv99zdbsy8ar ALBUMS_2015 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2016 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2017 UNUSABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2018 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2019 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2020 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2021 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2022 USABLE
We can see we’ve now made the associated Local Index partition UNUSABLE.
If we run the following query:
SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3599046327
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 986 (2) | 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 26 | 986 (2) | 00:00:01 | 3 | 3 |
|* 2 | TABLE ACCESS FULL | BIG_ZIGGY | 1 | 26 | 986 (2) | 00:00:01 | 3 | 3 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
filter(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
Statistics
----------------------------------------------------------
3 recursive calls
4 db block gets
5578 consistent gets
5571 physical reads
924 redo size
676 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
The CBO has no choice here but to do a full partition table scan.
If now wait again for the next AI task to strut its stuff:
SQL> select dbms_auto_index.report_last_activity() from dual; DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY() -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 11-MAY-2022 11:42:42 Activity end : 11-MAY-2022 11:43:13 Executions completed : 1 Executions interrupted : 0 Executions with fatal error : 0 ------------------------------------------------------------------------------- SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 0 Indexes created (visible / invisible) : 1 (1 / 0) Space used (visible / invisible) : 192.94 MB (192.94 MB / 0 B) Indexes dropped : 0 SQL statements verified : 4 SQL statements improved (improvement factor) : 1 (5573x) SQL plan baselines created : 0 Overall improvement factor : 1.1x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 ------------------------------------------------------------------------------- INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- | Owner | Table | Index | Key | Type | Properties | --------------------------------------------------------------------------------------------- | BOWIE | BIG_ZIGGY | SYS_AI_6wv99zdbsy8ar | RELEASE_DATE,TOTAL_SALES | B-TREE | LOCAL | --------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------- SQL> select index_name, partition_name, status from user_ind_partitions where index_name='SYS_AI_6wv99zdbsy8ar'; INDEX_NAME PARTITION_NAME STATUS ------------------------------ -------------------- -------- SYS_AI_6wv99zdbsy8ar ALBUMS_2015 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2016 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2017 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2018 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2019 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2020 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2021 USABLE SYS_AI_6wv99zdbsy8ar ALBUMS_2022 USABLE
The index partition is now automatically in a USABLE state again.
If we look at the index object data:
SQL> select object_name, subobject_name, to_char(created, 'dd-Mon-yy hh24:mi:ss') created, to_char(last_ddl_time, 'dd-Mon-yy hh24:mi:ss’) last_ddl_time from dba_objects where object_name='SYS_AI_6wv99zdbsy8ar'; OBJECT_NAME SUBOBJECT_NAME CREATED LAST_DDL_TIME ------------------------------ -------------------- --------------------------- --------------------------- SYS_AI_6wv99zdbsy8ar ALBUMS_2015 11-May-22 10:41:33 11-May-22 10:56:14 SYS_AI_6wv99zdbsy8ar ALBUMS_2016 11-May-22 10:41:33 11-May-22 10:56:15 SYS_AI_6wv99zdbsy8ar ALBUMS_2017 11-May-22 10:41:33 11-May-22 11:42:42 SYS_AI_6wv99zdbsy8ar ALBUMS_2018 11-May-22 10:41:33 11-May-22 10:56:18 SYS_AI_6wv99zdbsy8ar ALBUMS_2019 11-May-22 10:41:33 11-May-22 10:56:19 SYS_AI_6wv99zdbsy8ar ALBUMS_2020 11-May-22 10:41:33 11-May-22 10:56:20 SYS_AI_6wv99zdbsy8ar ALBUMS_2021 11-May-22 10:41:33 11-May-22 10:56:22 SYS_AI_6wv99zdbsy8ar ALBUMS_2022 11-May-22 10:41:33 11-May-22 10:56:22 SYS_AI_6wv99zdbsy8ar 11-May-22 10:41:33 11-May-22 11:43:13
We can see that just the impacted index partition has been rebuilt.
The CBO can now successfully use the index to avoid the full partition table scan:
SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3640710173
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0) | 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 26 | 4 (0) | 00:00:01 | 3 | 3 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_ZIGGY | 1 | 26 | 4 (0) | 00:00:01 | 3 | 3 |
|* 3 | INDEX RANGE SCAN | SYS_AI_6wv99zdbsy8ar | 1 | | 3 (0) | 00:00:01 | 3 | 3 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TOTAL_SALES"=123456)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
I’ll leave it to the discernible reader to determine if this also works in the scenario where the partitioned index were to be global… 🙂
Automatic Indexes: Automatically Rebuild Unusable Indexes Part I (“Andy Warhol”) May 10, 2022
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Oracle, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Rebuild Unusable Indexes.2 comments
Obviously, the main feature of Automatic Indexing (AI) is for Oracle to automatically create indexes, that have been proven to improve performance, in a relatively safe and timely manner.
However, another nice and useful capability is for AI to automatically rebuild indexes that are placed in an “Unusable” state.
The documentation states that:
“Automatic indexing provides the following functionality:
Rebuilds the indexes that are marked unusable due to table partitioning maintenance operations, such as ALTER TABLE MOVE.”
Now, when AI was initially released, I was unable to get this rebuild capability to work as advertised. I don’t know whether this was because the capability had not yet been successfully implemented or because of some failings in my testing.
However, with both the current versions of Oracle Database 19c (19.15.0.1.0 as now implemented in Autonomous Databases) and Oracle Database 21c, the following demo now works successfully.
Let’s begin by creating a simple partitioned table:
SQL> CREATE TABLE big_bowie(id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
PARTITION ALBUMS_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
PARTITION ALBUMS_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
PARTITION ALBUMS_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> INSERT INTO big_bowie SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
ceil(dbms_random.value(1,500000)) FROM dual CONNECT BY LEVEL <= 10000000;
10000000 rows created.
SQL> COMMIT;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE');
PL/SQL procedure successfully completed.
We next run a number of SQL statements such as the following:
SQL> SELECT * FROM big_bowie WHERE total_sales = 123456;
19 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1510748290
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop|
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 520 | 7958 (2) | 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 20 | 520 | 7958 (2) | 00:00:01 | 1 | 8 |
| * 2 | TABLE ACCESS FULL | BIG_BOWIE | 20 | 520 | 7958 (2) | 00:00:01 | 1 | 8 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("TOTAL_SALES"=123456)
filter("TOTAL_SALES"=123456)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
49573 consistent gets
42778 physical reads
0 redo size
1423 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19 rows processed
If we wait for the AI task to kick in, we notice is has successfully created an associated automatic index:
SQL> SELECT index_name, partitioned, auto, visibility, status FROM user_indexes WHERE table_name = 'BIG_BOWIE';
INDEX_NAME PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_17cd4101fvrk1 NO YES VISIBLE VALID
SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BIG_BOWIE';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ --------------- ---------------
SYS_AI_17cd4101fvrk1 TOTAL_SALES 1
As discussed previously, AI can now create a non-partitioned, Global index if deemed more efficient than a corresponding Local index.
Note that the newly created automatic index is currently VALID.
However, if we re-organise a partition within the table without using the Online clause:
SQL> alter table big_bowie move partition albums_2017; Table altered. SQL> select index_name, partitioned, auto, visibility, status from user_indexes where table_name = 'BIG_BOWIE'; INDEX_NAME PAR AUT VISIBILIT STATUS ------------------------------ --- --- --------- -------- SYS_AI_17cd4101fvrk1 NO YES VISIBLE UNUSABLE
The index as a result goes into an UNUSABLE state.
Running similar queries from this point will result in a FTS again:
SQL> select * from big_bowie where total_sales=42;
22 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1510748290
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 520 | 7937 (2) | 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 20 | 520 | 7937 (2) | 00:00:01 | 1 | 8 |
|* 2 | TABLE ACCESS FULL | BIG_BOWIE | 20 | 520 | 7937 (2) | 00:00:01 | 1 | 8 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("TOTAL_SALES"=123456)
filter("TOTAL_SALES"=123456)
Statistics
----------------------------------------------------------
126 recursive calls
0 db block gets
48962 consistent gets
42799 physical reads
0 redo size
1497 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
17 sorts (memory)
0 sorts (disk)
22 rows processed
If we now wait until the next AI task period and check out the index:
SQL> SELECT index_name, partitioned, auto, visibility, status FROM user_indexes WHERE table_name = 'BIG_BOWIE'; INDEX_NAME PAR AUT VISIBILIT STATUS ------------------------------ --- --- --------- -------- SYS_AI_17cd4101fvrk1 NO YES VISIBLE VALID
We notice the index is now back in a VALID state again.
Checking out the date attributes of the index confirms the index has indeed been rebuilt:
SQL> select object_name, to_char(created, 'dd-Mon-yy hh24:mi:ss') created, to_char(last_ddl_time, 'dd-Mon-yyhh24:mi:ss’) last_ddl_time from dba_objects where object_name='SYS_AI_17cd4101fvrk1'; OBJECT_NAME CREATED LAST_DDL_TIME ------------------------------ --------------------------- --------------------------- SYS_AI_17cd4101fvrk1 18-Apr-22 11:59:36 18-Apr-22 18:37:42
Being in a VALID state again, the CBO can now use the automatic index:
SQL> select * from big_bowie where total_sales=42;
22 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 920768077
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 520 | 23 (0) | 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | BIG_BOWIE | 20 | 520 | 23 (0) | 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | SYS_AI_17cd4101fvrk1 | 20 | | 3 (0) | 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TOTAL_SALES"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
48711 consistent gets
42799 physical reads
0 redo size
1497 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22 rows processed
Note: This scenario works the same if the table is Non-Partitioned.
In my next post, I’ll discuss a scenario where the automatic rebuild of an Unusable index will currently NOT work…
Automatic Indexes: AUTO_INDEX_TABLE Configuration (“Without You”) May 3, 2022
Posted by Richard Foote in 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, AUTO_INDEX_TABLE, DBMS_AUTO_INDEX.CONFIGURE, Exadata, Oracle, Oracle 21c, Oracle Cloud, Oracle General, Oracle Indexes.2 comments
One of the more common questions I get regarding Automatic Indexing (AI) are areas of concern around having large and expensive automatic index build operations suddenly occurring in one’s database and the impact this may have on overall performance.
Additionally, I’ve had questions around scenarios where very large automatic indexes are suddenly being built, but then get canceled because they couldn’t complete in the default (3600 seconds, 1 hour) allocated time, only for them to be attempted to be built again and for this cycle to be forever ongoing.
And this is fair enough. You may not necessarily want to have indexes built on specific tables, perhaps because they’re massive and you want to control when and how indexes on such tables are built, perhaps because you’re satisfied that such tables are already indexed satisfactorily, etc. etc.
Note: the impact on overall database performance of the AI task creating large indexes is reduced, by Oracle only allowing one index to be created serially at any given time.
However, to help address these concerns, Oracle has now (from Oracle Database 21c) introduced a new configuration option within the DBMS_AUTO_INDEX.CONFIGURE procedure, AUTO_INDEX_TABLE. This now allows us to explicitly state which tables we may wish to either include or exclude from the AI process. Previously, we only had the ability to state which schemas we wanted to in/exclude from the AI process.
To add the BOWIE.SALES table to an exclusion list:
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TABLE', ‘BOWIE.SALES’, FALSE);
PL/SQL procedure successfully completed.
To add the BOWIE.PRODUCTS table to an inclusion list:
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TABLE', ‘BOWIE.PRODUCTS', TRUE);
PL/SQL procedure successfully completed.
To view current AI settings:
SQL> select parameter_name, parameter_value from dba_auto_index_config;
PARAMETER_NAME PARAMETER_VALUE
----------------------------------- -----------------------------------------------------------------
AUTO_INDEX_COMPRESSION ON
AUTO_INDEX_DEFAULT_TABLESPACE USERDATA2
AUTO_INDEX_MODE IMPLEMENT
AUTO_INDEX_REPORT_RETENTION 100
AUTO_INDEX_RETENTION_FOR_AUTO 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA schema IN (BOWIE)
AUTO_INDEX_SPACE_BUDGET 100
AUTO_INDEX_TABLE table IN ("BOWIE"."PRODUCTS") AND table NOT IN ("BOWIE"."SALES")
To remove all tables from both inclusion/exclusion table lists:
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TABLE', NULL);
PL/SQL procedure successfully completed.
This means you can now more safely deploy AI, by determining explicitly which tables you wish to in/exclude.
Note if you wish to include large tables that can potentially take longer to build than the default 3600 seconds allowed for the AI task to complete, you can change the MAX_RUN_TIME of the AI task as follows (e.g. increase the max run time to 18000 seconds, 5 hours):
SQL> select task_id, task_name, enabled, interval, max_run_time, enabled from dba_autotask_settings
where task_name = 'Auto Index Task';
TASK_ID TASK_NAME ENABL INTERVAL MAX_RUN_TIME ENABL
---------- -------------------- ----- ---------- ------------ -----
3 Auto Index Task TRUE 900 3600 TRUE
SQL> exec dbms_auto_task_admin.modify_autotask_setting('Auto Index Task', 'MAX RUN TIME', 18000);
PL/SQL procedure successfully completed.
SQL> select task_id, task_name, enabled, interval, max_run_time, enabled from dba_autotask_settings
where task_name = 'Auto Index Task';
TASK_ID TASK_NAME ENABL INTERVAL MAX_RUN_TIME ENABL
---------- -------------------- ----- ---------- ------------ -----
3 Auto Index Task TRUE 900 18000 TRUE
Automatic Indexes: Scenarios Where Automatic Indexes NOT Created Part III (“Loaded”) April 28, 2022
Posted by Richard Foote in 19c, Advanced Index Compression, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Clustering Factor, Data Clustering, Exadata, Index Access Path, Index Column Order, Index Compression, Oracle, Oracle 21c, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, Overloading.add a comment
In my previous two posts, I’ve discussed scenarios where Automatic Indexing (AI) does not currently created automatic indexes and you may need to manually create the necessary indexes.
In this post, I’ll discuss a third scenario where AI will create an index, but you may want to manually create an even better one…
I’ll start by creating a relatively “large” table, with 20+ columns:
SQL> create table bowie_overload (id number, code1 number, code2 number, stuff1 varchar2(42), stuff2 varchar2(42), stuff3 varchar2(42), stuff4 varchar2(42), stuff5 varchar2(42), stuff6 varchar2(42), stuff7 varchar2(42), stuff8 varchar2(42), stuff9 varchar2(42), stuff10 varchar2(42), stuff11 varchar2(42), stuff12 varchar2(42), stuff13 varchar2(42), stuff14 varchar2(42), stuff15 varchar2(42), stuff16 varchar2(42), stuff17 varchar2(42), stuff18 varchar2(42), stuff19 varchar2(42), stuff20 varchar2(42), name varchar2(42)); Table created. SQL> insert into bowie_overload select rownum, mod(rownum, 1000)+1, '42', 'David Bowie', 'Major Tom', 'Ziggy Stardust', 'Aladdin Sane', 'Thin White Duke', 'David Bowie', 'Major Tom', 'Ziggy Stardust', 'Aladdin Sane', 'Thin White Duke','David Bowie', 'Major Tom', 'Ziggy Stardust', 'Aladdin Sane', 'Thin White Duke','David Bowie', 'Major Tom', 'Ziggy Stardust', 'Aladdin Sane', 'Thin White Duke', 'The Spiders From Mars' from dual connect by level <= 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_OVERLOAD'); PL/SQL procedure successfully completed.
The main columns to note here are CODE1 which contains 1000 distinct values (and so is kinda selective on a 10M row table, but not spectacularly so, especially with a poor clustering factor) and CODE2 which always contains the same value of “42” (and so will compress wonderfully for maximum effect).
I’ll next run the following query a number of times:
SQL> select code1, code2 from bowie_overload where code1=42;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1883860831
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 70000 | 74817 (1) | 00:00:03 |
| * 1 | TABLE ACCESS STORAGE FULL | BOWIE_OVERLOAD | 10000 | 70000 | 74817 (1) | 00:00:03 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("CODE1"=24)
filter("CODE1"=24)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
869893 consistent gets
434670 physical reads
0 redo size
183890 bytes sent via SQL*Net to client
7378 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
Without an index, the CBO currently has no choice but to perform a FTS. An index on the CODE1 column would provide the necessary filtering to fetch and return the required rows.
BUT, if this query was important enough, we could improve things further by “Overloading” this index with the CODE2 column, so we could use the index exclusively to get all the necessary data, without having to access the table at all. Considering an index on just the CODE1 column would need to fetch a reasonable number of rows (10000) and would need to visit a substantial number of different table blocks due to its poor clustering, overloading the index in this scenario would substantially reduce the necessary workloads of this query.
So what does AI do in this scenario, is overloading an index considered?
If we look at the AI report:
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 28-APR-2022 12:15:45
Activity end : 28-APR-2022 12:16:33
Executions completed : 1
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 1
Indexes created (visible / invisible) : 1 (1 / 0)
Space used (visible / invisible) : 134.22 MB (134.22 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 2
SQL statements improved (improvement factor) : 2 (47.1x)
SQL plan baselines created : 0
Overall improvement factor : 47.1x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
-------------------------------------------------------------------------------
| BOWIE | BOWIE_OVERLOAD | SYS_AI_aat8t6ad0ux0h | CODE1 | B-TREE | NONE |
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : bh5cuyv8ga0bt
SQL Text : select code1, code2 from bowie_overload where code1=42
Improvement Factor : 46.9x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 42619069 241844
CPU Time (s): 25387841 217676
Buffer Gets: 12148771 18499
Optimizer Cost: 74817 10021
Disk Reads: 6085380 9957
Direct Writes: 0 0
Rows Processed: 140000 10000
Executions: 14 1
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value : 1883860831
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 74817 | |
| 1 | TABLE ACCESS FULL | BOWIE_OVERLOAD | 10000 | 70000 | 74817 | 00:00:03 |
--------------------------------------------------------------------------------
- With Auto Indexes
-----------------------------
Plan Hash Value : 2541132923
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9281 | 64967 | 10021 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_OVERLOAD | 9281 | 64967 | 10021 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_aat8t6ad0ux0h | 10000 | | 18 | 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 an automatic index on just the CODE1 column was created.
SQL> select index_name, auto, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE_OVERLOAD'; INDEX_NAME AUT VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_aat8t6ad0ux0h YES VISIBLE ADVANCED LOW VALID 10000000 15363 10000000 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BOWIE_OVERLOAD' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------- --------------- --------------- SYS_AI_aat8t6ad0ux0h CODE1 1
If we now re-run the query (noting in Oracle21c after you invalidate the current cursor):
SQL> select code1, code2 from bowie_overload where code1=42;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2541132923
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 70000 | 10021 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_OVERLOAD | 10000 | 70000 | 10021 (1)| 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_aat8t6ad0ux0h | 10000 | | 18 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CODE1"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10021 consistent gets
0 physical reads
0 redo size
50890 bytes sent via SQL*Net to client
63 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
The query now uses the newly created automatic index.
BUT, at 10021 consistent gets, it’s still doing a substantial amount to work here.
If we manually create another index that overloads the only other column (CODE2) required in this query:
SQL> create index bowie_overload_code1_code2_i on bowie_overload(code1,code2) compress advanced low; Index created.
I’m using COMPRESS ADVANCED LOW as used by the automatic index, noting that CODE2 only contains the value “42” for all rows, making it particularly perfect for compression and a “best case” scenario when it comes to the minimal overheads potentially associated with overloading this index (I’m trying yo give AI every chance here):
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='BOWIE_OVERLOAD'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------------ --- --- --------- ------------- -------- ---------- ----------- ----------------- SYS_AI_aat8t6ad0ux0h YES NO VISIBLE ADVANCED LOW VALID 10000000 15363 10000000 BOWIE_OVERLOAD_CODE1_CODE2_I NO NO VISIBLE ADVANCED LOW VALID 10000000 15363 10000000 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BOWIE_OVERLOAD' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ --------------- --------------- BOWIE_OVERLOAD_CODE1_CODE2_I CODE1 1 BOWIE_OVERLOAD_CODE1_CODE2_I CODE2 2 SYS_AI_aat8t6ad0ux0h CODE1 1
In fact, my manually created index is effectively the same size as the automatic index, with the same number (15363) of leaf blocks.
So I’m giving AI the best possible scenario in which it could potentially create an overloaded index.
But I’ve never been able to get AI to create overloaded indexes. Only columns in filtering predicates are considered for inclusion in automatic indexes.
If I now re-run my query again:
SQL> select code1, code2 from bowie_overload where code1=42;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1161047960
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 70000 | 18 (0)| 00:00:01 |
| * 1 | INDEX RANGE SCAN | BOWIE_OVERLOAD_CODE1_CODE2_I | 10000 | 70000 | 18 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("CODE1"=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
50890 bytes sent via SQL*Net to client
63 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
We notice the CBO now uses the manually created index without any table access path, as it can just use the index to access the necessary data.
The number of consistent gets as a result has reduced significantly, down to just 21, a fraction of the previous 10021 when the automatic index was used.
So the scenario an of overloaded index that could significantly reduce database resources, which is currently not supported by AI, is another example of where may want to manually create a necessary index.
As always, this may change in the future releases…
Automatic Indexes: Scenarios Where Automatic Indexes NOT Created Part II (“Ragazzo Solo, Ragazza Sola” April 27, 2022
Posted by Richard Foote in 19c, 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Constraints, Exadata, Foreign Keys, Full Table Scans, Index Internals, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, Performance Tuning.1 comment so far
In my last post, I discussed how Automatic Indexing doesn’t create an automatic index in the scenario where the minimum or maximum of a column is required.
Another scenario when an automatic index is not created is when we hit issues associated with a missing index on a Foreign Key (FK) constraint.
As I’ve discussed many times previously, if you delete a parent record without an index on the dependant FK constraints, you hit a number of issues including having to perform a (potentially expensive and problematic) Full Table Scan (FTS) on the child tables and the associated locking problems.
To illustrate, I’ll first create a small parent table:
SQL> create table daddy (id number constraint daddy_pk primary key , name varchar2(42)); Table created. SQL> insert into daddy select rownum, 'David Bowie '|| rownum from dual connect by level <= 10000; 10000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'DADDY'); PL/SQL procedure successfully completed.
And then a somewhat larger child table, with no index on the associated foreign key constraint:
SQL> create table kiddy (id number constraint kiddy_pk primary key , code1 number constraint daddy_fk references daddy(id), code2 number, code3 number, name varchar2(42)); Table created. SQL> insert into kiddy select rownum, mod(rownum,1000)+1000 , mod(rownum, 10000)+1, mod(rownum, 100000)+1, 'Ziggy Stardust '|| rownum from dual connect by level <= 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'KIDDY'); PL/SQL procedure successfully completed.
If we delete a number of parent rows, for example:
SQL> delete from daddy where id = 101;
1 row deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 1477800718
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 4 | 1 (0) | 00:00:01 |
| 1 | DELETE | DADDY | | | | |
|* 2 | INDEX UNIQUE SCAN | DADDY_PK | 1 | 4 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=101)
Statistics
----------------------------------------------------------
18 recursive calls
13 db block gets
117462 consistent gets
22292 physical reads
4645500 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We notice that even though we only delete one row from a relatively small table, we perform a large number of consistent gets (117462) due to the necessary FTS on the child table, as Oracle is forced to check the table for any possible FK violations. Without an index on the child CODE1 column, Oracle has no choice but to perform the relatively expensive FTS.
Additionally, if we have an existing transaction of a child table (in Session 1):
SQL> insert into kiddy values (10000001,1042,1042,1042,'Iggy Pop'); 1 row created.
And then in another session attempt to delete a parent row (in Session 2):
SQL> delete from daddy where id = 112;
The delete hangs in a locked state due to the child transaction in Session 1. This can lead to further locking issues in other sessions (Session 3):
insert into kiddy values (10000002,1042,1042,1042,'Iggy Pop');
The FTS on the child table and these associated locks can all be avoided by having an index on the FK constraint, as the index can then be used to effectively police the constraint during such delete operations.
What does AI do in this scenario?
Currently, nothing.
I’ve been unable to ever get AI to create a usable automatic index in this scenario. In Oracle Database 19c, I’ve not been able to get an AI created at all. In Oracle Database 21c, the best I’ve seen has been a Unusable/Invisible AI:
SQL> select index_name, index_type, auto, constraint_index, visibility, status, num_rows from user_indexes where table_n ame='KIDDY'; INDEX_NAME INDEX_TYPE AUT CON VISIBILIT STATUS NUM_ROWS ------------------------------ --------------------------- --- --- --------- -------- ---------- KIDDY_PK NORMAL NO YES VISIBLE VALID 10000004 SYS_AI_31thttf8v6r35 NORMAL YES NO INVISIBLE UNUSABLE 10000004 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='KIDDY'; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ --------------- --------------- KIDDY_PK ID 1 SYS_AI_31thttf8v6r35 CODE1 1
So you may need to manually create such an index on the FK constraint to improve performance and eliminate these locking issues:
SQL> create index kiddy_code1_i on kiddy(code1);
Index created.
SQL> delete from daddy where id = 142;
1 row deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 1477800718
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 4 | 1 (0) | 00:00:01 |
| 1 | DELETE | DADDY | | | | |
|* 2 | INDEX UNIQUE SCAN | DADDY_PK | 1 | 4 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=142)
Statistics
----------------------------------------------------------
1 recursive calls
8 db block gets
2 consistent gets
2 physical reads
132 redo size
204 bytes sent via SQL*Net to client
41 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Consistent gets have dropped off massively (down to just 8) as Oracle can now use the index to avoid the FTS search on the child table. The associated locking issues are eliminated as well.
Note: As always, this AI behaviour can always change in the future…
Automatic Indexes: Scenarios Where Automatic Indexes NOT Created Part I (“Always Crashing In The Same Car”) April 26, 2022
Posted by Richard Foote in 19c, 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, MAX, MIN, Oracle, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Performance Tuning.1 comment so far
As I’ve discussed previously, Oracle has increased the number of scenarios in which it will now create automatic indexes, such as with non-equality predicates and JSON expressions.
However, as of Oracle Database 21c, there are still a number of scenarios where an automatic index will NOT be created, even though an index might prove beneficial.
One such scenario is when the Min/Max of a column is required.
As I’ve discussed a number of times previously, Oracle can very efficiently use an index to determine either the Min or Max value of a column, by (hopefully) just visiting the first or last leaf block in an index. The INDEX FULL SCAN (MIN/MAX) execution plan path can be used explicitly for this purpose.
If I create a simple table as follows:
SQL> create table bowie_min (id number constraint bowie_min_pk primary key, code number, name varchar2(42)); Table created. SQL> insert into bowie_min select rownum, mod(rownum, 1000000)+1, 'David Bowie' from dual connect by level <= 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_MIN'); PL/SQL procedure successfully completed.
And then run the following queries a number of times that return the Min and Max of the CODE column:
SQL> select min(code) from bowie_min;
Execution Plan
----------------------------------------------------------
Plan hash value: 1068446691
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 6706 (2) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS STORAGE FULL | BOWIE_MIN | 10M | 47M | 6706 (2) | 00:00:01 |
----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
39430 consistent gets
39421 physical reads
0 redo size
569 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select max(code) from bowie_min;
Execution Plan
----------------------------------------------------------
Plan hash value: 1068446691
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 6706 (2) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS STORAGE FULL | BOWIE_MIN | 10M | 47M | 6706 (2) | 00:00:01 |
----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
39430 consistent gets
39421 physical reads
0 redo size
569 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Currently, the CBO has no choice but to use a Full Table Scan (FTS) as there is currently no index on the CODE column.
So what does Automatic Indexing (AI) make of things?
Nothing.
Currently, AI will not create an index in this scenario, no matter how many times I execute these queries.
If we look at the indexes on the table after a significant period of time after running these queries:
SQL> select index_name, auto from user_indexes where table_name='BOWIE_MIN'; INDEX_NAME AUT ------------ --- BOWIE_MIN_PK NO
No Automatic Indexes. To improve the performance of these queries, we currently have to manually create the associated index:
SQL> create index bowie_min_code_i on bowie_min(code); Index created.
If we now re-run these queries and look at the execution plan:
SQL> select min(code) from bowie_min;
Execution Plan
----------------------------------------------------------
Plan hash value: 252811132
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FULL SCAN (MIN/MAX) | BOWIE_MIN_CODE_I | 1 | 5 | 3 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
569 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
We can see that the CBO is now indeed using the index to return the Min/Max values with a vastly reduced number of consistent gets (down to just 3 from the previous 38538).
However, a key point here is that Automatic Indexes only works on an Exadata platform and Exadata has various smarts that potentially makes accessing data via a “FTS” in this manner much more efficient than in non-Exadata environments.
Oracle may well take the position that getting Min/Max data on a Exadata is potentially efficient enough and doesn’t on its own warrant the creation of an index.
More on this in future posts…
Automatic Indexing: Deferred Invalidations (“The Post War Dream”) April 19, 2022
Posted by Richard Foote in 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Deferred Invalidation, Exadata, Function Based Indexes, Index Access Path, Index Internals, JSON, Oracle, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle Indexes, Richard's Blog.1 comment so far
In my previous post on how JSON expressions can now be automatically indexed, I mentioned there was an outstanding issue with the associated CBO execution plan, immediately post the creation of the automatic index:
SQL> select * from bowie_json where json_value(bowie_order, '$.PONumber')='42';
Execution Plan
----------------------------------------------------------
Plan hash value: 832017402
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 12M | 1524 (1) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_JSON | 20000 | 12M | 1524 (1) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_ayvj257jd93cv | 8000 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(JSON_VALUE("BOWIE_ORDER" /*+ LOB_BY_VALUE */ FORMAT OSON , '$.PONumber' RETURNING
VARCHAR2(4000) ERROR ON ERROR NULL ON EMPTY)='42')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
234168 consistent gets
200279 physical reads
0 redo size
1595 bytes sent via SQL*Net to client
526 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
If we look at the number of recursive calls, we notice that it remains at 0. If we look at both the number of consistent gets (234168) and physical reads (200279), they both remain very high and identical to that of the previous Full Table Scan plan.
Basically, although autotrace suggests the newly created automatic index is being used, in fact the previous Full Table Scan plan is still being invoked. (Note: this of course is one of the dangers of the autotrace plan, in that it might not display the actual plan being invoked).
So what’s going on here?
The Oracle Database 21c New Features Guide makes the following point: “an enhancement has been introduced to reduce the overhead of cursor invalidations when a new automatic index is created”.
Oracle 12.2 introduced a new feature in which one can now defer the invalidation of dependent SQL cursors when an index is created or modified. I’ve of course discussed this previously in this 12.2 Index Deferred Invalidation post.
When an automatic index is created in 21c, the current SQL cursors are NOT invalidated (to reduce the overhead of having to potentially reparse of large number of current SQL cursors). However, this means that currently inefficient SQL statements will keep their existing sub-optimal execution plans post the creation of newly created automatic indexes, until the existing SQL cursors aged out.
At which point, the new CBO plan using the automatic index will actually be invoked:
SQL> select * from bowie_json where json_value(bowie_order, '$.PONumber')='42';
Execution Plan
----------------------------------------------------------
Plan hash value: 832017402
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 671 | 4 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_JSON | 1 | 671 | 4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_ayvj257jd93cv | 1 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(JSON_VALUE("BOWIE_ORDER" /*+ LOB_BY_VALUE */ FORMAT OSON , '$.PONumber' RETURNING
VARCHAR2(4000) ERROR ON ERROR NULL ON EMPTY)='42')
Statistics
----------------------------------------------------------
30 recursive calls
0 db block gets
46 consistent gets
11 physical reads
0 redo size
1595 bytes sent via SQL*Net to client
526 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
So just be aware in Oracle Database 21c that your beautifully created automatic indexes may not actually get used as desired for a period of time…
Automatic Indexing: JSON Expressions Part I (Making Plans For Nigel) April 13, 2022
Posted by Richard Foote in Automatic Indexing, Autonomous Database, CBO, Exadata, Function Based Indexes, Index statistics, JSON, Oracle, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Statistics, Virtual Columns.1 comment so far
When Automatic Indexing was first released, one of the restrictions was that automatic indexes on JSON expressions were NOT supported.
However, the Oracle Database 21c doco mentions:
“Automatic indexes can be single or multi-column. They are considered for the following: Selected expressions (for example, JSON expressions)“.
So on my (admittedly dodgy) “Exadata” VM, I thought I’ll check out how AI now indeed deals with JSON expressions.
I start by creating a simple little table that uses the new 21c JSON datatype and populate it with some JSON documents (note the PONumber key has effectively unique numeric values assigned):
SQL> CREATE TABLE bowie_json
(id number,
bowie_date date,
bowie_order JSON);
SQL> insert into bowie_json
select
rownum,
sysdate,
'{"PONumber" : ' || rownum || ',
"Reference" : "2022' || rownum || 'DBOWIE",
"Requestor" : "David Bowie",
"User" : "DBOWIE",
"CostCenter" : "A42",
"ShippingInstructions" : {"name" : "David Bowie",
"Address": {"street" : "42 Ziggy Street",
"city" : "Canberra",
"state" : "ACT",
"zipCode" : 2601,
"country" : "Australia"},
"Phone" : [{"type" : "Office", "number" : "417-555-7777"},
{"type" : "Mobile", "number" : "417-555-1234"}]},
"Special Instructions" : null,
"AllowPartialShipment" : true,
"LineItems" : [{"ItemNumber" : 1,
"Part" : {"Description" : "Hunky Dory",
"UnitPrice" : 10.95},
"Quantity" : 5.0},
{"ItemNumber" : 2,
"Part" : {"Description" : "Pin-Ups",
"UnitPrice" : 10.95},
"Quantity" : 3.0}]}'
from dual connect by level <= 2000000;
2000000 rows created.
SQL> commit;
Commit complete
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_JSON');
PL/SQL procedure successfully completed.
As always, it’s important to ensure the table has statistics, as AI does not work properly without them.
I then run a number of SQL statements, with different JSON expression based predicates, including:
SQL> select * from bowie_json where json_value(bowie_order, '$.PONumber')='42';
SQL> select * from bowie_json z where z.bowie_order.PONumber.number()=4242;
SQL> select * from bowie_json where json_value(bowie_order, '$.PONumber' returning number)=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 1196930810
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 12M | 34476 (1) | 00:00:02 |
|* 1 | TABLE ACCESS FULL | BOWIE_JSON | 20000 | 12M | 34476 (1) | 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(JSON_VALUE("BOWIE_ORDER" /*+ LOB_BY_VALUE */ FORMAT OSON
, '$.PONumber' RETURNING NUMBER NULL ON ERROR)=42)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
259127 consistent gets
200279 physical reads
0 redo size
1595 bytes sent via SQL*Net to client
526 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
They all return just the one row, but must currently use a Full Table Scan with no indexes present.
So what does AI make of things?
The first thing to note is that running the AI last activity report generates the following error:
SQL> select dbms_auto_index.report_last_activity() report from dual; ERROR: ORA-30954: char 0 is invalid in json_value(BOWIE_ORDER, '$.PONumber' returning VA ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 177 ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 107 ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 8676 ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 8676 ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9226 ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 89 ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 167 ORA-06512: at line 1 no rows selected
If we look at the indexes now present with the table:
SQL> select index_name, index_type, auto, visibility, status, num_rows, leaf_blocks, clustering_factor from user_indexes
where table_name='BOWIE_JSON';
INDEX_NAME INDEX_TYPE AUT VISIBILIT STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------- ------------------------- --- --------- -------- ---------- ----------- -----------------
SYS_IL0000081096C00003$$ LOB NO VISIBLE VALID
SYS_AI_ayvj257jd93cv FUNCTION-BASED NORMAL YES VISIBLE VALID 2000000 5141 380000
SYS_AI_gpdkwzugdn055 FUNCTION-BASED NORMAL YES VISIBLE VALID 2000000 4596 200000
SQL> select index_name, column_expression from user_ind_expressions where table_name='BOWIE_JSON';
INDEX_NAME COLUMN_EXPRESSION
------------------------- --------------------------------------------------------------------------------
SYS_AI_ayvj257jd93cv JSON_VALUE("BOWIE_ORDER" FORMAT OSON , '$.PONumber' RETURNING VARCHAR2(4000) ERR
OR ON ERROR NULL ON EMPTY)
SYS_AI_gpdkwzugdn055 JSON_VALUE("BOWIE_ORDER" FORMAT OSON , '$.PONumber' RETURNING NUMBER ERROR ON ER
ROR NULL ON EMPTY)
We can see that AI has indeed created two new automatic indexes, one on the VARCHAR2 JSON expression and one on the NUMBER JSON expression.
If we re-run the SQLs, we notice 3 very important points. Note the following example was run soon after the automatic indexes were created:
SQL> select * from bowie_json where json_value(bowie_order, '$.PONumber')='42';
Execution Plan
----------------------------------------------------------
Plan hash value: 832017402
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 12M | 1524 (1) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_JSON | 20000 | 12M | 1524 (1) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_ayvj257jd93cv | 8000 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(JSON_VALUE("BOWIE_ORDER" /*+ LOB_BY_VALUE */ FORMAT OSON , '$.PONumber' RETURNING
VARCHAR2(4000) ERROR ON ERROR NULL ON EMPTY)='42')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
234168 consistent gets
200279 physical reads
0 redo size
1595 bytes sent via SQL*Net to client
526 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
The first point to note is that the CBO now chooses to use the newly created automatic index. As only one row is return, this is as one would hope.
But there are two other very important points/issues worth making about the above execution plan and associated costs and statistics. One is associated with new AI behaviour introduced in 21c and the other is associated with an old trap in relation to function-based indexes.
I’ll leave it to the discernible reader to spot these issues, before I cover them in Part II in the coming days…
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…
Oracle 19c Automatic Indexing: Function-Based Indexes? Part II (If You Can See Me) February 5, 2021
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Function Based Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, Virtual Columns.1 comment so far
In my previous post, I discussed how Automatic Indexing does not currently support creating an index based on a function or expression predicate, even if it’s an equality predicate. You must manually create the associated function-based index.
However, if you have access to the application, there’s a better strategy when frequently searching on a function-based predicate. That’s to create a Virtual Column and use this column in your searching criteria (as mentioned by Connor McDonald in this comment).
To illustrate, I’m going to drop the previously manually created function-based index and hence the associated hidden virtual column, as Oracle quite rightly doesn’t allow you to have two virtual columns based on the same expression in the same table.
SQL> drop index david_upper_name_i; Index dropped.
Since Oracle 11g, Oracle has supported the use of Visible Virtual Columns, a column that doesn’t physically exist, but defines a function/expression that can be easily accessed and populated when queried.
I’ll next create a Virtual Column called UPPER_NAME that is defined not based on a Data Type, but on the result on the UPPER function on the previously defined NAME column:
SQL> alter table david add (upper_name as (upper(name))); Table altered.
Regardless of size of table, this column is added virtually instantly (pun fully intended), as no data is physically stored in the table itself. I view it (yep, another pun) as a “mini-view”, that can be used to hide complexity from the developer, with the actual data derived at run-time when the column is accessed in an SQL.
After I generate fresh statistics:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'DAVID', estimate_percent=>null); PL/SQL procedure successfully completed. SQL> select column_name, hidden_column, virtual_column, num_distinct, density, histogram from dba_tab_cols where table_name='DAVID'; COLUMN_NAME HID VIR NUM_DISTINCT DENSITY HISTOGRAM -------------------- --- --- ------------ ---------- --------------- NAME NO NO 10000000 0 HYBRID MORE_STUFF9 NO NO 1 .00000005 FREQUENCY MORE_STUFF8 NO NO 1 .00000005 FREQUENCY MORE_STUFF7 NO NO 1 .00000005 FREQUENCY MORE_STUFF6 NO NO 1 .00000005 FREQUENCY MORE_STUFF5 NO NO 1 .00000005 FREQUENCY MORE_STUFF4 NO NO 1 .00000005 FREQUENCY MORE_STUFF3 NO NO 1 .00000005 FREQUENCY MORE_STUFF2 NO NO 1 .00000005 FREQUENCY MORE_STUFF10 NO NO 1 .00000005 FREQUENCY MORE_STUFF1 NO NO 1 .00000005 FREQUENCY ID NO NO 10000000 0 HYBRID CODE NO NO 10000 .0001 HYBRID UPPER_NAME NO YES 10000000 0 HYBRID
Note how the UPPER_NAME virtual column is NOT hidden and now has up to date statistics.
We can now run this simplified query based on the new UPPER_NAME column, which does not need to include the potentially complex function expression:
SQL> select * from david where upper_name='DAVID BOWIE 42';
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2426813604
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 200 | 3349 (6) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID | 1 | 200 | 3349 (6) | 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("UPPER_NAME"='DAVID BOWIE 42')
filter("UPPER_NAME"='DAVID BOWIE 42')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
263469 consistent gets
263452 physical reads
0 redo size
1328 bytes sent via SQL*Net to client
375 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
If we look at portions of the subsequent Automatic Indexing report:
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 1
Indexes created (visible / invisible) : 1 (1 / 0)
Space used (visible / invisible) : 360.71 MB (360.71 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 2
SQL statements improved (improvement factor) : 2 (263476.8x)
SQL plan baselines created : 0
Overall improvement factor : 263476.8x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
---------------------------------------------------------------------------
| BOWIE | DAVID | SYS_AI_4k4mkgkw049ht | UPPER_NAME | B-TREE | NONE |
---------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : 7tfqh3pu526mt
SQL Text : select * from david where upper_name='DAVID BOWIE 42'
Improvement Factor : 263484.7x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 1471249 1414
CPU Time (s): 300584 986
Buffer Gets: 3161816 4
Optimizer Cost: 3349 4
Disk Reads: 3161432 3
Direct Writes: 0 0
Rows Processed: 12 1
Executions: 12 1
PLANS SECTION
--------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value : 2426813604
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3349 | |
| 1 | TABLE ACCESS STORAGE FULL | DAVID | 1 | 200 | 3349 | 00:00:01 |
-----------------------------------------------------------------------------
Notes
-----
- dop = 1
- px_in_memory_imc = no
- px_in_memory = no
- cardinality_feedback = yes
- With Auto Indexes
-----------------------------
Plan Hash Value : 1447691372
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 200 | 4 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID | 1 | 200 | 4 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_4k4mkgkw049ht | 1 | | 3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("UPPER_NAME"='DAVID BOWIE 42')
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
We see from the report that Automatic Indexing has now created the associated, implicitly created function-based index (SYS_AI_4k4mkgkw049ht) based on the virtual UPPER_NAME column:
SQL> select index_name, index_type, auto, constraint_index, visibility, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='DAVID'; INDEX_NAME INDEX_TYPE AUT CON VISIBILIT STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR -------------------- --------------------------- --- --- --------- -------- ---------- ----------- ----------------- SYS_AI_4k4mkgkw049ht FUNCTION-BASED NORMAL YES NO VISIBLE VALID 10000000 43104 2136839 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='DAVID' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION -------------------- -------------------- --------------- SYS_AI_4k4mkgkw049ht UPPER_NAME 1
If we now re-run the SQL query:
SQL> select * from david where upper_name='DAVID BOWIE 4242';
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1447691372
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 200 | 4 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID | 1 | 200 | 4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_AI_4k4mkgkw049ht | 1 | | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("UPPER_NAME"='DAVID BOWIE 4242')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1334 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
The CBO now uses the new Automatic Index to significantly improve the performance of the query.
So not only is using a user defined Virtual Column a cleaner solution with respect to the frequent use of a function-based expressions, but has the added advantage of being supported with Automatic Indexing.
Oracle 19c Automatic Indexing: Function-Based Indexes? (No Plan) February 4, 2021
Posted by Richard Foote in 19c, 19c New Features, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Function Based Indexes, Oracle, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Virtual Columns.3 comments
I previously discussed how Automatic Indexing only currently supports Equality based predicates.
The question I have today is does Automatic Indexing support function-based indexes? Let’s take a look.
The below DAVID table has the key column NAME which is an effectively unique VARCHAR2 column:
SQL> create table david (id number, code number, name varchar2(42), more_stuff1 varchar2(42), more_stuff2 varchar2(42), more_stuff3 varchar2(42), more_stuff4 varchar2(42), more_stuff5 varchar2(42), more_stuff6 varchar2(42), more_stuff7 varchar2(42), more_stuff8 varchar2(42), more_stuff9 varchar2(42), more_stuff10 varchar2(42)); Table created. SQL> insert into david select rownum, mod(rownum, 10000)+1, 'David Bowie '|| rownum, 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust', 'Ziggy Stardust', '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', estimate_percent=>null); PL/SQL procedure successfully completed.
If we look at the current details of the table columns:
SQL> select column_name, num_distinct, density, histogram from dba_tab_cols where table_name='DAVID'; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM -------------------- ------------ ---------- --------------- NAME 10000000 0 HYBRID MORE_STUFF9 1 .00000005 FREQUENCY MORE_STUFF8 1 .00000005 FREQUENCY MORE_STUFF7 1 .00000005 FREQUENCY MORE_STUFF6 1 .00000005 FREQUENCY MORE_STUFF5 1 .00000005 FREQUENCY MORE_STUFF4 1 .00000005 FREQUENCY MORE_STUFF3 1 .00000005 FREQUENCY MORE_STUFF2 1 .00000005 FREQUENCY MORE_STUFF10 1 .00000005 FREQUENCY MORE_STUFF1 1 .00000005 FREQUENCY ID 10000000 0 HYBRID CODE 10000 .0001 HYBRID
We notice the same oddity of my previous post that all columns have histograms…
Let’s run the following query with an UPPER function-based predicate that returns only the one row:
SQL> select * from david where upper(name) = 'DAVID BOWIE 4242';
Execution Plan
----------------------------------------------------------
Plan hash value: 2426813604
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K | 17M | 3350 (6) | 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | DAVID | 100K | 17M | 3350 (6) | 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage(UPPER("NAME")='DAVID BOWIE 4242')
filter(UPPER("NAME")='DAVID BOWIE 4242')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
263469 consistent gets
263452 physical reads
0 redo size
1256 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
What does Automatic Indexing make of this scenario?
Basically, it does nothing. Currently, Automatic Indexing does NOT support such function-based indexes, even with equality based predicates (as of at least version 19.5.0.0.0). If we look at the next Automatic Indexing report:
SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 0 Indexes created : 0 Space used : 0 B Indexes dropped : 0 SQL statements verified : 2 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 -------------------------------------------------------------------------------
No such function-based index is ever created by Automatic Indexing:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='DAVID'; no rows selected
To improve the performance of this query, one has to manually create the necessary function-based index:
SQL> create index david_upper_name_i on david(upper(name)); Index created.
If we now re-run the query:
SQL> select name from david where upper(name) = 'DAVID BOWIE 4242';
Execution Plan
----------------------------------------------------------
Plan hash value: 2675555529
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K | 4199K | 3175 (1) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID | 100K | 4199K | 3175 (1) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | DAVID_UPPER_NAME_I | 40000 | | 3 (0) | 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("NAME")='DAVID BOWIE 4242')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
369 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
The query now uses the function-based index to significantly improve the performance of this query, with just 5 consistent gets.
Note however as with all function-based indexes, by default the estimated cardinality estimate and associated CBO costs are way off (100K rows are estimated, not the 1 row that is actually returned). This is due to the CBO having no real idea of the number and distribution of values coming out of the “black box” function-based predicate.
This is why Oracle automatically creates an hidden virtual column by which to store the necessary statistics associated to the function (in this case the SYS_NC00014$ column):
SQL> select column_name, num_distinct, density, histogram from dba_tab_cols where table_name='DAVID'; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM -------------------- ------------ ---------- --------------- NAME 10000000 0 HYBRID MORE_STUFF9 1 .00000005 FREQUENCY MORE_STUFF8 1 .00000005 FREQUENCY MORE_STUFF7 1 .00000005 FREQUENCY MORE_STUFF6 1 .00000005 FREQUENCY MORE_STUFF5 1 .00000005 FREQUENCY MORE_STUFF4 1 .00000005 FREQUENCY MORE_STUFF3 1 .00000005 FREQUENCY MORE_STUFF2 1 .00000005 FREQUENCY MORE_STUFF10 1 .00000005 FREQUENCY MORE_STUFF1 1 .00000005 FREQUENCY ID 10000000 0 HYBRID CODE 10000 .0001 HYBRID SYS_NC00014$ NONE
But we need to first collect statistics on this hidden virtual column for the statistics to be populated:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'DAVID', no_invalidate=> false, method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 1'); SQL> select column_name, num_distinct, density, histogram from dba_tab_cols where table_name='DAVID'; COLUMN_NAME NUM_DISTINCT DENSITY HISTOGRAM -------------------- ------------ ---------- --------------- NAME 10000000 0 HYBRID MORE_STUFF9 1 .00000005 FREQUENCY MORE_STUFF8 1 .00000005 FREQUENCY MORE_STUFF7 1 .00000005 FREQUENCY MORE_STUFF6 1 .00000005 FREQUENCY MORE_STUFF5 1 .00000005 FREQUENCY MORE_STUFF4 1 .00000005 FREQUENCY MORE_STUFF3 1 .00000005 FREQUENCY MORE_STUFF2 1 .00000005 FREQUENCY MORE_STUFF10 1 .00000005 FREQUENCY MORE_STUFF1 1 .00000005 FREQUENCY ID 10000000 0 HYBRID CODE 10000 .0001 HYBRID SYS_NC00014$ 9947366 0 HYBRID
Now the CBO has the necessary statistics by which to determine a much more accurate cardinality estimate for the function-based predicate and so potentially a more efficient execution plan:
SQL> select * from david where upper(name) = 'DAVID BOWIE 4242';
Execution Plan
----------------------------------------------------------
Plan hash value: 2675555529
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 200 | 4 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | DAVID | 1 | 200 | 4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | DAVID_UPPER_NAME_I | 1 | | 3 (0) | 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("NAME")='DAVID BOWIE 4242')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1256 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
With the virtual column statistics in place, the CBO now has the cardinality estimate of 1 and associated costs spot on, which is always a good thing.
This requirement to collect the necessary statistics on the associated virtual column created as a result of the function-based index to ensure the index is costed and used effectively is perhaps but one reason why function-based indexes are currently not supported by Automatic Indexing.
As always, this can always change in the future…
Richard Foote's Oracle Blog 