In the previous
blog post we discussed how Automatic Index (AI) recognized there was
already an existing manually created index and so created an effectively
Invisible redundant AI.
We also discussed
previously how AI was clever enough to logically add new columns to
existing AI if it determined such a new index can be used effectively for both
pervious and new workloads.
In this post we will see how AI will
handle this scenario if a previously manually created index could also potentially
improved by adding new column.
We will start by creating a table similar
to the previous blog post but with more distinct values for X3 column such that
a test query will be more selective and so make the CBO favor the use of new
index.
ai_demo@PDB19> create
table demo010
2 nologging as
3 select rownum as id,
4 mod(rownum,1000)+1 as x1,
5 ceil(dbms_random.value(0,100)) as x2,
6 ceil(dbms_random.value(0,100)) as x3,
7 a.*
8 from all_objects a,
9 all_users, all_users
10 where rownum <=10000000 ;
Table created.
ai_demo@PDB19> select
column_name,num_distinct,num_nulls,density
2 from user_tab_cols
3 where table_name ='DEMO010'
4 and ( column_name like 'X%'
5 or column_name like 'ID' )
6 order by column_name;
COLUMN_NAM NUM_DISTINCT NUM_NULLS
DENSITY
---------- ------------ ---------- ----------
ID 9914368 0 1.0086E-07
X1 1000 0 .001
X2 100 0 .01
X3 100 0 .01
We will now manually create an index for
both combination of X2 and X3 columns
ai_demo@PDB19> create
index demo010_x2_x3_idx on demo010(x2,x3) nologging;
Index created.
ai_demo@PDB19> create
index demo010_x3_x2_idx on demo010(x3,x2) nologging;
Index created.
ai_demo@PDB19> select
index_name,auto,tablespace_name,compression,
2 status,visibility,indexing
3 from user_indexes
4 where table_name ='DEMO010';
INDEX_NAME AUT TABLESPACE_NAME COMPRESSION STATUS
VISIBILIT INDEXIN
------------------------- --- ------------------------- ------------- -------- --------- -------
DEMO010_X2_X3_IDX NO TS_INDEX_DEMO DISABLED VALID VISIBLE FULL
DEMO010_X3_X2_IDX NO TS_INDEX_DEMO DISABLED VALID VISIBLE FULL
ai_demo@PDB19> select
index_name,column_name,column_position
2 from user_ind_columns
3 where table_name ='DEMO010'
4 order by 1,3;
INDEX_NAME COLUMN_NAM COLUMN_POSITION
------------------------- ---------- ---------------
DEMO010_X2_X3_IDX X2 1
DEMO010_X2_X3_IDX X3 2
DEMO010_X3_X2_IDX X3 1
DEMO010_X3_X2_IDX X2 2
If we run the following query,
ai_demo@PDB19> set
autotrace traceonly explain statistics
ai_demo@PDB19> select max(x1), count(*) from demo010 where x2 = &x2 and x3 = &x3;
old 1: select max(x1), count(*) from demo010 where x2 = &x2 and x3 = &x3
new 1: select max(x1), count(*) from demo010 where x2 = 71 and x3 = 21
Execution Plan
----------------------------------------------------------
Plan hash value: 600429135
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1003 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO010 | 1000 | 10000 | 1003 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEMO010_X2_X3_IDX | 1000 | | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
3 - access("X2"=71 AND
"X3"=21)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
880 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
546 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 favors the use of an index as
with just 1000 rows returned from a 10M rows table, the index is the cheaper
access method.
If we run the following query which also
includes the more selective X1 column predicates as well (which returns just 1
row)
ai_demo@PDB19> set
autotrace traceonly explain statistics
ai_demo@PDB19> select max(x1), count(*) from demo010 where x2 = &x2 and x3 = &x3 and x1 = &x1;
old 1: select max(x1), count(*) from demo010 where x2 = &x2 and x3 = &x3 and x1 = &x1
new 1: select max(x1), count(*) from demo010 where x2 = 68 and x3 = 70 and x1 = 2
Execution Plan
----------------------------------------------------------
Plan hash value: 600429135
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1003 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO010 | 1 | 10 | 1003 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEMO010_X2_X3_IDX | 1000 | | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
2 - filter("X1"=2)
3 - access("X2"=68 AND "X3"=70)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
993 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
546 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 again uses the same index based
on the columns X2 and X3 as this has already been proven to be more efficient
than a FTS. However an index that also includes the X1 column would be even
more efficient as the CBO could simply use the index to fetch just few rows of
interest without having to perform unnecessary filtering on the X1 column.
So what does AI do in this scenario? If
we look at the corresponding AI report
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 1
Indexes created (visible / invisible) : 1 (1 / 0)
Space used (visible / invisible) : 176.16 MB (176.16 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 1
SQL statements improved (improvement factor) : 1 (993x)
SQL plan baselines created : 0
Overall improvement factor : 993x
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
-----------------------------------------------------------------------------
| AI_DEMO | DEMO010 | SYS_AI_9dj7yy4b1w2ss | X2,X3,X1 | B-TREE | NONE |
-----------------------------------------------------------------------------
-------------------------------------------------------------------------------
We notice AI has created a new index
based on the column X2, X3, X1 however it has not dropped any indexes.
In this blog post we will see about how
Automatic Indexing (AI) will handle scenarios related to existing manually
created indexes.
If we look at the verification sections
of the AI report.
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : AI_DEMO
SQL ID : 1xhdktsbsf5qd
SQL Text : select max(x1), count(*) from demo010 where x2 = 68 and
x3 = 70 and x1 = 2
Improvement Factor : 993x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 35193 4648
CPU Time (s): 26897 3037
Buffer Gets: 3982 3
Optimizer Cost: 1003 3
Disk Reads: 382 2
Direct Writes: 0 0
Rows Processed: 4 1
Executions: 4 1
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value : 600429135
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1003 | |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | DEMO010 | 1 | 10 | 1003 | 00:00:01 |
| 3 | INDEX RANGE SCAN | DEMO010_X2_X3_IDX | 1000 | | 5 | 00:00:01 |
----------------------------------------------------------------------------------------------------
- With Auto Indexes
-----------------------------
Plan Hash Value : 4140481494
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| * 2 | INDEX RANGE SCAN | SYS_AI_9dj7yy4b1w2ss | 2 | 20 | 3 | 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
------------------------------------------
* 2 - access("X2"=68 AND "X3"=70 AND "X1"=2)
Notes
-----
- Dynamic sampling used for this statement ( level = 11 )
We can see that the index was created
because of 993x improvements in the performance of the query we ran.
If we look at the details of the indexes
that now exists on the table
ai_demo@PDB19> select
owner,index_name,tablespace_name,compression,
2 status,visibility,indexing,auto
3 from all_indexes
4 where table_name ='DEMO010'
5 and owner ='AI_DEMO';
OWNER INDEX_NAME TABLESPACE_NAME COMPRESSION STATUS
VISIBILIT INDEXIN AUT
---------- ------------------------- ------------------------- ------------- -------- --------- ------- ---
AI_DEMO DEMO010_X2_X3_IDX TS_INDEX_DEMO DISABLED VALID VISIBLE FULL NO
AI_DEMO DEMO010_X3_X2_IDX TS_INDEX_DEMO DISABLED VALID VISIBLE FULL NO
AI_DEMO SYS_AI_9dj7yy4b1w2ss TS_INDEX_DEMO ADVANCED LOW VALID VISIBLE FULL YES
ai_demo@PDB19> select
index_name,column_name,column_position
2 from user_ind_columns
3 where table_name ='DEMO010'
4 order by 1,3;
INDEX_NAME COLUMN_NAM COLUMN_POSITION
------------------------- ---------- ---------------
DEMO010_X2_X3_IDX X2 1
DEMO010_X2_X3_IDX X3 2
DEMO010_X3_X2_IDX X3 1
DEMO010_X3_X2_IDX X2 2
SYS_AI_9dj7yy4b1w2ss X2 1
SYS_AI_9dj7yy4b1w2ss X3 2
SYS_AI_9dj7yy4b1w2ss X1 3
7 rows selected.
We noticed a couple of key points.
First even though the previously created
manual index on the column X2 and X3 is now totally redundant because it has
the same column list as the leading columns of the newly created AI based on
the columns (X2,X3,X1), AI do not drop the manually created indexes.
AI only automatically drops and logically
creates auto indexes.
Next AI is aware of the previous
workloads because it created a new AI with the column list (X2, X3, X1) and not
the default X1, X2, X3 column order. This suggests AI is indeed trying to
create a new index that is able to cater for all known sql workloads
(predicates on just X2, X3 and predicates on X1,X2, X3 combinations)
However AI does not have the capability
to logically modify or drop redundant manually created indexes.
2 nologging as
3 select rownum as id,
4 mod(rownum,1000)+1 as x1,
5 ceil(dbms_random.value(0,100)) as x2,
6 ceil(dbms_random.value(0,100)) as x3,
7 a.*
8 from all_objects a,
9 all_users, all_users
10 where rownum <=10000000 ;
2 from user_tab_cols
3 where table_name ='DEMO010'
4 and ( column_name like 'X%'
5 or column_name like 'ID' )
6 order by column_name;
---------- ------------ ---------- ----------
ID 9914368 0 1.0086E-07
X1 1000 0 .001
X2 100 0 .01
X3 100 0 .01
2 status,visibility,indexing
3 from user_indexes
4 where table_name ='DEMO010';
------------------------- --- ------------------------- ------------- -------- --------- -------
DEMO010_X2_X3_IDX NO TS_INDEX_DEMO DISABLED VALID VISIBLE FULL
DEMO010_X3_X2_IDX NO TS_INDEX_DEMO DISABLED VALID VISIBLE FULL
2 from user_ind_columns
3 where table_name ='DEMO010'
4 order by 1,3;
------------------------- ---------- ---------------
DEMO010_X2_X3_IDX X2 1
DEMO010_X2_X3_IDX X3 2
DEMO010_X3_X2_IDX X3 1
DEMO010_X3_X2_IDX X2 2
ai_demo@PDB19> select max(x1), count(*) from demo010 where x2 = &x2 and x3 = &x3;
old 1: select max(x1), count(*) from demo010 where x2 = &x2 and x3 = &x3
new 1: select max(x1), count(*) from demo010 where x2 = 71 and x3 = 21
----------------------------------------------------------
Plan hash value: 600429135
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1003 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO010 | 1000 | 10000 | 1003 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEMO010_X2_X3_IDX | 1000 | | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
---------------------------------------------------
----------------------------------------------------------
0 recursive calls
0 db block gets
880 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
546 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
ai_demo@PDB19> select max(x1), count(*) from demo010 where x2 = &x2 and x3 = &x3 and x1 = &x1;
old 1: select max(x1), count(*) from demo010 where x2 = &x2 and x3 = &x3 and x1 = &x1
new 1: select max(x1), count(*) from demo010 where x2 = 68 and x3 = 70 and x1 = 2
----------------------------------------------------------
Plan hash value: 600429135
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 1003 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO010 | 1 | 10 | 1003 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | DEMO010_X2_X3_IDX | 1000 | | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
---------------------------------------------------
3 - access("X2"=68 AND "X3"=70)
----------------------------------------------------------
0 recursive calls
0 db block gets
993 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
546 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-------------------------------------------------------------------------------
Index candidates : 1
Indexes created (visible / invisible) : 1 (1 / 0)
Space used (visible / invisible) : 176.16 MB (176.16 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 1
SQL statements improved (improvement factor) : 1 (993x)
SQL plan baselines created : 0
Overall improvement factor : 993x
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
-----------------------------------------------------------------------------
| AI_DEMO | DEMO010 | SYS_AI_9dj7yy4b1w2ss | X2,X3,X1 | B-TREE | NONE |
-----------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : AI_DEMO
SQL ID : 1xhdktsbsf5qd
SQL Text : select max(x1), count(*) from demo010 where x2 = 68 and
x3 = 70 and x1 = 2
Improvement Factor : 993x
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 35193 4648
CPU Time (s): 26897 3037
Buffer Gets: 3982 3
Optimizer Cost: 1003 3
Disk Reads: 382 2
Direct Writes: 0 0
Rows Processed: 4 1
Executions: 4 1
---------------------------------------------------------------------------------------------
-----------------------------
Plan Hash Value : 600429135
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1003 | |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | DEMO010 | 1 | 10 | 1003 | 00:00:01 |
| 3 | INDEX RANGE SCAN | DEMO010_X2_X3_IDX | 1000 | | 5 | 00:00:01 |
----------------------------------------------------------------------------------------------------
-----------------------------
Plan Hash Value : 4140481494
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| * 2 | INDEX RANGE SCAN | SYS_AI_9dj7yy4b1w2ss | 2 | 20 | 3 | 00:00:01 |
-------------------------------------------------------------------------------------
------------------------------------------
* 2 - access("X2"=68 AND "X3"=70 AND "X1"=2)
-----
- Dynamic sampling used for this statement ( level = 11 )
2 status,visibility,indexing,auto
3 from all_indexes
4 where table_name ='DEMO010'
5 and owner ='AI_DEMO';
---------- ------------------------- ------------------------- ------------- -------- --------- ------- ---
AI_DEMO DEMO010_X2_X3_IDX TS_INDEX_DEMO DISABLED VALID VISIBLE FULL NO
AI_DEMO DEMO010_X3_X2_IDX TS_INDEX_DEMO DISABLED VALID VISIBLE FULL NO
AI_DEMO SYS_AI_9dj7yy4b1w2ss TS_INDEX_DEMO ADVANCED LOW VALID VISIBLE FULL YES
2 from user_ind_columns
3 where table_name ='DEMO010'
4 order by 1,3;
------------------------- ---------- ---------------
DEMO010_X2_X3_IDX X2 1
DEMO010_X2_X3_IDX X3 2
DEMO010_X3_X2_IDX X3 1
DEMO010_X3_X2_IDX X2 2
SYS_AI_9dj7yy4b1w2ss X2 1
SYS_AI_9dj7yy4b1w2ss X3 2
SYS_AI_9dj7yy4b1w2ss X1 3
No comments:
Post a Comment