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

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

大约 2 个小时

主题

本教程包括下列主题:

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

注意:此外,还可以在下面的步骤中将光标放在每个单独的图标上,从而仅加载和查看与该步骤相关的屏幕截图。

概述

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

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

返回主题列表

外部表

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

并行执行

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

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

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

返回主题列表

开始学习本教程之前,您应已经:

1.

完成了教程 使用真正应用集群 (RAC) 和自动存储管理 (ASM) 在 Windows 上安装 Oracle 数据库 10g

或者

完成了教程 在 Linux 上安装 Oracle 数据库 10g

2.

下载 etl1.zip 并将其解压缩到工作目录中 (/home/oracle/wkdir)

3.

下载 setup_dwh.zip 并将其解压缩到工作目录中 (/home/oracle/wkdir)

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

有必要对现有销售历史模式对象进行一些更改以及为用户 SH 增加一些系统权限。实施这些更改的 SQL 文件为 modifySH_10g.sql。 该文件在 setup_dwh.zip 文件中提供。 要利用数据仓库教程的设置文件,执行下列步骤:

1.

打开一个终端窗口,执行以下命令:

cd /home/oracle/wkdir
sqlplus sh/sh
@modifySH_10g

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

返回主题列表

使用外部表加载数据

在该教程的本节中,您将要使用外部表将数据加载到数据仓库中(外部表是在 Oracle9i Release 1 中引入的)。

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

1.

创建必要的目录对象。

2. 创建外部表。
3. 从外部表中选择。
4. 提供外部表的并行访问。
5. 复习 Oracle 的并行插入功能。
6.

执行并行插入。

返回主题列表

1. 要创建必要的目录对象,执行下列步骤:

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

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 '/home/oracle/wkdir';
CREATE DIRECTORY log_dir AS '/tmp';

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

这些脚本是针对 Linux 系统创建的,并且假设上机操作讨论会已经解压缩到了驱动器 /home/oracle/wkdir 中。
请注意,由于安全方面的原因,该数据库不支持符号链接作为 DIRECTORY 对象。


返回列表

2. 创建该表:

创建外部表时,您要定义两部分信息:

1.

用于在数据库中表示表的元数据信息

2. 用于从外部文件提取数据的 HOW 访问参数定义

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

要创建外部表,执行下列步骤:

1.

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

@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 命令访问外部文件中的数据了:

1.

在登录到该 SH 模式的 SQL*Plus 会话中,执行下列查询(请注意,您可以运行脚本文件 select_et.sql。):

@select_et.sql

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

如果您正确复制了这些文件,则最大的 TIME_ID 为“31-DEC-01”。

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


返回列表

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 访问该外部表,并使用一个提示控制。 该 select 语句为:

@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.

现在,再次运行该会话统计,看看有什么差别。 您将看到并行会话统计信息已经发生了变化;它显示上一次查询已经并行化,它还显示了并行度。

@parallel_select_from_ET.sql

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

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

返回列表

5. 用于高速加载的并行插入:

Oracle10g 提供了在每个分区内无限制的并行直接路径 INSERT 功能。 可以使用执行计划确定是否将以并行方式完成 INSERT;您还可以在它刚从 SQL 缓存执行出来之后立即检查操作的执行计划,而根本不需要解释计划命令。

我们首先来看串行计划是什么样子的。 由于所有对象都不是并行定义的,因此您将获得串行执行,除非您 (a) 更改其中一个对象的默认并行度或者 (b) 使用一个提示。

1.

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

@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 操作之前必须禁用这些约束:

@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 中。

@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.

行回滚。 在教程的下一步中,将使用 SQL*Loader 插入同样的数据。

ROLLBACK;

5.

发出一个回滚命令之后,您需要再次启用约束。

@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 获得相同的功能,您需要两个过程步骤,这就表示整个过程所需的工作更多,而性能更低。

返回列表

返回主题列表

在 Oracle9i 之前,您要使用 SQL*Loader 完成该操作。 您先前执行的外部表方法是数据加载和转换的首选方法。 但是,为了演示使用外部表的好处,您将使用 SQL*Loader 来执行加载和转换数据的任务。

为了演示如何使用 SQL*Loader 加载和转换数据,您将执行下列步骤:

1.

创建一个临时表。

2.

使用 SQL*Loader 将数据加载到该临时表中。

3. 将该临时表加载到目标数据库中。
4. 删除该临时表。

1. 创建临时表:

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

1.

现在您将运行 sql 脚本 create_stage.sql 来创建临时表:


@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 系统创建的,并且假设上机操作讨论会已经解压缩到了 /tmp 中。 在 /home/oracle/wkdir 目录中,从操作系统命令行执行该操作。

现在您将通过执行下列步骤,使用 sales_dec01.ctl 将数据加载到该临时表中:

1.

打开一个新的终端窗口。执行下列命令:

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

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

注意: 当使用 SQL*Loader 连接时,您可能需要指定数据库别名。 例如,如果您的数据库别名为 o10g1,则使用以下语句启动 SQL*Loader:

sqlldr sh/sh@o10g1 control=sales_dec01.ctl direct=true

2.

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

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

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

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

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

返回列表

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

加载了外部数据(使数据库可访问它),您就可以执行转换了。

1.

现在您将运行 SQL 来转换外部数据(这些数据已经临时存放在了数据库中),并通过执行 @load_stage_table.sql 将其插入到 sales 数据表中。

@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. 删除临时表:

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

1.

现在您可以删除临时表信息:

@drop_sales_dec01.sql>

DROP TABLE sales_dec01;

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

返回列表

返回主题列表

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


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


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

1. 压缩近期的分区:

为了衡量表压缩的巨大优点,我们首先要确定近期的分区没有启用压缩以及它的大小。 您可以运行 part_before_compression.sql script 或复制下列 SQL 来完成该任务:

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列语句:

@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 会话中,执行下列语句:

@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.

现在我们看看新的压缩分区分配了多少空间,并将其与未压缩分区的大小进行比较:

@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 前清理、合并甚至聚合了数据的数据库环境中通常都会看到的“自然排序”。

返回主题列表

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

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

要执行滚动窗口操作的步骤和了解 Oracle 数据库 10g 中的性能增强,执行下列步骤:

1.

滚动窗口操作,第 1 部分准备一个包含新数据的独立表

2.

滚动窗口操作,第 2 部分 向事实表添加新数据

3. 滚动窗口操作,第 3 部分: 从事实表删除旧数据
4.

Oracle 数据库 10g 为本地索引维护提供的性能增强: 联机本地索引维护将最近的季度分区分割为月分区,这是 Oracle 数据库 10g 的一个新功能

5.

使用 Oracle 的全局索引维护: 使用 Oracle9i 为全局索引维护提供的性能增强。

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

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

1.1

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

1.2

为这些新的第一季度销售数据创建该表。

1.3 加载该表。
1.4

为该表创建位图索引。

1.5

为该表创建约束。

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

在本节中,您要使用已经定义的那个外部表。 但是这次将使用另一个外部文件:第一季度销售数据。 因此,您必须修改该外部表的位置属性,指向这个新的数据文件。

1.

首先,再次检查当前外部表的行数。 (请注意,您可以运行 select_et.sql 脚本文件。)

@select_et.sql

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

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

包含 2001 年 12 月所有销售交易的文件将显示 30-DEC-2001。您会看到在操作系统级别更改了外部文件之后,行数和 MAX(time_id) 将有所不同。

2.

更改 LOCATION 属性:

@alter_loc_attrib.sql

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;
        

行数和 maximum TIME_ID 均被更改。 如果外部表文件正确,则最大 maximum TIME_ID 为 30-MAR-2002。

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

返回列表

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

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

1.

运行该 SQL 创建该表:

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

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

加载了 sales_delta 表中,收集该新创建表的统计信息。

2.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列语句:

@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.

在创建任何位图索引之前,您需要修改新创建的表以按压缩表(实际上并不压缩任何数据)用 DBMS_STATS 处理同一内部属性。 该操作对于创建可用于交换到已包含压缩分区的分区表中的位图索引是必需的。

@alter_sales_delta.sql


ALTER TABLE sales_delta COMPRESS;
ALTER TABLE sales_delta NOCOMPRESS;

2.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列语句:

@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 表的现有约束相同。

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询:

@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,以识别任何可能的违例。 所有违反上边界的记录都将被“隔离”到一个附加分区中。

您需要创建一个新的空分区。 为此,执行下列步骤:

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询:

@create_partition_for_sales.sql

Rem *****
Rem current partitions
Rem *****
                 
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 命令,它根本不会触及任何真正的数据。 为此,执行下列步骤:

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询:

@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 从分区选择

现在您可以从这个新添加和交换的分区中选择,以体验插入几千行的速度有多快。

请注意,您必须添加到已分区事实表的数据越多,使用这个仅元数据操作节省的时间就越多,您将体验到最低甚至零用户影响的优点就越多。

您需要逻辑分区,如范围分区;散列分区不能用于这种非常常见的滚动窗口操作。

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

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

正如上面提到的那样,您决定将数据加载到一个没有固定上边界的分区以避免任何可能的错误。 为了识别任何可能的违例,您要分割近期的分区,因此创建两个分区,其中一个具有固定上边界(使用 DBMS_STATS 处理业务规则)。

从 Oracle9i Release 2 开始,Oracle 使用了一个增强的快速分割操作。在该操作中,RDBMS 检测两个新分区中的任一分区是否会在 SPLIT 操作之后变为空分区。 如果是,Oracle 则不会创建两个新段;它只会创建一个使用 DBMS_STATS 处理新空分区的段,并将使用现有段作为包含所有数据的新分区。

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

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询:

@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

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

返回列表

返回主题

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 年的数据。

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列语句:

@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 为该表创建位图索引

现在创建本地索引。

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询:

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

现在创建约束。

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询:

@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 年第一季度的数据。

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询:

@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 分区进行交换。 为此,执行下列步骤:

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列 SQL 语句:

@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

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

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

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

返回列表

返回主题

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

从 Oracle10g 开始,执行所有分区维护操作均不会对其可用性产生任何影响。 本地索引维护允许您把分区表的本地索引当作任何原子分区维护操作的一部分来保持最新,从而在进行维护操作时,任何索引都可以使用且影响它的使用。
Oracle 扩展了分区维护操作的 SQL 语法来控制所有受影响本地索引结构的物理属性,如索引放置。

步骤:

4.1

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

4.2

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

4.3 清理。

成功加载了 2002 年第一季度的数据之后,DBA 小组认识到,由于不断变化的业务需求,查询模式发生了变化;现在很多业务用户不再重点关注季度分析,而是开始依赖于月报表和月分析。
为了满足这个变化的业务需求以及优化查询性能,DBA 小组决定利用 Oracle 分区,将最近一个季度的分区分割为月分区。 这个任务必须在不影响联机可用性的情况下完成。

本地索引维护的联机可用性不会明确显现;但是全局索引维护的联机可用性将显现,其工作方式与本地索引完全相同。

4.1 分割近期的分区

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

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

您需要创建一个新的空分区。 为此,执行下列步骤:

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列 SQL 语句,将最近一个月(2002 年 3 月)从季度分区分割出来,其中包括本地索引维护 split1_10g.sql

@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

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

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

返回列表

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

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

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列 SQL 语句,分割剩余的分区,其中包括本地索引维护 split2_10g.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_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');

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

返回列表

4.3 清理

现在我们来进行清理,离开 SYSAUX 表空间,返回标准命名(约定)。

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询:

@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';

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

返回列表

返回主题

5. 使用 Oracle 的全局索引维护:

全局索引维护允许您把分区表的全局索引当作任何原子分区维护操作的一部分来保持最新,从而在进行维护操作时,任何索引都可以使用且不影响它的使用。

步骤:

5.1

准备进行全局索引维护。

5.2

构建全局索引。

5.3 交换带有全局索引维护的分区,并体验它对全局索引的影响
5.4 交换不带全局索引维护的分区,并体验它对全局索引的影响。
5.5 删除全局索引,并交换回来(清理)。

5.1 准备进行全局索引维护

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

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询:

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

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

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

返回列表

5.2 构建全局索引

要演示全局索引功能,首先需要创建一个全局索引。 为此,执行下列步骤:

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询:

@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


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


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;

返回列表

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

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

窗口一中,您将执行下列操作:

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询:

@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

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

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

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 命令的一部分维护的,因此不会影响任何联机使用。

现在,您将研究 Oracle9i 之前没有全局索引维护的古老行为。

返回列表

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

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

窗口一中,您将执行下列操作:

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询:

@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

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

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

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

返回列表

5.5 删除全局索引并交换回来

要清理您的环境,您需要执行下列操作:

1.

在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询,进行模块特定的修改:

@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 分区执行滚动窗口操作

返回主题列表

返回主题列表

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