將函數索引從Oracle遷移到PostgreSQL

IT那活兒發布于2023-01-11 13:20 / 1289人閱讀

我們使用AWSSchema ConversionTool（SCT）來轉換數據庫的元數據。通過AWS轉換后的腳本在PostgreSQL中執行，發現函數索引無法成功執行。

通過Oracle查詢發現其使用了substr和to_char等函數。

CREATE INDEX "HB_E2E"."IDX_CUST_ID_I" ON "HB_E2E"."FTP_DIPAN" (SUBSTR(TO_CHAR("CUST_ID"),-1))

而在PostgreSQL中執行則報ERROR: functions in index expression must be marked IMMUTABLE

手動執行上述函數，并不報錯。

根據PostgreSQL文檔，函數可以是3種類型，每一個函數都有一個易變性分類可能是VOLATILE、STABLE或者IMMUTABLE。如果CREATEFUNCTION命令沒有指定一個分類，則默認是VOLATILE。

VOLATILE函數可以做任何事情，包括修改數據庫（比如Update）。在使用相同的參數連續調用時，它能返回不同的結果。優化器不會對這類函數的行為做任何假定。在每一行需要volatile 函數值時，一個使用 volatile 函數的查詢都會重新計算該函數。

STABLE函數不能修改數據庫,并且被確保對一個語句中的所有行用給定的相同參數返回相同的結果。這種分類允許優化器把該函數的多個調用優化成一個調用。特別是，在一個索引掃描條件中使用包含這樣一個函數的表達式是安全的（因為一次索引掃描只會計算一次比較值,而不是為每一行都計算一次，在一個索引掃描條件中不能使用VOLATILE函數）。

IMMUTABLE函數不能修改數據庫并且被確保用相同的參數永遠返回相同的結果。這種分類允許優化器在一個查詢用常量參數調用該函數時提前計算該函數。例如，一個 SELECT ... WHERE x = 2 + 2這樣的查詢可以被簡化為SELECT ... WHERE x = 4，因為整數加法操作符底層的函數被標記為IMMUTABLE

通過查詢pg_proc，可以確認函數類型，例如sysdate函數。

SELECT proname, provolatile, proargtypes, proargtypes[0]::regtype AS argtype, prosrc FROM pg_proc WHERE proname like ‘sysdate%’;

Provolatile為s，則說明是STABLE函數。

上述有點難以理解，我們來用案例實際說明一下。這里使用current_timestamp來說明。

可以看到current_timestamp是的Provolatile狀態s，是STABLE函數。

STABLE函數不能修改數據庫,并且被確保對一個語句中的所有行用給定的相同參數返回相同的結果。

我們理解如下：

這個函數不能修改數據庫，它只能查詢時間。
并且被確保對一個語句中的所有行用給定的相同參數返回相同的結果。

這個是什么意思呢？當你查一張大表，而這張表每一行要使用這個函數的時候，時間其實是在流逝的，但是你表上所有數據行都必須使用最開始的那個時間。（可以理解為事務開始的時間）。

我們來找類似的幾個時間函數測試一下。

除了clock_timestamp是VOLATILE，now和sysdate都是STABLE

我們創建一張表。

create table test_function

(

id numeric,

now_time timestamp without time zone,

sysdate_time timestamp without time zone,

clock_time timestamp without time zone

);

插入10000行數據

insert into test_function

select generate_series(1,10000),now(),oracle.sysdate(),clock_timestamp();

插入完成后可以看到，當查詢這張表的時候，now(),oracle.sysdate()這種為STABLE的，時間不會發生變化，而clock_timestamp為VOLATILE類型則發生了變化。

至此，要創建函數索引，就必須把函數設置成IMMUTABLE。而實現辦法可以自己建一個IMMUTABLE函數，該函數接受輸入參數作為numeric類型。然后在創建函數索引的地方使用自己創建的。由于我這里的函數索引使用了2種函數，一個是substr,一個是to_char，substr已經是IMMUTABLE的,所以只需要將to_char函數建成IMMUTABLE就行了。

CREATE OR REPLACE FUNCTION immutable_to_char(numeric) RETURNS character varying

select aws_oracle_ext.to_char($1)

LANGUAGE SQL IMMUTABLE;

CREATE INDEX idx_cust_id_i ON hb_e2e.ftp_dipan USING BTREE (aws_oracle_ext.substr(immutable_to_char(cust_id::numeric),lengthb(immutable_to_char(cust_id::numeric)) ) ASC);

再次執行asc排序類的sql，發現已經可以使用這個函數索引了。

參考文檔：

函數穩定性講解- retalk PostgreSQL functions [volatile|stable|immutable ]

https://github.com/digoal/blog/blob/master/201212/20121226_01.md

MigratingFunction based indexes from Oracle to PostgreSQL

https://askdba.org/weblog/2018/01/migrating-function-based-indexes-from-oracle-to-postgresql/

云服務器 GPU云服務器從oracle遷移到mysql PPAS 從oracle遷移到mysql PPAS 從db2遷移到oracle 如何將數據遷移到oss

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/130105.html

阿里云如何打破Oracle遷移上云的壁壘

摘要：摘要第九屆中國數據庫技術大會，阿里云數據庫產品專家蕭少聰帶來以阿里云如何打破遷移上云的壁壘為題的演講。于是，阿里云給出了上面的解決方案。摘要： 2018第九屆中國數據庫技術大會，阿里云數據庫產品專家蕭少聰帶來以阿里云如何打破Oracle遷移上云的壁壘為題的演講。Oracle是指數據庫管理系統，面對Oracle遷移上云的壁壘，阿里云如何能夠打破它呢？本文提出了Oracle 到云數據庫P...

chavesgu 2019-06-25 18:39 評論0 收藏0
新書推薦 |《PostgreSQL實戰》出版(提供樣章下載)

摘要：作者譚峰張文升出版日期年月頁數頁定價元本書特色中國開源軟件推進聯盟分會特聘專家撰寫，國內多位開源數據庫專家鼎力推薦。張文升中國開源軟件推進聯盟分會核心成員之一。很高興《PostgreSQL實戰》一書終于出版，本書大體上系統總結了筆者 PostgreSQL DBA 職業生涯的經驗總結，本書的另一位作者張文升擁有豐富的PostgreSQL運維經驗，目前就職于探探科技任首席PostgreS...

Martin91 2019-07-25 12:36 評論0 收藏0
你應該使用哪個云數據庫？

摘要：云計算云計算的基本原則是采用一次性和可更換的多臺機器，這對采用云計算技術以及在云中實施的數據庫系統的功能有直接影響。云數據庫屬于相同的類別，而新系統明顯傾向于并行優先。與非云系統相比，云計算系統向數據庫應用程序公開資源利用控制要常見得多。云計算的基本原則是采用一次性和可更換的多臺機器，這對采用云計算技術以及在云中實施的數據庫系統的功能有直接影響。傳統數據庫大致可以分為并行優先(例如Mo...

wuaiqiu 2021-11-15 11:36 評論0 收藏0
面對眾多云數據庫，應該使用哪個云數據庫好？

摘要：云計算的基本準則是采用一次性和可更換的多臺機器，這對采用云計算技術及其在云中實施的數據庫系統的功能有直接影響。與非云系統相比，云計算系統向數據庫應用程序公開資源利用控制要常見得多。云數據庫使用哪個云數據庫好云數據庫哪個好云計算的基本準則是采用一次性和可更換的多臺機器，這對采用云計算技術及其在云中實施的數據庫系統的功能有直接影響。傳統數據庫大致可以分為并行優先（...

Lowky 2021-11-22 13:54 評論0 收藏0