SQL Server 連結伺服器存取 Oracle 表：配置雙方案與索引失效解決方案

SQL Server 透過**連結伺服器** 可直接查詢、異動 Oracle 表資料，實現異構資料庫即時整合。本文詳細說明兩種主流配置方案——**OLE DB Provider for Oracle** 與 **OLE DB Provider for ODBC**——的逐步建立流程，並深度解析「WHERE 條件未下推導致索引失效」的根因與解決方案。

方案一：OLE DB Provider for Oracle (原生、推薦)

直接使用 Oracle OLE DB 驅動，無需額外 ODBC 層，效能較佳。

前置作業

1. SQL Server 主機安裝 **Oracle Client 11g/12c/19c** (32/64 位元需與 SQL Server 版本一致)
2. 配置 tnsnames.ora，確保 tnsping ORCL 通
3. 以系統管理員身分執行 regsvr32 oraoledb.dll 註冊提供者 (通常安裝程式已自動完成)

SSMS 建立連結伺服器步驟

1. 展開「伺服器物件」→ 右鍵「連結伺服器」→ 「新增連結伺服器」
2. 一般 頁籤：
   • 連結伺服器名：ORA_LINK (自訂)
   • 伺服器類型：其他資料來源
   • 提供者：Microsoft OLE DB Provider for Oracle
   • 產品名稱：Oracle
   • 資料來源：ORCL (tnsnames.ora 服務名)
   • 提供者字串：留空或 ORA_OLEDB
3. 安全性 頁籤：
   • 「使用此安全性內容建立」→ 輸入 Oracle 遠端登入帳密
   • 或「對應登入」對應本地 SQL 登入至遠端 Oracle 用戶
4. 伺服器選項 頁籤：建議勾選
   • Data Access = True
   • Collation Compatible = True (關鍵，見下文)
   • Lazy Schema Validation = True
   • Use Remote Collation = True

方案二：OLE DB Provider for ODBC (通用、相容性佳)

透過系統 DSN 連線，適合無法安裝 Oracle Client 或需支援多種異構來源的環境。

前置作業

1. 安裝 Oracle ODBC Driver (隨 Oracle Client 安裝)
2. 控制台 → 「ODBC 資料來源 (64/32 位)」→ 「系統 DSN」→ 新增 → 選「Oracle in OraClient12Home1」→ 設定 TNS 服務名、使用者
3. 測試 DSN 連線成功

SSMS 建立連結伺服器

• 提供者：**Microsoft OLE DB Provider for ODBC Drivers**
• 資料來源：系統 DSN 名稱 (如 ORA_DSN)
• 提供者字串：留空
• 其餘安全性/選項同方案一

查詢語法與範例

-- 四段式命名：連結伺服器..Schema.表
SELECT * FROM ORA_LINK..SCOTT.EMP WHERE DEPTNO = 10;

-- OPENQUERY (推薦：查詢在遠端完整執行，效能最佳)
SELECT * FROM OPENQUERY(ORA_LINK, 'SELECT * FROM SCOTT.EMP WHERE DEPTNO = 10');

-- 執行遠端 DML
INSERT INTO ORA_LINK..SCOTT.EMP (EMPNO, ENAME, DEPTNO) VALUES (9999, 'TEST', 20);
UPDATE ORA_LINK..SCOTT.EMP SET SAL = SAL * 1.1 WHERE DEPTNO = 30;
DELETE FROM ORA_LINK..SCOTT.EMP WHERE EMPNO = 9999;

核心痛點：WHERE 條件未下推導致全表掃描

現象

-- 本地執行，Oracle 端執行計畫顯示 FULL TABLE SCAN
SELECT * FROM ORA_LINK..SCOTT.EMP WHERE EMPNO = 7369;  -- EMPNO 有索引

Oracle 端 V$SQLAREA 顯示 SQL 為 SELECT * FROM "SCOTT"."EMP"，**WHERE 子句消失**，全表掃描回傳百萬列至 SQL Server 再過濾，效能災難。

根因分析

解決方案優先順序

1. 啟用 Collation Compatible = True (方案一/二通用，解決字元型別下推)
2. 使用 OPENQUERY：將完整查詢推送至 Oracle 執行，**繞過連結伺服器下推邏輯**，保證索引生效、效能最優。

   SELECT * FROM OPENQUERY(ORA_LINK, 'SELECT * FROM SCOTT.EMP WHERE EMPNO = 7369');

3. 建立本地檢視/預存程序封裝 OPENQUERY：對應用層隱藏異構查詢細節
4. 數值型別欄位改用字串比對 (Hack)：WHERE TO_CHAR(EMPNO) = '7369'，強制字元下推，**僅作臨時應急**
5. 升級 Oracle Client / OLE DB 驅動至 19c/21c：新版驅動大幅改善 NUMBER/DATE 下推支援

效能調校清單

• 大量資料**務必用 OPENQUERY** 或 EXECUTE AT 推送執行
• 連結伺服器屬性：Connection Timeout、Query Timeout 依業務調整
• 分散式交易：sp_configure 'remote proc trans', 1 啟用後需 MSDTC 配置
• 統計資訊：EXEC sp_statistics_rowset 'ORA_LINK', 'SCOTT', 'EMP' 更新遠端統計

掌握雙方案配置與「下推失效」解法，即可在 SQL Server 中高效、穩定地存取 Oracle 資料，滿足異構即時查詢、ETL 抽取、跨庫報表等實際業務場景。