Oracle外部表進階
用最新的武器裝備自己作者:JonathanGenn
在Oracle9i中,歷史悠久的SQL*Loader實用程序?qū)崿F(xiàn)了許多提取、轉(zhuǎn)換和裝載的新特性。
如果想把大量數(shù)據(jù)快速載入Oracle數(shù)據(jù)庫,則您首先能想到的恐怕便是SQL*Loader。到目前為止,這一想法在很大程序上仍是正確的。不過,Oracle9i數(shù)據(jù)庫提供的幾項有趣的新特性,可能(或許應該)改變您進行數(shù)據(jù)裝載和轉(zhuǎn)換的方式。這些新特性屬于Oracle稱為“提取、轉(zhuǎn)換和裝載”(Extraction、Transformation和Loading,ETL)類別,它們共同為您提供了一個功能強大的ETL工具箱。我感覺特別有趣的三個ETL特性是:外部表表函數(shù)MERGE語句
在本文中,我打算以一個典型的數(shù)據(jù)裝載問題為例,向大家說明這些特性。假定有以下人口普查數(shù)據(jù)需要載入數(shù)據(jù)庫,您按以下標準對這些數(shù)據(jù)進行了處理:用一行來存放每個城市/年的組合數(shù)據(jù):
City1990201*Baraga12311285Ishpeming7200Munising27832539
使用Oracle8i,您需要使用至少兩個單獨的步驟來完成數(shù)據(jù)的裝載與轉(zhuǎn)換。圖1向您展示的便是一種可能的操作方式。然而,利用Oracle9i的ETL特性,您可用一種更有趣的方式來完成這項任務--只需一個步驟,便能完成裝載與轉(zhuǎn)換。
圖1:裝載和轉(zhuǎn)換
圖1:一個多步驟、單線程的裝載過程外部表
Oracle9i的新特性之一是外部表的概念。這是在數(shù)據(jù)庫的數(shù)據(jù)字典中定義的一個表,但數(shù)據(jù)本身卻存儲在數(shù)據(jù)庫的外部。例如,您可定義一個外部表,它的數(shù)據(jù)來自用SQL*Loader裝載的文本文件。這樣其實正好,因為您需要裝載的人口普查數(shù)據(jù)就放在這樣一個文本文件中。創(chuàng)建外部表之前,需要先創(chuàng)建一個Oracle目錄對象,該Oracle目錄對象指向您的文本文件所在的操作系統(tǒng)目錄。CREATEDIRECTORYcensus_dataAS"/data/census";
現(xiàn)在,您可使用一種新形式的CREATETABLE語句,它看起來就像SQL語句和SQL*Loader控制文件的混合:CREATETABLEcity_populations_ext(city_nameVARCHAR(9),pop_1990NUMBER,pop_201*NUMBER)
ORGANIZATIONEXTERNAL(TYPEoracle_loader
DEFAULTDIRECTORYcensus_dataACCESSPARAMETERS(RECORDSFIXED20LOGFILEcensus_data:"city_populations.log"BADFILEcensus_data:"city_populations.bad"FIELDS
MISSINGFIELDVALUESARENULL(city_name(1:10)CHAR(9),pop_1990(11:15)INTEGEREXTERNAL(4),
POP_201*(16:20)INTEGEREXTERNAL(4)))
LOCATION("city_populations.dat"))
PARALLEL4REJECTLIMITUNLIMITED;
當創(chuàng)建一個外部表時,實際只創(chuàng)建了一些數(shù)據(jù)字典項。您可像對其他任何SQL表那樣對外部表進行查詢,F(xiàn)在,假定您有下面這張工作表:
CREATETABLEcity_populations(city_nameVARCHAR(9),census_yearNUMBER,populationNUMBER,
CONSTRAINTcity_populations_pkPRIMARYKEY(city_name,census_year));
有了這張表后,您可用下列INSERT...SELECTFROM語句以標準化格式從外部數(shù)據(jù)文件中提取人口普查數(shù)據(jù),并將其插入工作表。
INSERTINTOcity_populations(city_name,
census_year,population)SELECTcity_name,1990,pop_1990FROMcity_populations_extWHEREpop_1990ISNOTNULLUNIONALL
SELECTcity_name,201*,pop_201*FROMcity_populations_extWHEREpop_201*ISNOTNULL;
創(chuàng)建外部表時,由于將并行度設為4,所以數(shù)據(jù)庫將對文件進行劃分,使其由4個并行運行的進程讀取。并行處理是自動進行的,不需要由您采取額外的操作,這確實相當方便。要用SQL*Loader并行進行裝載,您必須將輸入文件人工分割為多個較小的文件。
圖2:表函數(shù)示例
圖2:表函數(shù)以一系列行作為輸入,返回的是一個不同的行集合
外部表性能
用SQL*Loader進行原始數(shù)據(jù)裝載和通過一個外部表進行相同的裝載,兩者的性能有何差異呢?這個問題實際上是當我聽說Oracle新的外部表特性時所首先想到的,而且我相信,它也是大多數(shù)人關心的問題。我向Oracle公司的HermanBaer提出這一問題時,他的答復是假如將一個外部表作為數(shù)據(jù)源,那么一個INSERT/*+APPEND*/語句采用的是同SQL*Loader相同的內(nèi)部直接路徑插入機制。
按我的設想,在比較外部表同SQL*Loader的性能時,需要關心的并不僅僅是完成原始數(shù)據(jù)裝載所需花費的時間,應考察整個裝載和轉(zhuǎn)換過程的性能。使用SQL*Loader,如果在裝載期間需要使用任何類型的SQL函數(shù),那么必須采用常規(guī)。如您所知,常規(guī)路徑裝載的性能通常遠不及直接路徑裝載的性能。采用外部表時,則不會在使用SQL函數(shù)時出現(xiàn)對性能的影響。同樣,在某些情況下,使用外部表使您能避免創(chuàng)建一個中間工作表。如果正在對數(shù)據(jù)進行歸納,則可用外部表一次完成歸納與裝載。但如果使用SQL*Loader,則必須將數(shù)據(jù)載入一個工作表,進行歸納,再將歸納結果插入到目標表。最后,從易用性的角度出發(fā)很重要的一點是,外部表使您能"透明地"并行訪問外部數(shù)據(jù)文件。
不管怎樣,在您考察外部表的性能,或在考察任何Oracle9i的新ETL(提取、轉(zhuǎn)換和裝載)特性性能時,一定要綜合考察整個過程,而不是僅僅考察這個過程中的單獨一個步驟。表函數(shù)
前面展示的INSERT語句是通過聯(lián)合兩條SELECT語句而實現(xiàn)的。這意味著外部數(shù)據(jù)文件被讀了兩遍--每條SELECT語句都要讀一遍。但是,讀兩遍輸入文件并不合人心意,特別是在文件非常大的情況下。幸運的是,用表函數(shù)可以解決這方面的問題,這是我在本文中將要介紹的第二個ETL特性。可將表函數(shù)想象成一個高度簡化的轉(zhuǎn)換引擎。如圖2所示,表函數(shù)將一組行作為輸入,然后返回一組不同的行作為輸出。和傳統(tǒng)函數(shù)不同的是,表函數(shù)可從SELECT語句的FROM子句中調(diào)用。
對于人口普查數(shù)據(jù)來說,您打算取得每一個輸入行,并將其轉(zhuǎn)換成兩個輸出行。每個輸入行都擁有來自兩個不同的人口普查年度的數(shù)據(jù),但是,標準目標表要求每一年的數(shù)據(jù)占一行,所以表函數(shù)必須能將來自每個輸入行的兩次人口計數(shù)轉(zhuǎn)換成兩個單獨的輸出行。
創(chuàng)建表函數(shù)之前,您需要創(chuàng)建一些類型。表函數(shù)返回的總是一個記錄集,所以在最開始的時候,請創(chuàng)建一個表類型,令其對應于目標數(shù)據(jù)表的定義。為此,首先創(chuàng)建一個對象類型來定義記錄,然后根據(jù)那個對象類型創(chuàng)建一個表類型。
CREATETYPEcity_populations_rowASOBJECT(city_nameVARCHAR2(9),census_yearNUMBER,populationNUMBER);/
CREATETYPEcity_populations_tableASTABLEOFcity_populations_row;/
函數(shù)的輸入是由對外部表city_populations_ext執(zhí)行一條SELECT語句而返回的數(shù)據(jù)行,因此,您需要一個恰當?shù)腞EFCURSOR類型。下面的語句將創(chuàng)建一個包,其中包含一個名為pop_cursor_type的REFCURSOR類型,它與city_populations_ext表的記錄結構相匹配。這個包還定義了一個表函數(shù),該表函數(shù)將這樣一個游標作為輸入?yún)?shù)。注意您首先必須創(chuàng)建表類型city_populations_table,以便可在表函數(shù)的RETURN子句中使用那種類型:
CREATEORREPLACEPACKAGEcensus_packageAS
TYPEpop_cursor_typeISREFCURSORRETURNcity_populations_ext%ROWTYPE;FUNCTIONcensus_transform(indataINpop_cursor_type)RETURNcity_populations_tablePARALLEL_ENABLE(PARTITIONindataBYANY)PIPELINED;END;/
函數(shù)中的PARALLEL_ENABLE子句使數(shù)據(jù)庫可并行執(zhí)行函數(shù)。PARTITIONindataBYANY子句指出輸入行可被分割為任意數(shù)量的數(shù)據(jù)桶,然后便可對其進行并行處理。PIPELINED子句使函數(shù)能遞增地返回結果集,同時其他輸入數(shù)據(jù)仍能得到處理。在調(diào)用函數(shù)的查詢執(zhí)行期間,可設想數(shù)據(jù)行都“經(jīng)過(flowingthrough)”此函數(shù)。在清單1中,在包主體(PACKAGEBODY)中定義的函數(shù)代碼負責將每個輸入行轉(zhuǎn)換成要求的兩個輸出行。
我知道這些設置起來似乎比較復雜。但它最終帶來的好處可以說明這些麻煩是完全值得的,特別是對那些需要經(jīng)常重復的有相同規(guī)律的裝載操作來說。通過啟用并行DML,然后使用表函數(shù),現(xiàn)在只需一次操作,即可實現(xiàn)人口普查數(shù)據(jù)的裝載和轉(zhuǎn)換:
ALTERSESSIONENABLEPARALLELDML;INSERT/*+APPENDPARALLEL(t,4)*/INTOcity_populationstSELECT*
FROMTABLE(census_package.census_transform(
CURSOR(SELECTcity_name,pop_1990,pop_201*
FROMcity_populations_ext)));
圖3:并行裝載和智能更新/插入
圖3:一個單步、流水線的和并行的裝載過程
人口普查數(shù)據(jù)將從外部表讀取,采用文件內(nèi)的并行機制,這一過程將被并行化處理。每個并行操作的輸出結果都會送入單獨的進程,這些進程也是并行運行的,可將每個輸入行轉(zhuǎn)換成您希望的兩個輸出行。由于表函數(shù)負責進行轉(zhuǎn)換,所以只需對外部文件遍歷一次即可。在此,還應提醒您注意的是不必用任何形式的工作表來暫存數(shù)據(jù),數(shù)據(jù)以流水線的方式從外部數(shù)據(jù)文件提取,經(jīng)過表函數(shù)的處理,然后直接傳送給目標表。由于減少了數(shù)據(jù)復制的次數(shù),所以有效地減少了對磁盤空間的占用。
MERGE語句
MERGE語句解決了長期存在的一個問題,那就是如何對以前裝載過的數(shù)據(jù)進行重新裝載。在Oracle9i問世之前,您需要編寫過程代碼來判斷一行是否已經(jīng)存在,然后相應地執(zhí)行INSERT或UPDATE語句。而在Oracle9i中,只需使用MERGE語句,然后讓數(shù)據(jù)庫處理所有細節(jié)。
最后,讓我們考慮一下和我們目前假設的情況有關的非常常見的數(shù)據(jù)裝載問題。假定我們以前已將人口普查數(shù)據(jù)裝載到city_population表中,而且當前的輸入文件下包含了新數(shù)據(jù)和對已有數(shù)據(jù)的更新。在這種情況下,對于文件中作了更新的數(shù)據(jù),我們希望更新city_population表中已存在的行;對于文件中的新數(shù)據(jù),我們希望在city_population表中插入新行。這是一個極其常見的問題,通常通過執(zhí)行用PL/SQL編寫的過程邏輯來處理。例如,清單2中的PL/SQL代碼每次可從city_population_ext中讀取一行,然后根據(jù)情況執(zhí)行UPDATE或INSERT語句。
但是,這樣的處理方式會帶來幾方面的問題。首先,由于不再使用表函數(shù),所以不能對裝載過程的"轉(zhuǎn)換"部分進行并行處理。另外,還不得不為每一條輸入記錄都執(zhí)行多達兩條DML語句。另外,我們不得不去關心最有可能的情況是UPDATE;還是INSERT。最后,由于要為這種類型的邏輯編制代碼,所以對我們來說無疑是一個痛苦的過程。那么,有沒有單獨一條語句即可做完所有這些工作呢?在Oracle9i中,終于出現(xiàn)這樣的語句,這便是MERGE(合并)語句。它可根據(jù)情況要么INSERT(插入)新行,要么UPDATE(更新)已存在的行。
編寫一條MERGE語句時,必須指定下述項:
目標表的名稱
一條SELECT語句,用作數(shù)據(jù)源一個條件,用于標識對現(xiàn)有數(shù)據(jù)進行更新的輸入行UPDATE和INSERT子句
下述MERGE語句可從外部表將數(shù)據(jù)正確地合并到city_populations表中。其中,ON子句引用了目標表的主鍵列。數(shù)據(jù)庫從源查詢讀一個數(shù)據(jù)行時,它會考察目標表中的每一行(本例通過主鍵索引),對其ON條件求值。如果在目標表中發(fā)現(xiàn)一行的ON條件為TRUE,那么數(shù)據(jù)庫會對源行作更新。
MERGEINTOcity_populationsdest
USING(SELECT*FROMTABLE(census_package.census_transform(CURSOR(SELECTcity_name,pop_1990,pop_201*FROMcity_populations_ext))))srcON(dest.city_name=src.city_nameANDdest.census_year=src.census_year)WHENMATCHEDTHENUPDATESET
dest.population=src.populationWHENNOTMATCHEDTHEN
INSERT(city_name,census_year,population)
VALUES(src.city_name,src.census_year,src.population);
這太令人激動了!只需一個語句,便可從外部數(shù)據(jù)文件讀取數(shù)據(jù),通過表函數(shù),對其進行轉(zhuǎn)換以符合我們數(shù)據(jù)表的要求,然后在我們數(shù)據(jù)庫表中相應地要么更新現(xiàn)有的行,要么插入新行。圖3對這個過程進行了圖示。另外,整個過程中不需要創(chuàng)建臨時表,所有這些工作可按您的愿望進行任何程度的并行化處理。總結
合并數(shù)據(jù)是一個由來已久的問題,令人高興的是,現(xiàn)在終于有了一個方法能夠在數(shù)據(jù)庫一級解決它,同時還能充分利用數(shù)據(jù)庫的伸縮性。我預計,盡管表函數(shù)是為簡化裝載過程而設計的,但它將會還有許多其他用途。現(xiàn)在想起來,在我過去幾年寫的數(shù)份報告中,其實都可利用它把事情做得更好。外部表承諾可以消除外部和內(nèi)部數(shù)據(jù)之間的界限,以便更容易地將數(shù)據(jù)從外部系統(tǒng)載入數(shù)據(jù)庫。使用這些嶄新的ETL特性,一個多步驟的、單線程的裝載過程(如圖1所示)可以轉(zhuǎn)換成單步驟的、流水線的以及高度并行的裝載過程(如圖3所示)。
JonathanGennick(jonathan@gennick.com)是一名經(jīng)驗豐富的Oracle數(shù)據(jù)庫管理員,也是一名獲得Oracle認證的專家,非常喜歡探索新的Oracle技術。他是最近出版的新書《SQL*Loader:TheDefinitiveGuide》(O"Reilly&Associates,201*)的作者。
擴展閱讀:Oracle之外部表
Oracle之外部表
有時候,數(shù)據(jù)庫海量數(shù)據(jù)的存儲,是一個令人頭疼的問題。別的不說,光是頻繁的執(zhí)行insert(1000次/秒)都是一場惡夢。但是,如果將數(shù)據(jù)保存到文家里,而不是數(shù)據(jù)庫中,數(shù)據(jù)序列化的開銷就小得多了。但是,對于文件的各種復雜檢索,又是一件相當麻煩的事。
幸好,Oracle有這樣一種特性,它可以將某些特定格式的文件映射到數(shù)據(jù)庫中,形成一個“表”,稱為“外部表”。單用戶更改文件內(nèi)容時,外部表中的數(shù)據(jù)即隨之改變。同時,用戶又可以像檢索普通表一樣,以只讀的方式對外部表進行檢索。
我們假設有這樣一個文件(DATA.TXT):1|thisisastring2|這里是個字符串3|ABC
要把這樣一個文件映射成外部表,有以下工作要做:首先,我們需要為Oracle創(chuàng)建一個Directory,
創(chuàng)建方式為,在數(shù)據(jù)庫中執(zhí)行,須用DBA用戶創(chuàng)建,并給應用授權。createdirectoryEXT_TABLE_DIRas"/home/oracle/app/oracle/oradata/php/"
注意“/home/oracle/app/oracle/oradata/php/”是一個存在于Oracle數(shù)據(jù)庫服務器本身上邊的實際存在的文件夾;
然后,將DATA.TXT文件拷貝到上述文件夾下;最后,創(chuàng)建一個對應外部表,createtableEXT_TABLE_NAME(
COL_1NUMBER,
COL_2VARCHAR2(512))
organizationexternal(
typeoracle_loader
defaultdirectoryEXT_TABLE_DIR
accessparameters(fieldsterminatedby"|")location("DATA.TXT"))
rejectlimitunlimited;
注意藍色部分,EXT_TABLE_NAME是要映射成的外部表名稱,EXT_TABLE_DIR是第一步里我們創(chuàng)建的Oracle的Directory,“|”是文件里的分割符,DATA.TXT是文件名。
需要補充的是,最后有一句“rejectlimit
unlimited”,告訴Oracle這個外部表沒有行數(shù)限制。否則,當文件中的數(shù)據(jù)量超過200萬行時,在對表進行檢索時,就會出現(xiàn)ORA-30653,“rejectlimitreached”錯誤。
Oracle9i的一項新特性就是External
Table,它就象通常的數(shù)據(jù)庫表一樣,擁有字段和數(shù)據(jù)類型約束,并且可以查詢,但是表中的數(shù)據(jù)卻不存儲在數(shù)據(jù)庫中,而是在與數(shù)據(jù)庫相關聯(lián)的普通外部文件里。當你查詢
ExternalTable時,Oracle將解析該文件并返回符合條件的數(shù)據(jù),就象該數(shù)據(jù)存儲在數(shù)據(jù)庫表中一樣。
具體的定義可以參見《Oracle概念手冊》,以下的幾點需要注意::外部表的描述:
>創(chuàng)建的語法類似于:"CREATETABLE...ORGANIZATIONEXTERNAL">數(shù)據(jù)在數(shù)據(jù)庫的外部組織,是操作系統(tǒng)文件。
>操作系統(tǒng)文件在數(shù)據(jù)庫中的標志是通過一個邏輯目錄來映射的。>數(shù)據(jù)是只讀的。(外部表相當于一個只讀的虛表)
>不可以在上面運行任何DML操作,不可以創(chuàng)建索引。>可以查詢操作和連接?梢圆⑿胁僮。例子:
假如有如下兩個數(shù)據(jù)文件:1:數(shù)據(jù)文件的格式F1.TXT文件:13234,FIRSTS46464,TESTAF2.TEXT文件:13234,SECONDS46464,TEST
2:創(chuàng)建目錄,并用DBA進行授權;
sql>createdirectorytest_diras"E:temp";
sql>grantread,writeondirectorytest_dirtousers;注意:創(chuàng)建完畢邏輯目錄之后要把平面文件拷貝到該目錄下,另外還要注意文件名字不要寫錯。
一定要給oracle用戶對這個目錄可讀可寫的權限,操作系統(tǒng)層面,如使用chmod-R777test_dir;
3:使用被授權的用戶users創(chuàng)建外部表:createtabletest_table(ms_novarchar(20),tipvarchar(20),descsvarchar(20))
ORGANIZATIONEXTERNAL(
TYPEORACLE_LOADER
DEFAULTDIRECTORYtest_dirACCESSParameters(
RECORDSDELIMITEDBYNEWLINEbadfile"bad_dev.txt"LOGFILE"log_dev.txt"
FIELDSTERMINATEDBY","
MISSINGFIELDVALUESARENULL(ms_no,tip,descs))
LOCATION("F1.txt","F2.txt"));
表創(chuàng)建完成.當然也可以導入一個文件
4:進行SELECT操作看是否正確;SQL>select*fromtest_table結果如下:
MS_NOTIPDESCS
------------------------------------------------------------13234FIRSTS46464TESTA13234SECONDS46464TEST
:如何得到外部表的有關信息:
SQL>DESCDBA_EXTERNAL_TABLES;NameTypeNullable
-----------------------------------------OWNERVARCHAR2(30)
TABLE_NAMEVARCHAR2(30)TYPE_OWNERCHAR(3)YTYPE_NAMEVARCHAR2(30)
DEFAULT_DIRECTORY_OWNERCHAR(3)YDEFAULT_DIRECTORY_NAMEVARCHAR2(30)REJECT_LIMITVARCHAR2(40)YACCESS_TYPEVARCHAR2(7)Y
ACCESS_PARAMETERSVARCHAR2(4000)YSQL>SELECT
OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERSFRFROM
DBA_EXTERNAL_TABLES;可以得到外部表的相關信息;
:如何得到外部路徑的信息:
SQL>descDBA_EXTERNAL_LOCATIONS;得到該表結構:
NameTypeNullable
-------------------------------------OWNERVARCHAR2(30)
TABLE_NAMEVARCHAR2(30)LOCATIONVARCHAR2(4000)YDIRECTORY_OWNERCHAR(3)Y
DIRECTORY_NAMEVARCHAR2(30)Y
SQL>select*fromDBA_EXTERNAL_LOCATIONS;得到具體信息
友情提示:本文中關于《Oracle外部表進階》給出的范例僅供您參考拓展思維使用,Oracle外部表進階:該篇文章建議您自主創(chuàng)作。
來源:網(wǎng)絡整理 免責聲明:本文僅限學習分享,如產(chǎn)生版權問題,請聯(lián)系我們及時刪除。