我們的技術(shù)專家討論所需空間的確定、隨機(jī)數(shù)的生成和外部連接的分區(qū)等問(wèn)題。
我希望得到在大型表（共2.6億行）的惟一列上創(chuàng)建惟一性索引和非惟一性索引以及進(jìn)行"創(chuàng)建"的次數(shù)等有關(guān)信息。你能幫助我嗎？
惟一性索引與非惟一性索引的惟一區(qū)別就是是否是把ROWID看作是要害字（key）的一部分以保證惟一性。在傳統(tǒng)的B*樹(shù)索引中，只有一類索引：惟一性索引。在非惟一性索引中，Oracle數(shù)據(jù)庫(kù)把ROWID看作是要害字的一部分。
因?yàn)橐粋€(gè)表內(nèi)的ROWID是惟一的，所以就能確保B*樹(shù)中的所有要害字都是惟一的。因此非惟一性B*樹(shù)索引中的要害字包含ROWID值。在惟一性索引中，Oracle數(shù)據(jù)庫(kù)不把ROWID看作是要害字的一部分--相反，只把它看作是數(shù)據(jù)。在這種方式下，實(shí)際上數(shù)據(jù)庫(kù)會(huì)確保索引列在表中是惟一的。對(duì)于這兩種情況，Oracle數(shù)據(jù)庫(kù)存儲(chǔ)索引列和ROWID。只有在非惟一性索引中才將ROWID用作要害字的一部分。
你會(huì)發(fā)現(xiàn)從創(chuàng)建和執(zhí)行索引的角度看，惟一性索引和非惟一性索引或多或少有一些相似之處。我裝入一個(gè)表，其行的惟一序列號(hào)從1到258564672：
SQL> select count(*)
2 > from tall_but_SKINny;
COUNT(*)
-----------
258564672
然后，啟用SQL_TRACE，創(chuàng)建了惟一性索引和非惟一性索引。TKPROF報(bào)告顯示如下：
create index tall_but_skinny_2
on tall_but_skinny(r);
call cpu elapsed disk
------- ------- --------- --------
Parse 0.00 0.16 0
Execute 2000.30 3375.34 1175887
Fetch 0.00 0.00 0
------- ------- --------- --------
total 2000.30 3375.51 1175887
create UNIQUE index tall_but_skinny_3 on tall_but_skinny(r);
call cpu elapsed disk
------- ------- --------- --------
Parse 0.00 0.24 0
Execute 1969.09 2857.14 1175908
Fetch 0.00 0.00 0
------- ------- --------- --------
total 1969.10 2857.39 1175908
結(jié)果大致相同。這件事是利用寫日志（所以生成了大量日志）來(lái)串行（非并行）完成的，所以從中得到的好處是會(huì)變的，但最終卻會(huì)得到相似的結(jié)構(gòu)，而且還會(huì)發(fā)現(xiàn)CREATE的執(zhí)行次數(shù)基本上相同。
估計(jì)所需的TEMP空間
我有一個(gè)包含42億行、8列的表，我需要將其按5列來(lái)分組。該表的大小為380GB。如何估計(jì)所需TEMP空間的大小呢？它將接近于380 x 2=760GB嗎？我需要使用并行查詢（并行度大概為8或16）。
在Oracle9i第2版中，新提供了一個(gè)非常棒的DBMS_XPLAN包，它有大量信息。我將通過(guò)解釋一條可完成大型排序的查詢來(lái)說(shuō)明如何利用它。在這個(gè)例子中，BIG_TABLE是ALL_OBJECTS的一個(gè)拷貝，大約有180萬(wàn)行。
SQL> delete from plan_table;
4 rows deleted.
SQL> explain plan for
2 select object_name, owner,
3 object_id, created,
4 last_ddl_time, count(*)
5 from big_table
6 group by object_name, owner,
7 object_id, created,
8 last_ddl_time;
Explained.
SQL> select * from
2 table( dbms_xplan.display );
清單 1 顯示了這條查詢的DBMS_XPLAN輸出。正如你所看到的，DBMS_XPLAN顯示的計(jì)劃中包括這個(gè)估計(jì)值。它指出"逐步排序分組需要大約225MB臨時(shí)空間"。DBMS_XPLAN只顯示了與你的查詢相關(guān)的信息；我的意思是，假如運(yùn)行explain plan命令但沒(méi)有看到任何有關(guān)TempSpc信息的話，你不要感到吃驚。這只是意味著實(shí)際上你不需要任何TEMP，因此DBMS_XPLAN沒(méi)有顯示任何內(nèi)容。
讓我們來(lái)看一個(gè)小型表的例子，它使用標(biāo)準(zhǔn)的EMP表：
SQL> analyze table emp
2 compute statistics;
Table analyzed.
SQL> delete from plan_table;
3 rows deleted.
SQL> explain plan for
2 select deptno, count(*)
3 from emp group by deptno;
Explained.
SQL> select * from
2 table( dbms_xplan.display );
清單 2 顯示了這條查詢的DBMS_XPLAN輸出。它說(shuō)明對(duì)于一個(gè)包含14行、deptno字段有3個(gè)不同取值的表來(lái)說(shuō)，不需要任何TEMP空間。現(xiàn)在我們假定EMP表包含很多行、有很多值：
SQL> begin
2 dbms_stats.set_table_stats
3 ( user, 'EMP', numrows => 2000000,
4 numblks => 1000000 );
5 dbms_stats.set_column_stats
6 ( user, 'EMP', 'DEPTNO',
7 distcnt => 2000000 );
8 end;
9 /
PL/SQL procedure sUCcessfully completed.
現(xiàn)在，假如刪除來(lái)自該計(jì)劃表的查詢結(jié)果并重新解釋完全相同的查詢，則DBMS_XPLAN顯示的查詢結(jié)果如清單 3所示。
這時(shí)你會(huì)看到，假如優(yōu)化器認(rèn)為表中有許多行并帶有許多不同的值，那么就需要大約30MB臨時(shí)磁盤空間來(lái)完成這件事。
生成一些隨機(jī)數(shù)據(jù)
如何用一條SQL語(yǔ)句生成6個(gè)1~49之間的惟一隨機(jī)數(shù)？
我從任何一個(gè)有49條或更多條記錄的表中生成這個(gè)數(shù)字集（參見(jiàn)隨后的最內(nèi)層查詢）；我還要寫一個(gè)PipELINED函數(shù)，而且也對(duì)它進(jìn)行解釋。首先快速回答一個(gè)問(wèn)題：
select r
from ( select r
from ( select rownum r
from all_objects
where rownum < 50 )
order by dbms_random.value )
where rownum <= 6;
該查詢通過(guò)內(nèi)聯(lián)視圖生成數(shù)字1到49。我把最內(nèi)層查詢放到內(nèi)聯(lián)視圖中，并利用DBMS_RANDOM.VALUE按一個(gè)隨機(jī)數(shù)進(jìn)行排序。再把結(jié)果集放到另一個(gè)內(nèi)聯(lián)視圖中并只取前6行。假如我反復(fù)運(yùn)行該查詢，則每次都會(huì)得到不同的6行。
這類問(wèn)題經(jīng)常會(huì)碰到--也許不是有關(guān)如何生成6個(gè)隨機(jī)數(shù)而是"如何得到N行"的問(wèn)題。例如，我想得到包含2003年1月1日到2003年1月15日之間的所有日期。我可以用一個(gè)包含15行的表輕松地來(lái)生成這些日期；事實(shí)上，select to_date('01-jan-2003')+rownum-1 from all_objects where rownum <= 15就能完成，但這會(huì)帶來(lái)一定數(shù)量的系統(tǒng)開(kāi)銷（ALL_OBJECTS是一個(gè)視圖，而且是一個(gè)復(fù)雜的視圖）。我本可以創(chuàng)建一個(gè)表并向其中填入一些行，但它也不總是讓人喜歡。這個(gè)問(wèn)題就變成了如何不用"真正的"表來(lái)完成這件事的問(wèn)題，Oracle9i及其PIPELINED函數(shù)功能能夠解決這個(gè)問(wèn)題。我可以寫一個(gè)PL/SQL函數(shù)，可以像表那樣來(lái)操作它。先從一個(gè)SQL集合類型開(kāi)始；它說(shuō)明我的PIPELINED函數(shù)返回的是什么。這樣，我選用一個(gè)數(shù)字表；所創(chuàng)建的虛表只簡(jiǎn)單的返回?cái)?shù)字1、2、3、…… N：
SQL> create type array
2 as table of number
3 /
Type created.
接下來(lái)，創(chuàng)建真正的PIPELINED函數(shù)。該函數(shù)接收一個(gè)輸入，用于限制返回行的數(shù)目。假如不提供輸入，該函數(shù)將一直不停地生成行（所以一定要當(dāng)心，確保在查詢中使用ROWNUM或其他限制）。第4行的PIPELINED要害字使該函數(shù)能像表一樣工作：
SQL> create function
2 vtable( n in number default null )
3 return array
4 PIPELINED
5 as
6 begin
7 for i in 1 .. nvl(n,999999999)
8 loop
9 pipe row(i);
10 end loop;
11 return;
12 end;
13 /
Function created.
假設(shè)我需要3行。我就可以用以下兩種方法之一來(lái)完成這件事：
SQL> select *
2 from TABLE(vtable(3))
3 /
COLUMN_VALUE
------------
1
2
3
或者
SQL> select *
2 from TABLE(vtable)
3 where rownum <= 3
4 /
COLUMN_VALUE
------------
1
2
3
現(xiàn)在我預(yù)備用下面的函數(shù)重新回答最開(kāi)始的問(wèn)題：
SQL> select *
2 from (
3 select *
4 from (
5 select *
6 from table(vtable(49))
7 )
8 order by dbms_random.random
9 )
10 where rownum <= 6
11 /
COLUMN_VALUE
------------
47
42
40
15
48
23
6 rows selected.
我可以用這個(gè)vtable函數(shù)做很多事，如生成那些日期的范圍：
SQL> select to_date('01-jan-2003')+
2 column_value-1
3 from TABLE(vtable(15));
TO_DATE('
---------
01-JAN-03
...
15-JAN-03
15 rows selected.
請(qǐng)注重我所使用的列名：COLUMN_VALUE。
這是PIPELINED函數(shù)返回的默認(rèn)列名。
在Oracle數(shù)據(jù)庫(kù)10g中有哪些最新的東西？
你能否指出Oracle數(shù)據(jù)庫(kù)10g中你最喜歡的幾個(gè)重要特性嗎？
我今天選出Oracle數(shù)據(jù)庫(kù)10g的3個(gè)特性，它們是：
自動(dòng)存儲(chǔ)治理（ASM）
總體上的可治理性，非凡是自動(dòng)數(shù)據(jù)庫(kù)診斷監(jiān)測(cè)器（ADDM）
普遍適用的SQL新特性，非凡是分區(qū)的"稀疏"外部連接
由于用文字很難說(shuō)明ASM和ADDM，所以我希望你到Oracle Technology Network上去了解有關(guān)內(nèi)容，而分區(qū)的"稀疏"外部連接很輕易說(shuō)明。
數(shù)據(jù)通常是以稀疏的形式存儲(chǔ)的。也就是說(shuō)，假如某一給定時(shí)間不存在值，則表中就不存在行。但是，當(dāng)數(shù)據(jù)在時(shí)間維度上很"密集"時(shí)，執(zhí)行時(shí)間序列計(jì)算（例如，按年計(jì)算）就非常輕易。這是因?yàn)樵诿總€(gè)時(shí)期，密集數(shù)據(jù)都布滿固定數(shù)量的行，這樣通過(guò)實(shí)際偏移量來(lái)使用分析窗口函數(shù)就變得很簡(jiǎn)單。舉一個(gè)SALES結(jié)果集的例子它具有PRODUCT_NAME、DATE_OF_SALE和TOT_SALES幾個(gè)屬性。你的目標(biāo)是顯示每行的TOT_SALES并與一年前同一行的TOT_SALES進(jìn)行對(duì)比。假如每個(gè)產(chǎn)品的所有月份都有數(shù)據(jù)，那么你只需用LAG()分析函數(shù)向后查看12行即可。但是假如有缺失的月份會(huì)出現(xiàn)什么情況呢？假設(shè)沒(méi)有上一年6月份的數(shù)據(jù)。這時(shí)你就會(huì)把今年6月份和去年5月份的數(shù)據(jù)--而不是空值--進(jìn)行比較（因?yàn)閷?shí)際上6月份的數(shù)據(jù)缺失）。在這種情況下分區(qū)外部連接就能派上用場(chǎng)了。
我們舉一個(gè)小例子。我想做的就是比較這個(gè)月與上個(gè)月的銷售額。所以，我需要按PRODUCT_NAME劃分結(jié)果集，按DATE_OF_SALE對(duì)它進(jìn)行排序，并使用LAG()向回查看一行的數(shù)據(jù)。從以下數(shù)據(jù)開(kāi)始：
ops$tkyte@ORA10g> select *
2 from sales
3 order by product_name,
4 date_of_sale;
PRODUCT_NAME DATE_OF_SALE TOT_SALES
------------ ------------ ---------
TV 01-JUL-03 496
TV 01-AUG-03 993
TV 01-OCT-03 468
TV 01-NOV-03 535
TV 01-DEC-03 665
VCR 01-JUL-03 617
VCR 01-AUG-03 984
VCR 01-SEP-03 308
VCR 01-OCT-03 954
VCR 01-DEC-03 947
10 rows selected.
請(qǐng)注重這里出現(xiàn)的缺失數(shù)據(jù)。11月份沒(méi)有VCR的銷售額數(shù)據(jù)，9月份沒(méi)有TV的銷售額數(shù)據(jù)。假如我現(xiàn)在試著用LAG()來(lái)分析：
ops$tkyte@ORA10g> select product_name,
2 date_of_sale,
3 lag(date_of_sale)
4 over (partition by product_name
5 order by date_of_sale)
6 last_month,
7 tot_sales,
8 lag(tot_sales)
9 over (partition by product_name
10 order by date_of_sale)
11 last_months_sales
12 from sales
13 /
得到的結(jié)果見(jiàn)清單 4 。
請(qǐng)注重清單 4 顯示出將10月份的TV銷售額與8月份的TV銷售額相對(duì)比，而12月份的VCR銷售額又與10月份VCR銷售額的相對(duì)比。這不是我所希望的。我希望比較上個(gè)月的數(shù)據(jù)（實(shí)際為空值）。我想得到像這樣的結(jié)果集：
PRODUCT_NAME DATE_OF_SALE TOT_SALES
------------ ------------ ---------
TV 01-JUL-03 496
TV 01-AUG-03 993
TV 01-SEP-03
TV 01-OCT-03 468
TV 01-NOV-03 535
TV 01-DEC-03 665
VCR 01-JUL-03 617
VCR 01-AUG-03 984
VCR 01-SEP-03 308
VCR 01-OCT-03 954
VCR 01-NOV-03
VCR 01-DEC-03 947
12 rows selected.
也就是說(shuō)，我希望結(jié)果集中填上缺失的日期。使用下面的查詢可以達(dá)到這一目的：
with dates as
( select add_months
( to_date( '01-jul-2003' ),
column_value-1) dt
from table( vtable(6) )
)
select product_name,
dt,
tot_sales
from dates left outer join
sales partition by (product_name)
on ( dates.dt =
sales.date_of_sale )
我借用了前面講的VTABLE的概念，因?yàn)槲乙龅木褪菢?gòu)建一個(gè)包含所需要的所有日期的結(jié)果集--最近6個(gè)月的。
子查詢DATES返回6行：每行報(bào)告每月的日期。現(xiàn)在我簡(jiǎn)單地將DATES子查詢與SALES數(shù)據(jù)做外部連接來(lái)合成缺失的行。但請(qǐng)注重在外部連接中PARTITION BY子句的使用。我把SALES表分成N個(gè)部分，每一部分與DATES的數(shù)據(jù)做外部連接。在這種方式下，SALES的每一部分都"補(bǔ)充"上了缺失數(shù)據(jù)以及相應(yīng)的分區(qū)要害字。這就是上面的結(jié)果，其中補(bǔ)充上了9月份和11月份的數(shù)據(jù)，它就是用這個(gè)查詢創(chuàng)建的。 下一步
在使用分區(qū)子句之前，我不得不生成了一個(gè)包含所有不同的PRODUCT_NAMES集，并取該集與DATES集的笛卡爾乘積。然后才能跟它做外部連接。一般來(lái)說(shuō)，這不是我們想要的，因?yàn)檫@樣就必須掃描所有SALES記錄來(lái)生成這個(gè)惟一的PRODUCT_NAMES集。這種查詢就像這樣：
with dates as
( select add_months
( to_date( '01-jul-2003' ),
column_value-1) dt
from table( vtable(6) )
),
products as
( select distinct product_name
from sales
),
dates_products as
( select * from dates, products
)
select sales.product_name,
dates_products.dt,
sales.tot_sales
from dates_products left outer join
sales
on ( dates_products.dt =
sales.date_of_sale
and
dates_products.product_name =
sales.product_name)
隨著要分析的數(shù)據(jù)的維數(shù)的增加，需要生成的這個(gè)笛卡爾乘積的原始大小也會(huì)增大。在外部連接上使用分區(qū)子句使這種查詢的規(guī)模更易于調(diào)整，而且，在這種情況下，還不用事先生成惟一的產(chǎn)品名集。
為執(zhí)行所需要的分析而進(jìn)行的整個(gè)查詢會(huì)簡(jiǎn)單地成為該分區(qū)外部連接查詢的一個(gè)邏輯擴(kuò)展。 一旦有了上面的數(shù)據(jù)，我就可以用內(nèi)聯(lián)視圖輕松完成LAG()分析：
with dates as
( select add_months
( to_date( '01-jul-2003' ),
column_value-1) dt
from table( vtable(6) )
)
select product_name,
dt,
lag(dt)
over (partition by product_name
order by dt) last_dt,
tot_sales,
lag(tot_sales)
over (partition by product_name
order by dt) last_sales
from (
select product_name,
dt,
tot_sales
from dates left outer join
sales partition by (product_name)
on (dates.dt = sales.date_of_sale)
)
該查詢比較本月和上個(gè)月的銷售額，即使上個(gè)月的銷售額"缺失"也可以。
