🐥

OracleとSQL Serverの空白の扱いの違いについて(異なるデータベースをリンクさせた場合)

2022/04/01に公開1

前回の続きです。
今度はSQL Sever、Oracle間でデータベースをリンクさせた場合の挙動について調べてみました。

検証

テーブル

前回の作ったものをそのまま使用した。
DBMSも前回と同様Oracle19cとSQLServer2019で行った。

CreateTable(Oracle).sql
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 ' );     -- 文字列の前後にスペース
CreateTable(SqlServer).sql
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の検証結果と同じになった。当然ではあるが。
https://docs.microsoft.com/ja-jp/sql/t-sql/functions/openquery-transact-sql?view=sql-server-ver15

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側のクエリの履歴を調べてみた。
http://www.it-view.net/sql-server-実行されたsql履歴確認-338.html

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側で実行されていることが分かった。

まとめ

前回の検証も含めて、分かったことは以下の通り。
文字列前後に空白が入っているデータを扱う場合、

  1. ORACLE、SQL ServerともCHARのカラムであれば同じ検索結果になるが、VARCHARカラムの場合は異なる検索結果になる。
  2. SQL Server ⇒ Oracle(SQL Serverのリンクサーバーを使用して、Oracleのテーブルを参照)時は、SQL Server側の検索仕様で問い合わせされる。
  3. 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','イ')

※SIRNMAというフィールドに「イ」を挿入しようとしたところ下記のような結果になります。SIRNMAのデータ型はCHAR(30)です。
			 (SIRNMAに全角ではなく半角を挿入し場合はクエリは問題なく成功します)

結果
リンク サーバー "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」と出ていますのでなにかがあるのだと思います。

予想可能な範囲で構いませんので、教えてください。