jump to navigation

Automatic Indexes: Automatically Rebuild Unusable Indexes Part IV (“Nothing Has Changed”) May 31, 2022

Posted by Richard Foote in 19c, 19c New Features, 21c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, Index Column Order, Index Internals, Local Indexes, Mixing Auto and Manual Indexes, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Indexing Internals Webinar, Oracle19c, Unusable Indexes.
trackback

In a previous post, I discussed how Automatic Indexing (AI) does not automatically rebuild a manually built index that is in an Unusable state (but will rebuild an Unusable automatically created index).

The demo I used was a simple one, based on manually created indexes referencing a non-partitioned table.

In this post, I’m going to use a demo based on manually created indexes referencing a partitioned table.

I’ll start by creating a rather basic range-based partitioned table, using the RELEASE_DATE column to partition the data by year:

SQL> CREATE TABLE big_bowie (id number, album_id number, country_id number, release_date date,
total_sales number) PARTITION BY RANGE (release_date)
(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 (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2021 VALUES LESS THAN (MAXVALUE));

Table created.

SQL> INSERT INTO big_bowie 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.

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE');

PL/SQL procedure successfully completed.

I’ll next manually create a couple indexes; a non-partitioned index based on just the ALBUM_ID column and a prefixed locally partitioned index, based on the columns RELEASE_DATE, TOTAL_SALES:

 

SQL> create index album_id_i on big_bowie(album_id);

Index created.

SQL> create index release_date_total_sales_i on big_bowie(release_date, total_sales) local;

Index created.

 

If we now re-organise just partition ALBUMS_2017 (without using the ONLINE clause):

SQL> alter table big_bowie move partition albums_2017;

Table altered.

This results in the non-partitioned index and the ALBUMS_2017 local index partition becoming Unusable:

SQL> select index_name, status from user_indexes where table_name='BIG_BOWIE';

INDEX_NAME                     STATUS
------------------------------ --------
ALBUM_ID_I                     UNUSABLE
RELEASE_DATE_TOTAL_SALES_I     N/A

SQL> select index_name, partition_name, status from user_ind_partitions
     where index_name='RELEASE_DATE_TOTAL_SALES_I';

INDEX_NAME                     PARTITION_NAME       STATUS
------------------------------ -------------------- --------
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2014          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2015          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2016          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2017          UNUSABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2018          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2019          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2020          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2021          USABLE

Let’s now run a number of queries a number of times. The first series is based on a predicate on just the ALBUM_ID column, such as:

SQL> select * from big_bowie where album_id=42;

2000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1510748290

-------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows | Bytes | Cost (%CPU) | Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           | 2000 | 52000 |    7959 (2) | 00:00:01 |       |       |
|   1 | PARTITION RANGE ALL |           | 2000 | 52000 |    7959 (2) | 00:00:01 |     1 |     8 |
| * 2 |  TABLE ACCESS FULL  | BIG_BOWIE | 2000 | 52000 |    7959 (2) | 00:00:01 |     1 |     8 |
-------------------------------------------------------------------------------------------------

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

2 - storage("ALBUM_ID"=42)
  - filter("ALBUM_ID"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      48593 consistent gets
      42881 physical reads
          0 redo size
      44289 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       2000 rows processed

We’ll also run a series of queries based on both the RELEASE_DATE column using dates from the unusable index partition and the TOTAL_SALES column, such as:

SQL> select * from big_bowie where release_date='01-JUN-2017' and total_sales=42;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3245457041

----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows | Bytes | Cost (%CPU) | Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |    1 |    26 |     986 (2) | 00:00:01 |       |       |
|   1 | PARTITION RANGE SINGLE |           |    1 |    26 |     986 (2) | 00:00:01 |     4 |     4 |
| * 2 |  TABLE ACCESS FULL     | BIG_BOWIE |    1 |    26 |     986 (2) | 00:00:01 |     4 |     4 |
----------------------------------------------------------------------------------------------------

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

2 - storage("TOTAL_SALES"=42 AND "RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
   - filter("TOTAL_SALES"=42 AND "RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))

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

Without a valid/usable index, the CBO currently has no choice but to use a Full Table Scan on the first query, and a Full Partition Scan on the partition with the unusable local index.

So what does AI make of things? Does it rebuild the unusable manually created indexes so the associated indexes can be used to improve these queries?

If we wait until the next AI task completes and check out the indexes on the table:

SQL> select index_name, status, partitioned from user_indexes where table_name='BIG_BOWIE';

INDEX_NAME                     STATUS   PAR
------------------------------ -------- ---
RELEASE_DATE_TOTAL_SALES_I     N/A      YES
ALBUM_ID_I                     UNUSABLE NO
SYS_AI_aw2825ffpus5s           VALID    NO
SYS_AI_2hf33fpvnqztw           VALID    NO

SQL> select index_name, partition_name, status from user_ind_partitions
     where index_name='RELEASE_DATE_TOTAL_SALES_I';

INDEX_NAME                     PARTITION_NAME       STATUS
------------------------------ -------------------- --------
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2014          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2015          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2016          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2017          UNUSABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2018          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2019          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2020          USABLE
RELEASE_DATE_TOTAL_SALES_I     ALBUMS_2021          USABLE

We notice that AI has created two new non-partitioned automatic indexes, while both the manually created indexes remain in the same unusable state. If we look at the columns associated with these new automatic indexes:

SQL> select index_name, column_name, column_position
from user_ind_columns where table_name='BIG_BOWIE';

INDEX_NAME                     COLUMN_NAME          COLUMN_POSITION
------------------------------ -------------------- ---------------
ALBUM_ID_I                     ALBUM_ID                           1
RELEASE_DATE_TOTAL_SALES_I     RELEASE_DATE                       1
RELEASE_DATE_TOTAL_SALES_I     TOTAL_SALES                        2
SYS_AI_aw2825ffpus5s           ALBUM_ID                           1
SYS_AI_aw2825ffpus5s           RELEASE_DATE                       2
SYS_AI_2hf33fpvnqztw           TOTAL_SALES                        1
SYS_AI_2hf33fpvnqztw           RELEASE_DATE                       2

As we can see, AI has logically replaced both unusable indexes.

The manual index based on ALBUM_ID has been replaced with an inferior index based on the ALBUM_ID, RELEASE_DATE columns. Inferior in that the automatic index is both redundant (if only the manual index on ALBUM_ID were rebuilt) and in that it has the logically unnecessary RELEASE_DATE column to inflate the size of the index.

The manual index based on the RELEASE_DATE, TOTAL_SALES columns has been replaced with a redundant automatic index based on the reversed TOTAL_SALES, RELEASE_DATE columns.

Now, AI has indeed automatically addressed the current FTS performance issues associated with these queries by creating these indexes, but a better remedy would have been to rebuild the unusable manual indexes and hence negate the need for these redundant automatic indexes.

But currently (including with version 21.3), AI will NOT rebuild unusable manually created indexes, no matter the scenario, and will instead create additional automatic indexes if it’s viable for it to do so.

A reason why Oracle at times recommends dropping all current manually created secondary indexes before implementing AI (although of course this comes with a range of obvious issues and concerns).

If these manually created indexes didn’t exist, I’ll leave it as an exercise to the discernable reader on what automatic indexes would have been created…

As always, this restriction may change in future releases…

Comments»

1. Rajeshwaran Jeyabal (@rajeshwaranj) - September 24, 2022

Richard,

without manual index, AI created one non-partitioned index for the predicate without partition keys and partitioned index for predicates involving partition keys, the below demo was from ATP database 21c (21.3)


ai_demo@ATP21C> set echo on linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000
ai_demo@ATP21C>
ai_demo@ATP21C> col column_name for a15
ai_demo@ATP21C> col index_name for a20
ai_demo@ATP21C> col owner for a10
ai_demo@ATP21C> col index_name for a25
ai_demo@ATP21C> col tablespace_name for a25
ai_demo@ATP21C> col table_name for a10
ai_demo@ATP21C> col report for a180
ai_demo@ATP21C> col INDEX_TYPE for a10
ai_demo@ATP21C>
ai_demo@ATP21C> CREATE TABLE big_bowie (id number, album_id number, country_id number, release_date date,
2 total_sales number) PARTITION BY RANGE (release_date)
3 (PARTITION ALBUMS_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')),
4 PARTITION ALBUMS_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016', 'DD-MON-YYYY')),
5 PARTITION ALBUMS_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),
6 PARTITION ALBUMS_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
7 PARTITION ALBUMS_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')),
8 PARTITION ALBUMS_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')),
9 PARTITION ALBUMS_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021', 'DD-MON-YYYY')),
10 PARTITION ALBUMS_2021 VALUES LESS THAN (MAXVALUE));

Table created.

ai_demo@ATP21C> INSERT /*+ append */ INTO big_bowie
2 SELECT rownum, mod(rownum,5000)+1, mod(rownum,100)+1, sysdate-mod(rownum,2800),
3 ceil(dbms_random.value(1,500000))
4 FROM all_objects a, all_objects b
5 where rownum commit;

Commit complete.

ai_demo@ATP21C> select index_name,index_type,indexing,auto,
2 status,visibility,partitioned
3 from all_indexes
4 where table_name = 'BIG_BOWIE'
5 and owner ='AI_DEMO';

no rows selected

ai_demo@ATP21C> exec dbms_stats.gather_table_stats(user,'BIG_BOWIE',granularity=>'all');

PL/SQL procedure successfully completed.

ai_demo@ATP21C> set feedback only
ai_demo@ATP21C> select * from big_bowie where album_id=42;

2000 rows selected.

ai_demo@ATP21C> select * from big_bowie where album_id=42;

2000 rows selected.

ai_demo@ATP21C> select * from big_bowie where album_id=42;

2000 rows selected.

ai_demo@ATP21C> select * from big_bowie where release_date = to_date('01-JUN-2017','dd-mon-yyyy') and total_sales=42;

no rows selected

ai_demo@ATP21C> select * from big_bowie where release_date = to_date('01-JUN-2017','dd-mon-yyyy') and total_sales=42;

no rows selected

ai_demo@ATP21C> select * from big_bowie where release_date = to_date('01-JUN-2017','dd-mon-yyyy') and total_sales=42;

no rows selected

ai_demo@ATP21C> set feedback 6
ai_demo@ATP21C>
ai_demo@ATP21C> select dbms_auto_index.report_activity(systimestamp -1/24, systimestamp,'TEXT','ALL','ALL') report from dual;

REPORT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 23-SEP-2022 14:00:15
Activity end : 23-SEP-2022 15:00:15
Executions completed : 4
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 2
Indexes created (visible / invisible) : 2 (2 / 0)
Space used (visible / invisible) : 368.05 MB (368.05 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 2
SQL statements improved (improvement factor) : 2 (25.2x)
SQL plan baselines created : 0
Overall improvement factor : 25.2x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------

INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
*: invisible
-------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
-----------------------------------------------------------------------------------------------
| AI_DEMO | BIG_BOWIE | SYS_AI_7tj531g2q5vc3 | RELEASE_DATE,TOTAL_SALES | B-TREE | LOCAL |
| AI_DEMO | BIG_BOWIE | SYS_AI_dm4m9v1kdbxj7 | ALBUM_ID | B-TREE | NONE |
-----------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : AI_DEMO
SQL ID : 7zf69kvt3gbs9
SQL Text : select * from big_bowie where release_date =
to_date('01-JUN-2017','dd-mon-yyyy') and total_sales=42
Improvement Factor : 5693x

Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 110774 1147
CPU Time (s): 108393 794
Buffer Gets: 17087 3
Optimizer Cost: 1582 4
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 0 0
Executions: 3 1

PLANS SECTION
---------------------------------------------------------------------------------------------

- Original
-----------------------------
Plan Hash Value : 3245457041

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1582 | |
| 1 | PARTITION RANGE SINGLE | | 1 | 26 | 1582 | 00:00:01 |
| 2 | TABLE ACCESS STORAGE FULL | BIG_BOWIE | 1 | 26 | 1582 | 00:00:01 |
----------------------------------------------------------------------------------

- With Auto Indexes
-----------------------------
Plan Hash Value : 676047501

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 | 00:00:01 |
| 1 | PARTITION RANGE SINGLE | | 1 | 26 | 4 | 00:00:01 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_BOWIE | 1 | 26 | 4 | 00:00:01 |
| * 3 | INDEX RANGE SCAN | SYS_AI_7tj531g2q5vc3 | 1 | | 3 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("RELEASE_DATE"=TO_DATE(' 2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TOTAL_SALES"=42)

Notes
-----
- Dynamic sampling used for this statement ( level = 11 )

-------------------------------------------------------------------------------
Parsing Schema Name : AI_DEMO
SQL ID : cazxpkyw5yap7
SQL Text : select * from big_bowie where album_id=42
Improvement Factor : 22.3x

Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 1252132 7701
CPU Time (s): 190857 5968
Buffer Gets: 131087 2008
Optimizer Cost: 12093 2007
Disk Reads: 130969 7
Direct Writes: 0 0
Rows Processed: 6000 2000
Executions: 3 1

PLANS SECTION
---------------------------------------------------------------------------------------------

- Original
-----------------------------
Plan Hash Value : 1510748290

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12093 | |
| 1 | PARTITION RANGE ALL | | 2068 | 53768 | 12093 | 00:00:01 |
| 2 | TABLE ACCESS STORAGE FULL | BIG_BOWIE | 2068 | 53768 | 12093 | 00:00:01 |
-----------------------------------------------------------------------------------

- With Auto Indexes
-----------------------------
Plan Hash Value : 3007959304

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2068 | 53768 | 2007 | 00:00:01 |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | BIG_BOWIE | 2068 | 53768 | 2007 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SYS_AI_dm4m9v1kdbxj7 | 2000 | | 7 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ALBUM_ID"=42)

Notes
-----
- Dynamic sampling used for this statement ( level = 11 )

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
ai_demo@ATP21C> select index_name,index_type,indexing,auto,
2 status,visibility,partitioned
3 from all_indexes
4 where table_name = 'BIG_BOWIE'
5 and owner ='AI_DEMO';

INDEX_NAME INDEX_TYPE INDEXIN AUT STATUS VISIBILIT PAR
------------------------- ---------- ------- --- -------- --------- ---
SYS_AI_dm4m9v1kdbxj7 NORMAL FULL YES VALID VISIBLE NO
SYS_AI_7tj531g2q5vc3 NORMAL FULL YES N/A VISIBLE YES

ai_demo@ATP21C> select index_name,column_name,column_position
2 from all_ind_columns
3 where index_name in ('SYS_AI_dm4m9v1kdbxj7','SYS_AI_7tj531g2q5vc3')
4 order by 1,3;

INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------- --------------- ---------------
SYS_AI_7tj531g2q5vc3 RELEASE_DATE 1
SYS_AI_7tj531g2q5vc3 TOTAL_SALES 2
SYS_AI_dm4m9v1kdbxj7 ALBUM_ID 1

ai_demo@ATP21C> select index_name,partition_name,partition_position,status,compression
2 from all_ind_partitions
3 where index_name ='SYS_AI_7tj531g2q5vc3'
4 order by partition_position ;

INDEX_NAME PARTITION_NAME PARTITION_POSITION STATUS COMPRESSION
------------------------- --------------- ------------------ -------- -------------
SYS_AI_7tj531g2q5vc3 ALBUMS_2014 1 USABLE ADVANCED LOW
SYS_AI_7tj531g2q5vc3 ALBUMS_2015 2 USABLE ADVANCED LOW
SYS_AI_7tj531g2q5vc3 ALBUMS_2016 3 USABLE ADVANCED LOW
SYS_AI_7tj531g2q5vc3 ALBUMS_2017 4 USABLE ADVANCED LOW
SYS_AI_7tj531g2q5vc3 ALBUMS_2018 5 USABLE ADVANCED LOW
SYS_AI_7tj531g2q5vc3 ALBUMS_2019 6 USABLE ADVANCED LOW
SYS_AI_7tj531g2q5vc3 ALBUMS_2020 7 USABLE ADVANCED LOW
SYS_AI_7tj531g2q5vc3 ALBUMS_2021 8 USABLE ADVANCED LOW

8 rows selected.

ai_demo@ATP21C>

Like


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 )

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: