成人无码视频,亚洲精品久久久久av无码,午夜精品久久久久久毛片,亚洲 中文字幕 日韩 无码

資訊專(zhuān)欄INFORMATION COLUMN

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

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

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

通過(guò)Oracle查詢(xún)發(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í)行則報(bào)ERROR: functions in index expression must be marked IMMUTABLE


手動(dòng)執(zhí)行上述函數(shù)，并不報(bào)錯(cuò)。


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


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


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


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


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

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


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


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

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


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


我們理解如下：

  1. 這個(gè)函數(shù)不能修改數(shù)據(jù)庫(kù)，它只能查詢(xún)時(shí)間。

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


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


我們來(lái)找類(lèi)似的幾個(gè)時(shí)間函數(shù)測(cè)試一下。

除了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)查詢(xún)這張表的時(shí)候，now(),oracle.sysdate()這種為STABLE的，時(shí)間不會(huì)發(fā)生變化，而clock_timestamp為VOLATILE類(lèi)型則發(fā)生了變化。


至此，要?jiǎng)?chuàng)建函數(shù)索引，就必須把函數(shù)設(shè)置成IMMUTABLE。而實(shí)現(xiàn)辦法可以自己建一個(gè)IMMUTABLE函數(shù)，該函數(shù)接受輸入?yún)?shù)作為numeric類(lèi)型。然后在創(chuàng)建函數(shù)索引的地方使用自己創(chuàng)建的。由于我這里的函數(shù)索引使用了2種函數(shù)，一個(gè)是substr,一個(gè)是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排序類(lèi)的sql，發(fā)現(xiàn)已經(jīng)可以使用這個(gè)函數(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)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://m.hztianpu.com/yun/130105.html

相關(guān)文章

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

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

    chavesgu 評(píng)論0 收藏0

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

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

    Martin91 評(píng)論0 收藏0

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

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

    wuaiqiu 評(píng)論0 收藏0

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

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

    Lowky 評(píng)論0 收藏0

發(fā)表評(píng)論

0條評(píng)論

最新活動(dòng)
閱讀需要支付1元查看
<