將函數型索引從 Oracle 遷移至 PostgreSQL - AWS 方案指引
Summary先決條件和限制架構工具史詩相關資源其他資訊

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

將函數型索引從 Oracle 遷移至 PostgreSQL

Veeranjaneyulu Grandhi 和 Navakanth Talluri，Amazon Web Services

Summary

索引是增強資料庫效能的常見方式。索引可讓資料庫伺服器比沒有索引時更快地尋找和擷取特定資料列。但索引也會為整體資料庫系統增加額外負荷，因此應該合理地使用它們。以函數為基礎的索引，以函數或表達式為基礎，可以涉及多個欄和數學表達式。以函數為基礎的索引可改善使用索引表達式的查詢效能。 

原本，PostgreSQL 不支援使用波動定義為穩定的函數建立以函數為基礎的索引。不過，您可以建立具有波動的類似函數，IMMUTABLE並在建立索引時使用它們。

IMMUTABLE 函數無法修改資料庫，並保證永遠傳回相同的結果給相同的引數。此類別可讓最佳化工具在查詢使用常數引數呼叫函數時預先評估函數。 

此模式有助於將 Oracle 函數型索引與 to_char、 to_date和 等函數搭配使用時遷移to_number至 PostgreSQL 對等項目。

先決條件和限制

先決條件

  • 作用中的 Amazon Web Services (AWS) 帳戶

  • 具有接聽程式服務設定和執行的來源 Oracle 資料庫執行個體

  • 熟悉 PostgreSQL 資料庫

限制

  • 資料庫大小限制為 64 TB。

  • 索引建立中使用的函數必須為 IMMUTABLE。

產品版本

  • 版本 11g （版本 11.2.0.3.v1 和更新版本） 和最高 12.2 和 18c 的所有 Oracle 資料庫版本

  • PostgreSQL 9.6 版及更新版本

架構

來源技術堆疊

  • 內部部署或 Amazon Elastic Compute Cloud (Amazon EC2) 執行個體或 Amazon RDS for Oracle 資料庫執行個體上的 Oracle 資料庫

目標技術堆疊

  • 任何 PostgreSQL 引擎

工具

  • pgAdmin 4 是 Postgres 的開放原始碼管理工具。pgAdmin 4 工具提供圖形界面，用於建立、維護和使用資料庫物件。

  • Oracle SQL Developer 是整合式開發環境 (IDE)，可在傳統和雲端部署中開發和管理 Oracle 資料庫。

史詩

任務描述所需的技能
使用 to_char 函數在資料欄上建立以函數為基礎的索引。

使用下列程式碼來建立以函數為基礎的索引。

postgres=# create table funcindex( col1 timestamp without time zone);
CREATE TABLE
postgres=# insert into funcindex values (now());
INSERT 0 1
postgres=# select * from funcindex;
            col1
----------------------------
 2022-08-09 16:00:57.77414
(1 rows)
 
postgres=# create index funcindex_idx on funcindex(to_char(col1,'DD-MM-YYYY HH24:MI:SS'));
ERROR:  functions in index expression must be marked IMMUTABLE

 

注意

PostgreSQL 不允許在沒有 IMMUTABLE子句的情況下建立以函數為基礎的索引。

DBA，應用程式開發人員
檢查函數的波動。

若要檢查函數波動，請使用其他資訊區段中的程式碼。 

DBA
任務描述所需的技能
建立包裝函式。

若要建立包裝函式，請使用其他資訊區段中的程式碼。

PostgreSQL 開發人員
使用包裝函式建立索引。

使用其他資訊區段中的程式碼，在與應用程式相同的結構描述IMMUTABLE中建立具有 關鍵字的使用者定義函數，並在索引建立指令碼中參考它。

如果在一般結構描述中建立使用者定義的函數 （從先前的範例），請更新顯示的 search_path。

ALTER ROLE <ROLENAME> set search_path=$user, COMMON;
DBA、PostgreSQL 開發人員
任務描述所需的技能
驗證索引建立。

驗證是否需要根據查詢存取模式建立索引。

DBA
驗證索引是否可以使用。

若要檢查 PostgreSQL Optimizer 是否收取函數型索引，請使用 explain 或 explain analysis 執行 SQL 陳述式。使用其他資訊區段中的程式碼。如果可能，也請收集資料表統計資料。

注意

如果您注意到解釋計畫，PostgreSQL 最佳化工具已因為述詞條件而選擇以函數為基礎的索引。

DBA

相關資源

其他資訊

建立包裝函式

CREATE OR REPLACE FUNCTION myschema.to_char(var1 timestamp without time zone, var2 varchar) RETURNS varchar AS $BODY$ select to_char(var1, 'YYYYMMDD'); $BODY$ LANGUAGE sql IMMUTABLE;

使用包裝函式建立索引

postgres=# create function common.to_char(var1 timestamp without time zone, var2 varchar) RETURNS varchar AS $BODY$ select to_char(var1, 'YYYYMMDD'); $BODY$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION
postgres=# create index funcindex_idx on funcindex(common.to_char(col1,'DD-MM-YYYY HH24:MI:SS'));
CREATE INDEX

檢查函數的波動

SELECT DISTINCT p.proname as "Name",p.provolatile as "volatility" FROM pg_catalog.pg_proc p
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
 WHERE n.nspname OPERATOR(pg_catalog.~) '^(pg_catalog)$' COLLATE pg_catalog.default AND p.proname='to_char'GROUP BY p.proname,p.provolatile
ORDER BY 1;

驗證索引是否可以使用

explain analyze <SQL>
 
 
postgres=# explain select col1 from funcindex where common.to_char(col1,'DD-MM-YYYY HH24:MI:SS') = '09-08-2022 16:00:57';
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Index Scan using funcindex_idx on funcindex  (cost=0.42..8.44 rows=1 width=8)
   Index Cond: ((common.to_char(col1, 'DD-MM-YYYY HH24:MI:SS'::character varying))::text = '09-08-2022 16:00:57'::text)
(2 rows)