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.trackback
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…
[…] a few hours of publishing my last blog piece on how Automatic Indexing (AI) can automatically rebuild indexes that have been placed in an […]
LikeLike
[…] Richard Foote – trivia: looks nothing like David Bowie. Suggested post: Automatically rebuilding unusable indexes (Autonomous Oracle Database) […]
LikeLike