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

25033 ワード
SQL SQL Server Oracle tech テキストリンク

前回の続きです。
今度は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を使用したアクセスからマテリアライズドビューに変更した際に実際に経験した。この記事を書こうと思った経緯でもあるのだが。。。）

Unityプロジェクトの『開店準備』
Create React App(TypeScript) + SWC + MUI v5 の環境構築