【IT168 服务器学院】我们的技术专家讨论所需空间的确定、随机数的生成和外部连接的分区等问题。
我希望得到在大型表（共2.6亿行）的惟一列上创建惟一性索引和非惟一性索引以及进行"创建"的次数等有关信息。你能帮助我吗？
惟一性索引与非惟一性索引的惟一区别就是是否是把ROWID看作是关键字（key）的一部分以保证惟一性。在传统的B*树索引中，只有一类索引：惟一性索引。在非惟一性索引中，Oracle数据库把ROWID看作是关键字的一部分。
因为一个表内的ROWID是惟一的，所以就能确保B*树中的所有关键字都是惟一的。因此非惟一性B*树索引中的关键字包含ROWID值。在惟一性索引中，Oracle数据库不把ROWID看作是关键字的一部分--相反，只把它看作是数据。在这种方式下，实际上数据库会确保索引列在表中是惟一的。对于这两种情况，Oracle数据库存储索引列和ROWID。只有在非惟一性索引中才将ROWID用作关键字的一部分。
你会发现从创建和执行索引的角度看，惟一性索引和非惟一性索引或多或少有一些相似之处。我装入一个表，其行的惟一序列号从1到258564672：
SQL> select count(*)
2 > from tall_but_skinny;
COUNT(*)
-----------
258564672
然后，启用SQL_TRACE，创建了惟一性索引和非惟一性索引。TKPROF报告显示如下：
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
结果大致相同。这件事是利用写日志（所以生成了大量日志）来串行（非并行）完成的，所以从中得到的好处是会变的，但最终却会得到相似的结构，而且还会发现CREATE的执行次数基本上相同。
估计所需的TEMP空间
我有一个包含42亿行、8列的表，我需要将其按5列来分组。该表的大小为380GB。如何估计所需TEMP空间的大小呢？它将接近于380 x 2=760GB吗？我需要使用并行查询（并行度大概为8或16）。
在Oracle9i第2版中，新提供了一个非常棒的DBMS_XPLAN包，它有大量信息。我将通过解释一条可完成大型排序的查询来说明如何利用它。在这个例子中，BIG_TABLE是ALL_OBJECTS的一个拷贝，大约有180万行。
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显示的计划中包括这个估计值。它指出"逐步排序分组需要大约225MB临时空间"。DBMS_XPLAN只显示了与你的查询相关的信息；我的意思是，如果运行explain plan命令但没有看到任何有关TempSpc信息的话，你不要感到吃惊。这只是意味着实际上你不需要任何TEMP，因此DBMS_XPLAN没有显示任何内容。让我们来看一个小型表的例子，它使用标准的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输出。它说明对于一个包含14行、deptno字段有3个不同取值的表来说，不需要任何TEMP空间。现在我们假定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.
现在，如果删除来自该计划表的查询结果并重新解释完全相同的查询，则DBMS_XPLAN显示的查询结果如清单 3所示。
这时你会看到，如果优化器认为表中有许多行并带有许多不同的值，那么就需要大约30MB临时磁盘空间来完成这件事。
生成一些随机数据
如何用一条SQL语句生成6个1~49之间的惟一随机数？
我从任何一个有49条或更多条记录的表中生成这个数字集（参见随后的最内层查询）；我还要写一个PIPELINED函数，而且也对它进行解释。首先快速回答一个问题：
select r
from ( select r
from ( select rownum r
from all_objects
where rownum < 50 )
order by dbms_random.value )
where rownum <= 6;
该查询通过内联视图生成数字1到49。我把最内层查询放到内联视图中，并利用DBMS_RANDOM.VALUE按一个随机数进行排序。再把结果集放到另一个内联视图中并只取前6行。如果我反复运行该查询，则每次都会得到不同的6行。
这类问题经常会遇到--也许不是有关如何生成6个随机数而是"如何得到N行"的问题。例如，我想得到包含2003年1月1日到2003年1月15日之间的所有日期。我可以用一个包含15行的表轻松地来生成这些日期；事实上，select to_date(''01-jan-2003'')+rownum-1 from all_objects where rownum <= 15就能完成，但这会带来一定数量的系统开销（ALL_OBJECTS是一个视图，而且是一个复杂的视图）。我本可以创建一个表并向其中填入一些行，但它也不总是让人喜欢。这个问题就变成了如何不用"真正的"表来完成这件事的问题，Oracle9i及其PIPELINED函数功能能够解决这个问题。我可以写一个PL/SQL函数，可以像表那样来操作它。先从一个SQL集合类型开始；它说明我的PIPELINED函数返回的是什么。这样，我选用一个数字表；所创建的虚表只简单的返回数字1、2、3、…… N：
SQL> create type array
2 as table of number
3 /
Type created.
接下来，创建真正的PIPELINED函数。该函数接收一个输入，用于限制返回行的数目。如果不提供输入，该函数将一直不停地生成行（所以一定要当心，确保在查询中使用ROWNUM或其他限制）。第4行的PIPELINED关键字使该函数能像表一样工作：
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.
假设我需要3行。我就可以用以下两种方法之一来完成这件事：
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
现在我准备用下面的函数重新回答最开始的问题：
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.
我可以用这个vtable函数做很多事，如生成那些日期的范围：
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.
请注意我所使用的列名：COLUMN_VALUE。这是PIPELINED函数返回的默认列名。
在Oracle数据库10g中有哪些最新的东西？
你能否指出Oracle数据库10g中你最喜欢的几个重要特性吗？
我今天选出Oracle数据库10g的3个特性，它们是：
自动存储管理（ASM）
总体上的可管理性，特别是自动数据库诊断监测器（ADDM）
普遍适用的SQL新特性，特别是分区的"稀疏"外部连接
由于用文字很难说明ASM和ADDM，所以我希望你到Oracle Technology Network上去了解有关内容，而分区的"稀疏"外部连接很容易说明。
数据通常是以稀疏的形式存储的。也就是说，如果某一给定时间不存在值，则表中就不存在行。但是，当数据在时间维度上很"密集"时，执行时间序列计算（例如，按年计算）就非常容易。这是因为在每个时期，密集数据都充满固定数量的行，这样通过实际偏移量来使用分析窗口函数就变得很简单。举一个SALES结果集的例子它具有PRODUCT_NAME、DATE_OF_SALE和TOT_SALES几个属性。你的目标是显示每行的TOT_SALES并与一年前同一行的TOT_SALES进行对比。如果每个产品的所有月份都有数据，那么你只需用LAG()分析函数向后查看12行即可。但是如果有缺失的月份会出现什么情况呢？假设没有上一年6月份的数据。这时你就会把今年6月份和去年5月份的数据--而不是空值--进行比较（因为实际上6月份的数据缺失）。在这种情况下分区外部连接就能派上用场了。
我们举一个小例子。我想做的就是比较这个月与上个月的销售额。所以，我需要按PRODUCT_NAME划分结果集，按DATE_OF_SALE对它进行排序，并使用LAG()向回查看一行的数据。从以下数据开始：
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.
请注意这里出现的缺失数据。11月份没有VCR的销售额数据，9月份没有TV的销售额数据。如果我现在试着用LAG()来分析：
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 /
得到的结果见清单 4 。
请注意清单 4 显示出将10月份的TV销售额与8月份的TV销售额相对比，而12月份的VCR销售额又与10月份VCR销售额的相对比。这不是我所希望的。我希望比较上个月的数据（实际为空值）。我想得到像这样的结果集：
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.
也就是说，我希望结果集中填上缺失的日期。使用下面的查询可以达到这一目的：
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的概念，因为我要做的就是构建一个包含所需要的所有日期的结果集--最近6个月的。子查询DATES返回6行：每行报告每月的日期。现在我简单地将DATES子查询与SALES数据做外部连接来合成缺失的行。但请注意在外部连接中PARTITION BY子句的使用。我把SALES表分成N个部分，每一部分与DATES的数据做外部连接。在这种方式下，SALES的每一部分都"补充"上了缺失数据以及相应的分区关键字。这就是上面的结果，其中补充上了9月份和11月份的数据，它就是用这个查询创建的。 下一步
在使用分区子句之前，我不得不生成了一个包含所有不同的PRODUCT_NAMES集，并取该集与DATES集的笛卡尔乘积。然后才能跟它做外部连接。一般来说，这不是我们想要的，因为这样就必须扫描所有SALES记录来生成这个惟一的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)
随着要分析的数据的维数的增加，需要生成的这个笛卡尔乘积的原始大小也会增大。在外部连接上使用分区子句使这种查询的规模更易于调整，而且，在这种情况下，还不用事先生成惟一的产品名集。
为执行所需要的分析而进行的整个查询会简单地成为该分区外部连接查询的一个逻辑扩展。 一旦有了上面的数据，我就可以用内联视图轻松完成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)
)
该查询比较本月和上个月的销售额，即使上个月的销售额"缺失"也可以。
关注我们
