(cache) Oracle Technology Network (OTN) Japan - 掲示板 : union を使用したビューからテーブルへコピーすると、該当件数が減少する ...

u23u23

投稿数: 5
登録日時: 07/02/05

union を使用したビューからテーブルへコピーすると、該当件数が減少する
投稿時刻: 2007/07/09 18:05

返信

こんにちは。

以下のような現象が発生しており、
原因が分からず困っております。

似たような現象が発生された方がおられましたら
情報をいただけないでしょうか？

-----------------------------------------------------------------------
1.以下の構成のビューを用意
create view V00 as
select * from T01
union
select * from T02
/

2. ﾊﾟｯｹｰｼﾞ中で以下のSQLを実行する
　insert into T00
select * from V00;

3. ﾊﾟｯｹｰｼﾞを実行すると、T00にコピーされる内容が、
　select * from V00
　と単独で実行させたSQLの内容と極端に異なる。

　具体的には、V00の構成SQLの
　 select c from T01
　の最初のレコード(1件)と
　 select c from T02
の結果レコード全件が合わさったレコードが記録される。

4. 念のため、T00に記録する際に、
　同時にT01とT02の内容をT01BとT02Bに記録しておき、
select c from T01B
union
select c from T02B
　として実行させても、
T00に記録された内容と比べても一致しない。

※T00, T01, T02, T01B, T02Bの定義は同じです。
-----------------------------------------------------------------------

もしもし

投稿数: 2,927
登録日時: 00/10/21

Re: union を使用したビューからテーブルへコピーすると、該当件数が減少する
投稿時刻: 2007/07/09 18:32

u23u23 さんへの返信です。

返信

重複データは？

jiropochi

投稿数: 5,213
登録日時: 00/04/03

Re: union を使用したビューからテーブルへコピーすると、該当件数が減少する
投稿時刻: 2007/07/09 18:32

u23u23 さんへの返信です。

返信

パッケージを使用せずにSQL*Plusから直接insert select文を
実行しても結果がおかしいでしょうか？

gojara

投稿数: 1,049
登録日時: 02/03/27

Re: union を使用したビューからテーブルへコピーすると、該当件数が減少する
投稿時刻: 2007/07/09 18:37

u23u23 さんへの返信です。

返信

union を union all にすれば良い要件かも？

茶太郎

投稿数: 11,812
登録日時: 99/03/15

Re: union を使用したビューからテーブルへコピーすると、該当件数が減少する
投稿時刻: 2007/07/09 19:42

u23u23 さんへの返信です。

返信

パッケージの中身と具体的な実行内容(sqlplusでの実行結果)を
そのまま提示した方が良いかと。

u23u23

投稿数: 5
登録日時: 07/02/05

Re: union を使用したビューからテーブルへコピーすると、該当件数が減少する
投稿時刻: 2007/07/09 20:02

jiropochi さんへの返信です。

返信

この現象は、直接 SQLを実行したときには発生しません。

このプロシージャは、一日に0？数回使用しているのですが、
毎回現象が発生するわけではなく、
1日のうち最初のほうでのみ発生します。

最初の方というのもあいまいで、何が基準かが分からないのですが、
一日の中で最初から数回まで発生する現象で、
発生しなくなるとその日は発生しません。

発生しない日もあります。

再現しようとしても、何度やっても再現できないので困っています。

asahide

投稿数: 1,373
登録日時: 07/02/23

Re: union を使用したビューからテーブルへコピーすると、該当件数が減少する
投稿時刻: 2007/07/09 20:07

茶太郎さんへの返信です。

返信

ついでに、DBの詳細なバージョンもご提示頂ければ。

ushitaki

投稿数: 7,079
登録日時: 98/10/30

Re: union を使用したビューからテーブルへコピーすると、該当件数が減少する
投稿時刻: 2007/07/09 22:39

u23u23 さんへの返信です。

返信

半角カタカナは利用規約上、遠慮願いますと書かれていますので
なるべく、(特別な理由が無い限り)使用しないで下さい。
http://otn.oracle.co.jp/info/forum/agreement.html
○数字とか一部の特殊文字(機種依存文字)は投稿後暫く経つと
？に化けますので使わないで下さい(今回は入っていませんが)。

また、同じく再現できないまでにしても
ほとんどの場合は環境(OS,Oracle のバージョンなど)は明らかにした方が
良いかも知れません。

さておき、パッケージ内で以下を行った結果はどうなりますか？

insert into t00_test0
select * from
(select * from t01 union select * from t02)
;

insert into t00_test1
select * from t01
;

insert into t00_test2
select * from t02
;

ところで、条件などは入っていませんか？
あるいは実際の union がもっと複雑で何段もの View になっているとかはありませんか？

insert select してから確認までの間に t01,t02 ともデータは絶対に変化ありませんか？
同じスキーマの同じテーブルなのは確かですか？

「一日の中で最初から」と言う一日の定義は何ですか？
その一日が始まる前に行われている処理は何ですか？
その処理が残っていて古いデータを見続けているとか
あるいはその後の処理で何か環境やSQLの実行パスが変わるような事はありませんか？

茶太郎

投稿数: 11,812
登録日時: 99/03/15

Re: union を使用したビューからテーブルへコピーすると、該当件数が減少する
投稿時刻: 2007/07/09 23:56

u23u23 さんへの返信です。

返信

> 最初の方というのもあいまいで、何が基準かが分からないのですが、
> 一日の中で最初から数回まで発生する現象で、
> 発生しなくなるとその日は発生しません。
>
> 発生しない日もあります。
>
> 再現しようとしても、何度やっても再現できないので困っています。

そういう場合は詳細なログをとった方が良いかと。

他には処理中に他から更新が入る可能性はありませんか？
監査等で確認してみては。

u23u23

投稿数: 5
登録日時: 07/02/05

Re: union を使用したビューからテーブルへコピーすると、該当件数が減少する
投稿時刻: 2007/07/10 17:58

ushitaki さんへの返信です。

返信

ご返信ありがとうございます。
半角カタカナや機種依存文字の使用に関しましては、
以後気をつけます。

OSはSolaris 9
Oracleのバージョンは、Oracle10g Database Standard Edition 10.2.0.2.0
です。

確かに実際のView定義は少し複雑です。

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

以下の定義のテーブルがあった場合、
テーブル TBL_U(
　ID number(10,0),
　SEQ number(5,0),
　CD varchar2(6),
　TYPE number(1),
　FLG number(1,0),
　CD_S varchar2(9),
　DTE date
) primary key(ID, SEQ)

テーブル TBL_S(
　ID number(10,0),
　SEQ number(5,0),
　CD varchar2(9),
　FLG number(1,0),
　DTE date
) primary key(ID, SEQ, )

テーブル TBL_SS(
　ID number(10,0),
　SEQ number(5,0),
　DIV number(1,0),
　ID_S number(10,0),
　VAL number(10,0),
　DTE date
) primary key(ID, SEQ, DIV, ID_S)

テーブル TBL_C(
　ID number(10,0),
　SEQ number(5,0),
　FLG number(1,0),
　DTE date
) primary key(ID, SEQ, )

テーブル TBL_CS(
　ID number(10,0),
　SEQ number(5,0),
　ID_U number(10,0),
　FLG_C number(1,0)
) primary key(ID, SEQ, ID_U)

ビュー定義は以下の通りです。

CREATE OR REPLACE VIEW V01
　　(DTE,CD_U,CD_U2)
AS
select
　GREATEST(U.DTE,S.DTE,U2.DTE) as dte,

　U.CD as CD_U,
　U2.CD as CD_U2,
　SS.VAL as VAL
from TBL_U U
inner join TBL_S S
　on U.CD_S　= S.CD and S.FLG = 1
　　and not exists(select * from TBL_SS SS where S.ID = SS.ID and S.SEQ = SS.SEQ and SS.DIV = 2)
　　and exists(select * from TBL_SS SS, TBL_U U2 where S.ID = SS.ID and S.SEQ = SS.SEQ and SS.DIV = 1
　　　and SS.ID_S = U2.ID and U2.FLG = 1 and U2.TYPE = 1
　　　and U2.CD = U.CD and SS.VAL = 1
　　)
inner join TBL_SS SS
　on S.ID = SS.ID and S.SEQ = SS.SEQ and SS.DIV = 1
inner join TBL_U U2
　on SS.ID_S = U2.ID and U2.FLG = 1 and U2.TYPE = 1
where U.FLG = 1
union
select
　GREATEST(U.DTE, S.DTE, C.DTE, U3.DTE, U2.DTE) as dte,

　U.CD as CD_U,
　case SS.DIV
　　when '1' then U3.CD
　　when '2' then U2.CD
　end as CD_U2,
　case SS.DIV
　　when '1' then SS.VAL
　　when '2' then SS.VAL * CS.VAL
　end as VAL
from TBL_U U
inner join TBL_S S
　on U.CD_S　= S.CD and S.FLG = 1
　　and (select count(*) from TBL_SS SS where S.ID = SS.ID and S.SEQ = SS.SEQ and SS.DIV = 2) = 1
　　and exists(select * from TBL_SS SS, TBL_C C, TBL_CS CS, TBL_U U2
　　　where S.ID = SS.ID and S.SEQ = SS.SEQ and SS.DIV = 2
　　　　and SS.ID_S = C.ID and C.FLG = 1
　　　　and C.ID = CS.ID and C.SEQ = CS.SEQ and CS.FLG_C = 1
　　　　and CS.ID_U = U2.ID and U2.FLG = 1 and U2.TYPE = 1

　　　　and U2.CD = U.CD and SS.VAL = 1 and CS.VAL = 1
　　)
inner join TBL_SS SS
　on S.ID = SS.ID and S.SEQ = SS.SEQ and SS.DIV in (1,2)
left join TBL_C C
　on SS.ID_S = C.ID and C.FLG = 1
left join TBL_CS CS
　on C.ID = CS.ID and C.SEQ = CS.SEQ
left join TBL_U U2
　on CS.ID_U = U2.ID and U2.FLG = 1 and U2.TYPE = 1 and U.CD = U2.CD
left join TBL_U U3
　on SS.ID_S = U3.ID and U3.FLG = 1 and U3.TYPE = 1
where
　U.FLG = 1
　and not(U2.CD is null and U3.CD is null)

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

unionの上下のSQLをそれぞれ別のテーブルに記録したのですが、
それぞれの内容は正しい件数がinsertされます。
その場合でも、union した場合のみ、
上側のSQLのレコードが1件だけになりました。

union をやめ、union allを使用し、
その結果をサブクエリとしてgroup by を使用し
unionと同じ結果が出るビューを作成し、
同じタイミングで記録するようにすると、
そちらの場合は正しい件数でコピーされます。

ビューを構成しているそれぞれのテーブルの内容も、
パッケージ内でinsertする際に記録しているのですが、
変化はありません。
テーブルにはトリガーなども設定されていません。

また、一日の最初と申し上げましたが、
ログから詳しく調べてみると、
必ずしも1日の最初とは限りませんでした。

また、より詳細なログを、実行の前後で取得するようにしました。
もう少し詳しく調べてみます。

tokizawa

投稿数: 320
登録日時: 02/05/12

Re: union を使用したビューからテーブルへコピーすると、該当件数が減少する
投稿時刻: 2007/07/10 18:14

u23u23 さんへの返信です。

返信

>union をやめ、union allを使用し、
>その結果をサブクエリとしてgroup by を使用し
>unionと同じ結果が出るビューを作成し、
>同じタイミングで記録するようにすると、
>そちらの場合は正しい件数でコピーされます。

マニュアルは調べましたか？
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19201-02/queries.html#3515
>UNIONの例
>次の文は、UNION演算子によって2つの問合せの結果を結合しています。
>結果に重複行は含まれません。

ushitaki

投稿数: 7,079
登録日時: 98/10/30

Re: union を使用したビューからテーブルへコピーすると、該当件数が減少する
投稿時刻: 2007/07/10 19:59

u23u23 さんへの返信です。

返信

>union をやめ、union allを使用し、
>その結果をサブクエリとしてgroup by を使用し
>unionと同じ結果が出るビューを作成し、
>同じタイミングで記録するようにすると、
>そちらの場合は正しい件数でコピーされます。

上記を explain plan もしくは sql trace にて実行パスを確認して見てください。
group by ではなく distinct を使用して下さい。

insert テーブル
select distinct * from
(第1のselect
union all
第2のselect)

パッケージで実行すると上記と下記の結果が異なると言う事ですよね？

insert テーブル
select * from
(第1のselect
union
第2のselect)

で、ヒントテキストで、ビューと外側のSQL を一緒にして( merge だっけかなんだっけか )
実行計画を組み立てると言ったものがあったはずなので
それを調整して見るとかすると糸口が見つかるかも知れないし
他の問題かも知れません。

asahide

投稿数: 1,373
登録日時: 07/02/23

Re: union を使用したビューからテーブルへコピーすると、該当件数が減少する
投稿時刻: 2007/07/11 10:04

ushitaki さんへの返信です。

返信

＞ヒントテキストで、ビューと外側のSQL を一緒にして( merge だっけかなんだっけか )

こちらのMERGEとかNO_MERGE辺りですね。
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19207-01/hintsref.html#964810

もしくはCOMPLEX_VIEW_MERGINGで検索すると色々と情報が得られますね。


ウェブサイトのご使用条件 \| 個人情報保護基本方針/情報保護基本方針