將函數索引從Oracle遷移到PostgreSQL

我們使用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
將函數索引從Oracle遷移到PostgreSQL


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

將函數索引從Oracle遷移到PostgreSQL

根據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%’;
將函數索引從Oracle遷移到PostgreSQL

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

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

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

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

我們理解如下:

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

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

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

將函數索引從Oracle遷移到PostgreSQL

除了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_functionselect generate_series(1,10000),now(),oracle.sysdate(),clock_timestamp();

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

將函數索引從Oracle遷移到PostgreSQL

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

CREATE OR REPLACE FUNCTION immutable_to_char(numeric) RETURNS character varyingAS’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,發現已經可以使用這個函數索引了。

將函數索引從Oracle遷移到PostgreSQL

參考文檔:

函數穩定性講解- retalk PostgreSQL function’s [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/

來源:IT那活兒,本文觀點不代表自營銷立場,網址:http://www.wfapiao.com/p/92977

發表評論

登錄后才能評論
侵權聯系
返回頂部
AV天堂日本AV天堂欧美AV天堂