利用分区执行高速数据加载和滚动窗口操作

目的

在本教程中，您将了解如何使用 Oracle 数据库进行高速数据加载以及如何利用 Oracle 分区进行滚动窗口操作。

所需时间

大约 2 个小时

主题

本教程包括下列主题：

概述
情景
前提条件
为销售历史模式实施模式更改
使用外部表加载数据

使用 SQL*Loader 与使用外部表进行加载和转换过程的比较
利用表压缩节省磁盘空间并降低 TCO
使用 Oracle 分区执行滚动窗口操作
总结

查看屏幕截图

将鼠标置于此图标上以加载和查看本教程的所有屏幕截图。（警告：因为此操作会同时加载所有屏幕截图，所以网速较慢时，响应时间可能会比较长。）

注：此外，您还可以在下列步骤中将鼠标放在每个单独的图标上，从而仅加载和查看与该步骤相关的屏幕截图。您可以单击单个屏幕截图将其隐藏。

概述

大多数情况下，向数据仓库提供数据的联机事务处理 (OLTP) 源系统都没有直接连接到提取新数据的数据仓库系统。通常，这些 OLTP 系统以外部文件的形式发送数据馈送。而这些数据必须加载到数据仓库中（最好是并行加载）才能充分利用现有资源。

例如，由于本教程中使用的示例公司 (MyCompany) 的业务需求和磁盘空间的限制，只有过去三年的数据与分析需求相关。这意味着在插入新数据时必须清除原数据或者利用 Oracle 数据库表压缩来释放磁盘空间。这种所谓的滚动窗口操作的维护是通过使用 Oracle 分区完成的。

返回主题列表

前提条件

开始本教程之前，您应该：

1.

安装 Oracle 数据库 11g。
2.

创建一个名为 wkdir 的目录。将 etl.zip 下载并解压缩到 wkdir 目录中。

返回主题列表

情景

外部表

为了将外部文件加载到它们的数据仓库中，MyCompany 使用了 Oracle 数据库外部表特性。该特性允许将外部数据（如平面文件）在数据库中显示为一个普通的数据库表。由于可以使用 SQL 访问外部表，因此您可以使用 SQL、PL/SQL 和 Java 的所有功能直接并行查询外部文件。通常，您会在提取、转换和加载 (ETL) 过程中使用外部表，将数据转换（通过 SQL）与数据加载组合到一个步骤中。对于 ETL 中很多可能的应用以及处理平面文件所在的其他数据库环境来说，外部表都是一项非常强大的功能。外部表是使用 SQL*Loader 的替代方法。

并行执行

并行执行可以显著减少大型数据库上的数据密集型操作的响应时间，这些操作通常与决策支持系统 (DSS) 和数据仓库一起协同使用。您还可以在某些类型的 OLTP 系统和混合系统上实施并行执行。简单地说，并行就是将任务分割，从而使多个进程同时执行任务，而不是在一个进程中由一个查询执行所有任务。例如，在四个进程同时处理一年中四个不同的季度，而不是由一个进程单独处理所有季度的情况下，您可以使用并行执行。

使用 Oracle 分区执行滚动窗口操作

数据仓库内部管理系统中一个非常重要的任务就是保持数据与 OLTP（源）系统中的各种更改同步。另外，从分析角度而言，数据的寿命通常很短，因此在加载新数据时必须将原数据从目标系统中清除；这种操作通常称为滚动窗口操作。理想状况下，您应该尽快地完成此操作，且不应涉及任何数据仓库系统的并发联机访问。

返回主题列表

为销售历史模式实施模式更改

在开始执行本 OBE 的任务之前，需要对现有的销售历史 (SH) 模式实施一些更改。您需要在 SH 模式下创建其他对象。此外，您还需要授予 SH 用户其他系统权限。用于应用这些更改的 SQL 文件是 modifySH_11g.sql。执行以下步骤：

1.

打开一个终端窗口。通过从终端会话执行以下命令，将工作目录更改为 /home/oracle/wkdir：

cd wkdir

(注：本教程假设您拥有 /home/oracle/wkdir 文件夹。如果没有，则需要创建此文件夹，并将 etl.zip 的内容解压缩到此文件夹中。)

2.

启动一个 SQL*Plus 会话，以 SH 用户身份和 SH 口令进行连接。

在您的 SQL*Plus 会话中按如下所示执行 modifySH_11g.sql 脚本： 

@modifySH_11g.sql

获得的输出的最后应与以下图像匹配。

返回主题列表

使用外部表加载数据

在此部分中，您将使用外部表将数据加载到数据仓库中。

要创建和使用外部表，执行以下步骤：

1.

创建必要的目录对象。
2. 创建外部表。
3. 从外部表中选择。
4. 为外部表提供透明的高速并行访问。
5. 查看 Oracle 的并行插入功能。
6.

执行并行插入。

返回主题列表

1. 创建必要的目录对象。

在创建外部表之前，您需要在数据库中创建一个目录对象，该对象将指向数据文件所在的文件系统上的目录。您还可以将日志、坏文件和废弃文件的位置与这些数据文件的位置分隔开。要创建该目录，执行以下步骤：

在以 SH 用户身份登录的 SQL*Plus 会话中，执行 create_directory.sql 脚本，或将以下 SQL 语句复制到 SQL*Plus 会话中：

DROP DIRECTORY data_dir;
DROP DIRECTORY log_dir;
CREATE DIRECTORY data_dir AS '/home/oracle/wkdir';
CREATE DIRECTORY log_dir AS '/home/oracle/wkdir';

将鼠标移到该图标上可以查看该图像

这些脚本是针对 Linux 系统创建的，并且假设文件已经解压缩到 o /home/oracle/wkdir 中。注意，由于安全方面的原因，该数据库不支持符号链接形式的 DIRECTORY 对象。

返回列表

2. 创建外部表

创建外部表时，需要定义以下内容：

1.

用于在数据库中表示表的元数据信息
2. 用于从外部文件提取数据的 HOW 访问参数定义

创建了这些元信息之后，无需初始加载就可以从数据库访问外部数据。

要创建外部表，执行以下步骤：

在以 SH 用户身份登录的 SQL*Plus 会话中，执行 create_external_table.sql 脚本，或将复制以下命令。

DROP TABLE sales_delta_XT;
CREATE TABLE sales_delta_XT
               (
   PROD_ID NUMBER,
   CUST_ID NUMBER,
   TIME_ID DATE,
   CHANNEL_ID CHAR(2),
   PROMO_ID NUMBER,
   QUANTITY_SOLD NUMBER(3),
   AMOUNT_SOLD NUMBER(10,2)
   )
   ORGANIZATION external
   (
   TYPE oracle_loader
   DEFAULT DIRECTORY data_dir
   ACCESS PARAMETERS
   (
   RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
   BADFILE log_dir:'sh_sales.bad'
   LOGFILE log_dir:'sh_sales.log_xt'
   FIELDS TERMINATED BY "|" LDRTRIM
   (prod_id, cust_id,
   time_id CHAR(11) DATE_FORMAT DATE MASK "DD-MON-YYYY",
   channel_id, promo_id, quantity_sold, amount_sold
   )
   )
   location
   (
   'salesDec01.dat'
   )
   )REJECT LIMIT UNLIMITED NOPARALLEL

将鼠标移到该图标上可以查看该图像

您可以通过下列数据字典视图查看外部表的信息：

        - [USER | ALL| DBA]_EXTERNAL_TABLES
        - [ALL| DBA]_DIRECTORIES
        - [USER | ALL| DBA]_EXTERNAL_LOCATIONS

返回列表

3. 从外部表中选择

无需任何其他操作，您现在就可以使用如下所示的 SQL 命令访问外部文件中的数据了：

在以 SH 用户身份登录的 SQL*Plus 会话中，执行以下查询或 select_et.sql 文件：

SELECT COUNT(*) FROM sales_delta_xt; 
SELECT MAX(time_id) FROM sales_delta_xt;

如果您正确复制了文件，则最大的 TIME_ID 应为 2001 年 12 月的最后一天。

将鼠标移到该图标上可以查看该图像

返回列表

4. 提供对外部表的告诉透明并行访问

与 SQL*Loader 不同，外部表的访问可以并行完成，且与外部文件的数量无关。SQL*Loader 只能基于单个文件运行。这表示，如果您希望进行并行化，则必须对大型源文件进行手动分割。使用外部表时，对并行程度的控制方式与一个普通表完全相同。在这里，您在默认情况下需要定义外部表 NOPARALLEL。下面一节将演示如何使用提示在语句级控制并行度。

1.

parallel_select_from_ET.sql 脚本包含了后面三个步骤要用到的 SQL 语句。

在以 SH 用户身份登录的 SQL*Plus 会话中，执行以下查询或 parallel_select_from_ET.sql 文件以查看当前并行会话的统计信息：


SELECT * 
FROM   v$pq_sesstat 
WHERE  statistic in ('Queries Parallelized', 
                     'Allocation Height');

将鼠标移到该图标上可以查看该图像
2.

执行先前用过的查询以并行度 4 访问该外部表，并使用一个提示控制。您可以使用以下的命令或 parallel_select_from_ET_2.sql 脚本。

SELECT /*+ parallel(a,4) */ COUNT(*) 
FROM   sales_delta_XT a;

将鼠标移到该图标上可以查看该图像

尽管外部表仅指向一个输入源文件，但您将从外部表中并行选择。另外，您可以使用如下所示的 ALTER TABLE 命令更改外部表的 PARALLEL 属性：

ALTER TABLE sales_delta_XT PARALLEL 4;
3.

再次查看会话的统计信息，寻找与原统计信息的差别。执行以下命令或 parallel_select_from_ET.sql 脚本。注意该并行会话的统计信息发生了变化。显示了最后一个并行化的查询，还显示了并行化的程度。 


SELECT * 
FROM   v$pq_sesstat 
WHERE  statistic in 
       ('Queries Parallelized', 'Allocation Height');

将鼠标移到该图标上可以查看该图像

返回列表

5. 查看 Oracle 的并行插入功能

Oracle 数据库在每个分区内提供了无限制的并行直接路径 INSERT 功能。您可以使用执行计划来确定是否以并行方式执行 INSERT。另外，您可以检查一个操作在 SAL 缓存中的执行计划，根本无需 EXPLAIN PLAN 命令。

检查以下串行计划。由于所有对象都不是并行定义的，除非您更改一个对象的默认并行度或者使用一个提示，您将自动以串行方式执行任务。

1.

要演示 SERIAL INSERT 行为的执行计划，执行 show_serial_exec_plan.sql，或将以下 SQL 语句复制到您的 SQL*Plus 会话中：

EXPLAIN PLAN FOR INSERT /*+ APPEND */ INTO sales
               (
   PROD_ID,
   CUST_ID,
   TIME_ID,
   CHANNEL_ID,
   PROMO_ID,
   QUANTITY_SOLD,
   AMOUNT_SOLD
   )
   SELECT
   PROD_ID,
   CUST_ID,
   TIME_ID,
   case CHANNEL_ID
   when 'S' then 3
   when 'T' then 9
   when 'C' then 5
   when 'I' then 4
   when 'P' then 2
   else 99
   end,
   PROMO_ID,
   sum(QUANTITY_SOLD),
   sum(AMOUNT_SOLD)
   FROM SALES_DELTA_XT
   GROUP BY 1, prod_id,time_id,cust_id,channel_id,promo_id;
set linesize 140
set pagesize 40
SELECT * FROM TABLE(dbms_xplan.display);


将鼠标移到该图标上可以查看该图像
2.

要演示 PARALLEL INSERT 执行计划，以 SH 用户身份登录执行以下命令或 show_parallel_exec_plan.sql 脚本。事务的第一条语句必须始终是一个并行 DML 命令。另外，如果存在主键和外键约束，则不能执行 DML 操作。因此，在执行并行 DML 操作之前必须禁用这些约束：


ALTER TABLE sales DISABLE CONSTRAINT sales_product_fk;
ALTER TABLE sales DISABLE CONSTRAINT sales_customer_fk;
ALTER TABLE sales DISABLE CONSTRAINT sales_time_fk;
ALTER TABLE sales DISABLE CONSTRAINT sales_channel_fk;
ALTER TABLE sales DISABLE CONSTRAINT sales_promo_fk;
COMMIT;


ALTER SESSION ENABLE PARALLEL DML;
EXPLAIN PLAN FOR INSERT /*+ APPEND PARALLEL(SALES,4) */ INTO sales
(
   PROD_ID,
   CUST_ID,
   TIME_ID,
   CHANNEL_ID,
   PROMO_ID,
   QUANTITY_SOLD,
   AMOUNT_SOLD
   )
   SELECT /*+ parallel (sales_delta_XT,4) */
   PROD_ID,
   CUST_ID,
   TIME_ID,
   case CHANNEL_ID
   when 'S' then 3
   when 'T' then 9
   when 'C' then 5
   when 'I' then 4
   when 'P' then 2
   else 99
   end,
   PROMO_ID,
   sum(QUANTITY_SOLD),
   sum(AMOUNT_SOLD)
   FROM SALES_DELTA_XT
   GROUP BY 1, prod_id,time_id,cust_id,channel_id,promo_id;
set linesize 140
set pagesize 40
SELECT * FROM TABLE(dbms_xplan.display);


将鼠标移到该图标上可以查看该图像

返回列表

6. 执行并行插入

在本教程的该步骤中，您将执行前面讨论过的并行插入。请注意，在插入之前，您不但要从外部表选择数据，而且作为选择的一部分还要执行聚合。您要将转换与真正的加载过程进行结合。如果只使用 SQL*Loader 实用程序则无法完成此操作。

1.

执行以下 SQL 语句或 parallel_insert_file.sql 文件，执行一个并行插入。将计时设置为 ON。


set timing on
COMMIT;
ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ APPEND PARALLEL(SALES,4) */ INTO sales
(
  PROD_ID,
  CUST_ID,
  TIME_ID,
  CHANNEL_ID,
  PROMO_ID,
  QUANTITY_SOLD,
  AMOUNT_SOLD
)
SELECT /*+ PARALLEL (sales_delta_XT,4) */
  PROD_ID,
  CUST_ID,
  TIME_ID,
  case CHANNEL_ID
  when 'S' then 3
  when 'T' then 9
  when 'C' then 5
  when 'I' then 4
  when 'P' then 2
  else 99
  end,
  PROMO_ID,
  sum(QUANTITY_SOLD),
  sum(AMOUNT_SOLD)
FROM SALES_DELTA_XT
GROUP BY 1, prod_id,time_id,cust_id,channel_id,promo_id;
set timing off

SELECT * FROM TABLE(dbms_xplan.display_cursor);


SELECT * 
FROM   v$pq_sesstat 
WHERE  statistic in ('DML Parallelized','Allocation Height');

记下该语句的执行时间，将其与使用 SQL*Loader 然后使用后续插入所需的总时间相比较。请注意，由于您是在使用一个磁盘的单 CPU 计算机上并行访问非常少量的数据，因此无法看到并行化外部表访问以及将转换与加载相结合的全部优点。

将鼠标移到该图标上可以查看该图像
2.

执行回滚以将数据返回到之前的状态。（在下一个示例中，使用 SQL*Loader 插入相同的数据。）

ROLLBACK;

将鼠标移到该图标上可以查看该图像
3.

发出 ROLLBACK 命令之后，您需要再次启用约束。执行以下命令或 enable_cons.sql 脚本。 


ALTER TABLE sales 
   MODIFY CONSTRAINT sales_product_fk ENABLE NOVALIDATE; 
ALTER TABLE sales 
   MODIFY CONSTRAINT sales_customer_fk ENABLE NOVALIDATE; 
ALTER TABLE sales 
   MODIFY CONSTRAINT sales_time_fk ENABLE NOVALIDATE; 
ALTER TABLE sales 
   MODIFY CONSTRAINT sales_channel_fk ENABLE NOVALIDATE; 
ALTER TABLE sales 
   MODIFY CONSTRAINT sales_promo_fk ENABLE NOVALIDATE;

将鼠标移到该图标上可以查看该图像


您刚刚用一步操作就加载并转换了数据。要使用 SQL*Loader 加载和转换数据，您需要两个过程步骤，这就表示整个过程所需的工作更多，而性能更低。

返回列表

返回主题列表

使用 SQL*Loader 与使用外部表进行加载和转换的过程的比较

您先前执行的外部表方法是数据加载和转换的首选方法。要演示使用外部表的优点，您可以使用 SQL*Loader 比较加载和转换数据所需的任务。

要使用 SQL*Loader 加载和转换数据，执行以下步骤：

1.

创建一个临时表。
2.

使用 SQL*Loader 将数据加载到临时表中。
3. 将临时表加载到目标数据库中。
4. 删除临时表。

1. 创建临时表

您需要一个临时表来加载数据，以便在第二步中在数据库内转换这些数据。

在一个以 SH 用户身份连接到的 SQL*Plus 会话中，执行以下命令或 create_stage.sql 脚本创建一个临时表：



CREATE TABLE sales_dec01 AS
  SELECT * 
  FROM   sales 
  WHERE  1=0;

 ALTER TABLE sales_dec01 MODIFY (channel_id CHAR(2) null);

将鼠标移到该图标上可以查看该图像

返回列表

2. 使用 SQL*Loader 将数据加载到临时表中

注：这些脚本是针对 Linux 系统创建的，并且假设文件已经解压缩到 /home/oracle/wkdir 目录中。

通过执行以下步骤，将 sales_dec01.ctl 文件中的数据文件加载到临时表中：

1.

从 OS 命令行执行以下命令：

cd /home/oracle/wkdir
sqlldr sh/sh control=sales_dec01.ctl direct=true

将鼠标移到该图标上可以查看该图像

注：使用 SQL*Loader 连接时，您可能需要指定数据库别名。使用以下语句启动 SQL*Loader：

sqlldr sh/sh@<database alias> control=sales_dec01.ctl direct=true
2.

请注意，您无法并行执行该任务。检查 SQL*Loader 日志文件 sales_dec01.log，记下加载进程的执行时间。

您可以使用任何编辑器来检查 sales_dec01.log 文件。该文件位于 /home/oracle/wkdir 目录中。

将鼠标移到该图标上可以查看该图像

与外部表不同，使数据可在数据库中访问将占用该数据库中空间。临时表占用的空间与为进一步转换而加载的数据量存在线性关系。

另外还要注意，如果外部文件很少，则不大可能并行执行使用 SQL*Loader 的加载。您可以对几个访问同一文件的 SQL*Loader 进程使用 SKIP 选项。但是，这样会强制每个 SQL*Loader 进程扫描整个外部文件。从而影响系统的整体性能。

您可通过以下数据字典视图访问有关对象空间使用的信息：
[USER | ALL| DBA]_SEGMENTS
[USER | ALL| DBA]_EXTENTS

返回列表

3. 将临时表加载到目标数据库中

加载外部数据（使数据库能够访问这些数据）以后，您就可以执行转换了。

以 SH 用户身份登录到 SQL*Plus。您将通过执行以下命令或 load_stage_table.sql 脚本，执行 SQL 来转换外部数据（这些数据已经临时存放在数据库中），并将其插入 SALES 事实表中。

set timing on
INSERT /*+ APPEND */ INTO sales
  ( PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID,
    PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD )
  SELECT
    PROD_ID, CUST_ID, TIME_ID,
    case CHANNEL_ID
    when 'S' then 3
    when 'T' then 9
    when 'C' then 5
    when 'I' then 4
    when 'P' then 2
    else 99
    end,
    PROMO_ID,
    sum(QUANTITY_SOLD),
    sum(AMOUNT_SOLD)
  FROM sales_dec01
  GROUP BY prod_id,time_id,cust_id,channel_id,promo_id;

set timing off

将鼠标移到该图标上可以查看该图像

返回列表

4. 删除临时表

现在您可以删除或截断临时表来释放它所占用的空间了。

使用以下命令或 drop_sales_dec01.sql 脚本删除临时表信息。

  
DROP TABLE sales_dec01;  

将鼠标移到该图标上可以查看该图像

使用外部表处理这个简单的加载和转换过程可以使您将加载与转换相结合，从而简化和加快了该过程。另外，外部表不需要在数据库中临时存放数据。外部表与 SQL*Loader 相比，外部数据量越大，您节省的临时空间和处理时间就越多。

返回列表

返回主题列表

利用表压缩节省磁盘空间并降低 TCO

将十二月份的数据成功加载到 SALES 事实表的第四季度分区之后，该分区会经受很少甚至不会再遇到 DML 操作了。这就使得表分区成为使用 Oracle 的表压缩功能进行存储的最佳选择。随着企业需要的信息越来越多，存储在关系数据库中的数据也在不断增加。保存大量数据的很大一部分成本是磁盘系统的成本以及管理这些数据所使用资源的成本。Oracle 数据库引入了一种独特的方法来处理这种成本，即压缩存储在关系表中数据，且基本不会对该数据的查询时间造成负面影响，从而节省了可观的成本。

商用关系数据库系统还没有对在关系表中存储的数据大量使用压缩技术。其中一个原因是，压缩时间和空间之间的权衡对于关系数据库来说并非总是那样具有吸引力。通常的压缩技术可能节省空间，但是需要付出数据查询时间大大延长的代价。另外，很多标准技术甚至不能保证数据大小在压缩之后不会增加。

Oracle 数据库提供了一种独特的压缩技术，它对大型数据仓库具有非常大的吸引力。它在很多方面都是那么独特。它节省的磁盘空间要显著多于标准压缩算法，这是因为它针对关系数据进行了优化。这种方法对压缩后数据的查询性能基本没有负面影响；对于访问大量数据的查询以及数据管理操作（如备份和恢复），它还可能产生明显的正面影响。另外，它可以确保压缩后的数据决不会比未压缩的数据大。

1. 压缩近期的分区

为了衡量表压缩的巨大优点，您首先要确定近期的分区没有启用压缩。此外，还要确定近期分区的大小。

1.

执行 part_before_compression.sql 脚本，或者将以下 SQL 语句复制到 SQL*Plus 会话中：



COLUMN partition_name FORMAT a50
COLUMN segment_name FORMAT a50

SELECT partition_name, compression FROM user_tab_partitions
WHERE partition_name='SALES_Q4_2001';


SELECT segment_name, bytes/(1024*1024) MB FROM user_segments
WHERE segment_name='SALES' AND partition_name='SALES_Q4_2001';

将鼠标移到该图标上可以查看该图像
2.

现在压缩该分区，并透明地维护所有现有索引。所有本地索引和全局索引都作为该 SQL 语句的一部分进行维护。该分区维护操作的联机索引维护功能将在本教程的后面部分中介绍。

请注意，压缩分区并不是原地压缩。您会创建一个新的压缩分区，并在操作的最后删除原来的未压缩分区。

在以 SH 用户身份登录的 SQL*Plus 会话中，执行 compress_salesQ4_2001.sql 脚本或以下 SQL 语句： 


ALTER TABLE sales MOVE PARTITION sales_q4_2001 COMPRESS UPDATE INDEXES;

将鼠标移到该图标上可以查看该图像
3.

通过执行以下命令或 part_after_compression.sql 脚本来看看新的压缩分区分配了多少空间，并将其与未压缩分区的大小进行比较。 


SELECT partition_name, compression FROM user_tab_partitions
WHERE partition_name='SALES_Q4_2001';

SELECT segment_name, bytes/(1024*1024) MB FROM user_segments
WHERE segment_name='SALES' AND partition_name='SALES_Q4_2001';


将鼠标移到该图标上可以查看该图像

真实情况下的数据压缩比例通常比这个销售历史模式的压缩比例还高。SALES 事实表中的数据是人工生成的，通常都会看到的“自然排序”并没有在执行 INSERT 前清理、合并甚至聚合了数据的数据库环境中出现。

返回主题列表

使用 Oracle 分区执行滚动窗口操作

很多数据仓库都维护着一个数据的滚动窗口。例如，该数据仓库存储着最近 12 个月的销售数据。正如可以将新分区添加到 SALES 表中一样，您也可以将旧分区快速（且单独地）从 SALES 表中删除。分区功能为这些操作提供了理想的框架。与添加分区一样，删除分区同样具有两个优点（降低资源使用，对最终用户影响最小）。

执行滚动窗口操作的步骤：

执行滚动窗口操作：

1.

准备一个包含新数据的独立表
2.

向事实表添加新数据
3. 从事实表中删除旧数据

1. 准备一个包含新数据的独立表

要执行滚动窗口操作，您需要创建和加载一个包含新数据的独立表，通过以下步骤可完成上述操作。注意，您将使用前面定义的外部表；然而，您不会将该外部表指向另一个外部文件。

1.1

修改外部表以使用第一季度销售数据。

1.2

为新的第一季度销售数据创建表。
1.3 加载该表。
1.4

为该表创建位图索引。
1.5

为该表创建约束。

1.1 修改外部表以使用第一季度销售数据

在本节中，您将使用已经定义的外部表。但这次您要使用另一个外部文件 sales_Q1_data。因此，您必须修改该外部表的位置属性，使其指向这个新的数据文件。

1.

首先，通过执行 select_et.sql 脚本或以下 SQL 语句检查当前外部表的行数： 

SELECT COUNT(*) FROM sales_delta_xt;
SELECT MAX(time_id) FROM sales_delta_xt;

将鼠标移到该图标上可以查看该图像

包含 2001 年 12 月所有销售事务的文件将显示 2001 年 12 月最后一天的值。您会看到在操作系统级别上更改了外部文件之后，外部表的行数和 MAX(time_id) 将有所不同。
2.

更改 LOCATION 属性。执行以下命令或 alter_loc_attrib.sql 脚本更改 LOCATION 属性：

ALTER TABLE sales_delta_xt location ( 'salesQ1.dat' );


将鼠标移到该图标上可以查看该图像

要检查新数据，执行以下命令或 select_et.sql 脚本：


SELECT COUNT(*) FROM sales_delta_xt;
SELECT MAX(time_id) FROM sales_delta_xt;

行数和最大 TIME_ID 均已更改。如果外部表文件正确，则最大 TIME_ID 为 2002 年 3 月的最后一天。

将鼠标移到该图标上可以查看该图像

返回列表

1.2 为新的第一季度销售数据创建表

在该步骤中，您将为新的第一季度销售数据创建一个空表。该表稍后将添加到已存在的 SALES 分区表中。

执行以下命令或 create_stage_table.sql 脚本创建该表：

DROP TABLE sales_delta;

CREATE TABLE sales_delta NOLOGGING NOCOMPRESS 
  AS SELECT * FROM sales WHERE 1=0;

将鼠标移到该图标上可以查看该图像

返回列表

1.3 加载该表

要加载该表，请执行以下步骤：

1.

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 load_stage_table2.sql 脚本加载该表：

INSERT /*+ APPEND */ INTO sales_delta
SELECT /*+ PARALLEL (SALES_DELTA_XT,4) */
  PROD_ID,
  CUST_ID,
  TIME_ID,
  case CHANNEL_ID
  when 'S' then 3
  when 'T' then 9
  when 'C' then 5
  when 'I' then 4
  when 'P' then 2
  else 99
  end,
  PROMO_ID,
  sum(QUANTITY_SOLD) quantity_sold,
  sum(AMOUNT_SOLD) amount_sold
FROM SALES_DELTA_XT
GROUP BY prod_id,time_id,cust_id,channel_id,promo_id;

COMMIT;

将鼠标移到该图标上可以查看该图像
2.

加载了 SALES_DELTA 表后，收集新创建表的统计信息。在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 gather_stat_stage_table.sql 脚本收集该表的统计信息：

exec dbms_stats.gather_table_stats('SH','sales_delta',estimate_percent=>20);

将鼠标移到该图标上可以查看该图像

返回列表

1.4 为该表创建位图索引

因为您稍后要将该独立表与 SALES 表的一个空分区进行交换，所以必须构建与现有 SALES 表完全相同的索引结构，以使该特定表的本地索引结构在交换之后仍然保持可用状态。

1.

创建任何位图索引之前，您需要将新创建的表更改为一个压缩表，而实际上并不压缩任何数据。对于创建可用于交换到已包含压缩分区的分区表中的位图索引，此操作是必需的。执行以下命令或 alter_sales_delta.sql 脚本修改该表。 


ALTER TABLE sales_delta COMPRESS;
ALTER TABLE sales_delta NOCOMPRESS;

将鼠标移到该图标上可以查看该图像
2.

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 create_static_bitmap_index.sql 脚本在 SALES_DELTA 表上创建位图索引。 

CREATE BITMAP INDEX sales_prod_local_bix
  ON sales_delta (prod_id)
  NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_cust_local_bix
  ON sales_delta (cust_id)
  NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_time_local_bix
  ON sales_delta (time_id)
  NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_channel_local_bix
   ON sales_delta (channel_id)
   NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_promo_local_bix
   ON sales_delta (promo_id)
   NOLOGGING COMPUTE STATISTICS ;

请注意，这些索引的统计信息将在创建索引时进行创建。

将鼠标移到该图标上可以查看该图像

返回列表

1.5 为该表创建约束

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 create_constraints.sql 脚本修改 SALES 表上的约束。

ALTER TABLE channels MODIFY CONSTRAINT CHANNELS_PK RELY;
ALTER TABLE countries MODIFY CONSTRAINT COUNTRIES_PK RELY;
ALTER TABLE customers MODIFY CONSTRAINT CUSTOMERS_PK RELY;
ALTER TABLE products MODIFY CONSTRAINT PRODUCTS_PK RELY;
ALTER TABLE promotions MODIFY CONSTRAINT PROMO_PK RELY;
ALTER TABLE times MODIFY CONSTRAINT TIMES_PK RELY;

ALTER TABLE sales_delta
               ADD ( CONSTRAINT sales_product_delta_fk
   FOREIGN KEY (prod_id)
   REFERENCES products RELY ENABLE NOVALIDATE
   , CONSTRAINT sales_customer_delta_fk
   FOREIGN KEY (cust_id)
   REFERENCES customers RELY ENABLE NOVALIDATE
   , CONSTRAINT sales_time_delta_fk
   FOREIGN KEY (time_id)
   REFERENCES times RELY ENABLE NOVALIDATE
   , CONSTRAINT sales_channel_delta_fk
   FOREIGN KEY (channel_id)
   REFERENCES channels RELY ENABLE NOVALIDATE
   , CONSTRAINT sales_promo_delta_fk
   FOREIGN KEY (promo_id)
   REFERENCES promotions RELY ENABLE NOVALIDATE
   ) ;

将鼠标移到该图标上可以查看该图像

返回列表

返回主题

2. 向事实表添加新数据

执行滚动窗口操作的下一个任务是将新加载和新索引的数据添加到事实表中。为此，执行以下步骤：

2.1

如果尚不存在任何分区，则创建一个新分区。
2.2

交换该分区。这只是一个数据字典操作，不会触及任何数据。
2.3 从分区进行选择以确保成功。
2.4 分割近期的分区以确保（业务）数据完整性。

2.1 创建新分区

在创建新的空分区时，您可以使用一个不同的上边界来创建新分区，也可以选择关键字 MAXVALUE 来创建该分区。后面一种选择可以确保不会拒绝违反潜在上边界条件的记录，从而使 INSERT 操作成功。

在此业务情景下，您将在加载操作之后发出 SPLIT PARTITION，以识别任何可能的违例。所有违反上边界的记录都将被“隔离”到一个附加分区中。

在创建新的空分区时，为此，请执行以下步骤：

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 create_partition_for_sales_etl.sql 脚本向 SALES 表添加分区。 


COLUMN partition_name FORMAT a20
                 
select partition_name, high_value 
from   user_tab_partitions 
where  table_name='SALES'
order by partition_position;

ALTER TABLE sales 
ADD PARTITION sales_q1_2002
VALUES LESS THAN (MAXVALUE);

SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_2002);

将鼠标移到该图标上可以查看该图像

返回列表

2.2 交换分区

现在您将执行 PARTITION EXCHANGE 命令，将新加载和索引的数据添加到真正的 SALES 事实表中。请注意，这只是一个 DDL 命令，不会触及任何真正的数据。为此，请执行以下步骤：

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 exchange_partition_wo_gim.sql 脚本更改 SALES 表，从而允许交换分区：


ALTER TABLE sales EXCHANGE PARTITION sales_q1_2002 
    WITH TABLE sales_delta INCLUDING INDEXES;

将鼠标移到该图标上可以查看该图像

返回列表

2.3 从分区选择

现在，您可以从新添加和交换的分区中进行选择。

注意，您向分区的事实表添加的数据越多，您使用这个只包含元数据的操作进行保存所需的时间就越多。

您需要使用逻辑分区，如 RANGE 分区。散列分区不能用于常见的滚动窗口操作。

SALES 表中的所有索引都要进行维护，并且都可使用。

1.

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下查询或 select_count.sql 脚本。它们将显示已交换分区和独立表（现在为空）中的行数。 

SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_2002);

SELECT COUNT(*)
FROM   sales_delta;

将鼠标移到该图标上可以查看该图像

2.

请注意，SALES 表的所有本地索引都是有效的。执行以下命令或 show_sales_idx_status.sql 脚本查看索引的状态：

SELECT ui.index_name, DECODE(uip.status,null,ui.status,uip.status)
FROM user_ind_partitions uip, user_indexes ui
WHERE ui.index_name=uip.index_name(+)
AND ui.table_name='SALES'
GROUP BY ui.index_name, DECODE(uip.status,null,ui.status,uip.status);

将鼠标移到该图标上可以查看该图像

您还可以在 PARTITION EXCHANGE 命令中使用 WITHOUT VALIDATION 子句。这将导致 Oracle 数据库服务器忽略对要交换表的有效性检查。否则，Oracle 数据库服务器会保证分区关键字的所有值都位于该分区边界之内。

返回列表

2.4 分割近期的分区以确保（业务）数据完整性

正如上面提到的那样，您决定将数据加载到一个没有固定上边界的分区以避免任何可能的错误。为了识别任何可能的违规，您需要分割最近的分区。因此您创建了两个分区，其中一个具有固定上边界。

Oracle 数据库使用了一个增强的快速分割操作，该操作检测两个新分区中的任一分区是否会在 SPLIT 操作之后变为空分区。如果是这种情况，Oracle 数据库服务器不会创建两个新分区。它只会通过使用新的空分区上的 DBMS_STATS 创建一个分区，并将使用现有分区作为包含所有数据的新分区。

这种优化完全是透明的。它减少了 SPLIT 操作的运行时间，节省了系统资源，并且无需任何索引维护。

1.

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 fast_split_sales.sql 脚本更改 SALES 表，并查看索引状态。 


ALTER TABLE sales SPLIT PARTITION sales_q1_2002 
  AT (TO_DATE('01-APR-2002','DD-MON-YYYY')) 
  INTO (PARTITION sales_q1_2002, PARTITION sales_beyond_q1_2002);
SELECT COUNT(*) FROM sales PARTITION (sales_beyond_q1_2002);
SELECT ui.index_name, DECODE(uip.status,null,ui.status,uip.status)
FROM user_ind_partitions uip, user_indexes ui
WHERE ui.index_name=uip.index_name(+)
AND ui.table_name='SALES'
GROUP BY ui.index_name, DECODE(uip.status,null,ui.status,uip.status);
ALTER TABLE sales DROP PARTITION sales_beyond_q1_2002;

将鼠标移到该图标上可以查看该图像
2.

请注意，SALES 表的所有本地索引都是有效的。执行 show_sales_idx_status.sql 脚本查看 SALES 表上本地索引的状态。 

@show_sales_idx_status.sql

将鼠标移到该图标上可以查看该图像

返回列表

返回主题

3. 从事实表中删除旧数据

执行滚动窗口操作的下一个任务是从事实表中删除旧数据。您只希望分析最近三年的最新数据。由于您添加了 Q1-2002，所以必须删除 Q1-1998 的数据。

如果没有范围分区，您必须对该表执行 DML 操作。通过分区，您可以再次利用 PARTITION EXCHANGE 命令从事实表中删除数据。与添加新数据相似，散列分区仍然无法在此处使用。

请注意，您并不是要删除数据，而是要将 SALES 事实表中包含这些数据的分区与具有相同逻辑结构的空独立表进行交换（逻辑替换）。然后，您可以将这些数据存档，或者删除交换后的分区，这取决于您的业务需求。

3.1

创建空的独立表。

3.2

为该表创建位图索引。
3.3 为该表创建约束。
3.4 显示交换前分区中的数据。
3.5 将新的空表与现有的 Q1-1998 分区进行交换。
3.6 显示交换后分区中的数据。

3.1 创建空的独立表

您需要创建一个空表，并在其中存储 1998 年的数据。

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 create_empty_sat.sql 脚本创建一个空表，该空表将保存 1998 年的数据： 


DROP TABLE sales_old_q1_1998;

CREATE TABLE sales_old_q1_1998 NOLOGGING COMPRESS 
  AS SELECT * FROM sales WHERE 1=0;

将鼠标移到该图标上可以查看该图像

返回列表

3.2 为该表创建位图索引

现在创建本地索引。

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 create_ndx.sql 脚本创建本地索引。 

CREATE BITMAP INDEX sales_prod_old_bix
       ON sales_old_q1_1998 (prod_id)
       NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_cust_old_bix
       ON sales_old_q1_1998 (cust_id)
       NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_time_old_bix
       ON sales_old_q1_1998 (time_id)
       NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_channel_old_bix
       ON sales_old_q1_1998 (channel_id)
       NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_promo_old_bix
       ON sales_old_q1_1998 (promo_id)
       NOLOGGING COMPUTE STATISTICS ;

将鼠标移到该图标上可以查看该图像

返回列表

3.3 为该表创建约束

现在创建约束。

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 create_constraints_old.sql 脚本修改和创建约束。 


ALTER TABLE channels MODIFY CONSTRAINT CHANNELS_PK RELY;
ALTER TABLE countries MODIFY CONSTRAINT COUNTRIES_PK RELY;
ALTER TABLE customers MODIFY CONSTRAINT CUSTOMERS_PK RELY;
ALTER TABLE products MODIFY CONSTRAINT PRODUCTS_PK RELY;
ALTER TABLE promotions MODIFY CONSTRAINT PROMO_PK RELY;
ALTER TABLE times MODIFY CONSTRAINT TIMES_PK RELY; 

ALTER TABLE sales_old_q1_1998
  ADD ( CONSTRAINT sales_product_old_fk
  FOREIGN KEY (prod_id)
  REFERENCES products RELY ENABLE NOVALIDATE
  , CONSTRAINT sales_customer_old_fk
  FOREIGN KEY (cust_id)
  REFERENCES customers RELY ENABLE NOVALIDATE
  , CONSTRAINT sales_time_old_fk
  FOREIGN KEY (time_id)
  REFERENCES times RELY ENABLE NOVALIDATE
  , CONSTRAINT sales_channel_old_fk
  FOREIGN KEY (channel_id)
  REFERENCES channels RELY ENABLE NOVALIDATE
  , CONSTRAINT sales_promo_old_fk
  FOREIGN KEY (promo_id)
  REFERENCES promotions RELY ENABLE NOVALIDATE
  ) ;

将鼠标移到该图标上可以查看该图像

返回列表

3.4 显示交换前分区中的数据

在执行交换前，观察将从该分区中删除的 1998 Q1 数据。

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 show_partition.sql 脚本查看将从该分区中删除的数据：


SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_1998);

将鼠标移到该图标上可以查看该图像

返回列表

3.5 交换分区

将空表与现有的 Q1-1998 分区进行交换。为此，请执行以下步骤：

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 exchange_old_partition.sql 脚本交换分区：

ALTER TABLE sales 
   EXCHANGE PARTITION sales_q1_1998 
   WITH TABLE sales_old_q1_1998 
   INCLUDING INDEXES;

将鼠标移到该图标上可以查看该图像

请注意，您可以使用一个 DROP PARTITION 语句来代替。SALES_OLD_Q1_1998 表现在存储了 1998 年第一季度的所有数据。您可以删除该表，从系统中完全删除这些数据。

返回列表

3.6 显示交换后分区中的数据

执行了交换之后，查看分区中的数据。

1.

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 count_sales.sql 脚本查看分区中的数据：

SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_1998);

SELECT COUNT(*) 
FROM   sales_old_q1_1998;

将鼠标移到该图标上可以查看该图像

与执行 EXCHANGE 命令之前不同，该独立表现在存储着几千行数据，而 SALES 的相应分区为空。
2.

本地索引不受交换的影响。执行以下命令或 show_sales_idx_status.sql 脚本查看索引信息。 

SELECT ui.index_name, DECODE(uip.status,null,ui.status,uip.status)
FROM   user_ind_partitions uip, user_indexes ui
WHERE  ui.index_name=uip.index_name(+)
AND    ui.table_name='SALES'
GROUP BY ui.index_name, DECODE(uip.status,null,ui.status,uip.status);

将鼠标移到该图标上可以查看该图像

返回列表

返回主题

Oracle 数据库 10g 为本地索引维护提供的增强

要了解 Oracle 数据库 10g 中有关本地索引维护的增强，您需要将最近一个季度的分区分割成带有联机本地索引维护的月分区。这是 Oracle 数据库 10g 的新功能。您还将使用全局索引维护功能（在 Oracle 9i 中引入）。

1.

使用 Oracle 数据库 10g 为本地索引维护提供的性能增强
2.

使用 Oracle 的全局索引维护

1.本地索引维护的性能增强：

从 Oracle 数据库 10g 开始，执行所有分区维护操作均不会对其可用性产生任何影响。通过本地索引维护，您可以为分区表的本地索引保持最新状态，而该过程可以作为任意原子分区维护操作的一部分完成。
Oracle 扩展了分区维护操作的 SQL 语法，从而控制了所有受影响的本地索引结构的物理属性，如索引放置。

步骤：

1.1

使用默认放置规则分割近期的分区。
1.2

使用为本地索引维护扩展的 SQL 语法分割分区。
1.3 清理。

查看此情景：成功加载了 2002 年第一季度的数据之后，您认识到，由于不断变化的业务需求，查询方式已经更改。很多业务用户已不再关注每季度一次的分析，而开始依赖月报告和分析。为了满足这个变化的业务需求并优化查询性能，您可以利用 Oracle 分区将最近一个季度的分区分割为月分区。

本地索引维护的联机可用性不会在本示例中演示。本文中演示了全局索引维护的联机可用性，其工作方式与本地索引完全相同。

1.1 分割近期的分区

在创建新的空分区时，此时，您可以使用一个不同的上边界来创建新分区，也可以选择关键字 MAXVALUE 来创建该分区。后面一种选择可以确保不会拒绝违反潜在上边界条件的记录，从而使 INSERT 操作成功。

在此业务情景下，您将在加载操作之后发出 SPLIT PARTITION 命令，以识别任何可能的违例。所有违反上边界的记录都将被“隔离”到一个附加分区中。

执行以下步骤，创建一个新的空分区：

1.

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下 SQL 语句，将最近一个月（2002 年 3 月）从季度分区中分割出来，其中包括本地索引维护。您可以执行以下命令或 split1_10g.sql 脚本完成此任务。

ALTER TABLE sales SPLIT PARTITION sales_q1_2002
AT (TO_DATE('01-MAR-2002','DD-MON-YYYY'))
INTO (PARTITION sales_1_2_2002 TABLESPACE example, 
PARTITION sales_MAR_2002 TABLESPACE example NOCOMPRESS)
UPDATE INDEXES;

将鼠标移到该图标上可以查看该图像
2.

您将看到新索引分区与表分区在同一位置，并且索引分区命名沿袭了分区命名。执行以下命令或 see_split.sql 脚本查看分区信息。

COL segment_name format a25
COL partition_name format a25
COL tablespace_name format a25
SELECT segment_name, partition_name, tablespace_name 
FROM user_segments
WHERE segment_type='INDEX PARTITION'
AND segment_name IN 
   (SELECT index_name 
    FROM   user_indexes 
    WHERE  table_name='SALES');

将鼠标移到该图标上可以查看该图像

...

将鼠标移到该图标上可以查看该图像

返回列表

1.2 使用扩展的 SQL 语法分割分区

将上一个季度分区的剩余部分分割为一月分区和二月分区。为了进行演示，请在 SYSAUX 表空间中创建其中一个新分区，并显式命名一些索引。

1.

在一个以 SH 身份登录的 SQL*Plus 会话中，执行以下 SQL 语句，分割剩余的分区，其中包括本地索引维护。您可以使用以下的命令或 split2_10g.sql 脚本。 

ALTER TABLE sales SPLIT PARTITION sales_1_2_2002 
  AT (TO_DATE('01-FEB-2002','DD-MON-YYYY'))
  INTO 
   (PARTITION sales_JAN_2002 TABLESPACE sysaux COMPRESS,
    PARTITION sales_FEB_2002 TABLESPACE example NOCOMPRESS)
  UPDATE INDEXES (sales_time_bix 
   (PARTITION jan_02 TABLESPACE example,
    PARTITION feb_02 TABLESPACE system));

将鼠标移到该图标上可以查看该图像
2.

您将看到新索引分区与表分区在同一位置，并且索引分区命名沿袭了分区命名。执行以下命令或 see_split.sql 脚本查看分区和区段信息。 

SELECT segment_name, partition_name, tablespace_name 
FROM   user_segments
WHERE  segment_type='INDEX PARTITION'
  AND  tablespace_name <>'EXAMPLE'
  AND  segment_name IN 
    (SELECT index_name 
     FROM   user_indexes 
     WHERE  table_name='SALES');

将鼠标移到该图标上可以查看该图像

返回列表

1.3 清理

通过将分区从 SYSAUX 表空间中移出并移入 EXAMPLE 表空间中，执行清理操作。使用标准命名惯例。

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 cleanup_split_10g.sql 脚本移动分区和更新索引。 

ALTER TABLE sales MOVE PARTITION sales_JAN_2002 TABLESPACE example COMPRESS
UPDATE INDEXES (sales_time_bix    (PARTITION sales_jan_2002 TABLESPACE example),
                sales_cust_bix    (PARTITION sales_jan_2002 TABLESPACE example),
                sales_channel_bix (PARTITION sales_jan_2002 TABLESPACE example),
                sales_prod_bix    (PARTITION sales_jan_2002 TABLESPACE example),
                sales_promo_bix   (PARTITION sales_jan_2002 TABLESPACE example))
;
ALTER INDEX sales_time_bix 
  REBUILD PARTITION feb_02 TABLESPACE example;
SELECT segment_name, partition_name, tablespace_name 
FROM   user_segments
WHERE  segment_type='INDEX PARTITION'
  AND  segment_name IN (SELECT index_name 
                        FROM   user_indexes 
                        WHERE  table_name='SALES')
  AND  tablespace_name <> 'EXAMPLE';

将鼠标移到该图标上可以查看该图像

返回列表

2. 使用 Oracle 的全局索引维护

通过全局索引维护，您可以为分区表的全局索引保持最新状态，而该过程可以作为任意原子分区维护操作的一部分完成。这使全局索引总能在进行维护操作的同时保持可用状态，并且不会影响它们的使用。

步骤：

2.1

准备进行全局索引维护。

2.2

构建全局索引。

2.3 交换带有全局索引维护的分区，并体验它对全局索引的影响。
2.4 交换不带全局索引维护的分区，并体验它对全局索引的影响。
2.5 删除全局索引，并交换回初始状态（清理）。

2.1 准备进行全局索引维护

将三月份的数据交换到存在全局索引的分区表中。首先，您必须构建必要的基础架构：

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 prep4_global_index.sql 脚本准备进行全局索引维护。 

CREATE TABLE sales_mar_2002_temp 
  NOLOGGING AS SELECT * FROM sales 
    PARTITION (sales_MAR_2002);
ALTER TABLE sales TRUNCATE PARTITION sales_MAR_2002;
SELECT COUNT(*) 
               FROM sales PARTITION (sales_MAR_2002);
               ALTER TABLE sales_mar_2002_temp COMPRESS;
               ALTER TABLE sales_mar_2002_temp NOCOMPRESS;
CREATE BITMAP INDEX sales_prod_mar_2002_bix
               ON sales_mar_2002_temp (prod_id)
               NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_cust_mar_2002_bix
               ON sales_mar_2002_temp (cust_id)
               NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_time_mar_2002_bix
               ON sales_mar_2002_temp (time_id)
               NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_channel_mar_2002_bix
               ON sales_mar_2002_temp (channel_id)
               NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_promo_mar_2002_bix
               ON sales_mar_2002_temp (promo_id)
               NOLOGGING COMPUTE STATISTICS ;

将鼠标移到该图标上可以查看该图像

返回列表

2.2 构建全局索引

要演示全局索引维护功能，首先要创建一个全局索引。为此，执行以下步骤：

1.

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 create_global_index.sql 脚本在 SALES 表上创建一个串联的唯一索引。

CREATE UNIQUE INDEX sales_pk
   ON sales (prod_id, cust_id, promo_id, channel_id, time_id)
   NOLOGGING COMPUTE STATISTICS;

这可能需要一分钟。

将鼠标移到该图标上可以查看该图像
2.

通过执行 add_sales_pk.sql 脚本构建一个利用此索引的约束：

ALTER TABLE sales ADD CONSTRAINT sales_pk
PRIMARY KEY (prod_id, cust_id, promo_id, channel_id, time_id) USING INDEX;

将鼠标移到该图标上可以查看该图像
3.

请注意，如果使用全局索引定义约束，则必须对要交换的表也定义相同的约束！执行以下命令或 add_salestemp_pk.sql 脚本完成此任务。 

ALTER TABLE sales_mar_2002_temp 
  ADD CONSTRAINT sales_mar_2002_temp_pk
  PRIMARY KEY (prod_id, cust_id, promo_id, channel_id, time_id)
  DISABLE VALIDATE;


将鼠标移到该图标上可以查看该图像

返回列表

2.3 交换带有全局索引维护的分区

要演示分区维护操作对并发联机访问的影响，您需要两个会话，因此需要两个窗口进行演示。执行下面的操作之前，请先仔细阅读下一节的内容。

在窗口一中，执行以下步骤：

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下命令或 use_global_index.sql 脚本创建一个解释计划并查看信息。


EXPLAIN PLAN FOR
   SELECT /*+ INDEX(sales, sales_pk) */ count(*)
   FROM   sales
   WHERE  prod_id BETWEEN 100 AND 500;

set linesize 140
SELECT * 
FROM TABLE(dbms_xplan.display);

将鼠标移到该图标上可以查看该图像

验证了计划之后并且在您使用全局索引时，再次执行以下语句或 run_select.sql 文件。您可以使用 SQL*Plus 功能“r”或“/”重新运行上一条语句。 


SELECT /*+ INDEX(sales, sales_pk) */ count(*)
FROM   sales
WHERE  prod_id BETWEEN 100 AND 500;

将鼠标移到该图标上可以查看该图像

当您执行该查询时，请在窗口二中执行下面的步骤。您将看到，在进行分区维护操作时，使用全局索引对于并发查询访问没有任何影响。

该查询不会失败。您还将发现，在分区交换命令成功之后，查询结果会立即发生变化。Oracle 数据库服务器会保证这种情况下的读取一致性，并且可以提供最高效的分区表和索引维护操作，而不会对联机使用造成限制。

在窗口二中，执行以下步骤：

1.

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下查询或 exchange_partition_w_gim.sql 脚本。


ALTER TABLE sales 
  EXCHANGE PARTITION sales_mar_2002
  WITH TABLE sales_mar_2002_temp
  INCLUDING INDEXES UPDATE GLOBAL INDEXES;

将鼠标移到该图标上可以查看该图像

尽管这是一个 DDL 命令，但是因为全局索引是作为 PARTITION EXCHANGE 原子命令的一部分进行维护的，所以可能需要一些时间。
2.

您将看到，所有索引在分区维护操作之后仍然有效。执行以下命令或 show_sales_idx_status.sql 脚本。


SELECT   ui.index_name,
         DECODE(uip.status,null,ui.status,uip.status) status, 
         count(*) num_of_part
FROM     user_ind_partitions uip, user_indexes ui
WHERE    ui.index_name=uip.index_name(+)
AND      ui.table_name='SALES'
GROUP BY ui.index_name, 
         DECODE(uip.status,null,ui.status,uip.status);

将鼠标移到该图标上可以查看该图像
3.

查看已交换分区和独立表中的信息：执行以下命令或 count_mar_sales.sql 脚本。

SELECT COUNT(*) 
FROM   sales PARTITION (sales_mar_2002);


SELECT COUNT(*) 
FROM   sales_mar_2002_temp;

将鼠标移到该图标上可以查看该图像

您已经使用该命令将数千行数据添加到分区表中，而独立表目前为空。

再次交换新的 Q1-2001 销售数据。全局索引是作为 PARTITION EXCHANGE 命令的一部分维护的，因此不会影响任何联机使用。

接下来将研究没有全局索引维护的行为。

返回列表

2.4 交换不带全局索引维护的分区

要演示该功能，您需要两个窗口。执行下面的操作之前，请先仔细阅读下一节的内容。

在窗口一中，执行以下步骤：

在一个以 SH 用户登录的 SQL*Plus 会话中，执行以下查询或 use_global_index.sql 脚本。


explain plan for
  SELECT /*+ INDEX(sales, sales_pk) */ count(*)
  FROM  sales 
  WHERE prod_id BETWEEN 100 AND 500;

set linesize 140

SELECT * 
FROM   table(dbms_xplan.display);

SELECT /*+ INDEX(sales, sales_pk) */ count(*)
FROM   sales 
WHERE prod_id BETWEEN 100 AND 500;

在窗口二中执行下面的步骤，然后执行上面的查询看看二者的区别。

它在处理分区维护命令时会失败。

将鼠标移到该图标上可以查看该图像

在窗口二中，执行以下步骤：

1.

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下查询或 exchange_partition_wo_gim2.sql 脚本。


ALTER TABLE sales 
    EXCHANGE PARTITION sales_mar_2002 
    WITH TABLE sales_mar_2002_temp
    INCLUDING INDEXES;

将鼠标移到该图标上可以查看该图像
2.

现在，全局索引标记为不可使用。执行以下命令或 show_sales_idx_status.sql 脚本查看该信息。 

SELECT   ui.index_name,
         DECODE(uip.status,null,ui.status,uip.status) status, 
         count(*) num_of_part
FROM     user_ind_partitions uip, user_indexes ui
WHERE    ui.index_name=uip.index_name(+)
AND      ui.table_name='SALES'
GROUP BY ui.index_name, DECODE(uip.status,null,ui.status,uip.status);

将鼠标移到该图标上可以查看该图像

返回列表

2.5 删除全局索引并交换回初始状态

要清理您的环境，执行以下步骤：

在一个以 SH 用户身份登录的 SQL*Plus 会话中，执行以下语句或 cleanup_mod1.sql 脚本清理 OBE 特定的修改。


ALTER TABLE sales DROP CONSTRAINT sales_pk;
DROP INDEX sales_pk;
ALTER TABLE sales 
  EXCHANGE PARTITION sales_q1_1998 
  WITH TABLE sales_old_q1_1998 INCLUDING INDEXES;


ALTER TABLE sales DROP PARTITION sales_jan_2002;


ALTER TABLE sales DROP PARTITION sales_feb_2002;


ALTER TABLE sales DROP PARTITION sales_mar_2002;
DROP TABLE sales_mar_2002_temp;

DROP TABLE sales_delta;

DROP TABLE sales_old_q1_1998;
         
set serveroutput on
exec dw_handsOn.cleanup_modules
SELECT * FROM TABLE(dw_handsOn.verify_env)

返回列表

返回主题

总结

在本教程中，您学习了如何：

使用外部表加载数据
比较 SQL*Loader 与外部表的使用
执行表压缩来节省磁盘空间
利用 Oracle 分区执行滚动窗口操作

返回主题列表

将鼠标置于该图标上可以隐藏所有的屏幕截图。