前回の記事ではOracle Autonomous Database Free Container ImageをノートPCのWindows環境で構築する手順をまとめました。
今回は構築した手元のADBを使い、以下ドキュメントの通り本来はExadataやADBでしか使えない自動索引を動かしてみました。
データベース・ライセンス情報ユーザー・マニュアル
自動索引付け
なお自動索引を試す手順は下記LiveLabsの手順を使いました。
Autotatic Indexing for Oracle Autonomous Database
Oracle Autonomous Database Free Container Imageの起動
前回構築したADBを起動しておきます。
まずはPodmanのVMを起動します。
>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のコンテナを起動します。
>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
起動状態を確認します。
>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> 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> exec dbms_stats.gather_table_stats(user,'AUTO_INDEX_TEST_TABLE')
本テーブルに対するクエリを発行した際に、スキャンや結果キャッシュが使われるよう1行だけ追加します。
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> exec dbms_auto_index.configure('AUTO_INDEX_MODE', 'IMPLEMENT')
PL/SQL procedure successfully completed.
自動索引による性能改善を確認しやすくするために、結果キャッシュをマニュアルモードにしておきます。
SQL> alter system set result_cache_mode = manual;
System altered.
ワークロードの実行
先ほど作成したテーブルを使うワークロードを流します。
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> 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> 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> 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> 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> 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 で利用できることを確認できました。
次回は自動パーティショニングも試してみたいと思います。