19c新機能検証の第9回になります。
今回も引き続き、自動索引について検証します。
検証に使用した環境は以下の通りです。
今回はファンクション・ベース・インデックスについて検証します。
自動的に関数索引まで作ってくれるなんて、凄いですね。
本当にそんな凄い事をやってくれるのか確かめる為、テーブルtest3にファンクションインデックスを作成し、インデックスが使用される事を確認します。
SQL> create index f_ind1_test3 on test3 (to_char(col6,'yyyy/mm/dd hh24:mi:ss'));
SQL> update test3 set col2=col2+1 where col6 is not null and to_char(col6,'yyyy/mm/dd hh24:mi:ss') <= '2019/12/16 11:01:19'
117353 rows updated.
Elapsed: 00:00:06.40
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID g0165h61ay8ys, child number 1
-------------------------------------
update test3 set col2=col2+1 where col6 is not null and
to_char(col6,'yyyy/mm/dd hh24:mi:ss') <= '2019/12/16 11:01:19'
Plan hash value: 1129324701
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 42098 (100)| |
| 1 | UPDATE | TEST3 | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 393K| 13M| 42098 (1)| 00:00:02 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST3 | 393K| 13M| 42098 (1)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | F_IND1_TEST3 | 247K| | 1076 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------
CTASでtest3と同じ構成、同じデータのtest4を作成します。
SQL> create table test4 as select * from test3;
Table created.
test3と同じupdate文を実行し、インデックスを使用した方が速い事を確認します。
SQL> update test4 set col2=col2+1 where col6 is not null and to_char(col6,'yyyy/mm/dd hh24:mi:ss') <= '2019/12/16 11:01:19';
117353 rows updated.
Elapsed: 00:01:04.16
インデックスありで6.4秒、無しで1分4秒程なので、インデックスの効果が出ていますね。
自動索引作成候補となるように、SQLを複数回、実行します。
比較の為、他のカラムをキーとするSQLも複数回実行しておきます。
一定時間経過後に確認してみました。無事に関数索引が作られているでしょうか…？
SQL> select TABLE_NAME,INDEX_NAME,visibility from ind;
TABLE_NAME INDEX_NAME VISIBILIT
-------------------- ------------------------------ ---------
TEST4 SYS_AI_cy2qzs9pkysy7 INVISIBLE
TEST4 SYS_AI_163qvs4b2s3s9 INVISIBLE
SQL> select INDEX_NAME,column_position,column_name from user_ind_columns where TABLE_NAME='TEST4' order by 1,2;
INDEX_NAME UMN_POSITION COLUMN_NAM
------------------------------ --------------- ----------
SYS_AI_163qvs4b2s3s9 1 COL5
SYS_AI_cy2qzs9pkysy7 1 COL3
SYS_AI_cy2qzs9pkysy7 2 COL4
where句に関数を使用しなかったカラムには自動索引が作成されましたが、関数を使用したカラム(col6)に対しては残念ながら作成されていません。
自動索引はファンクション・ベースも含めてBTreeインデックスに対応している筈なのですが…。
どういう事なのかサポート問い合わせ結果、以下のように拡張統計を取得しておくと自動で索引が作成されるとの事。
SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS('TESTUSER1', 'TEST4', '(UPPER("C4"))') FROM dual;
SQL> EXEC dbms_stats.gather_table_stats('TESTUSER1','TEST4');
さっそく拡張統計を取得して検証します。
SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS('TEST', 'TEST4', '(UPPER("COL7"))') FROM dual;
DBMS_STATS.CREATE_EXTENDED_STATS('TEST','TEST4','(UPPER("COL7"))')
--------------------------------------------------------------------------------
SYS_STUE9PCFQ$B8K0A30VYX17#AOG
SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS('TEST', 'TEST4', '(TO_CHAR("COL6",''yyyy/mm/dd hh24:mi:ss''))') FROM dual;
DBMS_STATS.CREATE_EXTENDED_STATS('TEST','TEST4','(TO_CHAR("COL6",''YYYY/MM/DDHH24:MI:SS''))')
--------------------------------------------------------------------------------------------------
SYS_STUAXS3C09EV60VAUJZHUI1BIS
UPPER(“COL7”)については、拡張統計取得後にSQLを実行し、自動索引の作成が確認されましたが、 to_char(COL6,“yyyy/mm/dd hh24:mi:ss”)については自動索引が作成されませんでした。
SQL> select INDEX_NAME,column_position,column_name from user_ind_columns where TABLE_NAME='TEST4' order by 1,2;
INDEX_NAME COLUMN_POSITION COLUMN_NAME
------------------------------ --------------- ------------------------------
SYS_AI_163qvs4b2s3s9 1 COL5
SYS_AI_aw0smt3r5v8tt 1 SYS_STUE9PCFQ$B8K0A30VYX17#AOG
SYS_AI_cy2qzs9pkysy7 1 COL3
SYS_AI_cy2qzs9pkysy7 2 COL4
そこで今度は拡張統計情報を確認したところ、手動で作成したto_char(COL6,“yyyy/mm/dd hh24:mi:ss”)の関数索引について、拡張統計が自動で取得されています。
CREATOR=SYSTEM、DROPPABLE =NOなので、自動で作成された削除できない拡張統計ですね。
SQL> select TABLE_NAME,EXTENSION_NAME,EXTENSION,CREATOR,DROPPABLE from all_stat_extensions where OWNER='TEST';
TABLE_NAME EXTENSION_NAME EXTENSION CREATO DRO
---------- ------------------------------ ---------------------------------------------- ------ ---
TEST3 SYS_NC00009$ (TO_CHAR("COL6",'yyyy/mm/dd hh24:mi:ss')) SYSTEM NO
TEST4 SYS_STUE9PCFQ$B8K0A30VYX17#AOG (UPPER("COL7")) USER YES
次は拡張統計が取得されているカラムに対し、手動でインデックスが作成できるか確認してみます。
SQL> create table test5 as select * from test3;
Table created.
SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS('TEST', 'TEST5', '(TO_CHAR("COL6",''yyyy/mm/dd hh24:mi:ss''))') FROM dual;
DBMS_STATS.CREATE_EXTENDED_STATS('TEST','TEST5','(TO_CHAR("COL6",''YYYY/MM/DDHH24:MI:SS''))')
------------------------------------------------------------------------------------------------
SYS_STUAXS3C09EV60VAUJZHUI1BIS
SQL> exec dbms_stats.gather_table_stats('test','test5');
PL/SQL procedure successfully completed.
Elapsed: 00:00:31.32
SQL> create index f_ind1_test5 on test5 (to_char(col6,'yyyy/mm/dd hh24:mi:ss'));
create index f_ind1_test5 on test5 (to_char(col6,'yyyy/mm/dd hh24:mi:ss'))
*
ERROR at line 1:
ORA-54018: A virtual column exists for this expression
インデックス作成がエラーになってしまいました…。
手動でインデックス作成ができないなら、自動作成も無理そうです。
再度、サポートに確認しました。
Q．添付の通り、拡張統計を取得したカラムに対して、手動で関数インデックスを作成しようとすると、エラーになります。
この事から、事前に拡張統計を取得したカラムに対して、自動でもインデックスが作成されないと思われますが、その認識で正しいですか？
A．弊社環境でもto_char(COL6,"yyyy/mm/dd hh24:mi:ss") の拡張統計を作成した環境には自動索引は作成されず、手動での索引作成もエラーなることを確認しました。
事例等を調査しましたが、upper, lower以外の関数で自動索引が作成されている事例は確認できませんでしたので、全てのファンクションで自動索引ができるということではないと考えられます。
どのファンクションで作成されるかなど公開されている情報がなく、ご案内できる情報がございません。
回りくどい言い方で回答されましたが、まとめるとto_char(COL6,“yyyy/mm/dd hh24:mi:ss”)のように手動でインデックスを作成すると自動で拡張統計が取得されるような関数索引については、自動索引は作成されない、が結論のようです。
どちらかと言うと自動で拡張統計が取得されるような関数索引の方が一般的に使用される頻度が高いのではないかと思うので、だとすればよく使われる関数索引は自動では作成されない…という事になりそうです。
自動索引は検証項目が多いので4回に亘って続けてきましたが、次回で最終回です。
