19c新機能検証の第7回になります。
前回に引き続き、自動索引について検証します。
検証に使用した環境は以下の通りです。
前回の検証ではなかなか自動索引が作成されなかったり、作成されてもvisibleにならなかったり、作成されたけれどインデックス使わない方が速かったり…と、残念な結果になっていました。
なかなか自動索引が作成されないのはテーブルサイズが大きすぎるからではないか？
という点を検証してみます。
Exaだと小ぶりなテーブルに対するSQLはテーブル・フルスキャンでもすぐに返ってくるので100GB、200GB超えのテーブルで検証していたのですが、今回は6GBのサイズ控えめのテーブルで検証します。
いかにもなSQLを複数回、実行して誘導します。
SQL> update t13 set col2=col2+1 where col5=180000;
15 rows updated.
Elapsed: 00:00:45.16
SQL> update t13 set col2=col2+1 where col5=180000 and col3=180000;
15 rows updated.
Elapsed: 00:00:45.22
SQL> update t13 set col2=col2+1 where col5=180000 and col4=180000;
15 rows updated.
Elapsed: 00:00:00.03
15分程度でインデックスが作成され、visibleになりました。where句で指定したcol3、col4、col5全てに自動索引が作成されていますね。
自動索引作成プロセスは、バックグラウンドで15分ごとに実行されるので、すぐに作られた事になります。
SQL> select table_name,index_name,column_name from user_ind_columns where index_name in (select index_name from ind where
visibility='VISIBLE') order by 1,2,3;
TABLE_NAME INDEX_NAME COLUMN_NAM
---------- ------------------------------ ----------
T13 SYS_AI_1wjcgk7gf47yx COL3
T13 SYS_AI_1wjcgk7gf47yx COL5
T13 SYS_AI_9v316420ndzs0 COL4
次に自動索引作成後のupdate結果です。
インデックス無しで45秒かかっていたupdateが0.03秒、同じく45秒かかっていたupdateが0.01秒で終了しています。
3つ目のupdate文は元々速いので省略してますが、元々速いのにcol4にインデックス作る意味はあったのでしょうか…？
SQL> update t13 set col2=col2+1 where col5=180000;
15 rows updated.
Elapsed: 00:00:00.03
SQL> update t13 set col2=col2+1 where col5=180000 and col3=180000;
15 rows updated.
Elapsed: 00:00:00.01
6GB程度のテーブルならばすぐに自動索引が作成されるのか…と思って、CTAS(Create Table As Select)で同じ構成とサイズのテーブルを作成し、同じようにupdateを実行して、自動索引が作成されるように誘導してみました。
…が、待てど暮らせど自動索引は作成されませんでした…。
これまでの検証ではSQLが余りに単純だったので、もう少し現実に即して複雑なSQLでテストしてみます。
かなり複雑なSQLで実行計画も長くなるのでここでは記載しませんが、
・インデックスは主キーのみが使用され、INDEX UNIQUE SCANとINDEX FULL SCAN が混在。
・テーブルのFULL SCANもあり。
という内容の実行計画で、SQL計画ベースラインが作成されていました。
暫く待つとすぐに自動索引が作られ、一部はvisibleになっているのが確認できます。
SQL> select owner,table_name,index_name,visibility from dba_indexes where auto='YES';
TABLE_NAME INDEX_NAME VISIBILIT
------------------------------ ------------------------------ ---------
TABLE_ST SYS_AI_1my55magb08by INVISIBLE
TABLE_EM SYS_AI_dmu05ydst6kqj VISIBLE
TABLE_IN SYS_AI_7jhg2yxwjdg1w VISIBLE
TABLE_IN SYS_AI_9gq2zrj3d56wf INVISIBLE
TABLE_IN SYS_AI_5rrxustdh5457 INVISIBLE
TABLE_CT SYS_AI_c3t9axbj85t79 VISIBLE
TABLE_CT SYS_AI_bu4ac9vuuyhx6 INVISIBLE
TABLE_CH SYS_AI_au2bw5c9sbjqv INVISIBLE
8 rows selected.
各テーブルのサイズは以下の通りです。
SQL> select segment_name,bytes/1024/1024 from user_segments where segment_type='TABLE' order by 2;
SEGMENT_NAME BYTES/1024/1024
------------------------------ ---------------
TABLE_CO .0625
TABLE_TE .3125
TABLE_EM 2
TABLE_US 2
TABLE_BU 248
TABLE_TOP 568
TABLE_CH_IN 608
TABLE_CT 720
TABLE_IN 1861
TABLE_ST 2176
自動索引が作成され、visibleになったテーブルはTABLE_EM(2MB)、TABLE_IN(1,861MB)、TABLE_CT(720MB)です。
この程度のサイズ感のテーブルならばすぐに自動索引が作成されるようです。2MBのテーブルにも自動索引が必要かはやや疑問ですが、OLTPで1時間に数千回も実行されるようなSQLであれば役に立つのでしょう。
とは言え、deleteやinsertが数千回、実行されるのであれば、インデックスが多い分、遅くなってしまいますが…。
ともあれ、成果確認の為、さっそく同じselect文を実行してみました。自動索引が使用されれば速くなる筈です。
SQL> SELECT * FROM （略） WHERE ROWNUM <= 102;
no rows selected
Elapsed: 00:00:19.34
…が、処理時間は変わりませんでした…。
SQL計画ベースラインがあるので実行計画は変わらず、当然ながら処理時間も変わりません。
せっかく自動索引、作ったのにね…。
リテラルだけ変えて実行すると、自動索引が使用され、処理時間が短縮されました。
19秒から約1秒へ、いい感じに性能向上しています。
SQL> SELECT * FROM（略） WHERE ROWNUM <= 410;
no rows selected
Elapsed: 00:00:00.91
SQL計画ベースラインに邪魔されてしまった感はありますが、今回はそこそこ良い結果が出たので、ここで一旦終わり、次回に続きます。
