This article is a continuation of last time's article.
I experimented with handling the spaces when linking Oracle and SQL Server this time.
Experiment
Table and data query
The query and DBMS's version are the same as the previous one.
CREATE TABLE CompareTestTable
(
Seq NUMBER
,ColCHAR CHAR(10)
,ColVARCHAR VARCHAR2(10)
);
INSERT INTO CompareTestTable VALUES( 1, 'aaa', 'bbb' ); -- No space
INSERT INTO CompareTestTable VALUES( 2, 'aaa ', 'bbb ' ); -- A space after a string
INSERT INTO CompareTestTable VALUES( 3, ' aaa', ' bbb' ); -- A space before a string
INSERT INTO CompareTestTable VALUES( 4, ' aaa ', ' bbb ' ); -- Spaces after and before a string
CREATE TABLE CompareTestTable
(
Seq INT
,ColCHAR CHAR(10)
,ColVARCHAR VARCHAR(10)
)
INSERT INTO CompareTestTable VALUES( 1, 'aaa', 'bbb' ); -- No space
INSERT INTO CompareTestTable VALUES( 2, 'aaa ', 'bbb ' ); -- A space after a string
INSERT INTO CompareTestTable VALUES( 3, ' aaa', ' bbb' ); -- A space before a string
INSERT INTO CompareTestTable VALUES( 4, ' aaa ', ' bbb ' ); -- Spaces after and before a string
SQL Server => Oracle (With using Link Server of SQL server, tables in oracle are accessed.)
The name of Link Server is created as "Oracle".
Tables of Oracle are in "TEST_USER" schema.
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColCHAR = 'aaa'; -- Hit SEQ=1,2 records
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColCHAR = 'aaa '; -- Hit SEQ=1,2 records
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColCHAR = ' aaa'; -- Hit SEQ=3,4 records
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColCHAR = ' aaa '; -- Hit SEQ=3,4 records
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColVARCHAR = 'bbb'; -- Hit SEQ=1,2 records
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColVARCHAR = 'bbb '; -- Hit SEQ=1,2 records
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColVARCHAR = ' bbb'; -- Hit SEQ=3,4 records
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColVARCHAR = ' bbb '; -- Hit SEQ=3,4 records
This result is the same as the previous result of SQL Server.
It is indicated that once the data was retrieved from Oracle to SQL Server, then the condition was evaluated at SQL Server.
As a side note, with using OPENQUERY of SQL Server which is to execute query at Oracle, the result is the same as the previous result of Oracle.
Oracle => SQL Server (With using Database Link of Oracle, tables in SQL Server are accessed.)
The name of Database Link is created as "SQL_SERVER".
select * from COMPARETESTTABLE@SQL_SERVER where "ColCHAR" = 'aaa'; -- Hit SEQ=1,2 records
select * from COMPARETESTTABLE@SQL_SERVER where "ColCHAR" = 'aaa '; -- Hit SEQ=1,2 records
select * from COMPARETESTTABLE@SQL_SERVER where "ColCHAR" = ' aaa'; -- Hit SEQ=3,4 records
select * from COMPARETESTTABLE@SQL_SERVER where "ColCHAR" = ' aaa '; -- Hit SEQ=3,4 records
select * from COMPARETESTTABLE@SQL_SERVER where "ColVARCHAR" = 'bbb'; -- Hit SEQ=1,2 records
select * from COMPARETESTTABLE@SQL_SERVER where "ColVARCHAR" = 'bbb '; -- Hit SEQ=1,2 records
select * from COMPARETESTTABLE@SQL_SERVER where "ColVARCHAR" = ' bbb'; -- Hit SEQ=3,4 records
select * from COMPARETESTTABLE@SQL_SERVER where "ColVARCHAR" = ' bbb '; -- Hit SEQ=3,4 records
This result is unexpected.
I think the result is the same as the previous result of Oracle, but actually is the same as the above result of SQL Server.
I wonder if the query was executed at SQL Server.
I investigated the history of the execution query at SQL Server with the following query.
SELECT TOP 1000
QS.creation_time,
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,
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
Just as expected, the query was executed at SQL Server.
Conclustion
Including the previous experiment, the summary is following
- In the event of column type is CHAR, the result of a search query is the same while a different result will have occurred in the case of VARCHAR.
- With using Link Server to connect from SQL Server to tables in Oracle, the query is executed as search specification of SQL Server.
- With using Database Link to connect from Oracle to tables in SQL Server, the query is executed as search specification of SQL Server.
We think you sometimes change the access by using the Oracle Database link to using Oracle Materialized View to improve query performance.
But Oracle Materialized View is simply a table so access with it will be the same as previous result of Oracle.
Therefore, the result is possible to differ from the previous result if you change it.
(Actually, I faced this problem in my work. It is the motivation I wrote these articles...)
Top comments (0)