Forum Stats

  • 3,838,710 Users
  • 2,262,394 Discussions
  • 7,900,739 Comments

Discussions

GROUP BYをEXISTSに変更したい

YASUYOKA
YASUYOKA Member Posts: 17
edited Jan 15, 2017 9:36PM in SQLとPL/SQL

首記の件について、ご質問をいたしますので、よろしくお願いいたします。

まず、環境から。

DBサーバのO/S:Windows Server 2012

                          20コア、56GBメモリ

DB環境:Oracle 12cR1(12.1.0.2) SE

             SGA:28GB,PGA:14GB

プログラム環境:PL/SQLパッケージ内関数

ご質問内容

1.メインとなるテーブルがあります。HOGE1と略します。

2.対象環境となるサブテーブルがあります。HOGE2と略します。

3.マスターテーブルが2つあり、HOGE3とHOGE4と略します。

  このマスターテーブルは、顧客コード(CUST_CD)、変換元コード(ORG_CD)、変換先コード(TRG_CD)

  の構成となっています。

4.変換中間テーブルがあります。HOGE5と略します。

  このテーブルは、変換元コード(ORG_CD)、変換中間コード(NID_CD)、変化先名(TRG_NM)

  の構成となっています。

5.これらのテーブルを毛合して、次の様なカーソル用SELECT文を作成しました。

  SELECT /*+ ALL_ROWS PARALLEL(10) */

             a.COL1

            ,a.COL2

               ・

               ・

               ・

           ,c.TRG_C

           ,d.TRG_C

           ,e.TRG_C

               ・

               ・

               ・

            ,f,TRG_NM

               ・

               ・

               ・

  FROM

         HOGE1 a

        ,HOGE2 b

        ,HOGE3 c

        ,HOGE3 d

        ,HOGE4 e

        ,HOGE5 f

   WHERE

        a.対応カラム = b.対応カラム

   AND

        a.HOGE3対応カラム1 = c.ORG_CD(+)

  AND

       c.CUST_C = '顧客コード

   AND

        a.HOGE3対応カラム2 = d.ORG_CD(+)

  AND

      d.CUST_C = '顧客コード

  AND

      a.HOGE3対応カラム3 = e.ORG_CD(+)

  AND

      e.CUST_C = '顧客コード

  AND

      a.HOGE3対応カラム4 = f.NID_CD(+)

;

このSQLを実行すると、重複行が発生するので、SELECTしている全カラムをその順番でGROUP BYしました。

6.発生した現象

 ・HOGE1テーブルとHOGE2テーブルは約一千万件あります。

 ・その他のテーブルは約500件程度です。

 ・実行した場合に約5時間経っても結果が帰りません。

7.望まれるパフォーマンスは、1時間以内に結果を返すことです。

 以前に、単一テーブルの大量データの重複を無くすために、

 EXISTS句を使った記憶があるのですが、上記のような、

 多数テーブルの結合でのEXISTS句の経験がありません。

 また、依頼者からもEXISTS句を使用した方がパフォーマンス

 が良くなる可能性大なので、EXISTS句を使用するように

 支持がでています。

大変初心者的な質問で申し訳ありませんが、上記のSQLを

EXISTS句を使用した形へと整形していただけるとありがたいです。

以上、長文となりましたが、よろしくお願いします。

Best Answer

  • YASUYOKA
    YASUYOKA Member Posts: 17
    edited Jan 15, 2017 9:36PM Answer ✓

    元質問者です。

    質問内容は、テーブルの結合による重複レコードが発生していた為発生していた現象です。

    今回、hoge3,hoge4,hoge5の各テーブルを、カーソルを読み込む前に、PL/SQLコレクション

    へと各テーブルを全件読み込んでおいて、カーソルではhoge3,hoge4,hoge5の結合を止める

    こととしました。

    そのうえで、カーソルを読み込んだ後に、それぞれの格納PL/SQLコレクションを検索して、

    必要データを編集する方法へと、方法論を変更いたしました。

    これにより、約1,000万件のデータを処理するのに、45分程度で終了するようになりました。

    ご検討していただいた皆様、ありがとうございました。

Answers

  • YASUYOKA
    YASUYOKA Member Posts: 17
    edited Jan 15, 2017 9:36PM Answer ✓

    元質問者です。

    質問内容は、テーブルの結合による重複レコードが発生していた為発生していた現象です。

    今回、hoge3,hoge4,hoge5の各テーブルを、カーソルを読み込む前に、PL/SQLコレクション

    へと各テーブルを全件読み込んでおいて、カーソルではhoge3,hoge4,hoge5の結合を止める

    こととしました。

    そのうえで、カーソルを読み込んだ後に、それぞれの格納PL/SQLコレクションを検索して、

    必要データを編集する方法へと、方法論を変更いたしました。

    これにより、約1,000万件のデータを処理するのに、45分程度で終了するようになりました。

    ご検討していただいた皆様、ありがとうございました。

This discussion has been closed.