jump to navigation

Oracle Database 19c Automatic Indexing – Need Another Index (Another Brick in The Wall Part 2) January 16, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Oracle Indexes.
trackback

I 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 explore this example further with some new workloads.

So, we previously ran SQL queries with SQL predicates in the following combinations:

  • CODE1=42 and CODE2=42 and CODE3=42
  • CODE2=42 and CODE3=42
  • CODE3=42

 

so an index based on CODE3, CODE2, CODE1 is able to satisfy all 3 existing predicates.

If we now run a new query with the following predicates based on CODE2=42 and CODE1=42:

SQL> select * from major_tom where code2=42 and code1=42;

no rows selected

Execution Plan

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)|Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    28 |  7318   (6)|00:00:01 |
|   1 |  PX COORDINATOR              |           |       |       |            |         |
|   2 |   PX SEND QC (RANDOM)        | :TQ10000  |     1 |    28 |  7318   (6)|00:00:01 |
|   3 |    PX BLOCK ITERATOR         |           |     1 |    28 |  7318   (6)|00:00:01 |
|*  4 |     TABLE ACCESS STORAGE FULL| MAJOR_TOM |     1 |    28 |  7318   (6)|00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - storage("CODE1"=42 AND "CODE2"=42)
    filter("CODE1"=42 AND "CODE2"=42)

Statistics
----------------------------------------------------------
         8  recursive calls
         4  db block gets
     45853  consistent gets
         0  physical reads
         0  redo size
       645  bytes sent via SQL*Net to client
       577  bytes received via SQL*Net from client
         1  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         0  rows processed

 

The current previously re-ordered index can’t now satisfy this new predicate as the leading column of the index (CODE3) is not specified in the SQL predicate and the CBO considers an Index Skip Scan as too expensive an operation. So a Full Table Scan is chosen here by the CBO.

If we wait and see what Automatic Indexing decides to now do:

 

SQL> select dbms_auto_index.report_last_activity() report from dual;

REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------

Activity start               : 21-JUN-2019 07:41:55
Activity end                 : 21-JUN-2019 07:42:49
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)              : 201.33 MB (201.33 MB / 0 B)
Indexes dropped                               : 0
SQL statements verified                       : 1
SQL statements improved (improvement factor)  : 1 (5.1x)
SQL plan baselines created                    : 0
Overall improvement factor                    : 5.1x

-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------

Unused indexes    : 0
Space used        : 0 B
Unusable indexes  : 0

-------------------------------------------------------------------------------

We notice that a new index has been created…

 

INDEX DETAILS

-------------------------------------------------------------------------------
The following indexes were created:
--------------------------------------------------------------------------------
| Owner | Table     | Index                | Key         | Type   | Properties |
--------------------------------------------------------------------------------
| BOWIE | MAJOR_TOM | SYS_AI_gmhdbjx21zcr1 | CODE1,CODE2 | B-TREE | NONE       |
--------------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------

Parsing Schema Name  : BOWIE
SQL ID               : 3y246nzwdpybv
SQL Text             : select * from major_tom where code2=42 and code1=4
Improvement Factor   : 5.1x

 

So Automatic Indexing has decided to now create a new, second index (SYS_AI_gmhdbjx21zcr1) on the table based on CODE1, CODE2, as a single index is no longer capable to address all known SQL predicates.

 

SQL> select index_name, column_name, column_position from user_ind_columns 
where table_name='MAJOR_TOM' order by index_name, column_position;

INDEX_NAME                COLUMN_NAME          COLUMN_POSITION
------------------------- -------------------- ---------------
SYS_AI_00hxxxkgb821n      CODE3                              1
SYS_AI_00hxxxkgb821n      CODE2                              2
SYS_AI_00hxxxkgb821n      CODE1                              3
SYS_AI_gmhdbjx21zcr1      CODE1                              1
SYS_AI_gmhdbjx21zcr1      CODE2                              2

 

So we now have two indexes to cater for all known SQL predicates in which an index has been shown to improve performance.

Automatic Indexing will try and create the minimum number of indexes possible to cater for all known SQL workloads.

 

In my next post, we’ll look at a more complex example in which there could potentially be numerous different indexes that could cater for more extensive workloads and how Automatic Indexing copes rather splendidly…

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: