

Oracle SQL のパフォーマンスチューニングに関する質問です。  
現在、一つのTableがあり、そのTableに 二つの日付型（TIMESTAMP(6) with local timezobe) の項目があります。

私がSQLを書くときに、それら二つの日付が合致している必要があるため、例えば下記のように書きます。

[code]

    select * from table1 where date1 = date2;
    
[/code]

すると、Query Planが必ずFull Table Accessになってしまい、SQLのコストが上がります。  
インデックスを下記のようにつけてみたのですが、Table full scan の状況に変化はないです。

[code]

    CREATE INDEX table1_IDX on table1 (date1 ASC, date2 ASC)
    
[/code]

どのようにすれば、このような状況でTable Full Scan を防げるでしょう？


CC BY-SA 4.0
2020-11-11T07:57:36.357
71868
2020-11-13T15:07:57.203
2020-11-11T10:25:17.520
32986
42683
question
sql oracle
ORACLE sql パフォーマンスチューニング。 一つのテーブルに二つの日付型の項目があり、それらをJoinするために効果的なIndexを作成したいです


> どのようにすれば、このような状況でTable Full Scan を防げるでしょう？

場当たり的な(しかし有効と予想される)対応ですが、[INDEXヒント](https://docs.oracle.com/cd/E16338_01/server.112/b56299/sql_elements006.htm#BABEFDFC)でオプティマイザにインデックスの使用を強制することができます。

[code]

    select /*+ INDEX(TABLE1 TABLE1_IDX) */
           * from table1 where date1 = date2;
    
[/code]

他の対応としてはレコードの更新時に動作するトリガーを仕込んでおいて、更新レコードのdate1とdate2が同じかを判別してフラグを立てる方法や別テーブルに更新レコードのキーを追加/削除する方法が考えられます。  
クエリ処理前に更新日が一定以上のレコードに対して上記処理を行う方法も運用によっては有用かもしれません。

そのクエリを何度も投げるならば、date1とdate2の値が同一レコードに絞ったマテリアライズド・ビューを更新してから処理を開始する方法もあります。


CC BY-SA 4.0
2020-11-12T01:40:16.830
71884
2020-11-12T01:40:16.830
9820
71868
answer


質問文中のクエリは左辺、右辺ともレコードを参照しないと値がわからないので、フルスキャンになるのは妥当かと思います。

[仮想列](https://docs.oracle.com/cd/F19136_01/sqlrf/ALTER-
TABLE.html#GUID-552E7373-BF93-477D-9DA3-B2C9386F2877__GUID-58889759-6A94-4458-A80D-D870424E4AEE)で`date1`と`date2`が同値かどうかを判断できるようにして、この仮想列にindexを張ればインデクスを用いたスキャンにはなると思います。

[code]

    alter table table1 add (
      same_date number(1)
      generated always as
      (
        case
          when date1 = date2 then 1
          else 0
        end
      ) virtual
    );
    
    create bitmap index idx_same_date on table1(same_date);
    
    select * from table1 where same_date = 1;
    
[/code]

(ただ、タイトルにある"Join"がこのクエリとどう関わるのかは、質問からは読み取れませんでした)


CC BY-SA 4.0
2020-11-13T15:07:57.203
71932
2020-11-13T15:07:57.203
2808
71868
answer