Oracle 19c Automatic Indexing: Indexing Partitioned Tables Part I (Conversation...
source link: https://richardfoote.wordpress.com/2020/10/14/oracle-19c-automatic-indexing-indexing-partitioned-tables-part-i-conversation-piece/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Oracle 19c Automatic Indexing: Indexing Partitioned Tables Part I (Conversation Piece) October 14, 2020
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Index Access Path, Local Indexes, Oracle, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, Partitioned Indexes, Partitioning, Performance Tuning.
trackback
In this little series, I’m going to discuss how Automatic Indexing works in relation to Partitioning.
I’ve discussed Indexing and Partitioning many times previously and how Oracle has various options when indexing a partitioned table:
- Non-Partitioned Index
- Globally Partitioned Index
- Locally Partitioned Index
So the question(s) are how does Automatic Indexing handle scenarios with partitioned objects?
A very important point to make at the start is that based on my research, the answer has already changed significantly since Automatic Indexing was first released. So it’s important to understand that Automatic Indexing is an ever evolving capability, that will advance and improve as time goes on.
I’ll focus on how the feature currently works (as of Oracle Database 19.5), but will mention previously identified behaviour as a reference on how things can easily change.
In my first simple little example, I’m just going to create a range-partitioned table, partitioned based on RELEASE_DATE, with a partition for each year’s worth of data:
SQL> CREATE TABLE big_bowie
1
(id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_
2013
VALUES LESS THAN (TO_DATE(
'01-JAN-2014'
,
'DD-MON-YYYY'
)),
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 (MAXVALUE));
Table created.
I’ll now add about 8 years worth of data:
SQL> INSERT INTO big_bowie
1
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.
As discussed previously, I’ll importantly collect statistics:
SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=>
'BIG_BOWIE1'
);
PL/SQL procedure successfully completed.
I’ll now run the following very selective query based the TOTAL_SALES column that is NOT part of the partitioning key:
SQL> SELECT * FROM big_bowie
1
WHERE total_sales =
42
;
19
rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value:
2468051548
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
20
|
520
|
643
(
15
)|
00:
00:
01
| | |
|
1
| PARTITION RANGE ALL | |
20
|
520
|
643
(
15
)|
00:
00:
01
|
1
|
8
|
|*
2
| TABLE ACCESS STORAGE FULL| BIG_BOWIE
1
|
20
|
520
|
643
(
15
)|
00:
00:
01
|
1
|
8
|
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2
- storage(
"TOTAL_SALES"
=
42
)
filter(
"TOTAL_SALES"
=
42
)
Note
-----
- automatic DOP: Computed Degree of Parallelism is
1
Statistics
----------------------------------------------------------
0
recursive calls
0
db
block
gets
44014
consistent gets
9516
physical reads
0
redo size
1107
bytes sent via SQL*Net to client
369
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
19
rows processed
Without an index in place, the CBO has no choice but to use a FTS. But what will Automatic Indexing make of things?
If we look at the next Automatic Indexing report:
SQL> select dbms_auto_index.report_last_activity() from dual;
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start :
13
-OCT
-2020
01:
47:
48
Activity end :
13
-OCT
-2020
02:
59:
48
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) :
184.55
MB (
184.55
MB /
0
B)
Indexes dropped :
0
SQL statements verified :
2
SQL statements improved (improvement factor) :
1
(
44119.6
x)
SQL plan baselines created :
0
Overall improvement factor :
25135.8
x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes :
0
Space used :
0
B
Unusable indexes :
0
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
*: invisible
-------------------------------------------------------------------------------
---------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
---------------------------------------------------------------------------------
| BOWIE | BIG_BOWIE
1
| SYS_AI_
2
zt
7
rg
40
mxa
4
n | TOTAL_SALES | B-TREE | NONE |
---------------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : chwm
2
gubm
8
fx
9
SQL Text : SELECT * FROM big_bowie
1
WHERE total_sales =
42
Improvement Factor :
44119.6
x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s):
4387193
1173
CPU Time (s):
2599423
1037
Buffer Gets:
749507
22
Optimizer Cost:
643
22
Disk Reads:
470976
2
Direct Writes:
0
0
Rows Processed:
323
19
Executions:
17
1
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value :
2468051548
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------
|
0
| SELECT STATEMENT | | | |
643
| |
|
1
| PARTITION RANGE ALL | |
20
|
520
|
643
|
00:
00:
01
|
|
2
| TABLE ACCESS STORAGE FULL | BIG_BOWIE
1
|
20
|
520
|
643
|
00:
00:
01
|
-----------------------------------------------------------------------------------
Notes
-----
- dop =
1
- px_in_memory_imc = no
- px_in_memory = no
- With Auto Indexes
-----------------------------
Plan Hash Value :
937174207
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
19
|
494
|
22
|
00:
00:
01
|
|
1
| TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | BIG_BOWIE
1
|
19
|
494
|
22
|
00:
00:
01
|
| *
2
| INDEX RANGE SCAN | SYS_AI_
2
zt
7
rg
40
mxa
4
n |
19
| |
3
|
00:
00:
01
|
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
*
2
- access(
"TOTAL_SALES"
=
42
)
Notes
-----
- Dynamic sampling used for this statement (
level
=
11
)
We notice a couple of interesting points.
Firstly, yes Automatic Indexing has created an index based on the TOTAL_SALES column (SYS_AI_2zt7rg40mxa4n) as it improves performance by a reported 44119.6x.
Note also that the Automatic Index is a Non-Partitioned (Global) Index. From a performance perspective, this is the most efficient index to create to improve the performance of this query as the CBO only has the one index structure to navigate (vs. a LOCAL index that would require having to navigate down all 8 index structures for each table partition.
If we look at the index details:
SQL> SELECT index_name, partitioned,
auto
, visibility, status FROM user_indexes
WHERE table_name =
'BIG_BOWIE1'
;
INDEX_NAME PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_
2
zt
7
rg
40
mxa
4
n NO YES VISIBLE VALID
We notice that this is indeed a Non-Partitioned Index, that is both VISIBLE and VALID and so can be potentially used by any database session.
If we now re-run the query:
SQL> SELECT * FROM big_bowie
1
WHERE total_sales =
42
;
19
rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value:
937174207
-----------------------------------------------------------------------------------------------------------------------------------
| 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
1
|
20
|
520
|
23
(
0
)|
00:
00:
01
| ROWID | ROWID |
|*
2
| INDEX RANGE SCAN | SYS_AI_
2
zt
7
rg
40
mxa
4
n |
20
| |
3
(
0
)|
00:
00:
01
| | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2
- access(
"TOTAL_SALES"
=
42
)
Note
-----
- automatic DOP: Computed Degree of Parallelism is
1
Statistics
----------------------------------------------------------
0
recursive calls
0
db
block
gets
23
consistent gets
0
physical reads
0
redo size
1166
bytes sent via SQL*Net to client
369
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
19
rows processed
We can see the query now uses the newly created Automatic Index and is indeed more efficient, performing now just 23 consistent gets (previously 44014 consistent gets).
However, this was NOT previous behaviour.
The documentation previously mentioned that only LOCAL indexes are used when indexing partitioned tables.
If we run the same demo on Oracle Database 19.3, we get the following report:
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start :
14
-OCT
-2020
13:
12:
07
Activity end :
14
-OCT
-2020
14:
24:
07
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) :
192.94
MB (
192.94
MB /
0
B)
Indexes dropped :
0
SQL statements verified :
1
SQL statements improved (improvement factor) :
1
(
1950.5
x)
SQL plan baselines created :
0
Overall improvement factor :
1950.5
x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes :
0
Space used :
0
B
Unusable indexes :
0
-------------------------------------------------------------------------------
INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
*: invisible
-------------------------------------------------------------------------------
---------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
---------------------------------------------------------------------------------
| BOWIE | BIG_BOWIE
1
| SYS_AI_
8
armv
0
hqq
73
fa | TOTAL_SALES | B-TREE | LOCAL |
---------------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID :
2
pp
8
ypramw
30
s
SQL Text : SELECT * FROM big_bowie
1
WHERE total_sales =
42
Improvement Factor :
1950.5
x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s):
6996973
27327
CPU Time (s):
6704215
12819
Buffer Gets:
815306
49
Optimizer Cost:
12793
28
Disk Reads:
2
40
Direct Writes:
0
0
Rows Processed:
475
25
Executions:
19
1
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value :
4294056405
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------
|
0
| SELECT STATEMENT | | | |
12793
| |
|
1
| PARTITION RANGE ALL | |
20
|
520
|
12793
|
00:
00:
01
|
|
2
| TABLE ACCESS FULL | BIG_BOWIE
1
|
20
|
520
|
12793
|
00:
00:
01
|
-----------------------------------------------------------------------------
- With Auto Indexes
-----------------------------
Plan Hash Value :
3781269341
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
25
|
650
|
28
|
00:
00:
01
|
|
1
| PARTITION RANGE ALL | |
25
|
650
|
28
|
00:
00:
01
|
|
2
| TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_BOWIE
1
|
25
|
650
|
28
|
00:
00:
01
|
| *
3
| INDEX RANGE SCAN | SYS_AI_
8
armv
0
hqq
73
fa |
25
| |
17
|
00:
00:
01
|
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
*
3
- access(
"TOTAL_SALES"
=
42
)
Notes
-----
- Dynamic sampling used for this statement (
level
=
11
)
As we can see, in this scenario, the newly created Automatic Index has a “Property” of LOCAL.
If we look at its index details:
SQL> SELECT index_name, partitioned,
auto
, visibility, status FROM user_indexes
WHERE table_name =
'BIG_BOWIE1'
;
INDEX_NAME PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_
8
armv
0
hqq
73
fa YES YES VISIBLE N/A
SQL> SELECT index_name, partitioning_type, partition_count, locality FROM user_part_indexes
WHERE table_name =
'BIG_BOWIE1'
;
INDEX_NAME PARTITION PARTITION_COUNT LOCALI
------------------------------ --------- --------------- ------
SYS_AI_
8
armv
0
hqq
73
fa RANGE
8
LOCAL
We can see how a Local Index was previously created.
As such if we re-run an equivalent query:
SQL> SELECT * FROM big_bowie
1
WHERE total_sales =
42
;
25
rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value:
3781269341
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
20
|
520
|
26
(
0
)|
00:
00:
01
| | |
|
1
| PARTITION RANGE ALL | |
20
|
520
|
26
(
0
)|
00:
00:
01
|
1
|
8
|
|
2
| TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| BIG_BOWIE
1
|
20
|
520
|
26
(
0
)|
00:
00:
01
|
1
|
8
|
|*
3
| INDEX RANGE SCAN | SYS_AI_
8
armv
0
hqq
73
fa |
20
| |
17
(
0
)|
00:
00:
01
|
1
|
8
|
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3
- access(
"TOTAL_SALES"
=
42
)
Statistics
----------------------------------------------------------
0
recursive calls
0
db
block
gets
50
consistent gets
0
physical reads
0
redo size
1555
bytes sent via SQL*Net to client
409
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
25
rows processed
Although the query is returning 6 more rows (as with the random number generation, has a slightly different data set), it’s more expensive proportionally now having to perform 50 consistent gets as it now has to read 8 index structures rather than just the one.
So (IMHO), Automatic Indexing has improved here, creating a more efficient index structure than previously. So always bear in mind that Automatic Indexing is an evolving beast, improving and adapting as time moves on.
However, note the compromise here is that by having an effectively Global index structure, there may be some additional issues depending on any subsequent structural changes to the table.
More on Automatic Indexing and Partitioning in my next post…
Comments»
No comments yet — be the first.
Leave a Reply Cancel reply
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK