I have just created my first Oracle connection, using the Polybase feature with SQL Server 2019. Here is the command I have used:
CREATE EXTERNAL TABLE dbo.COMPLETED_TRAINING (
EMP_NUM VARCHAR(24) COLLATE Latin1_General_100_BIN2_UTF8 NOT NULL,
COURSE_NUM Integer Not Null,
COMPLETED_DATE DateTime2 Not Null
Now, if I do a select statement with a hard-coded value, it comes back instantly:
select * from dbo.COMPLETED_TRAINING where EMP_NUM = '123456';
But, I would really like to use a variable. (Something I can't do using OPENQUERY). I have tried both of these Declare statements prior to executing the query:
Declare @EMP_NUM VARCHAR(24) = '123456';
Declare @EMP_NUM VARCHAR(24) = '123456' COLLATE Latin1_General_100_BIN2_UTF8;
select * from dbo.COMPLETED_TRAINING where EMP_NUM = @EMP_NUM;
In both cases the command takes forever to run.
I am sure it has to do with the collation. The collation I made during the "CREATE EXTERNAL TABLE" was done in that format, because if I didn't I got an error message that it was required.
When using the variable, it appears to be doing a full table scan instead of using the index that is on the Oracle server.
Execution plan using a variable (35 seconds):
I have also tried:
CREATE STATISTICS SampleStats ON dbo.COMPLETED_TRAINING (EMP_NUM) WITH FULLSCAN;