Forum Stats

  • 3,837,920 Users
  • 2,262,309 Discussions
  • 7,900,433 Comments

Discussions

FROMでの副問合せに関数をかませた結果列を複数列で利用すると、利用した列数分関数が呼ばれる

988825
988825 Member Posts: 4
edited Aug 29, 2017 4:09PM in SQLとPL/SQL

大量のテストデータを作りにあたり、少々困ったことがありまして、質問させてください。

既存の大量データを持つテーブルAと同じ数のレコードを、全く別のテーブルBに挿入しよう

としています。テーブルBにレコードを挿入するにあたり、一部の列にはテーブルAの列の値

をそのまま投入します。さらに、テーブルBには、レコード毎にランダムで同じ値を入れたい

列が複数存在します。

上記のようなデータを作成するにあたり、以下のようなパッケージ、およびINSERT文を作成

して動作確認したのですが、「複数列に対し、行毎にランダムで値の異なる値を設定」が

うまく機能せず、行毎だけでなく列毎にランダムとなってしまいました。以下、単純化した

再現可能なサンプルソースです。

-- パッケージ宣言

CREATE OR REPLACE PACKAGE TEST_PAC AS

    -- ランダムな文字列10バイトを返却する

    FUNCTION GET_RANDOM RETURN VARCHAR2;

END TEST_PAC;

/

-- パッケージ本体

CREATE OR REPLACE PACKAGE BODY TEST_PAC AS

    -- ランダムな文字列10バイトを返却する

    FUNCTION GET_RANDOM RETURN VARCHAR2 AS

    BEGIN

        DBMS_OUTPUT.PUT_LINE('GET_RANDOM called!');

        RETURN DBMS_RANDOM.STRING('x', 10);

    END GET_RANDOM;

END TEST_PAC;

/

-- テーブルA

CREATE TABLE A (

    COL1 VARCHAR2(10),

    COL2 VARCHAR2(10)

);

INSERT INTO A (COL1, COL2) VALUES ('oooo', 'pppp');

INSERT INTO A (COL1, COL2) VALUES ('qqqq', 'rrrr');

-- テーブルB

CREATE TABLE B (

    COLX VARCHAR2(10),

    COLY VARCHAR2(10),

    COLZ VARCHAR2(10)

);

SET SERVEROUTPUT ON;

-- テーブルAからテーブルBのレコードを挿入

INSERT INTO B (

    COLX,

    COLY,

    COLZ

) SELECT

    A_REC.COL1,

    A_REC.RANDOM_STR,

    A_REC.RANDOM_STR

FROM (

    SELECT

        COL1,

        TEST_PAC.GET_RANDOM() AS RANDOM_STR

    FROM A

) A_REC;

-- 上記SQL実行により、以下が出力される(2行×2列分で4回実行されている)

GET_RANDOM called!

GET_RANDOM called!

GET_RANDOM called!

GET_RANDOM called!

-- レコード内容確認

SELECT * FROM B;

COLX       COLY       COLZ

---------- ---------- ----------

oooo       I90VJJ9P5O Z7Y9CYTNN7

qqqq       SAILHIHFS8 MURUB7O177

上記の通り、2つのレコードのCOLY, COLZの値が異なる、という結果となっています。

意図としては、テーブルAのレコード数だけTEST_PAC.GET_RANDOM()が呼ばれ、返却値を

レコード毎に使い回して欲しいのですが、そうなっていません。

これに対し、以下のように DBMS_RANDOM を直接利用すると、意図した結果となっています。

-- テーブルBの内容を削除

DELETE FROM B;

-- DBMS_RANDOM パッケージを直接用いて挿入

INSERT INTO B (

    COLX,

    COLY,

    COLZ

) SELECT

    A_REC.COL1,

    A_REC.RANDOM_STR,

    A_REC.RANDOM_STR

FROM (

    SELECT

        COL1,

        DBMS_RANDOM.STRING('x', 10) AS RANDOM_STR

    FROM A

) A_REC;

-- レコード内容確認

COLX       COLY       COLZ

---------- ---------- ----------

oooo       N4RH2CVW4B N4RH2CVW4B

qqqq       P3XSGURCZ0 P3XSGURCZ0

実際のパッケージファンクションは、上記の例よりずっと複雑で、上記のように

インサート文に直接記述することができません。また、内部に DBMS_RANDOMを使用

しているため、RESULT_CACHEを指定することもできません。

普通こんなこと、やらないだろう、と思われるかもしれませんが、何かいい手は

ありますでしょうか?

よろしくお願いします。

Best Answer

  • tlokweng
    tlokweng Member Posts: 3 Blue Ribbon
    edited Aug 15, 2017 10:51AM Answer ✓

    クエリトランスフォーメーションのビューマージで2階層のクエリが1階層に置換され、結果として各行でプロシジャが2回づつ呼ばれているということですね。なので単純にプロシジャを呼んでいるインラインビューがマージされないようにすれば解決します。素直にNO_MERGEヒントでOKでしょう。テーブルサイズが大きくなければWITHにしてファクタリングするのもアリですね。

    > create table test_table(n) as select 1 from dual union select 2 from dual;

    > create function test_func return varchar2 as begin RETURN DBMS_RANDOM.STRING('x', 10); end;

    > select n, r r1, r r2 from (select n, test_func r from test_table);

            N R1          R2

    --------- ------------ ------------

            1 AJ1LNZ30HD  LAMQV4CQUY

            2 F911DGQDYZ  ID8FDREGKF

    > select n, r r1, r r2 from (select /*+ no_merge */ n, test_func r from test_table);

    --------- ------------ ------------

            1 K5MPMUR3DQ  K5MPMUR3DQ

            2 DD2Y5NUWPM  DD2Y5NUWPM

    > with t as (select /*+ materialize */ n, test_func r from test_table) select n, r r1, r r2 from t;

    --------- ------------ ------------

            1 0F2RE7Y8C3  0F2RE7Y8C3

            2 O6C4WCBAFP  O6C4WCBAFP

Answers

  • tlokweng
    tlokweng Member Posts: 3 Blue Ribbon
    edited Aug 15, 2017 10:51AM Answer ✓

    クエリトランスフォーメーションのビューマージで2階層のクエリが1階層に置換され、結果として各行でプロシジャが2回づつ呼ばれているということですね。なので単純にプロシジャを呼んでいるインラインビューがマージされないようにすれば解決します。素直にNO_MERGEヒントでOKでしょう。テーブルサイズが大きくなければWITHにしてファクタリングするのもアリですね。

    > create table test_table(n) as select 1 from dual union select 2 from dual;

    > create function test_func return varchar2 as begin RETURN DBMS_RANDOM.STRING('x', 10); end;

    > select n, r r1, r r2 from (select n, test_func r from test_table);

            N R1          R2

    --------- ------------ ------------

            1 AJ1LNZ30HD  LAMQV4CQUY

            2 F911DGQDYZ  ID8FDREGKF

    > select n, r r1, r r2 from (select /*+ no_merge */ n, test_func r from test_table);

    --------- ------------ ------------

            1 K5MPMUR3DQ  K5MPMUR3DQ

            2 DD2Y5NUWPM  DD2Y5NUWPM

    > with t as (select /*+ materialize */ n, test_func r from test_table) select n, r r1, r r2 from t;

    --------- ------------ ------------

            1 0F2RE7Y8C3  0F2RE7Y8C3

            2 O6C4WCBAFP  O6C4WCBAFP

  • 988825
    988825 Member Posts: 4
    edited Aug 29, 2017 4:09PM

    tlokweng様、お礼が遅くなってしまい、申し訳ありません。サンプルまでお示しくださいまして、誠にありがとうございます。

    クエリトランスフォーメーションがこのようなSQLに対し作用しているとは全く一度も想像すらつきませんでした。

    頂戴したサンプルで動作も確認出来ましたので、本来の組み込み先に入れてみようと思います。

    ありがとうございました。

This discussion has been closed.