甲骨論2012版oracle視頻教程-相克軍老師

分區(qū)（partitioning）最早在Oracle8.0中引入，這個過程是將一個表或索引物理地分解為多個更小、更可管理的部分。對索引或表進(jìn)行分區(qū)時可能發(fā)生3種情況：使用這些分區(qū)表的應(yīng)用可能運行得更慢；可能運行得更快；有可能沒有任何變化。調(diào)高可用性可用性的提高源自于每個分區(qū)的獨立性。對象中一個分區(qū)的可用性（或不可用）并不意味著對象本身是不可用的。優(yōu)化器知道有這種分區(qū)機(jī)制，會相應(yīng)地從查詢計劃中去除未引用的分區(qū)。在一個大對象中如果一個分區(qū)不可用，你的查詢可以消除這個分區(qū)而不予考慮，這樣Oracle就能成功地處理這個查詢。1、如果查詢必須涉及這個分區(qū)，那么查詢報錯2、如果查詢不涉及這個分區(qū)，那么可以正常查詢別的分區(qū)數(shù)據(jù)的“擺放”有些隨機(jī)。通過使用散列分區(qū)，我們讓Oracle隨機(jī)地（很可能均勻地）將數(shù)據(jù)分布到多個分區(qū)上。我們無法控制數(shù)據(jù)要分布到哪個分區(qū)上；Oracle會根據(jù)生成的散列鍵值來確定。模擬一個分區(qū)故障。減少管理負(fù)擔(dān)例如，假設(shè)數(shù)據(jù)庫中有一個10GB的索引。如果需要重建這個索引，而該索引未分區(qū)，你就必須將整個10GB的索引作為一個工作單元來重建。盡管可以在線地重建索引，但是要完全重建完整的10GB索引，還是需要占用大量的資源。如果將索引本身劃分為10個1GB的分區(qū)，各個索引的重建也更快（可能是原來的10倍）。你可能只需要重建全部聚集索引的10%，例如，只是“最新”的數(shù)據(jù)（活動數(shù)據(jù)）需要重組，而所有“較舊”的數(shù)據(jù)（相當(dāng)靜態(tài)）不受影響。你發(fā)現(xiàn)表中50%的行都是“移植”行，可能想進(jìn)行修正。建立一個分區(qū)表將有利于這個操作。為了“修正”移植行，你往往必須重建對象，在這種情況下，就是要重建一個表。如果有一個100GB的表，就需要在一個非常大的“塊”（chunk）上連續(xù)地使用ALTERTABLEMOVE來執(zhí)行這個操作。另一方面，如果你有25個分區(qū)，每個分區(qū)的大小為4GB，就可以一個接一個地重建各個分區(qū)。對于一個未分區(qū)對象所能做的工作，分區(qū)對象中的單個分區(qū)幾乎都能做到。你甚至可能發(fā)現(xiàn)，移植行都集中在一個很小的分區(qū)子集中，因此，可以只重建一兩個分區(qū)，而不是重建整個表。BIG_TABLE1和BIG_TABLE2都是從BIG_TABLE的一個10,000,000行的實例創(chuàng)建的。BIG_TABLE1是一個常規(guī)的未分區(qū)表，而BIG_TABLE2是一個散列分區(qū)表，有8個分區(qū)。createtablebig_table1(ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY)tablespacebig1asselectID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARYfrombig_table.big_table;createtablebig_table2(ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY)partitionbyhash(id)(partitionpart_1tablespacebig2,partitionpart_2tablespacebig2,partitionpart_3tablespacebig2,partitionpart_4tablespacebig2,partitionpart_5tablespacebig2,partitionpart_6tablespacebig2,partitionpart_7tablespacebig2,partitionpart_8tablespacebig2)asselectID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARYfrombig_table.big_table;每個表都在自己的表空間中，所以我們可以很容易地查詢數(shù)據(jù)字典，來查看每個表空間中已分配的空間和空閑空間：selectb.tablespace_name,mbytes_alloc,mbytes_freefrom(selectround(sum(bytes)/1024/1024)mbytes_free,tablespace_namefromdba_free_spacegroupbytablespace_name)a,(selectround(sum(bytes)/1024/1024)mbytes_alloc,tablespace_namefromdba_data_filesgroupbytablespace_name)bwherea.tablespace_name(+)=b.tablespace_nameandb.tablespace_namein('BIG1','BIG2');TABLESPACE MBYTES_ALLOC MBYTES_FREE---------- ------------ -----------BIG1 1496 344BIG2 1496 344ORA10GR1>altertablebig_table1move;altertablebig_table1move*ERRORatline1:ORA-01652:unabletoextendtempsegmentby1024intablespaceBIG1失敗了，BIG1表空間中要有足夠的空閑空間來放下BIG_TABLE1的完整副本，同時它的原副本仍然保留，簡單地說，我們需要一個很短的時間內(nèi)有大約兩倍的存儲空間（可能多一點，也可能少一點，這取決于重建后表的大?。?。粗略的認(rèn)為，這個表空間至少需要一倍于這個表的空閑空間。對BIG_TABLE2執(zhí)行同樣的操作：ORA10GR1>altertablebig_table2

move;altertablebig_table2move*ERRORatline1:ORA-14511:cannotperformoperationonapartitionedobject 無法對這個“表”執(zhí)行MOVE操作；我們必須在表的各個分區(qū)上執(zhí)行這個操作?？梢灾饌€地移動（相應(yīng)地重建和重組）各個分區(qū)：ORA10GR1>altertablebig_table2movepartitionpart_1;Tablealtered.ORA10GR1>altertablebig_table2movepartitionpart_2;Tablealtered.ORA10GR1>altertablebig_table2movepartitionpart_3;Tablealtered.ORA10GR1>altertablebig_table2movepartitionpart_4;Tablealtered.ORA10GR1>altertablebig_table2movepartitionpart_5;Tablealtered.ORA10GR1>altertablebig_table2movepartitionpart_6;Tablealtered.ORA10GR1>altertablebig_table2movepartitionpart_7;Tablealtered.ORA10GR1>altertablebig_table2movepartitionpart_8;Tablealtered.

我們需要的臨時資源將顯著減少。不僅如此，如果在移動到PART_4后但在PART_5完成“移動”之前系統(tǒng)失敗了（例如，掉電），我們并不會丟失以前所做的所有工作，這與執(zhí)行一個MOVE語句的情況不同。前4個分區(qū)仍是“移動”后的狀態(tài)，等系統(tǒng)恢復(fù)時，我們可以從分區(qū)PART_5繼續(xù)處理。ORA10GR1>beginforxin(selectpartition_namefromuser_tab_partitionswheretable_name='BIG_TABLE2')loopexecuteimmediate'altertablebig_table2movepartition'||x.partition_name;endloop;end;使用上面的腳本可以實現(xiàn)。改善語句性能分區(qū)最后一個總的（潛在）好處體現(xiàn)在改進(jìn)語句（SELECT、INSERT、UPDATE、DELETE、MERGE）的性能方面。并行DML修改數(shù)據(jù)庫中數(shù)據(jù)的語句有可能會執(zhí)行并行DML（parallelDML，PDML）。采用PDML時，Oracle使用多個線程或進(jìn)程來執(zhí)行INSERT、UPDATE或DELETE，而不是執(zhí)行一個串行進(jìn)程。在一個有充足I/O帶寬的多CPU主機(jī)上，對于大規(guī)模的DML操作，速度的提升可能相當(dāng)顯著。在Oracle9i以前的版本中，PDML要求必須分區(qū)。如果你的表沒有分區(qū)，在先前的版本中就不能并行地執(zhí)行這些操作。如果表確實已經(jīng)分區(qū)，Oracle會根據(jù)對象所有的物理分區(qū)數(shù)為對象指定一個最大并行度。從很大程度上講，在Oracle9i及以后版本中這個限制已經(jīng)放松，只有一個突出的例外；如果希望在一個表上執(zhí)行PDML，而且這個表的一個LOB列上有一個位圖索引，要并行執(zhí)行操作就必須對這個表分區(qū)；另外并行度就限制為分區(qū)數(shù)。不過，總的說來，使用PDML并不一定要求進(jìn)行分區(qū)。查詢性能在只讀查詢（SELECT語句）的性能方面，分區(qū)對兩類特殊操作起作用：1、分區(qū)消除（partitionelimination）：處理查詢時不考慮某些數(shù)據(jù)分區(qū)。我們已經(jīng)看到了一個分區(qū)消除的例子。2、并行操作（paralleloperation）：并行全表掃描和并行索引區(qū)間掃描就是這種操作的例子。OLTP系統(tǒng)事實上在一個OLTP系統(tǒng)中，查詢已經(jīng)有以下特點：即索引訪問相當(dāng)快，因此，分區(qū)不會讓索引訪問的速度有太大的提高（甚至根本沒有任何提高）。這并不是說要絕對避免在OLTP系統(tǒng)中使用分區(qū)；而只是說不要指望通過分區(qū)來提供大幅的性能提升。盡管有效情況下分區(qū)能夠改善查詢的性能，但是這些情況在大多數(shù)OLTP應(yīng)用中并不成立。不過在OLTP系統(tǒng)中，你還是可以得到另外兩個可能的好處：減輕管理負(fù)擔(dān)以及有更高的可用性。有分區(qū)的OLTP系統(tǒng)確實也有可能得到效率提示。例如，可以用分區(qū)來減少競爭，從而提高并發(fā)度。至于并行操作，你可能不希望在一個OLTP系統(tǒng)中執(zhí)行并行查詢。你會慎用并行操作，而是交由DBA來完成重建、創(chuàng)建索引、收集統(tǒng)計信息等工作。數(shù)據(jù)倉庫系統(tǒng)處理許多查詢時都要求執(zhí)行一個全表掃描，但是最后卻發(fā)現(xiàn)，一方面必須掃描數(shù)百萬條記錄，但另一方面其中大多數(shù)記錄并不適用于我們的查詢。如果使用一種明智的分區(qū)機(jī)制，就可以實現(xiàn)消除分區(qū)，這樣在查詢某個給定的數(shù)據(jù)時，就可以只對這個數(shù)據(jù)進(jìn)行全面掃描。這在所有可能的解決方案中是最佳的選擇。在一個數(shù)據(jù)倉庫/決策支持環(huán)境中，會頻繁地使用并行查詢。因此，諸如并行索引區(qū)間掃描或并行快速全面索引掃描等操作對我們很有好處。表分區(qū)機(jī)制目前Oracle中有4種對表分區(qū)的方法：1、區(qū)間分區(qū)：通過數(shù)據(jù)范圍進(jìn)行分區(qū)。例如，時間戳在Jan-2005內(nèi)的所有記錄都存儲在分區(qū)1中，時間戳在Feb-2005內(nèi)的所有記錄都存儲在分區(qū)2中，依此類推。這可能是Oracle中最常用的分區(qū)機(jī)制。2、散列分區(qū)：這是指在一個列（或多個列）上應(yīng)用一個散列函數(shù)，行會按這個散列值放在某個分區(qū)中。3、列表分區(qū)：指定一個離散值集，來確定應(yīng)當(dāng)存儲在一起的數(shù)據(jù)。例如，可以指定STATUS列值在（’A’,’M’,’Z’）中的行放在分區(qū)1中，STATUS值在（‘D’,’P’,’Q’）中的行放在分區(qū)2中，依此類推。4、組合分區(qū)：這是區(qū)間分區(qū)和散列分區(qū)的一種組合，或者是區(qū)間分區(qū)與列表分區(qū)的組合。通過組合分區(qū)，你可以先對某些數(shù)據(jù)應(yīng)用區(qū)間分區(qū)，再在區(qū)間中根據(jù)散列或列表來選擇最后的分區(qū)。區(qū)間分區(qū)ORA10GR1>CREATETABLErange_example(range_key_columndate,datavarchar2(20))PARTITIONBYRANGE(range_key_column)(PARTITIONpart_1VALUESLESSTHAN(to_date('01/01/2005','dd/mm/yyyy')),PARTITIONpart_2VALUESLESSTHAN(to_date('01/01/2006','dd/mm/yyyy'));

我們在范圍分區(qū)的時候，要注意范圍分區(qū)的值的情況。ORA10GR1>CREATETABLErange_example(range_key_columndate,datavarchar2(20))PARTITIONBYRANGE(range_key_column)(PARTITIONpart_1VALUESLESSTHAN(to_date('01/01/2005','dd/mm/yyyy')),PARTITIONpart_2VALUESLESSTHAN(to_date('01/01/2006','dd/mm/yyyy'))PARTITIONpart_3VALUESLESSTHAN(MAXVALUE)；向這個表插入一個行時，這一行肯定會放入三個分區(qū)中的某一個分區(qū)中，而不會再拒絕任何行，因為分區(qū)PART_3可以接受不能放在PART_1或PART_2中的任何RANG_KEY_COLUMN值（即使RANGE_KEY_COLUMN值為null，也會插入到這個新分區(qū)中）。散列分區(qū)對一個表執(zhí)行散列分區(qū)（hashpartitioning）時，Oracle會對分區(qū)鍵應(yīng)用一個散列函數(shù)，以此確定數(shù)據(jù)應(yīng)當(dāng)放在N個分區(qū)中的哪一個分區(qū)中。Oracle建議N是2的一個冪（2、4、8、16等），從而得到最佳的總體分布。散列分區(qū)設(shè)計為能使數(shù)據(jù)很好地分布在多個不同設(shè)備（磁盤）上，為表選擇的散列鍵應(yīng)當(dāng)是惟一的一個列或一組列，或者至少有足夠多的相異值，以便行能在多個分區(qū)上很好地（均勻地）分布。如果你選擇一個只有4個相異值的列，并使用兩個分區(qū)，那么最后可能把所有行都散列到同一個分區(qū)上，這就有悖于分區(qū)的最初目標(biāo)！ORA10G>CREATETABLEhash_example(hash_key_columndate,datavarchar2(20))PARTITIONBYHASH(hash_key_column)(partitionpart_1tablespacep1,partitionpart_2tablespacep2);1、如果使用散列分區(qū)，你將無從控制一行最終會放在哪個分區(qū)中。Oracle會應(yīng)用散列函數(shù)，并根據(jù)散列的結(jié)果來確定行會放在哪里。2、如果改變散列分區(qū)的個數(shù)，數(shù)據(jù)會在所有分區(qū)中重新分布（向一個散列分區(qū)表增加或刪除一個分區(qū)時，將導(dǎo)致所有數(shù)據(jù)都重寫，因為現(xiàn)在每一行可能屬于一個不同的分區(qū)）。3、分區(qū)數(shù)應(yīng)該是2的冪如果分區(qū)數(shù)是2的冪，那么分區(qū)將會均勻分布，如果不是2的冪，那么分區(qū)將會不均勻分布。我們顯然希望得到均勻分布的分區(qū)。列表分區(qū)列表分區(qū)（listpartitioning）是Oracle9iRelease1的一個新特性。它提供了這樣一種功能，可以根據(jù)離散的值列表來指定一行位于哪個分區(qū)。ORA10G>createtablelist_example(state_cdvarchar2(2),datavarchar2(20))partitionbylist(state_cd)(partitionpart_1values('ME','NH','VT','MA'),partitionpart_2values('CT','RI','NY'));1、如果我們想插入列表分區(qū)中未指定的一個值，Oracle會向客戶應(yīng)用返回一個合適的錯誤。ORA10G>insertintolist_examplevalues('VA','data');insertintolist_examplevalues('VA','data')*ERRORatline1:ORA-14400:insertedpartitionkeydoesnotmaptoanypartition ORA10G>altertablelist_exampleaddpartitionpart_3values(DEFAULT);ORA10G>insertintolist_examplevalues('VA','data');1rowcreated. 2、關(guān)于DEFAULT的使用，有一點要注意：一旦列表分區(qū)表有一個DEFAULT分區(qū)，就不能再向這個表中增加更多的分區(qū)了。此時必須刪除DEFAULT分區(qū)，然后增加PART_4，再加回DEFAULT分區(qū)。組合分區(qū)在組合分區(qū)中，頂層分區(qū)機(jī)制總是區(qū)間分區(qū)。第二級分區(qū)機(jī)制可能是列表分區(qū)或散列分區(qū)（在Oracle9iRelease1及以前的版本中，只支持散列子分區(qū)，而沒有列表分區(qū)）。使用組合分區(qū)時，并沒有分區(qū)段，而只有子分區(qū)段。分區(qū)本身并沒有段（這就類似于分區(qū)表沒有段）。數(shù)據(jù)物理的存儲在子分區(qū)段上，分區(qū)成為一個邏輯容器，或者是一個指向?qū)嶋H子分區(qū)的容器。ORA10G>CREATETABLEcomposite_example(range_key_columndate,hash_key_columnint,datavarchar2(20))PARTITIONBYRANGE(range_key_column)subpartitionbyhash(hash_key_column)subpartitions2(PARTITIONpart_1VALUESLESSTHAN(to_date('01/01/2005','dd/mm/yyyy'))(subpartitionpart_1_sub_1,subpartitionpart_1_sub_2),PARTITIONpart_2VALUESLESSTHAN(to_date('01/01/2006','dd/mm/yyyy'))(subpartitionpart_2_sub_1,subpartitionpart_2_sub_2);在區(qū)間-散列組合分區(qū)中，Oracle首先會應(yīng)用區(qū)間分區(qū)規(guī)則，得出數(shù)據(jù)屬于哪個區(qū)間。然后再應(yīng)用散列函數(shù)，來確定數(shù)據(jù)最后要放在哪個物理分區(qū)中。ORA10G>CREATETABLEcomposite_range_list_example(range_key_columndate,code_key_columnint,datavarchar2(20))PARTITIONBYRANGE(range_key_column)subpartitionbylist(code_key_column)(PARTITIONpart_1VALUESLESSTHAN(to_date('01/01/2005','dd/mm/yyyy'))(subpartitionpart_1_sub_1values(1,3,5,7),subpartitionpart_1_sub_2values(2,4,6,8)),PARTITIONpart_2VALUESLESSTHAN(to_date('01/01/2006','dd/mm/yyyy'))(subpartitionpart_2_sub_1values(1,3),subpartitionpart_2_sub_2values(5,7),subpartitionpart_2_sub_3values(2,4,6,8));每個分區(qū)的子分區(qū)的數(shù)目是不一樣的。如果用于確定分區(qū)的列有修改會發(fā)生什么。需要考慮兩種情況：1、修改不會導(dǎo)致使用一個不同的分區(qū)；行仍屬于原來的分區(qū)。這在所有情況下都得到支持。2、修改會導(dǎo)致行跨分區(qū)移動。只有當(dāng)表啟用了行移動時才支持這種情況；否則，會產(chǎn)生一個錯誤。ORA10G>insertintorange_example(range_key_column,data)values(to_date('15-dec-200400:00:00','dd-mon-yyyyhh24:mi:ss'),'applicationdata...');ORA10G>insertintorange_example(range_key_column,data)values(to_date('01-jan-200500:00:00','dd-mon-yyyyhh24:mi:ss')-1/24/60/60,'applicationdata...');ORA10G>select*fromrange_examplepartition(part_1);RANGE_KEY DATA--------- --------------------15-DEC-04 applicationdata...31-DEC-04 applicationdata...取其中一行，并更新其RANGE_KEY_COLUMN值，不過更新后它還能放在PART_1中：ORA10G>updaterange_examplesetrange_key_column=trunc(range_key_column)whererange_key_column=to_date('31-dec-200423:59:59','dd-mon-yyyyhh24:mi:ss');成功!再把RANGE_KEY_COLUMN更新為另一個值，但這次更新后的值將導(dǎo)致它屬于分區(qū)PART_2：ORA10G>updaterange_examplesetrange_key_column=to_date('02-jan-2005','dd-mon-yyyy')whererange_key_column=to_date('31-dec-2004','dd-mon-yyyy');updaterange_example*ERRORatline1:ORA-14402:updatingpartitionkeycolumnwouldcauseapartitionchange 在Oracle8i及以后的版本中，可以在這個表上啟用行移動（rowmovement），以允許從一個分區(qū)移動到另一個分區(qū)。行的ROWID會由于更新而改變：ORA10G>selectrowidfromrange_examplewhererange_key_column=to_date('31-dec-2004','dd-mon-yyyy');ROWID------------------AAARmfAAKAAAI+aAABORA10G>altertablerange_exampleenablerowmovement; ORA10G>updaterange_examplesetrange_key_column=to_date('02-jan-2005','dd-mon-yyyy')whererange_key_column=to_date('31-dec-2004','dd-mon-yyyy');ORA10G>selectrowidfromrange_examplewhererange_key_column=to_date('02-jan-2005','dd-mon-yyyy');ROWID------------------AAARmgAAKAAAI+iAAC在其他一些情況下，ROWID也有可能因為更新而改變。1、更新IOT的主鍵可能導(dǎo)致ROWID改變，該行的通用ROWID（UROWID）也會改變。2、Oracle10g的FLASHBACKTABLE命令可能改變行的ROWID3、Oracle10g的ALTERTABLESHRINK命令也可能使行的ROWID改變。1、執(zhí)行行移動時，實際上在內(nèi)部就好像先刪除了這一行，然后再將其重新插入。這會更新這個表上的索引，刪除舊的索引條目，再插入一個新條目。2、會完成DELETE再加一個INSERT的相應(yīng)物理工作。3、盡管在此執(zhí)行了行的物理刪除和插入，在Oracle看來卻還是一個更新，因此，不會導(dǎo)致INSERT和DELETE觸發(fā)器觸發(fā)，只有UPDATE觸發(fā)器會觸發(fā)。4、行移動的開銷比正常的UPDATE昂貴得多。如果構(gòu)建的系統(tǒng)會頻繁修改分區(qū)鍵，而且這種修改會導(dǎo)致分區(qū)移動，這實在是一個糟糕的設(shè)計決策。索引分區(qū)索引與表類似，也可以分區(qū)。對索引進(jìn)行分區(qū)有兩種可能的方法：1、隨表對索引完成相應(yīng)的分區(qū)：這也稱為局部分區(qū)索引（locallypertitionedindex）。每個表分區(qū)都有一個索引分區(qū)，而且只索引該表分區(qū)。一個給定索引分區(qū)中的所有條目都指向一個表分區(qū)，表分區(qū)中的所有行都表示在一個索引分區(qū)中。2、按區(qū)間對索引分區(qū)：這也稱為全局分區(qū)索引（globallypartitionedindex）。在此，索引按區(qū)間分區(qū)（或者在Oracle10g中該可以按散列分區(qū)），一個索引分區(qū)可能指向任何（和所有）表分區(qū)。對于全局分區(qū)索引，要注意實際上索引分區(qū)數(shù)可能不同于表分區(qū)數(shù)。由于全局索引只按區(qū)間或散列分區(qū)，如果希望有一個列表或組合分區(qū)索引，就必須使用局部索引。局部索引會使用底層表相同的機(jī)制分區(qū)。

注意全局索引的散列分區(qū)是Oracle10gRelease1及以后的版本中才有的新特性。在Oracle9i及以前的版本中，只能按區(qū)間進(jìn)行全局分區(qū)。分區(qū)消除行為如果查詢首先訪問索引，它是否能消除分區(qū)完全取決于查詢中的謂詞。ORA10G>CREATETABLEpartitioned_table(aint,bint,datachar(20))PARTITIONBYRANGE(a)(PARTITIONpart_1VALUESLESSTHAN(2)tablespacep1,PARTITIONpart_2VALUESLESSTHAN(3)tablespacep2);小于2的值都在分區(qū)PART_1中，小于3的值則都在分區(qū)PART_2中。創(chuàng)建一個局部前綴索引LOCAL_PREFIXED和一個局部非前綴索引LOCAL_NONPREFIXED。ORA10G>createindexlocal_prefixedonpartitioned_table(a,b)local;Indexcreated.ORA10G>createindexlocal_nonprefixedonpartitioned_table(b)local;Indexcreated.非前綴索引在其定義中沒有以A作為其前導(dǎo)列（最前列），這是這一點使之成為一個非前綴索引。ORA10G>insertintopartitioned_tableselectmod(rownum-1,2)+1,rownum,'x'fromall_objects;48967rowscreated.ORA10G>begindbms_stats.gather_table_stats(user,'PARTITIONED_TABLE',cascade=>TRUE);end;/PL/SQLproceduresuccessfullycompleted.向一個分區(qū)中插入一些數(shù)據(jù)，并收集統(tǒng)計信息。將表空間P2離線，其中包含用于表和索引的PART_2分區(qū)ORA10G>altertablespacep2offline;Tablespacealtered. 表空間P2離線后，Oracle就無法訪問這些特定的索引分區(qū)。這就好像是我們遭遇了“介質(zhì)故障”，導(dǎo)致分區(qū)不可用?，F(xiàn)在我們查詢這個表，來看看不同的查詢需要哪些索引分區(qū)。第一個查詢編寫為允許使用局部前綴索引：ORA10G>select*frompartitioned_tablewherea=1andb=1;A B DATA----------

----------

--------------------1

1

x 這個查詢成功了，通過查看解釋計劃，可以看到這個查詢?yōu)槭裁茨艹晒?。使用LOCAL_PREFIXED的查詢成功了。優(yōu)化器能消除LOCAL_PREFIXED的PART_2不予考慮，因為我們在查詢中指定了A=1，而且在計劃中可以清楚地看到PSTART和PSTOP都等于1.局部索引的限制：1、要能夠?qū)崿F(xiàn)消除分區(qū)，必須將分區(qū)鍵作為where條件ORA10G>select*frompartitioned_tablewhereb=1;ERROR:ORA-00376:file13cannotbereadatthistimeORA-01110:datafile13:'/home/ora10g/.../o1_mf_p2_1dzn8jwp_.dbf'norowsselected ORA10G>deletefromplan_table;4rowsdeleted.ORA10G>explainplanforselect*frompartitioned_tablewhereb=1;Explained.select*fromtable(dbms_xplan.display); select*frompartitioned_tablewhereb=1;沒有實現(xiàn)消除分區(qū)，因此訪問所有分區(qū)。是否消除分區(qū)，關(guān)鍵看的是是否使用分區(qū)鍵，和前綴索引、非前綴索引沒有關(guān)系。要使用非前綴索引，必須使用一個允許分區(qū)消除的查詢。，這樣效率才會提高。ORA10G>dropindexlocal_prefixed;Indexdropped.ORA10G>select*frompartitioned_tablewherea=1andb=1;A B DATA---------- ---------- --------------------1 1 x 非前綴索引也能實現(xiàn)消除分區(qū)。如果不能實現(xiàn)分區(qū)消除，那么oracle將會對多個分區(qū)的多個分區(qū)索引進(jìn)行掃描。不必對非前綴索引退避三舍，也不要認(rèn)為非前綴索引是主要的性能障礙。重點是，要盡可能保證查詢包含的謂詞允許索引分區(qū)消除。使用前綴局部索引可以保證這一點，使用非前綴索引則不能保證。主要是因為前綴索引將分區(qū)鍵作為前導(dǎo)列。如果你頻繁地用以下查詢來查詢先前的表：select...frompartitioned_tablewherea=:aandb=:b; select...frompartitioned_tablewhereb=:b; 可以考慮在（b,a）上使用一個局部非前綴索引。這個索引對于前面的兩個查詢都是有用的。（a,b）上的局部前綴索引只對第一個查詢有用。局部索引和惟一約束為了保證惟一性（這包括UNIQUE約束或PRIMARYKEY約束），如果你想使用一個局部索引來保證這個約束，那么分區(qū)鍵必須包括在約束本身中。這是局部索引的最大限制。Oracle只保證索引分區(qū)內(nèi)部的惟一性，而不能跨分區(qū)。這意味著不能一方面在一個TIMESTAMP字段上執(zhí)行區(qū)間分區(qū)，而另一方面在ID上有一個主鍵（使用一個局部分區(qū)索引來保證）。Oracle會利用全局索引來保證惟一性。分區(qū)鍵建立分區(qū)以后，保證在不同的分區(qū)內(nèi)，分區(qū)鍵一定是不同的。如果建立主鍵的時候，包括分區(qū)鍵，那么可以保證分區(qū)鍵不同的時候，一定在不同的分區(qū)內(nèi)，分區(qū)鍵相同的時候，一定在同一個分區(qū)內(nèi)。這樣局部唯一性約束可以保證整個表的唯一性。創(chuàng)建一個區(qū)間分區(qū)表，它按一個名為LOAD_TYPE的列分區(qū)，卻在ID列上有一個主鍵ORA10G>CREATETABLEpartitioned(load_datedate,idint,constraintpartitioned_pkprimarykey(id))PARTITIONBYRANGE(load_date)(PARTITIONpart_1VALUESLESSTHAN(to_date('01/01/2000','dd/mm/yyyy')),PARTITIONpart_2VALUESLESSTHAN(to_date('01/01/2001','dd/mm/yyyy')));主鍵索引是一個全局索引，沒有進(jìn)行分區(qū)。系統(tǒng)想在id列上建立一個全局索引，發(fā)現(xiàn)這個列上已經(jīng)建立了索引，系統(tǒng)報錯。如果這個列上沒有建立索引，那么主鍵就會建立成功。如果這個鍵上建立了一個全局索引，那么主鍵也會建立成功，主鍵將會使用這個索引。全局索引全局索引使用一種有別于底層表的機(jī)制進(jìn)行分區(qū)。表可以按一個TIMESTAMP列劃分為10個分區(qū)，而這個表上的一個全局索引可以按REGION列劃分為5個分區(qū)。與局部索引不同，全局索引只有一類，這就是前綴全局索引（prefixedglobalindex）。如果全局索引的索引鍵未從該索引的分區(qū)鍵開始，這是不允許的。這說明，不論用什么屬性對索引分區(qū)，這些屬性都必須是索引鍵的前幾列。CREATETABLEpartitioned(timestampdate,idint)PARTITIONBYRANGE(timestamp)(PARTITIONpart_1VALUESLESSTHAN(to_date('01-jan-2000','dd-mon-yyyy')),PARTITIONpart_2VALUESLESSTHAN(to_date('01-jan-2001','dd-mon-yyyy')));建立了一個分區(qū)表，分區(qū)鍵是timestamp。ORA10G>createindexpartitioned_indexonpartitioned(id)GLOBALpartitionbyrange(id)(partitionpart_1valueslessthan(1000),partitionpart_2valueslessthan(MAXVALUE));建立了全局索引以后，對全局索引進(jìn)行了分區(qū)。我們在進(jìn)行索引搜索的時候，首先根據(jù)where

id=100，迅速的確定這個搜索落在哪個索引分區(qū)內(nèi)，實現(xiàn)了索引分區(qū)的消減分區(qū)。也就是說，我們只需要搜索一個索引樹。注意，這個索引中使用了MAXVALUE。MAXVALUE不僅可以用于索引中，還可以用于任何區(qū)間分區(qū)表中。它表示區(qū)間的“無限上界”。在此前的所有例子中，我們都使用了區(qū)間的硬性上界（小于<某個值>的值）。不過，全局索引有一個需求，即最高分區(qū)（最后一個分區(qū)）必須有一個值為MAXVALUE的分區(qū)上界。這可以確保底層表中的所有行都能放在這個索引中。ORA10G>altertablepartitionedaddconstraintpartitioned_pkprimarykey(id);因為在id列上有一個全局索引，因此這個主鍵直接使用這個全局索引。ORA10G>dropindexpartitioned_index;dropindexpartitioned_index*ERRORatline1:ORA-02429:cannotdropindexusedforenforcementofunique/primarykey 這個表示我們建立了的索引被主鍵使用。ORA10G>createindexpartitioned_index2onpartitioned(timestamp,id)GLOBALpartitionbyrange(id)(partitionpart_1valueslessthan(1000),partitionpart_2valueslessthan(MAXVALUE))partitionbyrange(id)*ERRORatline4:ORA-14038:GLOBALpartitionedindexmustbeprefixed全局分區(qū)索引必須能夠?qū)崿F(xiàn)當(dāng)進(jìn)行where條件匹配的時候，首先能夠消減分區(qū)，然后在一個分區(qū)內(nèi)實現(xiàn)索引的搜索。我們可以以timestamp進(jìn)行分區(qū)，也可以以timestamp

id復(fù)合進(jìn)行分區(qū)。也就是必須使用索引的前導(dǎo)列進(jìn)行分區(qū)。數(shù)據(jù)倉庫和全局索引原先數(shù)據(jù)倉庫和全局索引是相當(dāng)互斥的。數(shù)據(jù)倉庫就意味著系統(tǒng)有某些性質(zhì)，如有大量的數(shù)據(jù)出入。許多數(shù)據(jù)倉庫都實現(xiàn)了一種滑動窗口（slidingwindow）方法來管理數(shù)據(jù)，也就是說，刪除表中最舊的分區(qū)，并為新加載的數(shù)據(jù)增加一個新分區(qū)。在過去（Oracle8i及以前的版本），數(shù)據(jù)倉庫系統(tǒng)都避免使用全局索引，對此有一個很好的原因：全局索引缺乏可用性。大多數(shù)分區(qū)操作（如刪除一個舊分區(qū)）都會使全局索引無效，除非重建全局索引，否則無法使用，這會嚴(yán)重地影響可用性，以前往往都是如此。滑動窗口和索引

下面的例子實現(xiàn)了一個經(jīng)典的數(shù)據(jù)滑動窗口。在許多實現(xiàn)中，會隨著時間的推移向倉庫中增加數(shù)據(jù)，而最舊的數(shù)據(jù)會老化。在很多時候，這個數(shù)據(jù)會按一個日期屬性進(jìn)行區(qū)間分區(qū)，所以最舊的數(shù)據(jù)多存儲在一個分區(qū)中，新加載的數(shù)據(jù)很可能都存儲在一個新分區(qū)中。每月的加載過程涉及：

1、去除老數(shù)據(jù)：最舊的分區(qū)要么被刪除，要么與一個空表交換（將最舊的分區(qū)變?yōu)橐粋€表），從而允許對舊數(shù)據(jù)進(jìn)行歸檔。

2、加載新數(shù)據(jù)并建立索引：將新數(shù)據(jù)加載到一個“工作”表中，建立索引并進(jìn)行驗證。

3、關(guān)聯(lián)新數(shù)據(jù)：一旦加載并處理了新數(shù)據(jù)，數(shù)據(jù)所在的表會與分區(qū)表中的一個空分區(qū)交換，將表中的這些新加載的數(shù)據(jù)變成分區(qū)表中的一個分區(qū)（分區(qū)表會變得更大）。在這個例子中，我們將處理每年的數(shù)據(jù)，并加載2004和2005財政年度的數(shù)據(jù)。這個表按TIMESTAMP列分區(qū)，并創(chuàng)建了兩個索引，一個是ID列上的局部分區(qū)索引，另一個是TIMESTAMP列上的全局索引：ORA10G>CREATETABLEpartitioned(timestampdate,idint)PARTITIONBYRANGE(timestamp)(PARTITIONfy_2004VALUESLESSTHAN(to_date('01-jan-2005','dd-mon-yyyy')),PARTITIONfy_2005VALUESLESSTHAN(to_date('01-jan-2006','dd-mon-yyyy')));ORA10G>insertintopartitionedpartition(fy_2004)selectto_date('31-dec-2004',’dd-mon-yyyy’)-mod(rownum,360),object_idfromall_objects;ORA10G>insertintopartitionedpartition(fy_2005)selectto_date('31-dec-2005',’dd-mon-yyyy’)-mod(rownum,360),object_idfromall_objects;ORA10G>createindexpartitioned_idx_localonpartitioned(id)LOCAL/ORA10G>createindexpartitioned_idx_globalonpartitioned(timestamp)GLOBAL/數(shù)據(jù)按財政年度分區(qū)，而且最后兩年的數(shù)據(jù)在線。這個表有兩個索引：一個是LOCAL索引，另一個是GLOBAL索引?，F(xiàn)在正處于年末，我們想做下面的工作：

（1）刪除最舊的財政年度數(shù)據(jù)。我們不想永遠(yuǎn)地丟掉這個數(shù)據(jù)，而只是希望它老化，并將其歸檔。（2）增加最新的財政年度數(shù)據(jù)。加載、轉(zhuǎn)換、建索引等工作需要一定的時間。我們想做這個工作，但是希望盡可能不影響當(dāng)前數(shù)據(jù)的可用性。第一步是為2004財政年度建立一個看上去就像分區(qū)表的空表。我們將使用這個表與分區(qū)表中的FY_2004分區(qū)交換。ORA10G>createtablefy_2004(timestampdate,idint);Tablecreated.ORA10G>createindexfy_2004_idxonfy_2004(id)Indexcreated.對要加載的新數(shù)據(jù)做同樣的工作。我們將創(chuàng)建并加載一個表，其結(jié)構(gòu)就像是現(xiàn)在的分區(qū)表。ORA10G>createtablefy_2006(timestampdate,idint);Tablecreated.ORA10G>insertintofy_2006selectto_date('31-dec-2006','dd-mon-yyyy')-mod(rownum,360),object_idfromall_objects;ORA10G>createindexfy_2006_idxonfy_2006(id)nologging; ORA10G>altertablepartitionedexchangepartitionfy_2004withtablefy_2004includingindexeswithoutvalidation/Tablealtered.ORA10G>altertablepartitioneddroppartitionfy_2004/Tablealtered.將分區(qū)交換出去，然后刪除舊的分區(qū)，同時也將分區(qū)索引交換出去。ORA10G>altertablepartitionedaddpartitionfy_2006valueslessthan(to_date('01-jan-2007','dd-mon-yyyy'))Tablealtered.ORA10G>altertablepartitionedexchangepartitionfy_2006withtablefy_2006includingindexeswithoutvalidation將表交換進(jìn)來，同時包括表的索引也交換進(jìn)來。這個工作會立即完成；這是通過簡單的數(shù)據(jù)字典更新實現(xiàn)的。增加空分區(qū)幾乎不需要多少時間來處理。然后，將新創(chuàng)建的空分區(qū)與滿表交換（滿表與空分區(qū)交換），這個操作也會很快完成。新數(shù)據(jù)是在線的。到此為止滑動窗口過程幾乎不會帶來任何停機(jī)時間，但是在我們重建全局索引時，需要相當(dāng)長的時間才能完成。如果查詢依賴于這些索引，在此期間它們的運行時查詢性能就會受到負(fù)面影響，可能根本不會運行，也可能運行時得不到索引提供的好處。所有數(shù)據(jù)都必須掃描，而且要根據(jù)數(shù)據(jù)重建整個索引。如果表的大小為數(shù)百DB，這會占用相當(dāng)多的資源。從Oracle9i開始，對于分區(qū)維護(hù)又增加了另一個選項：可以在分區(qū)操作期間使用UPDATEGLOBALINEXES子句來維護(hù)全局索引。在你刪除一個分區(qū)、分解一個分區(qū)以及在分區(qū)上執(zhí)行任何必要的操作時，Oracle會對全局索引執(zhí)行必要的修改，保證它是最新的。由于大多數(shù)分區(qū)操作都會導(dǎo)致全局索引無效，這個特征對于需要提供數(shù)據(jù)連續(xù)訪問的系統(tǒng)來說是一個大福音。你會發(fā)現(xiàn)，通過犧牲分區(qū)操作的速度，可以換取100%的數(shù)據(jù)可用性（盡管分區(qū)操作的總體響應(yīng)時間會更慢）。簡單地說，如果數(shù)據(jù)倉庫不允許有停機(jī)時間，而且必須支持?jǐn)?shù)據(jù)的滑入滑出等數(shù)據(jù)倉庫技術(shù)，這個特性就再合適不過了，但是你必須了解它帶來的影響。ORA10G>altertablepartitionedexchangepartitionfy_2004withtablefy_2004includingindexeswithoutvalidationUPDATEGLOBALINDEXESORA10G>altertablepartitioneddroppartitionfy_2004UPDATEGLOBALINDEXESORA10G>altertablepartitionedaddpartitionfy_2006valueslessthan(to_date('01-jan-2007','dd-mon-yyyy'))ORA10G>altertablepartitionedexchangepartitionfy_2006withtablefy_2006includingindexeswithoutvalidationUPDATEGLOBALINDEXES這里要做一個權(quán)衡：我們要在全局索引結(jié)構(gòu)上執(zhí)行INSERT和DELETE操作的相應(yīng)邏輯操作。刪除一個分區(qū)時，必須刪除可能指向該分區(qū)的所有全局索引條目。執(zhí)行表與分區(qū)的交換時，必須刪除指向原數(shù)據(jù)的所有全局索引條目，再插入指向剛滑入的數(shù)據(jù)的新條目。所以ALTER命令執(zhí)行的工作量會大幅增加。下面是用runstats來測試整個的性能：execrunStats_pkg.rs_start;altertablepartitionedexchangepartitionfy_2004withtablefy_2004includingindexeswithoutvalidation;altertablepartitioneddroppartitionfy_2004;altertablepartitionedaddpartitionfy_2006valueslessthan(to_date('01-jan-2007','dd-mon-yyyy'));altertablepartitionedexchangepartitionfy_2006withtablefy_2006includingindexeswithoutvalidation;alterindexpartitioned_idx_globalrebuild;execrunStats_pkg.rs_middle;altertablepartitionedexchangepartitionfy_2005withtablefy_2005includingindexeswithoutvalidationupdateglobalindexes;altertablepartitioneddroppartitionfy_2005updateglobalindexes;altertablepartitionedaddpartitionfy_2007valueslessthan(to_date('01-jan-2008','dd-mon-yyyy'));altertablepartitionedexchangepartitionfy_2007withtablefy_2007includingindexeswithoutvalidationupdateglobalindexes;execrunStats_pkg.rs_stop;OLTP和全局索引OLTP系統(tǒng)的特點是會頻繁出現(xiàn)許多小的讀寫事務(wù)，一般來講，在OLTP系統(tǒng)中，首要的是需要快速訪問所需的行，而且數(shù)據(jù)完整性很關(guān)鍵，另外可用性也非常重要。

在OLTP系統(tǒng)中，許多情況下全局索引很有意義。表數(shù)據(jù)可以按一個鍵（一個列鍵）分區(qū)。不過，你可能需要以多種不同的方式訪問數(shù)據(jù)。例如，可能會按表中的LOCATION來劃分EMPLOYEE數(shù)據(jù)，但是還需要按以下列快速訪問EMPLOYEE數(shù)據(jù)：1、DEPARTMENT：部門的地理位置很分散。部門和位置之間沒有任何關(guān)系。2、EMPLOYEE_ID：盡管員工ID能確定位置，但是你不希望必須按EMPLOYEE_ID和LOCATION搜索，因為這樣一來索引分區(qū)上將不能發(fā)生分區(qū)消除。而且EMPLOYEE_ID本身必然是惟一的。3、JOB_TITLE：JOB_TITLE和LOCATION之間沒有任何關(guān)系。任何LOCATION上都可以出現(xiàn)所有JOB_TITLE值。在一個數(shù)據(jù)倉庫中，可以只使用這些鍵上的局部分區(qū)索引，并使用并行索引區(qū)間掃描來快速收集大量數(shù)據(jù)。在這些情況下不必使用索引分區(qū)消除。在OLTP系統(tǒng)中則不同，確實需要使用分區(qū)消除，并發(fā)查詢對這些系統(tǒng)不合適；我們要適當(dāng)?shù)靥峁┧饕?。因此，需要利用某些字段上的全局索引。我們要滿足以下目標(biāo)：1、快速訪問2、數(shù)據(jù)完整性3、可用性在一個OLTP系統(tǒng)中，可以通過全局索引實現(xiàn)這些目標(biāo)。我們可能不實現(xiàn)滑動窗口，而且暫時不考慮審計。我們并不分解分區(qū)（除非有一個預(yù)定的停機(jī)時間），也不會移動數(shù)據(jù)，等等。對于數(shù)據(jù)倉庫中執(zhí)行的操作，一般來說不會在活動的OLTP系統(tǒng)中執(zhí)行它們。ORA10G>createtableemp(EMPNONUMBER(4)NOTNULL,ENAMEVARCHAR2(10),JOBVARCHAR2(9),MGRNUMBER(4),HIREDATEDATE,SALNUMBER(7,2),COMMNUMBER(7,2),DEPTNONUMBER(2)NOTNULL,LOCVARCHAR2(13)NOTNULL)partitionbyrange(loc)(partitionp1valueslessthan('C')tablespacep1,partitionp2valueslessthan('D')tablespacep2,partitionp3valueslessthan('N')tablespacep3,partitionp4valueslessthan('Z')tablespacep4);建立一個分區(qū)表，分區(qū)鍵是LOC。ORA10G>altertableempaddconstraintemp_pkprimarykey(empno);ORA10G>createindexemp_job_idxonemp(job)GLOBAL ORA10G>createindexemp_dept_idxonemp(deptno)GLOBAL 建立了三個全局索引。insertintoempselecte.*,d.locfromscott.empe,scott.deptdwheree.deptno=d.deptno;插入數(shù)據(jù)這里的計劃顯示出對未分區(qū)索引EMP_PK（為支持主鍵所創(chuàng)建）有一個INDEXUNIQUESCAN。然后還有一個TABLEACCESSGLOBALINDEXROWID，其PSTART和PSTOP為ROWID/ROWID，這說明從索引得到ROWID時，它會準(zhǔn)確地告訴我們讀哪個表分區(qū)來得到這一行。這個訪問和未分區(qū)表效果一樣。對于INDEXRANGESCAN，可以看到類似的結(jié)果。在此使用了我們的索引，而且可以對底層數(shù)據(jù)提供高速的OLTP訪問。如果索引進(jìn)行了分區(qū)，則必須是前綴索引，并保證索引分區(qū)消除；因此，這些索引也是可擴(kuò)縮的，這說明我們可以對其分區(qū)，而且能觀察到類似的行為。下面來看可用性方面。在OLTP系統(tǒng)中，全局分區(qū)索引與局部分區(qū)索引有著同樣的高度可用性。通過這個案例：全局索引在訪問底層表的時候，也會利用分區(qū)表的特性。ORA10G>selectempno,job,locfromempwherejob='CLERK';selectempno,job,locfromempwherejob='CLERK'*ERRORatline1:ORA-00376:file13cannotbereadatthistimeORA-01110:datafile13:'/home/ora10g/oradata/.../o1_mf_p2_1dzn8jwp_.dbf‘所有分區(qū)中都有CLERK數(shù)據(jù)，由于3個表空間離線，這一點確實會對我們帶來影響。這是不可避免的，除非我們在JOB上分區(qū)，但是這樣一來，就會像按LOC分區(qū)查詢數(shù)據(jù)一樣出現(xiàn)同樣的問題。Oracle會“盡其所能”地為你提供數(shù)據(jù)。ORA10G>selectcount(*)fromempwherejob='CLERK';COUNT(*)----------4 如果可以由索引來回答查詢，就要避免TABLEACCESSBYROWID，數(shù)據(jù)不可用的事實并不重要。分區(qū)和性能對總體查詢性能來說，分區(qū)的影響無非有以下三種可能：1、使你的查詢更快2、根本不影響查詢的性能3、使你的查詢更慢，而且與未分區(qū)實現(xiàn)相比，會占用多出幾倍的資源在一個數(shù)據(jù)倉庫中，如果查詢頻繁地全面掃描很大的數(shù)據(jù)表，通過消除大段的數(shù)據(jù)，分區(qū)能夠?qū)@些查詢有很好的影響。通過分區(qū)消除，90%的數(shù)據(jù)都可以不考慮。你的查詢往往會運