インデックスが効くようにしたい SQLが組めずに困っています。

Question

初心者です。
記載されているSQL(データベース)のFULLSCANをindexを効かせたいです。
使用しているのは SQL Developerです。

主キー
a.カラムA、a.カラムC、
b.カラムA、b.カラムC

index
a.カラムA、a.カラムC、
b.カラムA、b.カラムC

テーブルa
a b c(START) d(END)
0001 aa 17-08-01 17-08-31
0002 ab 18-09-01 18-09-30
0003 ac 18-04-01 18-09-30
0004 ad 17-03-01 18-04-30
0005 ae 18-03-01 99-12-31
: : : :
9999 ad 17-08-31 17-10-31

テーブルb
a b c(START) d(END)
aa 1組 20140801 20160630
aa 2組 20160701 20180930
ab 3組 20180201 20180930
ac 4組 20170101 20180430
ac 5組 20180501 99991231
ad 6組 20180201 99991231
ae 7組 20180201 99991231

ーもとめている実行結果ー
2018年9月の対象者を実行結果として表示させたいです

テーブルa
a b c d
0002 ab 18-09-01 18-09-30
0003 ac 18-04-01 18-09-30
0005 ae 18-03-01 99-12-31

テーブルb
a b c d
ab 3組 20180201 20180930
ac 5組 20180501 99991231
ae 6組 20180201 99991231

該当のソースコード

SQL


SELECT
a.カラムA(varchar2型)
a.カラムB(varchar2型)
a.カラムC(date型)
a.カラムD(date型)
b.カラムA(varchar2型)
b.カラムB(varchar2型)
b.カラムC(varchar2型)
b.カラムD(varchar2型)

FROM
    テーブルa,テーブルb

WHERE a.カラムB=b.カラムA

AND
(a.カラムA、a.カラムC、b.カラムA、b.カラムC) 
IN

(SELECT 
 a.カラムA、
MAX(a.カラムC)、
b.カラムA、
MAX(b.カラムC)

 FROM  
 テーブルa、テーブルb

 WHERE        
a.カラムB=b.カラムA


AND
a.カラムC<=
TO_DATE('20180930','YYYYMMDD')


AND
a.カラムD
>=TO_DATE('20180901','YYYYMMDD') 

AND
b.カラムC<= '20180930'

AND     
b.カラムD >='20180901'

GROUP BY  
a.カラムA、b.カラムA
)

考えられる原因
(1)IN句が使っているためインデックスが効かず、フルスキャンされてしまう。

Comments

[m.ts10806]

表もマークダウンで組めるのでそちらをご利用ください

Answer 1

テーブル定義やインデックス定義はCREATE TABLE, CREATE INDEX を、統計情報を取得 し、実行計画 を 対応しているMarkdownの機能 の[コードを入力]を使ってください。

saziさんのコードを一部修正。

          AND b.カラムC<='20180930'
          AND b.カラムD>='20180901'

          ↓

          AND b.カラムC<=TO_DATE('20180930','YYYYMMDD')
          AND b.カラムD>=TO_DATE('20180901','YYYYMMDD')

full scan になり易いので暗黙の型変換に頼ってはなりません。

Comments

[sazi]

テーブルAでは型変換行っていますし、テーブルBのサンプルも日付型ではないようですから、特に変換はかけていません。

[Orlofsky]

あら、date型じゃないのね。
このシステムの開発はいばらの道ですなぁ。

[maxLightning]

Orlofskyさん、回答ありがとうございます。
ありがとうございます
いばらのみちを突き進みます

[Orlofsky]

変に設計してしまったテーブルを直す、って選択肢もあります。

Answer 2

見辛いので、整形しました。

SELECT *
FROM  テーブルa,テーブルb
WHERE a.カラムB=b.カラムB
 AND (a.カラムA, a.カラムC, b.カラムA, b.カラムC) IN (
        SELECT a.カラムA, MAX(a.カラムC), b.カラムA, MAX(b.カラムC)
        FROM  テーブルa, テーブルb
        WHERE a.カラムB=b.カラムB
          AND a.カラムC<=TO_DATE('20180930','YYYYMMDD')
          AND a.カラムD>=TO_DATE('20180901','YYYYMMDD')
          AND b.カラムC<='20180930'
          AND b.カラムD>='20180901'
        GROUP BY  a.カラムA, b.カラムA
      )

主キー
a.カラムA, a.カラムC、
b.カラムA, b.カラムC

index
a.カラムA, a.カラムC、
b.カラムA, b.カラムC

同じ内容のインデックスなので、無駄です。

現状でインデックスが効かないなら、インデックスを以下のようにしてみる。

index

--サブクエリー用
a.カラムC, a.カラムD, a.カラムB, a.カラムA
b.カラムC, b.カラムD, b.カラムB, b.カラムA
--メイン用
a.カラムB, a.カラムA, a.カラムC
b.カラムB, b.カラムA, b.カラムC
または
a.カラムA, a.カラムC, a.カラムB
b.カラムA, b.カラムC, b.カラムB

where条件やgroup by 項目などがインデックスに含まれるようにする事と、
範囲の大きいものから小さいもの順にインデックス項目が並ぶようにすると良いと思います。

インデックスの並びを変えたりしながら、実行計画を見つつチューニングして下さい。

追記

SQLを別な組み立てにすると、以下の様になるかと思います。

select * 
from テーブルa as a inner join テーブルb b 
     on a.カラムB=b.カラムB
where   a.カラムC = (
            select max(カラムC) from テーブルa 
            where カラムA=a.カラムA
              and カラムC<=TO_DATE('20180930','YYYYMMDD') 
              and カラムD>=TO_DATE('20180901','YYYYMMDD')
        )
    and b.カラムC = (
            select max(カラムC) from テーブルb 
            where カラムA=b.カラムA
              and カラムC<='20180930'
              and カラムD>='20180901'
        )

または、

select * 
from テーブルa as a inner join テーブルb b 
     on a.カラムB=b.カラムB
where   (a.カラムA, a.カラムC) in (
            select カラムA, max(カラムC) from テーブルa 
            where カラムC<=TO_DATE('20180930','YYYYMMDD')
              and カラムD>=TO_DATE('20180901','YYYYMMDD')
            group by カラムA
        )
    and (b.カラムA, b.カラムC = (
            select カラムA, max(カラムC) from テーブルb 
            where カラムA=b.カラムA
              and カラムC<='20180930'
              and カラムD>='20180901'
            group by カラムA
        )

こっちだと、

--サブクエリー用
a.カラムA, a.カラムC, a.カラムD
b.カラムA, b.カラムC, b.カラムD
--メイン用
a.カラムB, a.カラムA, a.カラムC
b.カラムB, b.カラムA, b.カラムC

もう1パターン。（これが一番素直な絞り込み[高速？]な気がします）

select * 
from テーブルa as a inner join テーブルb b 
     on a.カラムB=b.カラムB
where   a.カラムC<=TO_DATE('20180930','YYYYMMDD')
    and a.カラムD>=TO_DATE('20180901','YYYYMMDD')
    and a.カラムC=(
            select max(カラムC) from テーブルa where カラムA=a.カラムA
        )
    and b.カラムC<='20180930'
    and b.カラムD>='20180901'
    and b.カラムC=(
            select max(カラムC) from テーブルb where カラムA=b.カラムA
        )

Comments

[maxLightning]

お忙しい中、早急な回答ありがとうございます。
回答のとおり確認してみます。

[maxLightning]

回答ありがとうございます。
お忙しい中、SQL文を組み立ててくれてありがとうございます。