成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

將函數(shù)索引從Oracle遷移到PostgreSQL

IT那活兒 / 1303人閱讀
將函數(shù)索引從Oracle遷移到PostgreSQL

我們使用AWSSchema ConversionTool（SCT）來轉(zhuǎn)換數(shù)據(jù)庫的元數(shù)據(jù)。通過AWS轉(zhuǎn)換后的腳本在PostgreSQL中執(zhí)行，發(fā)現(xiàn)函數(shù)索引無法成功執(zhí)行。

通過Oracle查詢發(fā)現(xiàn)其使用了substr和to_char等函數(shù)。

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


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


手動執(zhí)行上述函數(shù)，并不報錯。


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


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


  • STABLE函數(shù)不能修改數(shù)據(jù)庫,并且被確保對一個語句中的所有行用給定的相同參數(shù)返回相同的結(jié)果。這種分類允許優(yōu)化器把該函數(shù)的多個調(diào)用優(yōu)化成一個調(diào)用。特別是，在一個索引掃描條件中使用包含這樣一個函數(shù)的表達(dá)式是安全的（因?yàn)橐淮嗡饕龗呙柚粫嬎阋淮伪容^值,而不是為每一行都計算一次，在一個索引掃描條件中不能使用VOLATILE函數(shù)）。


  • IMMUTABLE函數(shù)不能修改數(shù)據(jù)庫并且被確保用相同的參數(shù)永遠(yuǎn)返回相同的結(jié)果。這種分類允許優(yōu)化器在一個查詢用常量參數(shù)調(diào)用該函數(shù)時提前計算該函數(shù)。例如，一個 SELECT ... WHERE x = 2 + 2這樣的查詢可以被簡化為SELECT ... WHERE x = 4，因?yàn)檎麛?shù)加法操作符底層的函數(shù)被標(biāo)記為IMMUTABLE


通過查詢pg_proc，可以確認(rèn)函數(shù)類型，例如sysdate函數(shù)。

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


Provolatile為s，則說明是STABLE函數(shù)。


上述有點(diǎn)難以理解，我們來用案例實(shí)際說明一下。這里使用current_timestamp來說明。

可以看到current_timestamp是的Provolatile狀態(tài)s，是STABLE函數(shù)。


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


我們理解如下：

  1. 這個函數(shù)不能修改數(shù)據(jù)庫，它只能查詢時間。

  2. 并且被確保對一個語句中的所有行用給定的相同參數(shù)返回相同的結(jié)果。


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


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

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


我們創(chuàng)建一張表。

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行數(shù)據(jù)

insert into test_function

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


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


至此，要創(chuàng)建函數(shù)索引，就必須把函數(shù)設(shè)置成IMMUTABLE。而實(shí)現(xiàn)辦法可以自己建一個IMMUTABLE函數(shù)，該函數(shù)接受輸入?yún)?shù)作為numeric類型。然后在創(chuàng)建函數(shù)索引的地方使用自己創(chuàng)建的。由于我這里的函數(shù)索引使用了2種函數(shù)，一個是substr,一個是to_char，substr已經(jīng)是IMMUTABLE的,所以只需要將to_char函數(shù)建成IMMUTABLE就行了。

CREATE OR REPLACE FUNCTION immutable_to_char(numeric) RETURNS character varying

AS

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);


再次執(zhí)行asc排序類的sql，發(fā)現(xiàn)已經(jīng)可以使用這個函數(shù)索引了。


參考文檔：

函數(shù)穩(wěn)定性講解- 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/

文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址：http://systransis.cn/yun/130105.html

相關(guān)文章

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

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

    chavesgu 評論0 收藏0

  • 新書推薦 |《PostgreSQL實(shí)戰(zhàn)》出版(提供樣章下載)

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

    Martin91 評論0 收藏0

  • 你應(yīng)該使用哪個云數(shù)據(jù)庫？

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

    wuaiqiu 評論0 收藏0

  • 面對眾多云數(shù)據(jù)庫，應(yīng)該使用哪個云數(shù)據(jù)庫好？

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

    Lowky 評論0 收藏0

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<