Automatic Indexes: Automatically Rebuild Unusable Indexes Part I (“Andy Warhol”)

Obviously, the main feature of Automatic Indexing (AI) is for Oracle to automatically create indexes, that have been proven to improve performance, in a relatively safe and timely manner.

However, another nice and useful capability is for AI to automatically rebuild indexes that are placed in an “Unusable” state.

The documentation states that:

“Automatic indexing provides the following functionality:

Rebuilds the indexes that are marked unusable due to table partitioning maintenance operations, such as ALTER TABLE MOVE.”

Now, when AI was initially released, I was unable to get this rebuild capability to work as advertised. I don’t know whether this was because the capability had not yet been successfully implemented or because of some failings in my testing.

However, with both the current versions of Oracle Database 19c (19.15.0.1.0 as now implemented in Autonomous Databases) and Oracle Database 21c, the following demo now works successfully.

Let’s begin by creating a simple partitioned table:

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_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 (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
 PARTITION ALBUMS_2022 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.

We next run a number of SQL statements such as the following:

SQL> SELECT * FROM big_bowie WHERE total_sales = 123456;

19 rows selected.

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

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

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

2 - storage("TOTAL_SALES"=123456)
    filter("TOTAL_SALES"=123456)

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
      49573 consistent gets
      42778 physical reads
          0 redo size
       1423 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)
         19 rows processed

If we wait for the AI task to kick in, we notice is has successfully created an associated automatic index:

SQL> SELECT index_name, partitioned, auto, visibility, status FROM user_indexes WHERE table_name = 'BIG_BOWIE';

INDEX_NAME                     PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_17cd4101fvrk1           NO  YES VISIBLE   VALID

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

INDEX_NAME                     COLUMN_NAME     COLUMN_POSITION
------------------------------ --------------- ---------------
SYS_AI_17cd4101fvrk1           TOTAL_SALES                   1

As discussed previously, AI can now create a non-partitioned, Global index if deemed more efficient than a corresponding Local index.

Note that the newly created automatic index is currently VALID.

However, if we re-organise a partition within the table without using the Online clause:

SQL> alter table big_bowie move partition albums_2017;

Table altered.

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

INDEX_NAME                     PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_17cd4101fvrk1           NO  YES VISIBLE   UNUSABLE

The index as a result goes into an UNUSABLE state.

Running similar queries from this point will result in a FTS again:

SQL> select * from big_bowie where total_sales=42;

22 rows selected.

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

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

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

2 - storage("TOTAL_SALES"=123456)
    filter("TOTAL_SALES"=123456)

Statistics
----------------------------------------------------------
          126 recursive calls
            0 db block gets
        48962 consistent gets
        42799 physical reads
            0 redo size
         1497 bytes sent via SQL*Net to client
           52 bytes received via SQL*Net from client
            2 SQL*Net roundtrips to/from client
           17 sorts (memory)
            0 sorts (disk)
           22 rows processed

If we now wait until the next AI task period and check out the index:

SQL> SELECT index_name, partitioned, auto, visibility, status FROM user_indexes WHERE table_name = 'BIG_BOWIE';

INDEX_NAME                     PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_17cd4101fvrk1           NO  YES VISIBLE   VALID

We notice the index is now back in a VALID state again.

 

Checking out the date attributes of the index confirms the index has indeed been rebuilt:

SQL> select object_name, to_char(created, 'dd-Mon-yy hh24:mi:ss') created, to_char(last_ddl_time, 'dd-Mon-yyhh24:mi:ss’)
last_ddl_time from dba_objects where object_name='SYS_AI_17cd4101fvrk1';

OBJECT_NAME                    CREATED                     LAST_DDL_TIME
------------------------------ --------------------------- ---------------------------
SYS_AI_17cd4101fvrk1           18-Apr-22 11:59:36          18-Apr-22 18:37:42

Being in a VALID state again, the CBO can now use the automatic index:

SQL> select * from big_bowie where total_sales=42;

22 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 920768077

-----------------------------------------------------------------------------------------------------------------------------------
| 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            |   20 |   520 |      23 (0) | 00:00:01 | ROWID | ROWID |
|* 2 |   INDEX RANGE SCAN                          | SYS_AI_17cd4101fvrk1 |   20 |       |       3 (0) | 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------

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

2 - access("TOTAL_SALES"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      48711 consistent gets
      42799 physical reads
          0 redo size
       1497 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)
         22 rows processed

Note: This scenario works the same if the table is Non-Partitioned.

In my next post, I’ll discuss a scenario where the automatic rebuild of an Unusable index will currently NOT work…