Oracle Database 19c Automatic Indexing – Indexed Column Reorder (What Shall We Do Now?) December 18, 2019
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Index Column Order, Index Column Reorder.trackback
I previously discussed how the default column order of an Automatic Index (in the absence of other factors) is based on the Column ID, the order in which the columns are defined in the table.
But what if there are “other factors” based on new workloads and the original index column order is no longer optimal or appropriate ?
I’ll begin by creating a table, with the following key column defined in CODE1, CODE2, CODE3 order:
SQL> create table major_tom (id number, code1 number, code2 number, code3 number, name varchar2(42)); Table created. SQL> insert into major_tom select rownum, mod(rownum, 10)+1, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 1000)), '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=>'MAJOR_TOM'); PL/SQL procedure successfully completed. SQL> select column_name, num_distinct, density from user_tab_columns where table_name='MAJOR_TOM'; COLUMN_NAME NUM_DISTINCT DENSITY -------------------- ------------ ---------- ID 9914368 1.0086E-07 CODE1 10 .00000005 CODE2 100 .00000005 CODE3 1000 .001 NAME 1 1
If I now run the following query with predicates based on these three columns:
SQL> select * from major_tom where code3=42 and code2=42 and code1=4;
15 rows selected.
Execution Plan
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 280 | 7354 (7)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10 | 280 | 7354 (7)| 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 10 | 280 | 7354 (7)| 00:00:01 |
|* 4 | TABLE ACCESS STORAGE FULL| MAJOR_TOM | 10 | 280 | 7354 (7)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("CODE3"=42 AND "CODE2"=42 AND "CODE1"=4)
filter("CODE3"=42 AND "CODE2"=42 AND "CODE1"=4)
Statistics
----------------------------------------------------------
34 recursive calls
5 db block gets
45861 consistent gets
0 physical reads
1044 redo size
1087 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed
After the default 15 minutes period in which the Automatic Index task is run, if we look at what Automatic Index has been created:
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
--------------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
--------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_9mrs058nrg9d5 | CODE1,CODE2,CODE3 | B-TREE | NONE |
--------------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : ayuj12hggwrvc
SQL Text : select * from major_tom where code3=42 and code2=42 and code1=4
Improvement Factor : 45853.8x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 3103394 946
CPU Time (s): 3092860 1017
Buffer Gets: 596102 18
Optimizer Cost: 7354 18
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 195 15
Executions: 13 1
We can see Oracle has indeed created an Automatic Index (SYS_AI_9mrs058nrg9d5) in the default CODE1, CODE2, CODE3 order.
But if we now run a new query, based on a predicate on just the CODE3 column:
SQL> select * from major_tom where code3=42;
9961 rows selected.
Execution Plan
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 273K| 7354 (7)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10000 | 273K| 7354 (7)| 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 10000 | 273K| 7354 (7)| 00:00:01 |
|* 4 | TABLE ACCESS STORAGE FULL| MAJOR_TOM | 10000 | 273K| 7354 (7)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("CODE3"=42)
filter("CODE3"=42)
Statistics
----------------------------------------------------------
8 recursive calls
4 db block gets
45853 consistent gets
0 physical reads
0 redo size
166137 bytes sent via SQL*Net to client
599 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9961 rows processed
We can see the CBO has NOT used the index, as the leading column of the existing index is not mentioned in the SQL predicate and the CBO deems an Index Skip Scan as too expensive:
If we now run an SQL with predicates based on just the CODE2 and CODE3 columns:
SQL> select * from major_tom where code3=42 and code2=42;
101 rows selected.
Execution Plan
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2800 | 7354 (7)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 100 | 2800 | 7354 (7)| 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 100 | 2800 | 7354 (7)| 00:00:01 |
|* 4 | TABLE ACCESS STORAGE FULL| MAJOR_TOM | 100 | 2800 | 7354 (7)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("CODE3"=42 AND "CODE2"=42)
filter("CODE3"=42 AND "CODE2"=42)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
45853 consistent gets
0 physical reads
0 redo size
2281 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
101 rows processed
The existing Automatic Index is again not used as the important CODE1 column which is the leading column of the index is not mentioned in the SQL predicates and the CBO deems an Index Skip Scan as too expensive.
I know from experience many DBAs would simply create a new index with CODE3, CODE2 as the leading columns. But what does Automatic Indexing do in the scenario?
SQL> select dbms_auto_index.report_last_activity() report from dual; REPORT -------------------------------------------------------------------------------- GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 21-JUN-2019 02:39:32 Activity end : 21-JUN-2019 02:40:18 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) : 243.27 MB (243.27 MB / 0 B) Indexes dropped (space reclaimed) : 1 (243.27 MB) SQL statements verified : 1 SQL statements improved : 0 SQL plan baselines created : 0 Overall improvement factor : 0x ------------------------------------------------------------------------------- SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0
We notice in the Automatic Indexing report it’s stating that one new index has been created BUT that one index has also been dropped.
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
--------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_00hxxxkgb821n | CODE3,CODE2,CODE1 | B-TREE | NONE |
--------------------------------------------------------------------------------------
1. The following indexes were dropped:
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
--------------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_9mrs058nrg9d5 | CODE1,CODE2,CODE3 | B-TREE | NONE |
--------------------------------------------------------------------------------------
SQL> select index_name, column_name, column_position from user_ind_columns where table_name='MAJOR_TOM' order by column_position;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
-------------------- --------------- ---------------
SYS_AI_00hxxxkgb821n CODE3 1
SYS_AI_00hxxxkgb821n CODE2 2
SYS_AI_00hxxxkgb821n CODE1 3
Automatic Indexing has created a new index (SYS_AI_00hxxxkgb821n), with the columns in CODE3, CODE2, CODE1 order, as this index is able to service all currently known SQL predicate combinations:
WHERE CODE3 = x AND CODE2 = y AND CODE1= z
WHERE CODE3 = x AND CODE2 = y
WHERE CODE3 = x
as the leading column in the index is listed in all three current scenarios.
This means the previous index in CODE1, CODE2, CODE3 order is now redundant as the new index can fully service all know SQL predicate combinations. As a result, Automatic Indexing drops the redundant index.
This is a really nice capability of Automatic Indexing, the ability to effectively reorder the columns within an index based on new workloads.
But what if subsequent new SQL workloads means the new index is not able on its own to service all such workloads. I’ll discuss this scenario in my next post.
Richard Foote's Oracle Blog 
[…] previously discussed how Automatic Indexing can effectively cleverly reorder an existing index if it means it can now use the new index to satisfy new SQL predicates. In this post, we’ll […]
LikeLike
[…] previous articles, I discussed how Automatic Indexing has the capability to add columns or reorder the column list of previously created Automatic Indexes. However, how does Automatic Indexing handle these types of […]
LikeLike