jump to navigation

Oracle 19c Automatic Indexing: DDL Statements With Auto Indexes (No Control) September 1, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, Drop Automatic Indexing, Drop Index, Index Coalesce, Index Rebuild, Index Shrink, Invisible Indexes, Online DDL, Oracle Indexes.
2 comments

 

I’ve had a number of questions in relation to DDL support for Automatic Indexes since my last post on how one can now drop Automatic Indexes, so decided to quickly discuss what DDL statements are supported with Automatic Indexes.

Many DDL commands are NOT supported with Automatic Indexes, such as making indexes (IN)VISIBLE and (UN)USABLE and changing storage attributes:

 

SQL> alter index "SYS_AI_600vgjmtqsgv3" invisible;

alter index "SYS_AI_600vgjmtqsgv3" invisible
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes


SQL> alter index "SYS_AI_600vgjmtqsgv3" unusable;

alter index "SYS_AI_600vgjmtqsgv3" unusable
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes


SQL> ALTER INDEX "SYS_AI_600vgjmtqsgv3" INITRANS 5;
ALTER INDEX "SYS_AI_600vgjmtqsgv3" INITRANS 5
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

You also can’t drop indexes with the DDL statement:

SQL> drop index "SYS_AI_600vgjmtqsgv3";

drop index "SYS_AI_600vgjmtqsgv3"
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

 

Although as discussed in my last post, you can now drop Automatic Indexes by using DBMS_AUTO_INDEX.DROP_AUTO_INDEXES.

 

You can however potentially improve the structure of an Automatic Index by using the REBUILD, COALESCE or SHRINK (SPACE) options:

 

SQL> alter index "SYS_AI_600vgjmtqsgv3" rebuild online;

Index altered.

SQL> alter index "SYS_AI_600vgjmtqsgv3" coalesce;

Index altered.

SQL> alter index "SYS_AI_600vgjmtqsgv3" shrink space;

Index altered.

 

Interestingly, if Oracle considers an Automatic Index but decides it’s not efficient enough to be created, the Automatic Indexing process can leave a new Automatic Index in UNUSABLE / INVISIBLE state (as previously discussed), which can be subsequently rebuilt:

SQL> select index_name, status, visibility from user_indexes where index_name='SYS_AI_600vgjmtqsgv3';

INDEX_NAME                     STATUS   VISIBILIT
------------------------------ -------- ---------
SYS_AI_600vgjmtqsgv3           UNUSABLE INVISIBLE

SQL> alter index "SYS_AI_600vgjmtqsgv3" rebuild online;

Index altered.

SQL> select index_name, status, visibility from user_indexes where index_name='SYS_AI_600vgjmtqsgv3';

INDEX_NAME                     STATUS   VISIBILIT
------------------------------ -------- ---------
SYS_AI_600vgjmtqsgv3           VALID    INVISIBLE

 

So the index is now VALID and actually physically created. But you can’t subsequently make it VISIBLE, which means it can’t ordinarily be used by the CBO:

SQL> alter index "SYS_AI_600vgjmtqsgv3" visible;
alter index "SYS_AI_600vgjmtqsgv3" visible
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

 

When you rebuild an Automatic Index, you can however change the manner in which it’s compressed:

SQL> select index_name, status, visibility, compression from user_indexes
where index_name='SYS_AI_600vgjmtqsgv3';

INDEX_NAME                     STATUS   VISIBILIT COMPRESSION
------------------------------ -------- --------- -------------
SYS_AI_600vgjmtqsgv3           VALID    INVISIBLE ADVANCED LOW

SQL> alter index "SYS_AI_600vgjmtqsgv3" rebuild nocompress;

Index altered.

SQL> select index_name, status, visibility, compression from user_indexes
where index_name='SYS_AI_600vgjmtqsgv3';

INDEX_NAME                     STATUS   VISIBILIT COMPRESSION
------------------------------ -------- --------- -------------
SYS_AI_600vgjmtqsgv3           VALID    INVISIBLE DISABLED

 

And no, you can’t rename an Automatic Index:

 

SQL> alter index "SYS_AI_600vgjmtqsgv3" rename to BOWIE_INDEX;
alter index "SYS_AI_600vgjmtqsgv3" rename to BOWIE_INDEX
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

 

So the answer is it depends on what one can and can’t do currently with an Automatic Index, which of course is subject to change in the future…

Oracle 19c Automatic Indexing: Dropping Automatic Indexes Part II (New Angels of Promise) August 25, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Drop Automatic Indexing, Drop Index, DROP_AUTO_INDEXES.
3 comments

Just a quick update on a previous post on dropping Automatic Indexes.

As I discussed previously, one could drop Automatic Indexes by moving them to a new tablespace and then dropping the tablespace. This cumbersome technique was necessary because there was no direct facility to drop Automatic Indexes. Additionally, it’s worth noting this process isn’t viable in Autonomous Databases Cloud environments as it’s not possible to create/drop tablespaces.

As I’ve also discussed previously, there may be scenarios when you may want to drop an Automatic Index, such as when newly created Automatic Indexes don’t get used by the CBO in a manner as predicted by the Automatic Indexing process.

Thankfully, there is now (since Oracle Database 20c and back-ported to 19.5) an API option to easily drop Automatic Indexes when desired:

SQL> exec DBMS_AUTO_INDEX.DROP_AUTO_INDEXES(owner=>’BOWIE’, index_name=>'”SYS_AI_600vgjmtqsgv3″‘, allow_recreate=>true);

PL/SQL procedure successfully completed.

 

Note: the index name must be further enclosed in a ” “ due to the mixed-case naming convention of Automatic Indexes.

The last parameter controls whether or not you want to allow the Automatic Indexing process to subsequently potentially recreate the index again.  The default is false.

As I’ve mentioned a number of times in this series, Automatic Indexing is still a relatively new feature that will only improve and expand in future versions…

 

A big thanks to Nigel Bayliss for the heads-up on this new Auto Indexing feature.

Oracle 19c Automatic Indexing: Dropping Automatic Indexes (Fall Dog Bombs The Moon) May 12, 2020

Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Drop Index, Index Rebuild, Oracle Indexes.
3 comments

reality

 

Julian Dontcheff recently wrote a nice article on the new Automatic Index Optimization feature available in the upcoming Oracle Database 20c release (I’ll of course blog about this new 20c feature in the near future).

Within the article, Julian mentioned a clever method of how to effectively drop Automatic Indexes that I thought would be worth checking out.

For a number of reasons (which I’ll cover in some detail in upcoming articles, but for now using Automatic Indexing in REPORT ONLY mode is but one reason), you can easily be left with an Automatic Index that might get in the way of things and you may want to drop it.

However, as we’ll see, you can’t easily drop an Automatic Index. The only “supported” manner to drop an Automatic Index is to wait for the Automatic Index Retention period to be exceeded (which is by default some 373 days and assumes the index is not used during this period).

Julian has come up with an alternate strategy.

By way of a demo, I currently have the following Automatic Index (SYS_AI_5zjkc60knz9zp):

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

INDEX_NAME                     AUT STATUS   VISIBILIT
------------------------------ --- -------- ---------
CRACKED_ACTOR_CODE1_CODE2_I    NO  VALID    VISIBLE
SYS_AI_5zjkc60knz9zp           YES VALID    INVISIBLE

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

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
SYS_AI_5zjkc60knz9zp           ID                                           1

 

So I have an Automatic Index on the ID column of the CRACKED_ACTOR table, BUT it’s currently INVISIBLE and so can’t be used be default by the CBO.

If I run the following query:

SQL> select * from cracked_actor where id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 786009234

-----------------------------------------------------------------------------------
| Id | Operation        | Name          | Rows | Bytes | Cost (%CPU)| Time        |
-----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |               |    1 |    24 |       3 (0)| 00:00:01    |
|* 1 | TABLE ACCESS FULL| CRACKED_ACTOR |    1 |    24 |       3 (0)| 00:00:01    |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

     1 - filter("ID"=42)
Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
      7042 consistent gets
         0 physical reads
         0 redo size
       789 bytes sent via SQL*Net to client
       401 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

 

The CBO uses a Full Table Scan because the available Automatic Index on the ID column is current invisible.

If I try to convert it to being VISIBLE:

SQL> alter index "SYS_AI_5zjkc60knz9zp" visible;
alter index "SYS_AI_5zjkc60knz9zp" visible
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

I can’t, because you can’t alter an Automatic Index to be Visible/Invisible.

If I try to just drop the Automatic Index:

SQL> drop index "SYS_AI_5zjkc60knz9zp";
drop index "SYS_AI_5zjkc60knz9zp"
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

Again, I can’t just simply drop an Automatic Index.

However, I am allowed to Rebuild (or Coalesce or Shrink) an Automatic Index. Therefore, I can create a new dummy tablespace and rebuild the Automatic Index to reside in this particular tablespace:

SQL> alter index "SYS_AI_5zjkc60knz9zp" rebuild tablespace bowie_stuff;

Index altered.

 

I can then drop this tablespace including all its contents (and hence drop the Automatic Index contained within):

SQL> drop tablespace bowie_stuff including contents and datafiles;

Tablespace dropped.

 

The problematic Automatic Index is now gone:

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

INDEX_NAME                     AUT STATUS   VISIBILIT
------------------------------ --- -------- ---------
CRACKED_ACTOR_CODE1_CODE2_I    NO  VALID     VISIBLE

I can now either manually create the necessary index or wait for the Automatic Index to now hopefully create a new, visible Automatic Index to address my query:

SQL> create index cracked_actor_id_i on cracked_actor(id);

Index created.

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

INDEX_NAME                     AUT STATUS   VISIBILIT
------------------------------ --- -------- ---------
CRACKED_ACTOR_CODE1_CODE2_I    NO  VALID    VISIBLE
CRACKED_ACTOR_ID_I             NO  VALID    VISIBLE

 

I’ve now addressed the problematic query:

SQL> select * from cracked_actor where id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 4160941723

----------------------------------------------------------------------------------------------------------
| Id | Operation                          | Name               | Rows | Bytes | Cost (%CPU)| Time        |
----------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                   |                    |    1 |    24 |       2 (0)| 00:00:01    |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED| CRACKED_ACTOR      |    1 |    24 |       2 (0)| 00:00:01    |
|* 2 | INDEX RANGE SCAN                   | CRACKED_ACTOR_ID_I |    1 |       |       1 (0)| 00:00:01    |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

      2 - access("ID"=42)
Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
         3 consistent gets
         0 physical reads
         0 redo size
       793 bytes sent via SQL*Net to client
       401 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

 

Thanks Julian for the cool tip ūüôā

12c Enhanced Online Index DDL Operations (Lady Godiva’s Operation) February 17, 2014

Posted by Richard Foote in 12c, Drop Index, Invisible Indexes, Online DDL, Oracle Indexes, Unusable Indexes.
6 comments

In my last couple of posts, I discussed how table partitions can be moved online since 12c, keeping all indexes in sync as part of the process.

12c also introduced enhancements to a number of index related DDL statements, removing blocking locks and making their use online and far less intrusive. The following commands now have a new ONLINE option:

DROP INDEX ONLINE

ALTER INDEX UNUSABLE ONLINE

So if we look at a little example (initially on 11g R2), where we create a table and associated index on the CODE column:

SQL> create table radiohead (id number, code number, name varchar2(30));

Table created.

SQL> insert into radiohead select rownum, mod(rownum,1000), 'DAVID BOWIE' from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create index radiohead_code_i on radiohead(code);

Index created.

If we now insert a new row in one session but not commit:

SQL> insert into radiohead values (1000001, 42, 'ZIGGY STARDUST');

1 row created.

And then attempt any of the following DDL commands in another session:

SQL> drop index radiohead_code_i;

drop index radiohead_code_i
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter index radiohead_code_i invisible;

alter index radiohead_code_i invisible
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter index radiohead_code_i unusable;

alter index radiohead_code_i unusable
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

They all get the well-known “ORA-00054: resource busy” error.

If on the other hand, one of these DDL statements is already running in a session:

SQL> alter index radiohead_code_i unusable;

All DML statements in other sessions will hang until the DDL completes:

SQL> insert into radiohead values (1000002, 42, 'THIN WHITE DUKE');

Once the index is finally made unusable:

SQL> alter index radiohead_code_i unusable;

Index altered.

SQL> select index_name, status from dba_indexes where index_name = 'RADIOHEAD_CODE_I';

INDEX_NAME                     STATUS
------------------------------ --------
RADIOHEAD_CODE_I               UNUSABLE

SQL> select segment_name, blocks, extents from dba_segments where segment_name = 'RADIOHEAD_CODE_I';

no rows selected

We can see not only is the index now in an unusable state but the index segment has been dropped (in 11g r2) as the storage associated with the unusable index is of no further use.

So these commands prior to the Oracle 12c Database previously had locking related issues.

If we now perform the same setup in 12c and again have an outstanding transaction in a session:

SQL> drop index radiohead_code_i online;

The Drop Index command doesn’t now¬†get the Ora-00054: resource busy, but rather hangs until¬†all prior¬†transactions complete.

However, while the Drop Index command hangs, it doesn’t¬†in turn lock out transactions within other sessions. In another session:

SQL> insert into radiohead values (1000002, 42, 'THIN WHITE DUKE');

1 row created.

And in yet other session:

SQL> delete radiohead where id = 42;

1 row deleted.

SQL> commit;

Commit complete.

These all complete successfully. The Drop Index command itself will eventually complete successfully once all prior transaction have finished.

SQL> drop index radiohead_code_i online;

Index dropped.

Another more subtle difference in behaviour¬†with 12c. If there’s an existing transaction when you decide to make an index unusable:

SQL> insert into radiohead values (1000001, 42, 'ZIGGY STARDUST');

1 row created.
SQL> alter index radiohead_code_i unusable online;

As in the previous demo, the alter index command will hang indefinitely until the previous transaction commits:

SQL> commit;

Commit complete.

SQL> alter index radiohead_code_i unusable online;

Index altered.

SQL> select index_name, status from dba_indexes where index_name = 'RADIOHEAD_CODE_I';

INDEX_NAME                STATUS
------------------------- --------
RADIOHEAD_CODE_I          UNUSABLE

SQL> select segment_name, blocks, extents from dba_segments where segment_name = 'RADIOHEAD_CODE_I';

SEGMENT_NAME         BLOCKS    EXTENTS
---------------- ---------- ----------
RADIOHEAD_CODE_I       2176         32

We note the index has eventually been made Unusable, however the segment has not now been dropped (as it was in the 11g R2 demo) due to the use of the ONLINE clause.

With the Oracle 12c Database, the locking implications and concurrency issues associated these index related DDL commands have been reduced with these new ONLINE options.