jump to navigation

Automatic Indexes: Automatically Rebuild Unusable Indexes Part II (“I Wish You Would”) May 11, 2022

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Full Table Scans, Local Indexes, Oracle, Oracle Blog, Oracle Cloud, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle19c, Partitioned Indexes, Partitioning, Performance Tuning, Rebuild Unusable Indexes.
1 comment so far

Within a few hours of publishing my last blog piece on how Automatic Indexing (AI) can automatically rebuild indexes that have been placed in an UNUSABLE state, I was asked by a couple of readers a similar question: “Does this also work if just a single partition of an partitioned index becomes unusable”?

My answer to them both is that I’ve provided them the basic framework in the demo to check out the answer to that question for themselves (Note: a fantastic aspect of working with the Oracle Database is that it’s available for free to play around with, including the Autonomous Database environments).

But based on the principle that for every time someone asks a question, there’s probably a 100 others who potentially might be wondering the same thing, thought I’ll quickly whip up a demo to answer this for all.

I’ll begin with the same table format and data as my previous blog:

SQL> CREATE TABLE big_ziggy(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_ziggy 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_ZIGGY');

PL/SQL procedure successfully completed.

 

But this time, I’ll run a number of queries similar to the following, that also has a predicate based on the partitioned key (RELEASE_DATE) of the table:

SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3599046327

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

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

2 - storage(("TOTAL_SALES"=123456 AND "RELEASE_DATE"=TO_DATE('2017-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
    filter(("TOTAL_SALES"=123456 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
       5618 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

 

If we wait for the next AI task to kick in:

DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY()
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start              : 11-MAY-2022 10:55:43
Activity end                : 11-MAY-2022 10:56:27
Executions completed        : 1
Executions interrupted      : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                             : 0
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                      : 6
SQL statements improved (improvement factor) : 3 (6670.1x)
SQL plan baselines created                   : 0
Overall improvement factor                   : 2x
-------------------------------------------------------------------------------

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

INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------
| Owner | Table     | Index                | Key                      | Type   | Properties |
---------------------------------------------------------------------------------------------
| BOWIE | BIG_ZIGGY | SYS_AI_6wv99zdbsy8ar | RELEASE_DATE,TOTAL_SALES | B-TREE | LOCAL      |
---------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------

 

We can see that AI has indeed automatically created a LOCAL, partitioned index (on columns RELEASE_DATE, TOTAL_SALES) in this scenario, as we have an equality predicate based on the partitioned key (RELEASE_DATE).

Currently, all is well with the index, with all partitions in a USABLE state:

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

INDEX_NAME                     PAR AUT VISIBILIT STATUS
------------------------------ --- --- --------- --------
SYS_AI_6wv99zdbsy8ar           YES YES VISIBLE   N/A

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

INDEX_NAME                     PARTITION_NAME       STATUS
------------------------------ -------------------- --------
SYS_AI_6wv99zdbsy8ar           ALBUMS_2015          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2016          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2017          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2018          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2019          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2020          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2021          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2022          USABLE

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

INDEX_NAME                     COLUMN_NAME     COLUMN_POSITION
------------------------------ --------------- ---------------
SYS_AI_6wv99zdbsy8ar           RELEASE_DATE                  1
SYS_AI_6wv99zdbsy8ar           TOTAL_SALES                   2

 

But if we now do an offline reorg of a specific table partition:

SQL> alter table big_ziggy move partition albums_2017;

Table altered.

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

INDEX_NAME                     PARTITION_NAME       STATUS
------------------------------ -------------------- --------
SYS_AI_6wv99zdbsy8ar           ALBUMS_2015          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2016          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2017          UNUSABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2018          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2019          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2020          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2021          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2022          USABLE

 

We can see we’ve now made the associated Local Index partition UNUSABLE.

If we run the following query:

SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3599046327

----------------------------------------------------------------------------------------------------
| 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 |      3 |     3 |
|* 2 |  TABLE ACCESS FULL     | BIG_ZIGGY |    1 |    26 |     986 (2) | 00:00:01 |      3 |     3 |
----------------------------------------------------------------------------------------------------

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

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

Statistics
----------------------------------------------------------
          3 recursive calls
          4 db block gets
       5578 consistent gets
       5571 physical reads
        924 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

The CBO has no choice here but to do a full partition table scan.

If now wait again for the next AI task to strut its stuff:

SQL> select dbms_auto_index.report_last_activity() from dual;

DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY()
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start              : 11-MAY-2022 11:42:42
Activity end                : 11-MAY-2022 11:43:13
Executions completed        : 1
Executions interrupted      : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates                             : 0
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                      : 4
SQL statements improved (improvement factor) : 1 (5573x)
SQL plan baselines created                   : 0
Overall improvement factor                   : 1.1x
-------------------------------------------------------------------------------

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

INDEX DETAILS
-------------------------------------------------------------------------------
The following indexes were created:
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------
| Owner | Table     | Index                | Key                      | Type   | Properties |
---------------------------------------------------------------------------------------------
| BOWIE | BIG_ZIGGY | SYS_AI_6wv99zdbsy8ar | RELEASE_DATE,TOTAL_SALES | B-TREE | LOCAL      |
---------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------


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

INDEX_NAME                     PARTITION_NAME       STATUS
------------------------------ -------------------- --------
SYS_AI_6wv99zdbsy8ar           ALBUMS_2015          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2016          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2017          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2018          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2019          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2020          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2021          USABLE
SYS_AI_6wv99zdbsy8ar           ALBUMS_2022          USABLE

The index partition is now automatically in a USABLE state again.

If we look at the index object data:

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

OBJECT_NAME                    SUBOBJECT_NAME       CREATED                     LAST_DDL_TIME
------------------------------ -------------------- --------------------------- ---------------------------
SYS_AI_6wv99zdbsy8ar           ALBUMS_2015          11-May-22 10:41:33          11-May-22 10:56:14
SYS_AI_6wv99zdbsy8ar           ALBUMS_2016          11-May-22 10:41:33          11-May-22 10:56:15
SYS_AI_6wv99zdbsy8ar           ALBUMS_2017          11-May-22 10:41:33          11-May-22 11:42:42
SYS_AI_6wv99zdbsy8ar           ALBUMS_2018          11-May-22 10:41:33          11-May-22 10:56:18
SYS_AI_6wv99zdbsy8ar           ALBUMS_2019          11-May-22 10:41:33          11-May-22 10:56:19
SYS_AI_6wv99zdbsy8ar           ALBUMS_2020          11-May-22 10:41:33          11-May-22 10:56:20
SYS_AI_6wv99zdbsy8ar           ALBUMS_2021          11-May-22 10:41:33          11-May-22 10:56:22
SYS_AI_6wv99zdbsy8ar           ALBUMS_2022          11-May-22 10:41:33          11-May-22 10:56:22
SYS_AI_6wv99zdbsy8ar                                11-May-22 10:41:33          11-May-22 11:43:13

 

We can see that just the impacted index partition has been rebuilt.

The CBO can now successfully use the index to avoid the full partition table scan:

SQL> select * FROM big_ziggy where release_date = '01-JUN-2017' and total_sales = 123456;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3640710173

-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                  | Name                 | Rows | Bytes | Cost (%CPU)| Time     | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                           |                      |    1 |    26 |      4 (0) | 00:00:01 |        |       |
|  1 | PARTITION RANGE SINGLE                     |                      |    1 |    26 |      4 (0) | 00:00:01 |      3 |     3 |
|  2 |  TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | BIG_ZIGGY            |    1 |    26 |      4 (0) | 00:00:01 |      3 |     3 |
|* 3 |   INDEX RANGE SCAN                         | SYS_AI_6wv99zdbsy8ar |    1 |       |      3 (0) | 00:00:01 |      3 |     3 |
-----------------------------------------------------------------------------------------------------------------------------------

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"=123456)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          3 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

 

I’ll leave it to the discernible reader to determine if this also works in the scenario where the partitioned index were to be global… 🙂

Automatic Indexes: Automatically Rebuild Unusable Indexes Part I (“Andy Warhol”) May 10, 2022

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Database, Autonomous Transaction Processing, CBO, Exadata, Oracle, Oracle Cloud, Oracle General, Oracle Indexes, Oracle19c, Rebuild Unusable Indexes.
2 comments

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…