質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.37%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 4,158

score 13

初心者です。
記載されている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句が使っているためインデックスが効かず、フルスキャンされてしまう。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • m.ts10806

    2018/11/20 07:10

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

    キャンセル

回答 2

checkベストアンサー

0

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

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
        )

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/11/20 08:01

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

    キャンセル

  • 2018/11/20 12:36

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

    キャンセル

0

テーブル定義やインデックス定義は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 になり易いので暗黙の型変換に頼ってはなりません。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/11/20 09:23

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

    キャンセル

  • 2018/11/20 09:29

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

    キャンセル

  • 2018/11/20 12:32

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

    キャンセル

  • 2018/12/06 16:41

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.37%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る