OBE 主页 > 10gR2 单实例 > 商务智能

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

目的

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

所需时间

大约 2 个小时

主题

本教程包括下列主题：

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

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

利用表压缩节省磁盘空间并降低 TCO
使用 Oracle 分区执行滚动窗口操作
总结

查看屏幕截图

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

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

概述

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

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

返回主题列表

前提条件

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

1.

完成了教程在 Windows 上安装 Oracle 数据库 10g。
2.

下载 etl.zip 并将其解压缩到您的工作目录（即 c:\wkdir）中。

返回主题列表

案例

外部表

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

并行执行

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

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

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

返回主题列表

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

开始执行 OBE 的任务之前，您需要对现有的 Sales History 模式实施些许改动。需要增加一些必要的附加对象，并向用户 SH 授予额外的系统权限。用于应用这些更改的 SQL 文件是 modifySH_10gR2.sql。要使用数据仓库教程的设置文件，请执行下列步骤：

1.

启动一个 SQL*Plus 会话。选择开始 > 程序 > Oracle-OraDB10g_home > Application Development > SQL Plus。

（请注意：本教程假设您拥有 c:\wkdir 文件夹。如果没有，则需要创建此文件夹，并将 etl.zip 的内容解压缩到此文件夹中。在执行脚本时，可指定路经。）

 
2.

以 SH 用户的身份登录。输入 SH 作为 User Name，并输入 SH 作为 Password。然后单击 OK。


.

3.

从 SQL*Plus 会话运行 modifySH_10gR2.sql 脚本。 

@c:\wkdir\modifySH_10gR2.sql

获得的输出的底部应与以下图像匹配。

返回主题列表

使用外部表加载数据

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

为了演示如何创建和使用外部表，请执行下列步骤：

1.

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

执行并行插入。

返回主题列表

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

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

在登录到 SH 模式的 SQL*Plus 会话中，运行 create_directory.sql，或将以下 SQL 语句复制到 SQL*Plus 会话中：

@create_directory.sql

DROP DIRECTORY data_dir;
DROP DIRECTORY log_dir;
CREATE DIRECTORY data_dir AS 'c:\wkdir';
CREATE DIRECTORY log_dir AS 'c:\wkdir';

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

这些脚本是针对 Windows 系统创建的，并且假设上机操作研习会
已经解压缩到驱动器 C:\ 上。

返回列表

2. 创建外部表

创建外部表时，您要定义两部分信息：

1.

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

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

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

在登录到 SH 模式的 SQL*Plus 会话中运行 create_external_table.sql。结果如下：

@c:\wkdir\create_external_table
REM 10gR1
set echo on
Rem *****
Rem CREATE EXTERNAL TABLE
Rem *****
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 脚本文件）：

@c:\wkdir\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 会话中，运行以下查询，以查看当前并行会话的统计信息：

@c:\wkdir\parallel_select_from_ET.sql

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

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

 
2.

使用先前用过的查询以并行度 4 访问该外部表，并使用一个提示控制。该 select 语句为：

@c:\wkdir\parallel_select_from_ET_2.sql

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

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

rem ALTER TABLE sales_delta_XT PARALLEL 4;

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

 
3.

再次查看会话的统计信息，寻找与原统计信息的差别。您会看到该并行会话的统计信息发生了变化。它显示了最后一个并行化的查询，还显示了并行化的程度。 

@c:\wkdir\parallel_select_from_ET.sql

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

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

返回列表

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

Oracle10g 在每个分区内提供了无限制的并行直接路径 INSERT 功能。您可以使用执行计划来确定是否以并行方式执行 INSERT。另外，在操作运行之后，您即可在 SQL 缓存中检查该操作的执行计划，而无需解释计划命令。

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

1.

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

@c:\wkdir\show_serial_exec_plan.sql

Rem 10gR1
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 操作之前必须禁用这些约束：

@c:\wkdir\show_parallel_exec_plan.sql

Rem 10gR1
Rem *****
Rem SHOW PARALLEL EXECUTION PLAN
Rem *****
Rem   *****
PROMPT   need to set parallel DML explicitly
PROMPT   and to disable constraints !!!
Rem   *****
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;
Rem *****
PROMPT SHOW PARALLEL EXECUTION PLAN
Rem *****
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 或将以下语句复制到 SQL*Plus 会话 set timing on 中。

@c:\wkdir\parallel_insert_file.sql
Rem 10gR1
set timing on
COMMIT;
ALTER SESSION ENABLE PARALLEL DML;
Rem *****
Rem PARALLEL INSERT
Rem *****
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);

Rem   Alternatively, you can control the successful parallel DML with a V$ view
SELECT * 
FROM   v$pq_sesstat 
WHERE  statistic in ('DML Parallelized','Allocation Height');

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

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

 
2.

执行一次行 rollback 操作。在教程的下一步中，您将使用 SQL*Loader 插入同样的数据。 

ROLLBACK;

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

发出 rollback 命令之后，您需要再次启用约束。 

@c:\wkdir\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 与使用外部表进行简单加载和转换的比较

在 Oracle9i 之前，您需要使用 SQL*Loader 完成该操作。您先前执行的外部表方法是数据加载和转换的首选方法。但是，为了演示使用外部表的优点，您可以使用 SQL*Loader 执行加载和转换数据的任务。

为了演示如何使用 SQL*Loader 加载和转换数据，请执行下列步骤：

1.

创建一个临时表。
2.

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

1. 创建临时表

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

运行 sql 脚本 create_stage.sql，创建一个临时表：


@c:\wkdir\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 将数据加载到临时表中

请注意：这些脚本是针对 Windows 系统创建的，并且假设上机操作研习会已经解压缩到驱动器 C:\ 上。

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

1.

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

cd 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 文件。该文件位于 c:\wkdir 文件夹中。

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

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

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

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

返回列表

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

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

 

您将通过执行 load_stage_table.sql 脚本，运行 SQL 来转换外部数据（这些数据已经临时存放在数据库中），并将其插入 SALES 事实表中。

@c:\wkdir\load_stage_table.sql

REM 10gR1
Rem Load from staging table into target
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 脚本。

@c:\wkdir\drop_sales_dec01.sql  
DROP TABLE sales_dec01;  

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

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

返回列表

返回主题列表

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

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

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

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

1. 压缩近期的分区

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

1.

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

@c:\wkdir\part_before_compression.sql


PROMPT Space consumption before compression
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 会话中，执行下列语句： 

@c:\wkdir\compress_salesQ4_2001.sql

PROMPT now compress the partition including index maintenance (details later))
ALTER TABLE sales MOVE PARTITION sales_q4_2001 COMPRESS UPDATE INDEXES;

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

现在请查看新的压缩分区分配了多少空间，并将其与未压缩分区的大小进行比较： 

@c:\wkdir\part_after_compression.sql

PROMPT Space consumption AFTER compression

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 到 5。这就是说，如果压缩系数为 5，则以前一个分配了 500 GB 磁盘空间的表将减少到 100 GB。
SALES 事实表中的数据是人工生成的，通常都会看到的“自然排序”并没有在执行 INSERT 前清理、合并甚至聚合了数据的数据库环境中出现。

返回主题列表

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

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

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

要运行滚动窗口操作，请执行下列步骤：

1.

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

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

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

要执行滚动窗口操作，您需要创建和加载一个包含新数据的独立表，通过下列步骤可完成上述操作。请注意，您将要使用已经定义的外部表，但是现在要将其指向不同的外部文件：

1.1

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

1.2

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

为该表创建位图索引。
1.5

为该表创建约束。

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

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

1.

首先，检查当前外部表的行数。（请注意，您可以运行 select_et.sql 脚本文件。）

@c:\wkdir\select_et.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 的属性：

@c:\wkdir\alter_loc_attrib.sql

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


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

然后检查新数据。运行 select_et.sql 检查新数据：

@c:\wkdir\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 创建该表：

@c:\wkdir\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_tables.sql 加载该表：

@c:\wkdir\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;

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

加载了 SALES_DELTA 表后，收集新创建表的统计信息。

 
2.

在登录到 SH 模式的 SQL*Plus 会话中，运行脚本 gather_stat_stage_table.sql 收集该表的统计数据：

@c:\wkdir\gather_stat_stage_table.sql

Rem gather statistics for the table
exec dbms_stats.gather_table_stats('SH','sales_delta',estimate_percent=>20);

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

返回列表

1.4 为该表创建位图索引

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

1.

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

@c:\wkdir\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 表上创建位图索引。 

@c:\wkdir\create_static_bitmap_index.sql

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 为该表创建约束

与 SALES 表的现有约束相同。

 

在登录到 SH 模式的 SQL*Plus 会话中，运行脚本 create_constraints.sql 修改 SALES 表上的约束：

@c:\wkdir\create_constraints.sql
set echo on
Rem *****
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 表添加分区。 

@c:\wkdir\create_partition_for_sales_etl.sql

Rem *****
Rem current partitions
Rem *****

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

Rem *****
Rem create ADDITIONAL PARTITION on sales
Rem *****

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

Rem *****
Rem what is in the partition now?
Rem empty
Rem *****

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

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

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

返回列表

2.2 交换分区

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

 

在登录到 SH 模式的 SQL*Plus 会话中，运行脚本 exchange_partition_wo_gim.sql 以更改 SALES 表，使该分区能够进行交换：

@c:\wkdir\exchange_partition_wo_gim.sql

Rem *****
Rem EXCHANGE IT
Rem *****

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 脚本。

@c:\wkdir\select_count.sql

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

SELECT COUNT(*)
FROM   sales_delta;

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

以前插入这些行的速度有这么快吗？

 
2.

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

@c:\wkdir\show_sales_idx_status.sql

PROMPT   INDEXES ARE MAINTAINED

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 分割近期的分区以确保（业务）数据完整性

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

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

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

1.

在登录到 SH 模式的 SQL*Plus 会话中，运行脚本 fast_split.sql 更改 SALES 表，并查看索引状态。 

@c:\wkdir\fast_split_sales.sql

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);
PROMPT Let's control the count in the most recent partition. should be empty
SELECT COUNT(*) FROM sales PARTITION (sales_beyond_q1_2002);
PROMPT INDEXES ARE MAINTAINED
Rem since no data is moved with the SPLIT operation, the indexes are still valid
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);
PROMPT Now you can drop the empty overflow partition
ALTER TABLE sales DROP PARTITION sales_beyond_q1_2002;

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

 
2.

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

@c:\wkdir\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 年的数据： 

@c:\wkdir\create_empty_sat.sql

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 会话中，运行脚本 creat_ndx.sql 创建本地索引。 

@c:\wkdir\create_ndx.sql

set echo on

Rem   *****
PROMPT   CREATE LOCAL INDEXES
Rem   *****
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 修改并创建约束。 

@c:\wkdir\create_constraints_old.sql

Rem   ***10g***
REM   TO DO: cannot enable fk rely when pk is set to norely
Rem   *****

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 查看将从该分区中删除的数据：

@c:\wkdir\show_partition.sql

set echo on

Rem *****
Rem show the actual content of the partition to be aged out 
Rem BEFORE exchange
Rem *****
  
SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_1998);

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

返回列表

3.5 交换分区

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

 

在登录到 SH 模式的 SQL*Plus 会话中，运行脚本 exchange_old_partition.sql 交换分区：

@c:\wkdir\exchange_old_partition.sql

Rem   *****
PROMPT   EXCHANGE IT TO "REMOVE" OLD DATA
Rem   the exchange happens in a fraction of seconds, 
Rem   since it is only a DDL command
Rem   *****

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 查看分区中的数据：

@c:\wkdir\count_sales.sql

PROMPT   show the actual content of the partition to be aged out AFTER exchange
Rem   no more data in it
SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_1998);

SELECT COUNT(*) 
FROM   sales_old_q1_1998;

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

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

 
2.

本地索引未受影响。运行脚本 show_sales_idx_status.sql 查看索引信息。 

@c:\wkdir\show_sales_idx_status.sql

PROMPT   INDEXES ARE MAINTAINED
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 的新功能。之后，您将使用 Oracle9i 版本中引入的全局索引维护功能。

1.

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

使用 Oracle 的全局索引维护

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

从 Oracle10g 开始，执行所有分区维护操作均不会对其可用性产生任何影响。本地索引维护允许您把分区表的本地索引当作任何原子分区维护操作的一部分进行更新，从而在进行维护操作时，任何索引都可以使用并会影响它的使用。
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 完成此任务：

@c:\wkdir\split1_10g.sql

PROMPT Now leverage the new functionality and break down the existing
PROMPT quarter partition into monthly ones, leveraging new 10g functionality
Rem note that an index location is not specified, so they will be colocated with the partitions.
Rem the name will be inherited by the partition name
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 查看分区信息： 

@c:\wkdir\see_split.sql

COL segment_name format a25
COL partition_name format a25
COL tablespace_name format a25
Rem you will see that the newly created index segments 
Rem are co-located with the table partitions
Rem Also note the automatically derived naming
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 完成此任务：

@c:\wkdir\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_split2.sql 查看分区和区段信息： 

@c:\wkdir\see_split2.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 移动该分区并更新索引。 

@c:\wkdir\cleanup_split_10g.sql

PROMPT bring them back in shape (into tablespace EXAMPLE)

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))
;
PROMPT Let's move the last cowboy back to EXAMPLE ..
ALTER INDEX sales_time_bix 
  REBUILD PARTITION feb_02 TABLESPACE example;
PROMPT no index structures outside EXAMPLE anymore
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 准备进行全局索引维护。 

@c:\wkdir\prep4_global_index.sql

Rem PREPARE FOR GLOBAL INDEX MAINTENANCE
CREATE TABLE sales_mar_2002_temp 
  NOLOGGING AS SELECT * FROM sales 
    PARTITION (sales_MAR_2002);
ALTER TABLE sales TRUNCATE PARTITION sales_MAR_2002;
Rem control ...
Rem is empty
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 表上创建一个串联的唯一索引：

@c:\wkdir\create_global_index.sql

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 脚本构建一个利用此索引的约束：

@c:\wkdir\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 脚本可完成此任务：

@c:\wkdir\add_salestemp_pk


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 脚本创建一个解释计划并查看信息：

@c:\wkdir\use_global_index.sql

Rem STATEMENT WHICH USES GLOBAL INDEX - Window 1 

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);

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

验证了要使用全局索引的计划之后，不断地重复处理该语句。您可以使用 SQL*Plus 功能 "r" 返回最后一个语句，也可以运行 run_select.sql 文件。 

@c:\wkdir\run_select.sql

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：

@c:\wkdir\exchange_partition_w_gim.sql

set echo on
Rem *****
Rem EXCHANGE IT
Rem - with global index maintenance
Rem demonstrate influence of index on other queries in 
Rem second window
Rem *****

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：

@c:\wkdir\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：

@c:\wkdir\count_mar_sales.sql


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


SELECT COUNT(*) 
FROM   sales_mar_2002_temp;

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

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

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

接下来研究 Oracle9i 之前没有全局索引维护时的过程。

返回列表

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

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

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

 

在登录到 SH 模式的 SQL*Plus 会话中，执行以下查询。运行脚本 use_global_index.sql：

@c:\wkdir\use_global_index.sql

Rem STATEMENT WHICH USES GLOBAL INDEX - Window 1
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：

@c:\wkdir\exchange_partition_wo_gim2.sql

Rem EXCHANGE IT
Rem - without global index maintenance
Rem demonstrate influence of index on other queries in 
Rem second window
Rem *****

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

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

 
2.

现在，全局索引标记为不可使用。运行脚本 show_sales_idx_status.sql 查看此信息。 

@c:\wkdir\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：

@c:\wkdir\cleanup_mod1.sql

Rem   drop unique index sales_pk. you do not need it, and it
Rem   might cause some problems later on with DML operations
Rem   *****

ALTER TABLE sales DROP CONSTRAINT sales_pk;
DROP INDEX sales_pk;
Rem Cleanup - bring data back
Rem Necessary - that way you do not have to check for it at the end - top goal is no
Rem interference with the original SH schema ...
Rem - data for 1998
Rem - no data for 2001
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;
         
PROMPT original situation again
Rem SELECT COUNT(*) FROM sales PARTITION (sales_q1_2001);
Rem SELECT COUNT(*) FROM sales PARTITION (sales_q1_1998);
PROMPT just to be safe ... -cleanup module
set serveroutput on
exec dw_handsOn.cleanup_modules
PROMPT control whether the correction script was applied properly ..
SELECT * FROM TABLE(dw_handsOn.verify_env)

请确保正确重置了环境。一个不“干净”的环境可能会影响其他教程中的使用和运行时行为。

返回列表

返回主题

总结

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

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

返回主题列表

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