oracle 10g:数据仓库和集成特性

第 4 部分：数据仓库和集成特性 (转自oracle:Oracle ACE Arup Nanda)

用于更高效管理物化视图、查询重写、可传输表空间以及表分区的新特性使数据仓库变得更加强大且占用资源更少。

这一部分涉及：
·
·
·
·
·
·
·

分区更改跟踪：不需要 MV 日志

要了解此增强功能，首先必须了解物化视图 (MV) 刷新过程中的分区修整概念。

假设基于列 ACC_MGR_ID 对表 ACCOUNTS 进行了分区，每个 ACC_MGR_ID 值一个分区。您根据 ACCOUNTS 创建了一个名为 ACC_VIEW 的 MV，该 MV 也根据列 ACC_MGR_ID 进行了分区，每个 ACC_MGR_ID 一个分区，如下图所示：

假设已经更新了表 ACCOUNTS 中的记录，但只在分区 P1 中进行了此更新。要快速刷新此 MV，您只需刷新分区 P1 而非整个表，这里正是与 ACC_MGR_ID 相关的数据所在的分区。Oracle 自动执行此任务，通过一个名为分区更改跟踪 (PCT) 的特性跟踪对分区的更改。但有一个问题需稍加注意：要在快速刷新的过程中启用 PCT，必须创建 MV 日志，当表中的行发生变化会填充这些日志。发出刷新命令后，刷新进程将读取 MV 日志以识别这些更改。

不用说，该要求增加了操作的总执行时间。此外，附加的插入操作将消耗 CPU 周期和 I/O 带宽。

幸好，在 Oracle 数据库 10g 第 2 版中，PCT 不需要 MV 日志即可工作。让我们看一看它的作用方式。首先，确认表 ACCOUNTS 中没有 MV 日志。

SQL> select *
2  from dba_mview_logs
3  where master = 'ACCOUNTS';

no rows selected

现在，更新该表中的某个记录。

update accounts set last_name = '...'
where acc_mgr_id = 3;

该记录位于分区 P3 中。

现在，您就可以刷新此 MV 了。但首先记录表 ACCOUNTS 所有段的段级统计信息。稍后，您将使用这些统计信息了解使用了哪些段。

select SUBOBJECT_NAME, value from v$segment_statistics
where owner = 'ARUP'
and OBJECT_NAME = 'ACCOUNTS'
and STATISTIC_NAME = 'logical reads'
order by SUBOBJECT_NAME
/

SUBOBJECT_NAME                      VALUE
------------------------------ ----------
P1                                   8320
P10                                  8624
P2                                  12112
P3                                  11856
P4                                   8800
P5                                   7904
P6                                   8256
P7                                   8016
P8                                   8272
P9                                   7840
PMAX                                  256

11 rows selected. 

使用快速刷新刷新物化视图 ACC_VIEW。

execute dbms_mview.refresh('ACC_VIEW','F')

'F' 参数指示快速刷新。但如果表没有 MV 日志，它是否可以起作用？

刷新完成后，再次检查表 ACCOUNTS 的段统计信息。结果如下所示：

SUBOBJECT_NAME                      VALUE
------------------------------ ----------
P1                                   8320
P10                                  8624
P2                                  12112
P3                                  14656
P4                                   8800
P5                                   7904
P6                                   8256
P7                                   8016
P8                                   8272
P9                                   7840
PMAX                                  256

这些段统计信息显示了在一个逻辑读取过程中选择的段。由于这些统计信息是累积的，因此您必须查看值（而非绝对值）中的更改。如果仔细查看以上值，您便会发现只有分区 P3 的值发生了变化。因此，在刷新过程中只选择了分区 P3 而非整个表，确认 PCT 能否在表即使没有 MV 日志的情况下工作。

即使在基表没有 MV 日志的情况下也可以快速刷新 MV 的能力是一个强大而有用的特性，从而允许您可以在已分区的 MV 中执行快速刷新而不会增加性能开销。我认为，该特性是 Oracle 数据库 10g 第 2 版中最有用的数据仓库增强功能。

---

使用多个 MV 进行查询重写

Oracle8i 中引入的查询重写特性在数据仓库开发人员和 DBA 中轰动一时。从本质上而言，它将用户查询重写为从 MV 而非表中进行选择以利用现成的摘要。例如，请考虑以下一家大型连锁酒店的数据库中的三个表。

SQL> DESC HOTELS
Name                              Null?Type
 ----------------------------------------- -------- -------------
HOTEL_ID                                  NOT NULL NUMBER(10)
CITY                                               VARCHAR2(20)
STATE                                              CHAR(2)
MANAGER_NAME                                       VARCHAR2(20)
RATE_CLASS                                         CHAR(2)

SQL> DESC RESERVATIONS
Name                              Null?Type
 ----------------------------------------- -------- -------------
RESV_ID                                   NOT NULL NUMBER(10)
HOTEL_ID                                           NUMBER(10)
CUST_NAME                                          VARCHAR2(20)
START_DATE                                         DATE
END_DATE                                           DATE
RATE                                               NUMBER(10)

SQL> DESC TRANS
Name                              Null?Type
 ----------------------------------------- -------- -------------
TRANS_ID                                  NOT NULL NUMBER(10)
RESV_ID                                   NOT NULL NUMBER(10)
TRANS_DATE                                         DATE
ACTUAL_RATE                                        NUMBER(10)

表 HOTELS 保存酒店的相关信息。当顾客预订酒店时，将在表 RESERVATIONS（包含房间价格报价）中创建一个记录。当顾客在酒店结帐时，将在另一个表 TRANS 中记录现金交易。

但在结帐前，酒店可能决定根据订房情况、升级、优惠等因素向顾客提供不同的房价。因此，最终的房价可能与预订时的报价不同，而且可以每天都各不相同。为正确记录这些价格变化，表 TRANS 有一行专门用来保存每天的房价信息。

为缩短查询响应时间，您可能决定根据用户发出的不同查询构建 MV，如：

create materialized view mv_hotel_resv
refresh complete
enable query rewrite
as
select city, resv_id, cust_name
from hotels h, reservations r
where r.hotel_id = h.hotel_id;

和

create materialized view mv_actual_sales
refresh complete
enable query rewrite
as
select resv_id, sum(actual_rate) from trans group by resv_id;

因此，如果设置了某些参数（如 query_rewrite_enabled = true），则类似如下所示的查询

select city, cust_name
from hotels h, reservations r
where r.hotel_id = h.hotel_id;

将重写为

select city, cust_name
from mv_hotel_resv;

。您可以通过运行该查询并启用自动跟踪来确认 MV。

SQL> set autot traceonly explain
SQL> select city, cust_name
2> from hotels h, reservations r
3> where r.hotel_id = h.hotel_id;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=80 Bytes=2480)
1    0   MAT_VIEW ACCESS (FULL) OF 'MV_HOTEL_RESV' (MAT_VIEW) (Cost=3 Card=80 Bytes=2480)

注意，查询是如何从物化视图 MV_HOTEL_RESV 而非表 HOTELS 和 RESERVATIONS 中进行选择的。这正是您所需要的。同样，当您编写一个查询来汇总每个预订编号的实际价格时，将使用物化视图 MV_ACTUAL_SALES 而非表 TRANS。

我们来采用一个不同的查询。如果要查明每个城市的实际销售额，则将发出

select city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and r.hotel_id = h.hotel_id
group by city;

注意此查询结构：从 MV_ACTUAL_SALES 中，您可以获得 RESV_ID 和预订的总销售额。从 MV_HOTEL_RESV 中，您可以获得 CITY 和 RESV_ID。

您能将这两个 MV 连接在一起吗？当然可以，但在 Oracle 数据库 10g 第 2 版之前，查询重写机制只使用两个 MV 中的一个（而非两个）自动重写用户查询。

以下是 Oracle9i 数据库中的执行计划输出。您可以看到，只使用了 MV_HOTEL_RESV 和 TRANS 的整表扫描。

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=6 Bytes=120)
1    0   SORT (GROUP BY) (Cost=8 Card=6 Bytes=120)
2    1     HASH JOIN (Cost=7 Card=516 Bytes=10320)
3    2       MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_HOTEL_RESV' (MAT_VIEW REWRITE) 
(Cost=3 Card=80 Bytes=1040)
4    2       TABLE ACCESS (FULL) OF 'TRANS' (TABLE) 
(Cost=3 Card=516 Bytes=3612)

即使 MV 可用，该方法也将生成一个非最优的执行计划。唯一的救济就是创建另一个将所有三个表连接在一起的 MV。但该方法将导致 MV 的增多，从而大大增加刷新 MV 所需的时间。

Oracle 数据库 10g 第 2 版解决了此问题。现在，以上查询将重写为使用两个 MV，如执行计划中所示。

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=6 Bytes=120)
1    0   SORT (GROUP BY) (Cost=8 Card=6 Bytes=120)
2    1     HASH JOIN (Cost=7 Card=80 Bytes=1600)
3    2       MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE) 
(Cost=3 Card=80 Bytes=560)
4    2       MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_HOTEL_RESV' (MAT_VIEW REWRITE) 
(Cost=3 Card=80 Bytes=1040)

注意，该执行计划是如何只使用了 MV 而未使用任何其他基表的。

该增强功能在数据仓库中具有显著的优点，这是因为您不必为每个可能的查询创建和刷新 MV。相反，你可以在关键地方创建几个没有太多连接和聚合的 MV，Oracle 将使用它们来重写查询。

---

通过备份实现可传输表空间

Oracle8i 中引入的可传输表空间为实现更快的跨数据库数据传输提供了迫切需要的支持。使用此特性，您可以只导出表空间的元数据、传输数据文件并将转储文件导出到目标数据库主机以及导入元数据以将表空间“插入”到目标数据库中。该表空间中的数据在目标数据库中随即可用。该方法解决了数据仓库中曾一度存在的一个很棘手的问题：快速、高效地跨数据库移动数据。

但在 OLTP 数据库中，该条件通常是不可能存在的，因此传输表空间也是不可能的。如果 OLTP 数据库是数据仓库的数据源，则您可能始终无法使用可传输表空间加载它。

在 Oracle 数据库 10g 第 2 版中，可以传输表空间并从另一个数据源（即备份）中插入它。例如，如果要传输表空间 ACCDATA，则可以发出 RMAN 命令

RMAN> transport tablespace accdata 
2> TABLESPACE DESTINATION = '/home/oracle'
3> auxiliary destination = '/home/oracle';

该命令在位置 /home/oracle 中创建一个辅助实例，并从其中的备份恢复文件。此辅助实例的名称是随机生成的。创建实例后，该过程将基于目录创建一个目录对象，并恢复表空间 ACCDATA（我们正在传输的表空间）的文件 - 所有操作均自动完成，您不必发出任何命令！

目录 /home/oracle 将包含表空间 ACCDATA 的所有数据文件、表空间元数据的转储文件以及脚本 impscrpt.sql（最重要的）。该脚本包含将此表空间插入目标表空间所必需的所有命令。该表空间并非由 impdp 命令进行传输，而是通过对 dbms_streams_tablespace_adm.attach_tablespaces 程序包的调用进行传输。可以在该脚本中找到所有必要的命令。

您可能会问，如果出现错误该怎么办？这种情况下，可以轻松地进行诊断。首先，该辅助实例在 $ORACLE_HOME/rdbms/log 中创建警报日志文件，以便您可以检查该日志以查明潜在的问题。其次，在提供 RMAN 命令时，您可以通过发出 RMAN 命令（该命令将所有输出置于文件 tts.log 中）将命令和输出重定向到日志文件

rman target=/ log=tts.log

然后，您便可以检查该文件来查明故障的确切原因。

最后，将把这些文件恢复到 /home/oracle 的 TSPITR__ 目录中。例如，如果主数据库的 SID 为 ACCT，RMAN 创建的辅助实例的 SID 为 KYED，则目录名为 TSPITR_ACCT_KYED。该目录还包含两个其他子目录：datafile（用于数据文件）和 onlinelog（用于重做日志）。在完成新表空间的创建之前，可以查看该目录以了解恢复了哪些文件。（这些文件在该过程结束时会被删除。）

长期以来，DBA 一直期待着能够通过 RMAN 备份创建一个可传输的表空间。但请注意，您是从备份（而不是从联机表空间）中插入传输的表空间。因此，它将不是最新的。

---

对已分区的按索引组织的表实现快速的分区分割

考虑这样一种情况：假设您拥有一个已分区的表。月末到了，但您忘了为下一个月定义分区。您现在有哪些选择呢？

您唯一的救济方法就是将最大值分区分割为两个部分：一个用于新月份的分区和一个新的最大值分区。但将该方法用于已分区的按索引组织的表时将遇到一个小问题。这种情况下，将先创建物理分区，并将行从最大值分区移动到该分区，这样将消耗 I/O 和 CPU 周期。

在 Oracle 数据库 10g 第 2 版中，该过程得到显著简化。如下图所示，假设您将分区一直定义到 5 月份，然后已经将 PMAX 分区定义为一个通用分区。由于 6 月份没有特定分区，因此 6 月份数据进入 PMAX 分区。灰显的方框显示了填充到该段中的数据。由于只填充了部分 PMAX 分区，因此您只看到一部分灰色区域。

现在，在 6 月 30 日对分区 PMAX 进行分割，以创建 6 月分区和新的 PMAX 分区。由于当前 PMAX 中的所有数据都将进入新的 6 月分区，因此 Oracle 数据库 10g 第 2 版只创建新的最大值分区，并使现有分区成为新创建的月分区。这就导致了根本不会发生数据移动（因此没有“空”的 I/O 和 CPU 周期）。而最好之处在于，ROWID 不会发生变化。

---

通过联机重新定义将 LONG 转换为 LOB

如果数据仓库数据库已经存在一段时间，并且您要处理大型文本数据，则您可能拥有大量数据类型为 LONG 的列。毋庸质疑，LONG 数据类型在大多数数据操作环境（如通过 SUBSTR 进行搜索）中是没有用处的。您肯定需要将它们转换为 LOB 列。

可以使用 DBMS_REDEFINITION 程序包联机执行该操作。但在 Oracle 数据库 10g 第 2 之前，有一个很大的限制。

将 LONG 列转换为 LOB 列时，您很希望获得高性能；您需要使该过程尽可能地快。如果将表进行了分区，则该过程将跨分区并行执行。但如果未将表进行分区，则该过程将串行执行，从而可能持续很长时间。

幸好，在 Oracle 数据库 10g 第 2 版中，即使表未分区也可以在 DBMS_REDEFINITION 程序包内部执行从 LONG 到 LOB 的联机转换。我们通过一个示例来了解该转换的过程。以下是一个用于保存发送给客户的电子邮件的表。由于邮件正文（存储在 MESG_TEXT 中）通常是较长的文本数据，因此已将该列定义为 LONG。

SQL> desc acc_mesg
Name                              Null?Type
 ----------------------------------------- -------- ---------

ACC_NO                                    NOT NULL NUMBER
MESG_DT                                   NOT NULL DATE
MESG_TEXT                                          LONG

您需要将该列转换为 CLOB。首先，创建一个结构相同的（最后一列除外，它被定义为 CLOB）空临时表。

create table ACC_MESG_INT
(
acc_no number,
mesg_dt  date,
mesg_text clob
);

现在，启动重新定义过程。

1  begin
2     dbms_redefinition.start_redef_table (
3        UNAME        => 'ARUP',
4        ORIG_TABLE   => 'ACC_MESG',
5        INT_TABLE    => 'ACC_MESG_INT',
6        COL_MAPPING  => 'acc_no acc_no, mesg_dt mesg_dt, to_lob(MESG_TEXT) MESG_TEXT'
  7  );
8* end;

注意第 6 行，该行已经对列进行了映射。前两列保持不变，但第三列 MESG_TEXT 已被映射，以便通过对源表的列应用函数 TO_LOB 来填充目标表的 MESG_TEXT 列。

如果要重新定义的表很大，则需要定期对源表和目标表之间的数据进行同步。该方法加快了最终同步的速度。

begin
dbms_redefinition.sync_interim_table( 
uname      => 'ARUP',  
orig_table => 'ACC_MESG', 
int_table  => 'ACC_MESG_INT'
    );
end;
/

根据表的大小，您可能需要多次执行以上命令。最后，使用以下代码完成重新定义过程

begin
dbms_redefinition.finish_redef_table (
UNAME        => 'ARUP',
ORIG_TABLE   => 'ACC_MESG',
INT_TABLE    => 'ACC_MESG_INT'
);
end;
/

表 ACC_MESG 已经发生了变化：

SQL> desc acc_mesg
Name                              Null?Type
 ----------------------------------------- -------- ---------

ACC_NO                                    NOT NULL NUMBER
MESG_DT                                   NOT NULL DATE
MESG_TEXT

注意，MESG_TEXT 列现在为 CLOB 而非 LONG。

该特性对于将错误定义的数据结构或原先遗留的数据结构转换为更容易管理的数据类型非常有用。

---

联机重组单个分区

假设您有一个包含事务历史的表 TRANS。该表基于 TRANS_DATE 进行分区，每个季度作为一个分区。在正常的业务过程中，最新的分区经常更新。某个季度过后，该分区上可能没有很多活动了，因此可以将它移动到其他位置。但移动本身将需要对表进行锁定，从而拒绝对分区的公共访问。如何在不影响其可用性的情况下移动分区？

在 Oracle 数据库 10g 第 2 版中，可以对单个分区使用联机重新定义。您可以像对整个表执行重新定义（使用 DBMS_REDEFINITION 程序包）一样执行此任务，但底层机制并不相同。常规表是通过对源表创建物化视图重新定义的，而单个分区是通过交换分区方法重新定义的。

我们来看一下它的工作原理。以下是 TRANS 表的结构：

SQL> desc trans
Name                              Null?Type
 --------------------------------- -------- -------------------------
TRANS_ID                                   NUMBER
TRANS_DATE                                 DATE
TXN_TYPE                                   VARCHAR2(1)
ACC_NO                                     NUMBER
TX_AMT                                     NUMBER(12,2)
STATUS     

该表已经按如下所示进行了分区：

partition by range (trans_date)
(
partition y03q1 values less than (to_date('04/01/2003','mm/dd/yyyy')),
partition y03q2 values less than (to_date('07/01/2003','mm/dd/yyyy')),
partition y03q3 values less than (to_date('10/01/2003','mm/dd/yyyy')),
partition y03q4 values less than (to_date('01/01/2004','mm/dd/yyyy')),
partition y04q1 values less than (to_date('04/01/2004','mm/dd/yyyy')),
partition y04q2 values less than (to_date('07/01/2004','mm/dd/yyyy')),
partition y04q3 values less than (to_date('10/01/2004','mm/dd/yyyy')),
partition y04q4 values less than (to_date('01/01/2005','mm/dd/yyyy')),
partition y05q1 values less than (to_date('04/01/2005','mm/dd/yyyy')),
partition y05q2 values less than (to_date('07/01/2005','mm/dd/yyyy'))
)

在某个时刻，您决定将分区 Y03Q2 移动到另一个表空间 (TRANSY03Q2)，该表空间可能位于一个不同类型的磁盘（一个慢一点、便宜一点的磁盘）上。为此，请首先确认您可以联机重新定义该表：

begin
dbms_redefinition.can_redef_table( 
uname        => 'ARUP',  
tname        => 'TRANS', 
options_flag => dbms_redefinition.cons_use_rowid, 
part_name    => 'Y03Q2'); 
end;
/

此处没有输出，因此您确认可以联机重新定义该表。接下来，创建一个临时表保存该分区的数据：

create table trans_temp
(
trans_id        number,
trans_date date,
txn_type varchar2(1),
acc_no number,
tx_amt number(12,2),
status varchar2(1)
)
tablespace transy03q2
/

请注意，由于表 TRANS 进行了范围分区，因此您已经将该表定义为未分区表。该表在所需的表空间 TRANSY03Q2 中创建。如果表 TRANS 包含一些本地索引，则表示您已经对表 TRANS_TEMP 创建了这些索引（当然是创建为未分区索引）。

现在，您就可以启动重新定义过程：

begin
dbms_redefinition.start_redef_table( 
uname        => 'ARUP',  
orig_table   => 'TRANS', 
int_table    => 'TRANS_TEMP', 
col_mapping  => NULL, 
options_flag => dbms_redefinition.cons_use_rowid, 
part_name    => 'Y03Q2'); 
end;
/

该调用有几个注意事项。第一，将参数 col_mapping 设置为 NULL；在单个分区重新定义中，该参数没有意义。第二，一个新参数 part_name 指定了要重新定义的分区。第三，注意其中没有 COPY_TABLE_DEPENDENTS 参数，该参数也没有意义，原因是表本身无法更改；只移动分区。

如果该表很大，此操作可能持续很长时间；因此请在操作过程中对它进行同步。

begin
dbms_redefinition.sync_interim_table( 
uname      => 'ARUP',  
orig_table => 'TRANS', 
int_table  => 'TRANS_TEMP', 
part_name  => 'Y03Q2'); 
end;
/

最后，使用以下代码完成该过程

begin
dbms_redefinition.finish_redef_table( 
uname      => 'ARUP',  
orig_table => 'TRANS', 
int_table  => 'TRANS_TEMP', 
part_name  => 'Y03Q2'); 
end;

此时，分区 Y03Q2 位于表空间 TRANSY03Q2 中。如果该表存在任何全局索引，则它们将被标记为 UNUSABLE 并且必须被重新构建。

单个分区重新定义对于跨表空间移动分区（一个常见的信息生命周期管理任务）很有用。但显而易见，其中存在几个限制。例如，您无法在重新定义过程中更改分区方法（即从范围更改为散列）或更改表的结构。

---

逐块地删除表

您注意到过删除一个分区的表需要多长时间吗？这是因为每个分区都是一个必须删除的段。在 Oracle 数据库 10g 第 2 版中，当您删除分区的表时，分区将逐个被删除。由于每个分区是单独删除的，因此所需的资源要比删除整个表少。

要演示这个新行为，您可以使用 10046 跟踪跟踪该会话。

alter session set events '10046 trace name context forever, level 12';

然后，删除该表。如果查看跟踪文件，则将看到分区表删除的代码：

delete from tabpart$ where bo# = :1
delete from partobj$ where obj#=:1
delete from partcol$ where obj#=:1
delete from subpartcol$ where obj#=:1

请注意，分区是按顺序删除的。该方法最大限度地降低了删除过程中的资源使用率并增强了性能。