Oracle Autonomous Database Free Container Imageを使って手元のラップトップPCで自動索引を試してみた


[前回の記事](https://qiita.com/ssfujita/items/2e74f34747f5d55312f3)ではOracle Autonomous Database Free Container ImageをノートPCのWindows環境で構築する手順をまとめました。
今回は構築した手元のADBを使い、以下ドキュメントの通り本来はExadataやADBでしか使えない自動索引を動かしてみました。

> [データベース・ライセンス情報ユーザー・マニュアル](https://docs.oracle.com/cd/F19136_01/dblic/Licensing-Information.html#GUID-B6113390-9586-46D7-9008-DCC9EDA45AB4)
> 自動索引付け

なお自動索引を試す手順は下記LiveLabsの手順を使いました。
[Autotatic Indexing for Oracle Autonomous Database](https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/run-workshop?p210_wid=3328&p210_wec=)

## Oracle Autonomous Database Free Container Imageの起動

前回構築したADBを起動しておきます。
まずはPodmanのVMを起動します。

```dosbatch
>podman machine start

Starting machine "podman-machine-default"

This machine is currently configured in rootless mode. If your containers
require root permissions (e.g. ports < 1024), or if you run into compatibility
issues with non-podman clients, you can switch using the following command:

        podman machine set --rootful

API forwarding listening on: npipe:////./pipe/docker_engine

Docker API clients default to this address. You do not need to set DOCKER_HOST.
Machine "podman-machine-default" started successfully
```

ADBのコンテナを起動します。

```dosbatch
>podman run -d ^
-p 1521:1522 ^
-p 1522:1522 ^
-p 8443:8443 ^
-p 27017:27017 ^
--hostname localhost ^
--cap-add SYS_ADMIN ^
--device /dev/fuse ^
--name adb_container ^
container-registry.oracle.com/database/adb-free:latest

b8756b31008404a261a736927d5ceecb38998e705139aa9d87beeb44c94d950d
```

起動状態を確認します。

```dosbatch
>podman ps
CONTAINER ID  IMAGE                                                   COMMAND     CREATED        STATUS                   PORTS           NAMES
b8756b310084  container-registry.oracle.com/database/adb-free:latest              5 seconds ago  Up 6 seconds (starting)  0.0.0.0:1521->1522/tcp, 0.0.0.0:1522->1522/tcp, 0.0.0.0:8443->8443/tcp, 0.0.0.0:27017->27017/tcp  adb_container
```

## 検証用スキーマ・オブジェクトの作成

LiveLabsの手順に従い、検証用テーブルを作成します。

```sql
SQL> create table AUTO_INDEX_TEST_TABLE as
select 1                sum_col,
    rownum              f1,
    mod(rownum,100000)  f2,
    mod(rownum,1000000) f3,
    mod(rownum,500000)  f4,
    mod(rownum,200000)  f5,
    mod(rownum,100000)  f6,
    mod(rownum,10000)   f7,
    mod(rownum,5000)    f8,
    mod(rownum,1000)    f9,
    mod(rownum,100)     f10,
    pad
from   (select dbms_random.string('u',1000) pad from dual connect by level<=100) a,
       (select 1 c from dual connect by level<=100000) b;

Table created.
```

自動索引を使うには有効な統計が必要ですので、念のため統計情報を収集しておきます。

```sql
SQL> exec dbms_stats.gather_table_stats(user,'AUTO_INDEX_TEST_TABLE')
```

本テーブルに対するクエリを発行した際に、スキャンや結果キャッシュが使われるよう1行だけ追加します。

```sql
SQL> insert into AUTO_INDEX_TEST_TABLE
     values (0,0,0,0,0,0,0,0,0,0,0,'X');

1 row created.

SQL> commit;

Commit complete.
```

検証用テーブルに対して自動索引を有効化します。

```sql
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE', 'IMPLEMENT')

PL/SQL procedure successfully completed.
```

自動索引による性能改善を確認しやすくするために、結果キャッシュをマニュアルモードにしておきます。

```sql
SQL> alter system set result_cache_mode = manual;

System altered.
```

## ワークロードの実行

先ほど作成したテーブルを使うワークロードを流します。

```sql
SQL> set serveroutput on
SQL> declare
      qry varchar2(1000);
      talias  varchar2(30);
      n1  number;
      n2  number;
  begin
      for i in 1..10
      loop
          talias := 'T'||to_char(sysdate,'YYYYMMDDHH24MISS');
          qry := 'select /* AUTO_INDEX_TEST_QUERY */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE '||talias||' where f'||i||' = 1 group by f10';
          execute immediate qry into n1,n2;
          execute immediate qry into n1,n2;
          execute immediate qry into n1,n2;
          dbms_output.put_line('QRY = '||qry);
          dbms_output.put_line('SUM = '||n2);
      end loop;
      qry := 'select /* AUTO_INDEX_TEST_QUERY */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE '||talias||' where f1 = 1 and f2 = 1 group by f10';
      execute immediate qry into n1,n2;
      execute immediate qry into n1,n2;
      execute immediate qry into n1,n2;
      dbms_output.put_line('QRY = '||qry);
      qry := 'select /* AUTO_INDEX_TEST_QUERY */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE '||talias||' where f1 = 1 and f2 = 1 group by f10';
      execute immediate qry into n1,n2;
      execute immediate qry into n1,n2;
      execute immediate qry into n1,n2;
      dbms_output.put_line('QRY = '||qry);
  end;
  /
```

ワークロードを流したまま、別のターミナルを起動して以降の手順に進みます。

## Auto STSの作成状況確認

ADB上で実行されているワークロードは、Auto STSというオブジェクトに収集されます。
Auto STS内のSQL統計は自動索引のインプットとなりますので、Auto STSに検証用ワークロードが収集されたことを確認する必要があります。
このタスクは15分置きに実行されますので、タイミングによっては待つ必要がある点にご留意ください。

以下のクエリでAuto STSのワークロード収集タスク実行状況を確認できます。

```sql
SQL>  select task_name,
         status,
         enabled,
         interval,
         last_schedule_time,
         systimestamp-last_schedule_time ago
  from dba_autotask_schedule_control
  where dbid = sys_context('userenv','con_dbid')
  and   task_name like '%STS%';

TASK_NAME                                                        STATUS
---------------------------------------------------------------- ----------
ENABL   INTERVAL
----- ----------
LAST_SCHEDULE_TIME
---------------------------------------------------------------------------
AGO
---------------------------------------------------------------------------
Auto STS Capture Task                                            SUCCEEDED
TRUE         900
02-NOV-23 05.09.37.242 AM +00:00
+000000000 00:14:02.402713
```

LAST_SCHEDULE_TIME が検証用ワークロード実行後のタイムスタンプになっている必要があります。
またAuto STSに収集されたSQLは以下のように確認できます。

```sql
SQL> select sql_text
  from   dba_sqlset_statements
  where  sql_text like '%AUTO_INDEX_TEST_QUERY%'
  and    sqlset_name = 'SYS_AUTO_STS';

SQL_TEXT
--------------------------------------------------------------------------------
select /* AUTO_INDEX_TEST_QUERY */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T
select /* AUTO_INDEX_TEST_QUERY */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T
select /* AUTO_INDEX_TEST_QUERY */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T
select /* AUTO_INDEX_TEST_QUERY */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T
```

## 自動索引タスクのレポート確認

Auto STSに検証用ワークロードが収集されましたら、次は自動索引タスクの実行です。
自動索引タスクも15分置きに実行されますので、Auto STSに検証用ワークロードが収集された後に、自動索引タスクが実行されるまで少し待つ必要があるかもしれません。

自動索引タスクの実行状況は以下のように確認できます。

```sql
SQL>  select task_name,
         status,
         enabled,
         interval,
         last_schedule_time,
         systimestamp-last_schedule_time ago
  from dba_autotask_schedule_control
  where dbid = sys_context('userenv','con_dbid')
  and   task_name = 'Auto Index Task';

TASK_NAME                                                        STATUS
---------------------------------------------------------------- ----------
ENABL   INTERVAL
----- ----------
LAST_SCHEDULE_TIME
---------------------------------------------------------------------------
AGO
---------------------------------------------------------------------------
Auto Index Task                                                  SUCCEEDED
TRUE         900
02-NOV-23 05.14.15.744 AM +00:00
+000000000 00:12:24.465264
```

LAST_SCHEDULE_TIME のタイムスタンプが、Auto STSに検証用ワークロードが収集されたタイミングより後のタイムスタンプになっている必要があります。
ちなみに私の環境では STATUS が RUNNING になってから SUCCEEDED になるまで5分ほど掛かりました。
問題なければ以降の手順で自動索引タスクのレポートを確認します。

```sql
SQL> exec :rep := dbms_auto_index.report_last_activity(section=>'ALL',level=>'ALL')

PL/SQL procedure successfully completed.

SQL> select :rep report from dual;

REPORT
------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 02-NOV-2023 05:45:10
 Activity end                 : 02-NOV-2023 05:49:33
 Executions completed         : 1
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------

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

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 |
------------------------------------------------------------------------------------
| ADMIN | AUTO_INDEX_TEST_TABLE | SYS_AI_3j13qx7f8hsa5 | F8  | B-TREE | NONE       |
| ADMIN | AUTO_INDEX_TEST_TABLE | SYS_AI_3vj4dhuq4n300 | F9  | B-TREE | NONE       |
| ADMIN | AUTO_INDEX_TEST_TABLE | SYS_AI_gdu3958w4jtrf | F10 | B-TREE | NONE       |
------------------------------------------------------------------------------------
-------------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
 Parsing Schema Name  : ADMIN
 SQL ID               : 1487qmx3m65n2
 SQL Text             : select /* AUTO_INDEX_TEST_QUERY */ f10,sum(sum_col)
                      from AUTO_INDEX_TEST_TABLE T20231102053524 where f9 = 1
                      group by f10
 Improvement Factor   : 166.37x

Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  208989300                     2497686
 CPU Time (s):      126928268                     543594
 Buffer Gets:       5000174                       10018
 Optimizer Cost:    452435                        9903
 Disk Reads:        5000049                       9843
 Direct Writes:     0                             0
 Rows Processed:    3                             1
 Executions:        3                             1


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

- Original
-----------------------------
 Plan Hash Value  : 521587665

----------------------------------------------------------------------------------------
| Id | Operation            | Name                  | Rows | Bytes | Cost   | Time     |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |                       |      |       | 452435 |          |
|  1 |   HASH GROUP BY      |                       |  100 |  1000 | 452435 | 00:00:18 |
|  2 |    TABLE ACCESS FULL | AUTO_INDEX_TEST_TABLE | 9881 | 98810 | 452434 | 00:00:18 |
----------------------------------------------------------------------------------------

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

---------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                  | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                       |  100 |  1000 | 9903 | 00:00:01 |
|   1 |   HASH GROUP BY                        |                       |  100 |  1000 | 9903 | 00:00:01 |
|   2 |    TABLE ACCESS BY INDEX ROWID BATCHED | AUTO_INDEX_TEST_TABLE | 9881 | 98810 | 9902 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN                   | SYS_AI_3vj4dhuq4n300  | 9881 |       |   18 | 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("F9"=1)


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


-------------------------------------------------------------------------------
 Parsing Schema Name  : ADMIN
 SQL ID               : bsrhwtznmmrfy
 SQL Text             : select /* AUTO_INDEX_TEST_QUERY */ f10,sum(sum_col)
                      from AUTO_INDEX_TEST_TABLE T20231102053749 where f10 = 1
                      group by f10
 Improvement Factor   : 15.62x

Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  196475772                     28623990
 CPU Time (s):      120237126                     12146518
 Buffer Gets:       4693597                       100157
 Optimizer Cost:    452440                        100180
 Disk Reads:        4693655                       93944
 Direct Writes:     0                             0
 Rows Processed:    2                             1
 Executions:        3                             1


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

- Original
-----------------------------
 Plan Hash Value  : 547843854

---------------------------------------------------------------------------------------------
| Id | Operation              | Name                  | Rows   | Bytes  | Cost   | Time     |
---------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |                       |        |        | 452440 |          |
|  1 |   SORT GROUP BY NOSORT |                       | 100000 | 600000 | 452440 | 00:00:18 |
|  2 |    TABLE ACCESS FULL   | AUTO_INDEX_TEST_TABLE | 100000 | 600000 | 452440 | 00:00:18 |
---------------------------------------------------------------------------------------------

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

------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows   | Bytes  | Cost   | Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |  71041 | 426246 | 100180 | 00:00:04 |
|   1 |   SORT GROUP BY NOSORT         |                       |  71041 | 426246 | 100180 | 00:00:04 |
|   2 |    TABLE ACCESS BY INDEX ROWID | AUTO_INDEX_TEST_TABLE |  71041 | 426246 | 100180 | 00:00:04 |
| * 3 |     INDEX RANGE SCAN           | SYS_AI_gdu3958w4jtrf  | 100000 |        |    157 | 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("F10"=1)


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


-------------------------------------------------------------------------------
 Parsing Schema Name  : ADMIN
 SQL ID               : d9dv43b9mjg47
 SQL Text             : select /* AUTO_INDEX_TEST_QUERY */ f10,sum(sum_col)
                      from AUTO_INDEX_TEST_TABLE T20231102053253 where f8 = 1
                      group by f10
 Improvement Factor   : 3662.26x

Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  218909309                     10129
 CPU Time (s):      135103452                     12296
 Buffer Gets:       5000215                       2006
 Optimizer Cost:    452429                        2077
 Disk Reads:        5000050                       57
 Direct Writes:     0                             0
 Rows Processed:    3                             1
 Executions:        3                             1


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

- Original
-----------------------------
 Plan Hash Value  : 521587665

----------------------------------------------------------------------------------------
| Id | Operation            | Name                  | Rows | Bytes | Cost   | Time     |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |                       |      |       | 452429 |          |
|  1 |   HASH GROUP BY      |                       |  100 |  1000 | 452429 | 00:00:18 |
|  2 |    TABLE ACCESS FULL | AUTO_INDEX_TEST_TABLE | 2068 | 20680 | 452428 | 00:00:18 |
----------------------------------------------------------------------------------------

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

---------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                  | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                       |  100 |  1000 | 2077 | 00:00:01 |
|   1 |   HASH GROUP BY                        |                       |  100 |  1000 | 2077 | 00:00:01 |
|   2 |    TABLE ACCESS BY INDEX ROWID BATCHED | AUTO_INDEX_TEST_TABLE | 2068 | 20680 | 2075 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN                   | SYS_AI_3j13qx7f8hsa5  | 2068 |       |    6 | 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("F8"=1)


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


-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

ERRORS
---------------------------------------------------------------------------------------------
No errors found.
---------------------------------------------------------------------------------------------
```

こちらのレポートから、直近の自動索引タスクでは列 F8, F9, F10 に対して索引が作成されたと分かります。
また各索引が関係するSQLの実行計画も記載されており、索引の効果が分かるようbefore、afterの実行計画が並んでいます。

自動索引タスクの中で作成された索引、および実行されたDDLの履歴も確認できます。
中には作成したものの効果がみられないと判断し、INVISIBLE になっている索引もあります。

```sql
SQL> break on index_name
SQL> SELECT c.index_name,c.table_name,c.column_name,c.column_position, i.visibility, i.status
  2  FROM   user_ind_columns c , user_indexes i
  3  WHERE  c.index_name = i.index_name
  4  AND    i.auto = 'YES'
  5  ORDER BY c.table_name,c.index_name,c.column_position;

INDEX_NAME                     TABLE_NAME                COLUMN_NAME          COLUMN_POSITION VISIBILIT STATUS
------------------------------ ------------------------- -------------------- --------------- --------- --------
SYS_AI_3j13qx7f8hsa5           AUTO_INDEX_TEST_TABLE     F8                                 1 VISIBLE   VALID
SYS_AI_3vj4dhuq4n300           AUTO_INDEX_TEST_TABLE     F9                                 1 VISIBLE   VALID
SYS_AI_7tsh190vs7fnk           AUTO_INDEX_TEST_TABLE     F1                                 1 INVISIBLE UNUSABLE
                               AUTO_INDEX_TEST_TABLE     F2                                 2 INVISIBLE UNUSABLE
SYS_AI_7ydrxqpm6693n           AUTO_INDEX_TEST_TABLE     F6                                 1 INVISIBLE UNUSABLE
SYS_AI_8asuvwcvrdmsc           AUTO_INDEX_TEST_TABLE     F2                                 1 INVISIBLE UNUSABLE
SYS_AI_b9wjbw50d46mw           AUTO_INDEX_TEST_TABLE     F4                                 1 VISIBLE   VALID
SYS_AI_cb4ay0uh6ava9           AUTO_INDEX_TEST_TABLE     F7                                 1 INVISIBLE UNUSABLE
SYS_AI_cx646fvtr64u5           AUTO_INDEX_TEST_TABLE     F3                                 1 INVISIBLE UNUSABLE
SYS_AI_fb8yhtuk4m7p1           AUTO_INDEX_TEST_TABLE     F5                                 1 INVISIBLE UNUSABLE
SYS_AI_gdu3958w4jtrf           AUTO_INDEX_TEST_TABLE     F10                                1 VISIBLE   VALID

11 rows selected.

SQL> SELECT to_char(end_time,'YYYY-MM-DD HH24:MI') dt,table_name,index_name,statement
  2  FROM   dba_auto_index_ind_actions
  3  ORDER BY end_time;

DT                        TABLE_NAME                INDEX_NAME                     STATEMENT
------------------------- ------------------------- ------------------------------ ----------------------------------------------------------------------------------------------------
2023-11-02 05:30          AUTO_INDEX_TEST_TABLE     SYS_AI_5zxvr23n06y2w           CREATE INDEX "ADMIN"."SYS_AI_5zxvr23n06y2w"   ON "ADMIN"."AUTO_INDEX_TEST_TABLE"("F1") TABLESPACE "D
                                                                                   ATA" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW  ONLINE

2023-11-02 05:30          AUTO_INDEX_TEST_TABLE     SYS_AI_cb4ay0uh6ava9           CREATE INDEX "ADMIN"."SYS_AI_cb4ay0uh6ava9"   ON "ADMIN"."AUTO_INDEX_TEST_TABLE"("F7") TABLESPACE "D
                                                                                   ATA" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW  ONLINE

2023-11-02 05:30          AUTO_INDEX_TEST_TABLE     SYS_AI_7ydrxqpm6693n           CREATE INDEX "ADMIN"."SYS_AI_7ydrxqpm6693n"   ON "ADMIN"."AUTO_INDEX_TEST_TABLE"("F6") TABLESPACE "D
                                                                                   ATA" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW  ONLINE

2023-11-02 05:30          AUTO_INDEX_TEST_TABLE     SYS_AI_fb8yhtuk4m7p1           CREATE INDEX "ADMIN"."SYS_AI_fb8yhtuk4m7p1"   ON "ADMIN"."AUTO_INDEX_TEST_TABLE"("F5") TABLESPACE "D
                                                                                   ATA" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW  ONLINE

2023-11-02 05:30          AUTO_INDEX_TEST_TABLE     SYS_AI_b9wjbw50d46mw           CREATE INDEX "ADMIN"."SYS_AI_b9wjbw50d46mw"   ON "ADMIN"."AUTO_INDEX_TEST_TABLE"("F4") TABLESPACE "D
                                                                                   ATA" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW  ONLINE

2023-11-02 05:30          AUTO_INDEX_TEST_TABLE     SYS_AI_cx646fvtr64u5           CREATE INDEX "ADMIN"."SYS_AI_cx646fvtr64u5"   ON "ADMIN"."AUTO_INDEX_TEST_TABLE"("F3") TABLESPACE "D
                                                                                   ATA" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW  ONLINE

2023-11-02 05:30          AUTO_INDEX_TEST_TABLE     SYS_AI_8asuvwcvrdmsc           CREATE INDEX "ADMIN"."SYS_AI_8asuvwcvrdmsc"   ON "ADMIN"."AUTO_INDEX_TEST_TABLE"("F2") TABLESPACE "D
                                                                                   ATA" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW  ONLINE

2023-11-02 05:31          AUTO_INDEX_TEST_TABLE     SYS_AI_b9wjbw50d46mw           ALTER INDEX "ADMIN"."SYS_AI_b9wjbw50d46mw"   REBUILD  ONLINE
2023-11-02 05:32          AUTO_INDEX_TEST_TABLE                                    ALTER INDEX "ADMIN"."SYS_AI_b9wjbw50d46mw"   VISIBLE
2023-11-02 05:45          AUTO_INDEX_TEST_TABLE     SYS_AI_5zxvr23n06y2w           DROP INDEX "ADMIN"."SYS_AI_5zxvr23n06y2w"     ONLINE
2023-11-02 05:45          AUTO_INDEX_TEST_TABLE     SYS_AI_3j13qx7f8hsa5           CREATE INDEX "ADMIN"."SYS_AI_3j13qx7f8hsa5"   ON "ADMIN"."AUTO_INDEX_TEST_TABLE"("F8") TABLESPACE "D
                                                                                   ATA" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW  ONLINE

2023-11-02 05:45          AUTO_INDEX_TEST_TABLE     SYS_AI_7tsh190vs7fnk           CREATE INDEX "ADMIN"."SYS_AI_7tsh190vs7fnk"   ON "ADMIN"."AUTO_INDEX_TEST_TABLE"("F1","F2") TABLESPA
                                                                                   CE "DATA" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW  ONLINE

2023-11-02 05:45          AUTO_INDEX_TEST_TABLE     SYS_AI_gdu3958w4jtrf           CREATE INDEX "ADMIN"."SYS_AI_gdu3958w4jtrf"   ON "ADMIN"."AUTO_INDEX_TEST_TABLE"("F10") TABLESPACE "
                                                                                   DATA" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW  ONLINE

2023-11-02 05:45          AUTO_INDEX_TEST_TABLE     SYS_AI_3vj4dhuq4n300           CREATE INDEX "ADMIN"."SYS_AI_3vj4dhuq4n300"   ON "ADMIN"."AUTO_INDEX_TEST_TABLE"("F9") TABLESPACE "D
                                                                                   ATA" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW  ONLINE

2023-11-02 05:46          AUTO_INDEX_TEST_TABLE     SYS_AI_3j13qx7f8hsa5           ALTER INDEX "ADMIN"."SYS_AI_3j13qx7f8hsa5"   REBUILD  ONLINE
2023-11-02 05:47          AUTO_INDEX_TEST_TABLE     SYS_AI_3vj4dhuq4n300           ALTER INDEX "ADMIN"."SYS_AI_3vj4dhuq4n300"   REBUILD  ONLINE
2023-11-02 05:48          AUTO_INDEX_TEST_TABLE     SYS_AI_gdu3958w4jtrf           ALTER INDEX "ADMIN"."SYS_AI_gdu3958w4jtrf"   REBUILD  ONLINE
2023-11-02 05:49          AUTO_INDEX_TEST_TABLE     SYS_AI_3vj4dhuq4n300           ALTER INDEX "ADMIN"."SYS_AI_3vj4dhuq4n300"   VISIBLE
2023-11-02 05:49          AUTO_INDEX_TEST_TABLE     SYS_AI_gdu3958w4jtrf           ALTER INDEX "ADMIN"."SYS_AI_gdu3958w4jtrf"   VISIBLE
2023-11-02 05:49          AUTO_INDEX_TEST_TABLE     SYS_AI_3j13qx7f8hsa5           ALTER INDEX "ADMIN"."SYS_AI_3j13qx7f8hsa5"   VISIBLE

20 rows selected.
```

以上、本来ExadataやOCIのADBでしか使えない自動索引が、Oracle Autonomous Database Free Container Image で利用できることを確認できました。
次回は自動パーティショニングも試してみたいと思います。