Oracle 19c Automatic Indexing: Indexing Partitioned Tables Part II (Neighbourhoo...
source link: https://richardfoote.wordpress.com/2021/01/13/oracle-19c-automatic-indexing-indexing-partitioned-tables-part-ii-neighbourhood-threat/
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 II (Neighbourhood Threat) January 13, 2021
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Transaction Processing, CBO, Exadata, Local Indexes, Oracle, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Partitioned Indexes, Partitioning.
trackback
In my first post on Automatic Indexing on Partitioned Tables, I discussed how Automatic Indexing (AI) can now create a Non-Partitioned index if deemed the most effective indexing structure (this wasn’t supported when AI was initially released). A Non-Partitioned index is indeed likely the most efficient indexing structure if the underlying table has many partitions and associated SQL equality predicates only reference non-partition key columns. A Non-Partitioned index ensure Oracle only needs to scan the single index structure and not all the partitions of a Local index.
But what if SQLs do reference the column by which the underlying table is partitioned?
The following SQL has an equality filtering predicate on the RELEASE_DATE column, the column by which the BIG_BOWIE1 table is partitioned:
SQL> SELECT * FROM big_bowie1 where release_date = to_date('2013-12-30 22:15:25', 'syyyy-mm-dd hh24:mi:ss');no rows selectedIf we look at the subsequent AI report:
INDEX DETAILS-------------------------------------------------------------------------------The following indexes were created:*: invisible-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Owner | Table | Index | Key | Type | Properties |------------------------------------------------------------------------------------------------| BOWIE | BIG_BOWIE1 | SYS_AI_14gpurjp8m76s | RELEASE_DATE | B-TREE | LOCAL |-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------We notice that Automatic Indexing has in this instance created a Local Index.
If we look further down the AI report:
-------------------------------------------------------------------------------Parsing Schema Name : BOWIESQL ID : 4mm3mbkk38pa8SQL Text : SELECT * FROM big_bowie1 where release_date = to_date('2013-12-30 22:15:25', 'syyyy-mm-dd hh24:mi:ss')Improvement Factor : 8339xExecution Statistics:-----------------------------Original Plan Auto Index Plan---------------------------- ----------------------------Elapsed Time (s): 146957 71CPU Time (s): 146124 71Buffer Gets: 16678 3Optimizer Cost: 162 4Disk Reads: 0 0Direct Writes: 0 0Rows Processed: 0 0Executions: 2 1PLANS SECTION---------------------------------------------------------------------------------------------- Original-----------------------------Plan Hash Value : 4031749531-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 162 | || 1 | PARTITION RANGE SINGLE | | 3602 | 93652 | 162 | 00:00:01 || 2 | TABLE ACCESS STORAGE FULL | BIG_BOWIE1 | 3602 | 93652 | 162 | 00:00:01 |-----------------------------------------------------------------------------------Notes------ dop = 1- px_in_memory_imc = no- px_in_memory = no- With Auto Indexes-----------------------------Plan Hash Value : 4049653350--------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | Time |--------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 78 | 4 | 00:00:01 || 1 | PARTITION RANGE SINGLE | | 3 | 78 | 4 | 00:00:01 || 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_BOWIE1 | 3 | 78 | 4 | 00:00:01 || * 3 | INDEX RANGE SCAN | SYS_AI_14gpurjp8m76s | 1 | | 3 | 00:00:01 |--------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):------------------------------------------* 3 - access("RELEASE_DATE"=TO_DATE(' 2013-12-30 22:15:25', 'syyyy-mm-dd hh24:mi:ss'))Notes------ Dynamic sampling used for this statement ( level = 11 )We can see Automatic Indexing has created the index because it provides an average Improvement Factor of 8339x. As the necessary indexed column(s) matches the table partitioning key, it makes sense for the associated index be a Local index as Oracle is certain which specific index partition to visit based on the value of the equality predicate.
If we look at the details of this new AI:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factorfrom user_indexes where table_name='BIG_BOWIE1';INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR------------------------------ --- --- --------- ------------- -------- ---------- ----------- -----------------SYS_AI_14gpurjp8m76s YES NO VISIBLE ADVANCED LOW N/A 20000000 30742 19941449SYS_AI_8armv0hqq73fa YES NO VISIBLE ADVANCED LOW VALID 20000000 42697 19995451SQL> select index_name, column_name, column_position from user_ind_columns where table_name='BIG_BOWIE1' order by index_name, column_position;INDEX_NAME COLUMN_NAME COLUMN_POSITION------------------------------ --------------- ---------------SYS_AI_14gpurjp8m76s RELEASE_DATE 1SYS_AI_8armv0hqq73fa TOTAL_SALES 1SQL> 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_14gpurjp8m76s RANGE 8 LOCALSQL> select index_name, partition_name, status, compression from user_ind_partitionswhere index_name in (select index_name from user_indexes where table_name='BIG_BOWIE1')order by partition_position;INDEX_NAME PARTITION_NAME STATUS COMPRESSION-------------------- -------------------- -------- -------------SYS_AI_14gpurjp8m76s ALBUMS_2013 USABLE ADVANCED LOWSYS_AI_14gpurjp8m76s ALBUMS_2014 USABLE ADVANCED LOWSYS_AI_14gpurjp8m76s ALBUMS_2015 USABLE ADVANCED LOWSYS_AI_14gpurjp8m76s ALBUMS_2016 USABLE ADVANCED LOWSYS_AI_14gpurjp8m76s ALBUMS_2017 USABLE ADVANCED LOWSYS_AI_14gpurjp8m76s ALBUMS_2018 USABLE ADVANCED LOWSYS_AI_14gpurjp8m76s ALBUMS_2019 USABLE ADVANCED LOWSYS_AI_14gpurjp8m76s ALBUMS_2020 USABLE ADVANCED LOWWe can see that indeed, a Visible, Usable, Local index was created by Automatic Indexing.
So depending on the column(s) within the index, Automatic Indexing can potentially create either a Local or Non-Partitioned index when indexing a partitioned table.
Comments»
1. Oracle 19c Automatic Indexing: Non-Equality Predicates Part I (Lucy Can’t Dance) | Richard Foote's Oracle Blog -
January 14, 2021
[…] as I discussed in the previous series on Automatic Indexing on Partitioned tables, we already saw how Oracle can actually also create Non-Partitioned (Global) indexes. So the […]
Liked by 1 person
2. Rajeshwaran Jeyabal -
January 25, 2021
Richard,
Thanks for the nice demo.
But looking at this output, the AI is not only partitioned, but also compressed automatically.
is that due to the Auto Index Compression turned ON at this database ? It is worth to note that in one of your older post(https://richardfoote.wordpress.com/2019/07/29/oracle-19c-automatic-indexing-configuration-all-i-need/) , you mentioned that this compression is not documented.
EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_COMPRESSION’,’ON’);
SQL> select index_name, partition_name, status, compression from user_ind_partitions
where index_name in (select index_name from user_indexes where table_name='BIG_BOWIE1')
order by partition_position;
INDEX_NAME PARTITION_NAME STATUS COMPRESSION
-------------------- -------------------- -------- -------------
SYS_AI_14gpurjp8m76s ALBUMS_2013 USABLE ADVANCED LOW
SYS_AI_14gpurjp8m76s ALBUMS_2014 USABLE ADVANCED LOW
SYS_AI_14gpurjp8m76s ALBUMS_2015 USABLE ADVANCED LOW
SYS_AI_14gpurjp8m76s ALBUMS_2016 USABLE ADVANCED LOW
SYS_AI_14gpurjp8m76s ALBUMS_2017 USABLE ADVANCED LOW
SYS_AI_14gpurjp8m76s ALBUMS_2018 USABLE ADVANCED LOW
SYS_AI_14gpurjp8m76s ALBUMS_2019 USABLE ADVANCED LOW
SYS_AI_14gpurjp8m76s ALBUMS_2020 USABLE ADVANCED LOW
Liked by 2 people
Richard Foote -
January 27, 2021
Hi Rajeshwaran
Yes, this has changed. With the ATP Autonomous Databases (at least), Advanced Compression Low is now the default setting for Automatic Indexes. I have on my to do list to update this with a new blog article. Will try and complete within the next few days.
3. Oracle Database 19c Automatic Indexing: Index Compression Update (New Morning) | Richard Foote's Oracle Blog -
January 27, 2021
[…] was reminded in a recent comment by Rajeshwaran Jeyabal that I hadn’t updated my post on Automatic Indexing with Advanced Compression that’s in […]
Leave a Reply Cancel reply
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK