OracleとSQL Serverの空白の扱いの違いについて(異なるデータベースをリンクさせた場合)
前回の続きです。
今度はSQL Sever、Oracle間でデータベースをリンクさせた場合の挙動について調べてみました。
検証
テーブル
前回の作ったものをそのまま使用した。
DBMSも前回と同様Oracle19cとSQLServer2019で行った。
CREATE TABLE CompareTestTable
(
Seq NUMBER
,ColCHAR CHAR(10)
,ColVARCHAR VARCHAR2(10)
);
INSERT INTO CompareTestTable VALUES( 1, 'aaa', 'bbb' ); -- スペースなし
INSERT INTO CompareTestTable VALUES( 2, 'aaa ', 'bbb ' ); -- 文字列の後にスペース
INSERT INTO CompareTestTable VALUES( 3, ' aaa', ' bbb' ); -- 文字列の前にスペース
INSERT INTO CompareTestTable VALUES( 4, ' aaa ', ' bbb ' ); -- 文字列の前後にスペース
CREATE TABLE CompareTestTable
(
Seq INT
,ColCHAR CHAR(10)
,ColVARCHAR VARCHAR(10)
)
INSERT INTO CompareTestTable VALUES( 1, 'aaa', 'bbb' ); -- スペースなし
INSERT INTO CompareTestTable VALUES( 2, 'aaa ', 'bbb ' ); -- 文字列の後にスペース
INSERT INTO CompareTestTable VALUES( 3, ' aaa', ' bbb' ); -- 文字列の前にスペース
INSERT INTO CompareTestTable VALUES( 4, ' aaa ', ' bbb ' ); -- 文字列の前後にスペース
SQL Server ⇒ Oracle (SQL Serverのリンクサーバーを使用して、Oracleのテーブルを参照)
※リンクサーバーは"ORACLE"という名前で作成。テーブルはOracleデータベースの"TEST_USER"のスキーマ内に配置。
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColCHAR = 'aaa'; -- SEQ=1,2のデータがヒット
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColCHAR = 'aaa '; -- SEQ=1,2のデータがヒット
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColCHAR = ' aaa'; -- SEQ=3,4のデータがヒット
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColCHAR = ' aaa '; -- SEQ=3,4のデータがヒット
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColVARCHAR = 'bbb'; -- SEQ=1,2のデータがヒット
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColVARCHAR = 'bbb '; -- SEQ=1,2のデータがヒット
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColVARCHAR = ' bbb'; -- SEQ=3,4のデータがヒット
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColVARCHAR = ' bbb '; -- SEQ=3,4のデータがヒット
前回のSQL Serverの検証結果と同じになった。
現象から推測すると、一旦OracleのデータをSQL Server側に持ってきて、そのあとSQL Serverで改めて検索条件を評価しているのだろうか。
ちなみにOPENQUERYを使って、Oracle側でクエリを実行させると、前回のOracleの検証結果と同じになった。当然ではあるが。
select * from openquery(ORACLE,'select * from CompareTestTable where ColCHAR = ''aaa''') -- SEQ=1,2のデータがヒット
select * from openquery(ORACLE,'select * from CompareTestTable where ColCHAR = ''aaa ''') -- SEQ=1,2のデータがヒット
select * from openquery(ORACLE,'select * from CompareTestTable where ColCHAR = '' aaa''') -- SEQ=3,4のデータがヒット
select * from openquery(ORACLE,'select * from CompareTestTable where ColCHAR = '' aaa ''') -- SEQ=3,4のデータがヒット
select * from openquery(ORACLE,'select * from CompareTestTable where ColVARCHAR = ''bbb''') -- SEQ=1のデータがヒット
select * from openquery(ORACLE,'select * from CompareTestTable where ColVARCHAR = ''bbb ''') -- SEQ=2のデータがヒット
select * from openquery(ORACLE,'select * from CompareTestTable where ColVARCHAR = '' bbb''') -- SEQ=3のデータがヒット
select * from openquery(ORACLE,'select * from CompareTestTable where ColVARCHAR = '' bbb ''') -- SEQ=4のデータがヒット
Oracle ⇒ SQL Server (OracleのDatabase Linkを使用して、SQL Serverのテーブルを参照)
※Database Linkは"SQL_SERVER"という名前で作成。
select * from COMPARETESTTABLE@SQL_SERVER where "ColCHAR" = 'aaa'; -- SEQ=1,2のデータがヒット
select * from COMPARETESTTABLE@SQL_SERVER where "ColCHAR" = 'aaa '; -- SEQ=1,2のデータがヒット
select * from COMPARETESTTABLE@SQL_SERVER where "ColCHAR" = ' aaa'; -- SEQ=3,4のデータがヒット
select * from COMPARETESTTABLE@SQL_SERVER where "ColCHAR" = ' aaa '; -- SEQ=3,4のデータがヒット
select * from COMPARETESTTABLE@SQL_SERVER where "ColVARCHAR" = 'bbb'; -- SEQ=1,2のデータがヒット
select * from COMPARETESTTABLE@SQL_SERVER where "ColVARCHAR" = 'bbb '; -- SEQ=1,2のデータがヒット
select * from COMPARETESTTABLE@SQL_SERVER where "ColVARCHAR" = ' bbb'; -- SEQ=3,4のデータがヒット
select * from COMPARETESTTABLE@SQL_SERVER where "ColVARCHAR" = ' bbb '; -- SEQ=3,4のデータがヒット
こちらは意外な結果になった。
前回のOracleの検証結果と同じ結果になるかと思いきや、SQL Serverと同じになった。
現象から推測すると、SQL Server側でクエリが実行されているのだろうか。
気になったので、以下の記事を参考にSQL Server側のクエリの履歴を調べてみた。
SELECT TOP 1000
--作成時間
QS.creation_time,
--SQL文
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1
) AS statement_text,
--実行SQL文
ST.text,
--実行計画
QS.total_worker_time,
QS.last_worker_time,
QS.max_worker_time,
QS.min_worker_time
FROM
sys.dm_exec_query_stats QS
--キーワード
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
ORDER BY
QS.creation_time DESC
結果、やはりクエリはSQL Server側で実行されていることが分かった。
まとめ
前回の検証も含めて、分かったことは以下の通り。
文字列前後に空白が入っているデータを扱う場合、
- ORACLE、SQL ServerともCHARのカラムであれば同じ検索結果になるが、VARCHARカラムの場合は異なる検索結果になる。
- SQL Server ⇒ Oracle(SQL Serverのリンクサーバーを使用して、Oracleのテーブルを参照)時は、SQL Server側の検索仕様で問い合わせされる。
- Oracle ⇒ SQL Server (OracleのDatabase Linkを使用して、SQL Serverのテーブルを参照)時も、SQL Server側の検索仕様で問い合わせされる。
上記3.をパフォーマンス改善等で修正する際、Oracle Database Linkを使用したアクセスからマテリアライズドビューに変更するケースも実際の業務だとあると思う。
しかしマテリアルズドビューの実体はただのOracleのテーブルのため、マテリアルズドビュー介した検索は前回のOracleの検証結果と同じになる。
従って安易に変更すると、変更前後で同じ結果が返ってこない可能性がある。
(というか、私がDatabase Linkを使用したアクセスからマテリアライズドビューに変更した際に実際に経験した。この記事を書こうと思った経緯でもあるのだが。。。)
Discussion
はじめまして、よろしければご教示ください。
同じようにSQLServerからオラクルにリンクサーバーを利用しています。
現状はSQLServer2014の32BitでMSDAORAを利用し、問題なく実装出来ています。
SQLServerを64Bitする必要性が出てきたため、SQLSERVERを64BITに変更し。
プロバイダーを、MSDAORAからOraOLEDB.Oracleに変更しました。
(64bitではMSDAORAが存在しないため)
環境は下記です
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production(こちらは正常時から64Bitです。)
SQLSERVER2014 64bit(バージョン15.0.4198.2)エクスプレスです。
OPENQUERYを利用して、INSERTを実行したところ全角文字を含むフィールドに関する処理は失敗するようになりました。
INSERTした場合
INSERT OPENQUERY (SVRSC01, 'SELECT DATKB, SIRMSTKB, SIRCD, SIRNMA
FROM SIRMTA')
VALUES('1','4','999998','イ')
結果
リンク サーバー "SVRSC01" の OLE DB プロバイダー "OraOLEDB.Oracle" から、メッセージ "ORA-12899: 列"BNG_USR1"."SIRMTA"."SIRNMA"の値が大きすぎます(実際: 31、最大: 30)" が返されました。
メッセージ 7343、レベル 16、状態 2、行 1
リンク サーバー "SVRSC01" の OLE DB プロバイダー "OraOLEDB.Oracle" でテーブル "[OraOLEDB.Oracle]" を INSERT INTO できませんでした。
上記のように全角1文字の「イ」を挿入しているにも関わらず、「実際31」と出ていますのでなにかがあるのだと思います。
予想可能な範囲で構いませんので、教えてください。