2.4　表

比較常見(jiàn)的表類型有規(guī)則表（regular table），嚴(yán)格意義上來(lái)說(shuō)又叫heap table（堆表），這是最普通的一張表，其他類型還有partition table、index-organized table、cluster三種表類型，本節(jié)的重點(diǎn)就是講解一些普通的表。

2.4.1　堆表

對(duì)于一張普通的表，它存放數(shù)據(jù)的規(guī)則是無(wú)序，假設(shè)把數(shù)據(jù)的存儲(chǔ)空間看成學(xué)生宿舍樓一個(gè)連一個(gè)的房間，并不是第一個(gè)來(lái)的人就一定先在第一個(gè)房間。先來(lái)的人只要發(fā)現(xiàn)某個(gè)房間還有床位是空的就可以入住。

那么如何讓他變成有序的呢？我可以專業(yè)創(chuàng)建一列來(lái)記錄順序。宿管在一樓門口發(fā)號(hào)碼，進(jìn)來(lái)一個(gè)同學(xué)，發(fā)一個(gè)號(hào)碼，上面標(biāo)注幾號(hào)房間幾號(hào)床位。這樣所有入住的同學(xué)都是有序的。

堆表是數(shù)據(jù)庫(kù)中最常見(jiàn)的表類型，以堆的形式進(jìn)行組織。換句話說(shuō)，表中的行沒(méi)有按照任何特定的順序存儲(chǔ)，在create table命令中，可以指定子句來(lái)定義以堆表的形式組織的表，但是堆表屬于默認(rèn)值，所以一般用戶在創(chuàng)建數(shù)據(jù)表時(shí)不需要添加這個(gè)關(guān)鍵字。在堆表中，每一行包含一列或者多列，每一列都有一種數(shù)據(jù)類型和一個(gè)長(zhǎng)度，從Oracle 8i版本開(kāi)始，列也可以包含用戶定義的對(duì)象類型。

【示例2-1】下面舉一個(gè)簡(jiǎn)單的例子。

創(chuàng)建一個(gè)較為復(fù)雜的單表：

2.4.2　臨時(shí)表

臨時(shí)表就是用來(lái)暫時(shí)保存臨時(shí)數(shù)據(jù)（或叫中間數(shù)據(jù)）的一個(gè)數(shù)據(jù)庫(kù)對(duì)象，和普通表有些類似，然而又有很大區(qū)別。它只能存儲(chǔ)在臨時(shí)表空間，而非用戶的表空間。Oracle臨時(shí)表是會(huì)話或事務(wù)級(jí)別的，只對(duì)當(dāng)前會(huì)話或事務(wù)可見(jiàn)。每個(gè)會(huì)話只能查看和修改自己的數(shù)據(jù)。

目前所有使用Oracle作為數(shù)據(jù)庫(kù)支撐平臺(tái)的應(yīng)用大部分都是數(shù)據(jù)量比較龐大的系統(tǒng)，即一般情況下都是在百萬(wàn)級(jí)以上的數(shù)據(jù)量。當(dāng)然在Oracle中創(chuàng)建分區(qū)是一種不錯(cuò)的選擇，但是當(dāng)發(fā)現(xiàn)應(yīng)用有多張表關(guān)聯(lián)并且這些表大部分都比較龐大，在關(guān)聯(lián)的時(shí)候會(huì)發(fā)現(xiàn)其中的某一張或者某幾張表關(guān)聯(lián)之后得到的結(jié)果集非常小并且查詢得到這個(gè)結(jié)果集的速度非常快，那么這個(gè)時(shí)候就應(yīng)考慮在Oracle中創(chuàng)建“臨時(shí)表”了。

對(duì)臨時(shí)表的概念也可以這樣理解，在Oracle中創(chuàng)建一張表，這個(gè)表不用于其他的什么功能，主要用于自己的軟件系統(tǒng)一些特有功能，用完之后表中的數(shù)據(jù)就沒(méi)用了。Oracle的臨時(shí)表創(chuàng)建之后基本不占用表空間，如果沒(méi)有指定臨時(shí)表（包括臨時(shí)表的索引）存放的表空間時(shí)，插入到臨時(shí)表的數(shù)據(jù)是存放在Oracle系統(tǒng)的臨時(shí)表空間（Temp）中的。

Oracle臨時(shí)表有兩種類型：會(huì)話級(jí)的臨時(shí)表和事務(wù)級(jí)的臨時(shí)表。

1. ON COMMIT DELETE ROWS

它是臨時(shí)表的默認(rèn)參數(shù)，表示臨時(shí)表中的數(shù)據(jù)僅在事務(wù)（transaction）過(guò)程中有效，當(dāng)事務(wù)提交（commit）后，臨時(shí)表的暫時(shí)段將被自動(dòng)截?cái)啵╰runcate），但是臨時(shí)表的結(jié)構(gòu)以及元數(shù)據(jù)還存儲(chǔ)在用戶的數(shù)據(jù)字典中。在臨時(shí)表完成它的使命后，最好將其刪除，否則數(shù)據(jù)庫(kù)會(huì)殘留很多臨時(shí)表的表結(jié)構(gòu)和元數(shù)據(jù)。

會(huì)話級(jí)的臨時(shí)表的數(shù)據(jù)和當(dāng)前會(huì)話有關(guān)系，當(dāng)前SESSION不退出的情況下，臨時(shí)表中的數(shù)據(jù)就還存在，臨時(shí)表的數(shù)據(jù)只有退出當(dāng)前SESSION時(shí)才被截?cái)啵╰runcate table）。

【示例2-2】會(huì)話級(jí)別的臨時(shí)表創(chuàng)建

create global temporary table tmp_test(id number,name varchar2(32)) on commit
preserve rows;

2. ON COMMIT PRESERVE ROWS

它表示臨時(shí)表的內(nèi)容可以跨事務(wù)而存在，不過(guò)，當(dāng)該會(huì)話結(jié)束時(shí)，臨時(shí)表的暫時(shí)段將隨著會(huì)話的結(jié)束而被丟棄，臨時(shí)表中的數(shù)據(jù)自然也就隨之丟棄，但是臨時(shí)表的結(jié)構(gòu)以及元數(shù)據(jù)還存儲(chǔ)在用戶的數(shù)據(jù)字典中。在臨時(shí)表完成它的使命后，最好將其刪除，否則數(shù)據(jù)庫(kù)會(huì)殘留很多臨時(shí)表的表結(jié)構(gòu)和元數(shù)據(jù)。

事務(wù)級(jí)的臨時(shí)表（默認(rèn)）與事務(wù)有關(guān)，當(dāng)進(jìn)行事務(wù)提交或者事務(wù)回滾時(shí)，臨時(shí)表的數(shù)據(jù)將自行截?cái)啵串?dāng)commit或rollback時(shí)，數(shù)據(jù)就會(huì)被截?cái)啵渌奶匦院蜁?huì)話級(jí)的臨時(shí)表一致。

【示例2-3】事務(wù)級(jí)臨時(shí)表的創(chuàng)建方法

create global temporary table tmp_test(id number,name varchar2(32)) on commit
delete rows;

2.4.3　索引組織表

索引組織表（index organized table, IOT）就是存儲(chǔ)在一個(gè)索引結(jié)構(gòu)中的表。

創(chuàng)建索引，可以更有效地查找表中的特定行，然而創(chuàng)建索引將帶來(lái)額外的一些系統(tǒng)開(kāi)銷，因?yàn)閿?shù)據(jù)庫(kù)必須同時(shí)維護(hù)表的數(shù)據(jù)行和索引條目。如果表包含的列并不是很多，并且對(duì)表的訪問(wèn)主要集中在某一行上，那么應(yīng)該怎么做呢？

在這種情況下索引組織表可能就是正確的解決方案。索引組織表是以b樹(shù)索引的形式存儲(chǔ)表中的行，其中b樹(shù)索引的每個(gè)節(jié)點(diǎn)都包含作為鍵的列以及一個(gè)或多個(gè)非索引列。索引組織表最明顯的優(yōu)點(diǎn)在于只需要維護(hù)一個(gè)存儲(chǔ)結(jié)構(gòu)，而不是兩個(gè)。類似的，表中主鍵的值只在索引組織表中存儲(chǔ)一次，而在普通表中則需要存儲(chǔ)兩次。使用索引組織表也有一些缺點(diǎn)，有些表，例如記錄事件的表可能不需要主鍵，或者在某些情況下不需要任何鍵，而索引組織表則必須有主鍵，同時(shí)索引組織表不可以是集群的成員。最后，如果表中有大量的列并且在檢索表中的行時(shí)需要頻繁地訪問(wèn)許多列，那么索引組織表可能就不是最佳的解決方案。

那么到底IOT表有什么意義呢？

使用堆組織表時(shí)，用戶必須為表和表主鍵上的索引分別留出空間。IOT不存在主鍵的空間開(kāi)銷，因?yàn)樗饕褪菙?shù)據(jù)，數(shù)據(jù)就是索引，二者已經(jīng)合二為一。但是，IOT帶來(lái)的好處并不止于節(jié)約了磁盤空間的占用，更重要的是大幅度降低了I/O，減少了訪問(wèn)緩沖區(qū)緩存。盡管從緩沖區(qū)緩存獲取數(shù)據(jù)比從硬盤讀要快得多，但緩沖區(qū)緩存并不免費(fèi)，而且也絕對(duì)不是廉價(jià)的。每個(gè)緩沖區(qū)緩存獲取都需要緩沖區(qū)緩存的多個(gè)閂，而閂是串行化設(shè)備，會(huì)限制應(yīng)用的擴(kuò)展能力。

IOT適用的場(chǎng)合有：

?　完全由主鍵組成的表。這樣的表如果采用堆組織表，則表本身完全是多余的開(kāi)銷，因?yàn)樗械臄?shù)據(jù)全部同樣也保存在索引里，此時(shí)，堆表是沒(méi)用的。

?　代碼查找表，如果只會(huì)通過(guò)一個(gè)主鍵來(lái)訪問(wèn)一個(gè)表，這個(gè)表就非常適合實(shí)現(xiàn)為IOT。

?　如果想保證數(shù)據(jù)存儲(chǔ)在某個(gè)位置上，或者希望數(shù)據(jù)以某種特定的順序物理存儲(chǔ)，IOT就是一種合適的結(jié)構(gòu)。

IOT提供如下好處：

?　提高緩沖區(qū)緩存效率，因?yàn)榻o定查詢?cè)诰彺嬷行枰膲K更少。

?　減少緩沖區(qū)緩存訪問(wèn)，這會(huì)改善可擴(kuò)縮性。

?　獲取數(shù)據(jù)的工作總量更少，因?yàn)楂@取數(shù)據(jù)更快。

?　每個(gè)查詢完成的物理I/O更少，因?yàn)閷?duì)于任何給定的查詢，需要的塊更少，而且對(duì)地址記錄的一個(gè)物理I/O很可能可以獲取所有地址（而不只是其中一個(gè)地址，但堆表實(shí)現(xiàn)就只是獲取一個(gè)地址）。如果經(jīng)常在一個(gè)主鍵或唯一鍵上使用BETWEEN查詢也是如此，因?yàn)橄嘟挠涗洿嬖谝黄穑樵儠r(shí)引入的邏輯IO和物理IO都會(huì)更少。

2.4.4　集群表

如果經(jīng)常同時(shí)訪問(wèn)兩個(gè)或多個(gè)表，例如一個(gè)訂單表和一個(gè)項(xiàng)目表，那么創(chuàng)建集群表可能是一個(gè)較好的方法，它可以改進(jìn)應(yīng)用這些表的查詢性能。

在具有相關(guān)行是項(xiàng)目表和訂單表共同擁有時(shí)，訂單表和項(xiàng)目表的相關(guān)信息可以存儲(chǔ)在同一個(gè)數(shù)據(jù)塊中，從而減少檢索的IO開(kāi)銷，還可以減少存儲(chǔ)兩個(gè)表共有的列所需的存儲(chǔ)空間。

兩個(gè)表共有的列稱為集群鍵值。集群鍵值存儲(chǔ)在集群索引中，針對(duì)集群索引的操作，非常類似于傳統(tǒng)的索引。通過(guò)集群鍵值訪問(wèn)集群表時(shí)，可以改進(jìn)對(duì)集群表的查詢效率，共同的列只需存儲(chǔ)一次，而不必針對(duì)每個(gè)行重復(fù)存儲(chǔ)。相對(duì)于表執(zhí)行的查詢語(yǔ)句數(shù)量，如果需要頻繁地對(duì)表執(zhí)行插入更新和刪除操作，則集群表的優(yōu)點(diǎn)會(huì)減弱，此外經(jīng)常對(duì)集群中的單個(gè)表進(jìn)行查詢，集群表的特點(diǎn)也不會(huì)得到更好的體現(xiàn)。

2.4.5　分區(qū)表

對(duì)表進(jìn)行分區(qū)，或?qū)λ饕M(jìn)行分區(qū)，可幫助建立更加易于管理的大型表。可以將表分區(qū)為較小的部分，從應(yīng)用程序的觀點(diǎn)來(lái)看，分區(qū)是透明的。也就是說(shuō)，在終端用戶的SQL中不需要對(duì)任何特定分區(qū)進(jìn)行顯式的引用，用戶唯一能夠觀察到的是在WHERE子句后面使用符合分區(qū)方案的篩選條件。對(duì)分區(qū)表進(jìn)行查詢，用戶會(huì)發(fā)現(xiàn)SQL運(yùn)行得更為快速，從DBA的角度看，對(duì)表進(jìn)行分區(qū)有很多優(yōu)點(diǎn)，如果表的一個(gè)分區(qū)位于已損壞的磁盤卷上，用戶仍然可以查詢表的其他分區(qū)。

分區(qū)有三種類型：范圍分區(qū)、散列分區(qū)以及從Oracle 9i開(kāi)始引入的列表分區(qū)。從Oracle 11g開(kāi)始，也可以根據(jù)父與子的關(guān)系進(jìn)行分區(qū)，可以由應(yīng)用程序控制分區(qū)，并且可以對(duì)基本分區(qū)類型進(jìn)行很多組合，包括列表-散列、列表-列表、列表-范圍和范圍-范圍等組合分區(qū)類型，分區(qū)表中的每一行只能存在于一個(gè)分區(qū)中，分區(qū)鍵用于對(duì)行數(shù)據(jù)指定正確的分區(qū)，分區(qū)鍵可以是組合鍵，最多可組合表中的16個(gè)列。

一般來(lái)說(shuō)，推薦對(duì)于任何大于2GB的表，應(yīng)盡量考慮對(duì)其進(jìn)行分區(qū)并且表中包含歷史數(shù)據(jù)，新的數(shù)據(jù)被增加到新的分區(qū)中。

表分區(qū)的優(yōu)點(diǎn)：

?　改善查詢性能：對(duì)分區(qū)對(duì)象的查詢可以僅搜索自己關(guān)心的分區(qū)，提高檢索速度。

?　增強(qiáng)可用性：如果表的某個(gè)分區(qū)出現(xiàn)故障，表在其他分區(qū)的數(shù)據(jù)仍然可用。

?　維護(hù)方便：如果表的某個(gè)分區(qū)出現(xiàn)故障，需要修復(fù)數(shù)據(jù)，只修復(fù)該分區(qū)即可。

?　均衡I/O：可以把不同的分區(qū)映射到磁盤以平衡I/O，改善整個(gè)系統(tǒng)性能。

下面羅列表分區(qū)的幾種類型及操作方法。

1. 范圍分區(qū)

范圍分區(qū)將數(shù)據(jù)基于范圍映射到每一個(gè)分區(qū)，這個(gè)范圍是你在創(chuàng)建分區(qū)時(shí)指定的分區(qū)鍵決定的。這種分區(qū)方式是最為常用的，并且分區(qū)鍵經(jīng)常采用日期。例如，你可能會(huì)將銷售數(shù)據(jù)按照月份進(jìn)行分區(qū)。

當(dāng)使用范圍分區(qū)時(shí)，請(qǐng)考慮以下幾個(gè)規(guī)則：

?　每一個(gè)分區(qū)都必須有一個(gè)VALUES LESS THEN子句，它指定了一個(gè)不包括在該分區(qū)中的上限值。分區(qū)鍵的任何值等于或者大于這個(gè)上限值的記錄都會(huì)被加入下一個(gè)高一些的分區(qū)中。

?　除了第一個(gè)分區(qū)，其他分區(qū)都會(huì)有一個(gè)隱式的下限值，這個(gè)值就是此分區(qū)的前一個(gè)分區(qū)的上限值。

?　在最高的分區(qū)中，MAXVALUE被定義。MAXVALUE代表了一個(gè)不確定的值。這個(gè)值高于其他分區(qū)中任何分區(qū)鍵的值，也可以理解為高于任何分區(qū)中指定的VALUE LESS THEN的值，同時(shí)包括空值。

假設(shè)有一個(gè)CUSTOMER表，表中有數(shù)據(jù)200000行，將此表通過(guò)CUSTOMER_ID進(jìn)行分區(qū)，每個(gè)分區(qū)存儲(chǔ)100000行，將每個(gè)分區(qū)保存到單獨(dú)的表空間中，這樣數(shù)據(jù)文件就可以跨越多個(gè)物理磁盤了。

【示例2-4】創(chuàng)建表和分區(qū)

【示例2-5】按時(shí)間分區(qū)

2. 列表分區(qū)

列表分區(qū)的特點(diǎn)是某列的值只有幾個(gè)，基于這樣的特點(diǎn)可以采用列表分區(qū)。

【示例2-6】列表分區(qū)

3. 散列分區(qū)

這類分區(qū)是在列值上使用散列算法，以確定將行放入哪個(gè)分區(qū)中。當(dāng)列的值沒(méi)有合適的條件時(shí)，建議使用散列分區(qū)。散列分區(qū)為通過(guò)指定分區(qū)編號(hào)來(lái)均勻分布數(shù)據(jù)的一種分區(qū)類型，因?yàn)橥ㄟ^(guò)在I/O設(shè)備上進(jìn)行散列分區(qū)，使得這些分區(qū)大小一致。

【示例2-7】散列分區(qū)

散列分區(qū)最主要的機(jī)制是根據(jù)hash算法來(lái)計(jì)算具體某條記錄應(yīng)該插入哪個(gè)分區(qū)中，hash算法中最重要的是hash函數(shù)，Oracle中如果要使用hash分區(qū)，只需指定分區(qū)的數(shù)量即可。建議分區(qū)的數(shù)量采用2的n次方，這樣可以使各個(gè)分區(qū)間數(shù)據(jù)分布更加均勻。

4. 組合范圍散列分區(qū)

這種分區(qū)是基于范圍分區(qū)和列表分區(qū)的，表首先按某列進(jìn)行范圍分區(qū)，然后按某列進(jìn)行列表分區(qū)，分區(qū)之中的分區(qū)被稱為子分區(qū)。

【示例2-8】組合范圍散列分區(qū)

5. 復(fù)合范圍散列分區(qū)

這種分區(qū)是基于范圍分區(qū)和散列分區(qū)的，表首先按某列進(jìn)行范圍分區(qū)，然后按某列進(jìn)行散列分區(qū)。

【示例2-9】復(fù)合范圍散列分區(qū)