在本教程中,您将了解如何使用 Oracle10g 进行高速数据加载以及如何利用 Oracle 分区进行滚动窗口操作。
大约 2 个小时
本教程包括下列主题:
![]() |
概述 |
![]() |
情景 |
![]() |
前提条件 |
![]() |
为销售历史模式实施模式更改 |
![]() |
使用外部表加载数据 |
![]() |
|
![]() |
利用表压缩节省磁盘空间、降低 TCO |
![]() |
利用 Oracle 分区执行滚动窗口操作 |
![]() |
总结 |
![]() |
相关信息 |
将光标置于此图标上可以加载和查看本教程的所有屏幕截图。(警告:因为此操作会同时加载所有屏幕截图,所以网速较慢时,响应时间可能会比较长。)
注意:此外,还可以在下面的步骤中将光标放在每个单独的图标上,从而仅加载和查看与该步骤相关的屏幕截图。
大多数情况下,向数据仓库提供数据的 OLTP(源)系统都没有直接连接到提取新数据的数据仓库系统。 通常,这些 OLTP 系统以外部文件的形式发送数据馈送。 必须将这些数据加载到数据仓库中(最好是并行地),以便充分利用现有资源。
另外,由于示例公司 MyCompany 的业务需求和磁盘空间的限制,只有过去三年的数据与分析需求相关。 这就意味着,在插入新数据时必须清除旧数据或者利用 Oracle 表压缩来释放磁盘空间。 这种所谓滚动窗口操作的维护是通过 Oracle 分区完成的。
为了将外部文件加载到它们的数据仓库中,MyCompany 使用了 Oracle10g 外部表特性。该特性允许将外部数据(如平面文件)在数据库中表现为像一个普通的数据库表一样。 由于可以使用 SQL 访问外部表,因此可以使用 SQL、PL/SQL 和 Java 的所有功能直接并行地查询外部文件。 通常在提取、转换和加载 (ETL) 过程中使用外部表,从而将数据转换(通过 SQL)与数据加载组合到一个步骤中。 外部表一个非常强大的特性,在 ETL 和其他处理平面文件的数据库环境中有很多可能的应用;它是使用 SQL*Loader 的替代方法。
并行执行可以显著减少大型数据库上通常与决策支持系统 (DSS) 和数据仓库相关的数据密集型操作的响应时间。 您还可以在某些类型的联机事务处理 (OLTP) 系统和混合系统上实施并行执行。 简单来说,并行就是将任务分割,并且多个进程同时执行工作的每个部分一,而不是在一个进程中由一个查询执行所有工作。 例如,四个进程处理一年中的四个不同季度,而不是一个进程独自处理所有四个季度。
数据仓库内部管理系统中一个非常重要的任务就是保持数据与 OLTP(源)系统中的各种更改同步。 另外,从分析角度而言,数据的寿命通常非常短,因此在加载新数据时必须将旧数据从目标系统中清除;这种操作通常称为滚动窗口操作。 应该尽快得完成该操作,且不应涉及任何数据仓库系统并发联机访问。
开始学习本教程之前,您应已经:
1. |
完成了教程 使用真正应用集群 (RAC) 和自动存储管理 (ASM) 在 Windows 上安装 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. |
在登录到 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'; 这些脚本是针对 Linux 系统创建的,并且假设上机操作讨论会已经解压缩到了驱动器 /home/oracle/wkdir 中。
|
创建外部表时,您要定义两部分信息:
1. |
用于在数据库中表示表的元数据信息 |
2. | 用于从外部文件提取数据的 HOW 访问参数定义 |
创建了这些元信息之后,无需初始加载就可以从数据库访问外部数据。
要创建外部表,执行下列步骤:
1. |
在登录到 SH 模式的 SQL*Plus 会话中,运行 @create_external_table.sql。 结果如下: @create_external_table REM 10gR1 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
|
无需任何其他操作,您现在就可以使用如下所示的 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”。
|
与 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'); |
Oracle10g 提供了在每个分区内无限制的并行直接路径 INSERT 功能。 可以使用执行计划确定是否将以并行方式完成 INSERT;您还可以在它刚从 SQL 缓存执行出来之后立即检查操作的执行计划,而根本不需要解释计划命令。
我们首先来看串行计划是什么样子的。 由于所有对象都不是并行定义的,因此您将获得串行执行,除非您 (a) 更改其中一个对象的默认并行度或者 (b) 使用一个提示。
在本教程的该步骤中,您将执行前面讨论的并行插入。 请注意,在插入之前,您不但要从外部表选择数据,而且作为选择的一部分还要执行聚合。 您要将转换与真正的加载过程相结合 - 这是只使用 SQL*Loader 无法完成的。
在 Oracle9i 之前,您要使用 SQL*Loader 完成该操作。 您先前执行的外部表方法是数据加载和转换的首选方法。 但是,为了演示使用外部表的好处,您将使用 SQL*Loader 来执行加载和转换数据的任务。
为了演示如何使用 SQL*Loader 加载和转换数据,您将执行下列步骤:
1. | |
2. | |
3. | 将该临时表加载到目标数据库中。 |
4. | 删除该临时表。 |
您需要一个临时表来加载数据,以便可以在第二步中在数据库中转换这些数据。
1. |
现在您将运行 sql 脚本 create_stage.sql 来创建临时表: @create_stage.sql CREATE TABLE sales_dec01 AS |
这些脚本是针对 Linux 系统创建的,并且假设上机操作讨论会已经解压缩到了 /tmp 中。 在 /home/oracle/wkdir 目录中,从操作系统命令行执行该操作。
现在您将通过执行下列步骤,使用 sales_dec01.ctl 将数据加载到该临时表中:
加载了外部数据(使数据库可访问它),您就可以执行转换了。
1. | 现在您将运行 SQL 来转换外部数据(这些数据已经临时存放在了数据库中),并通过执行 @load_stage_table.sql 将其插入到 sales 数据表中。 @load_stage_table.sql REM 10gR1 |
现在您可以删除或截断该临时表来释放它所占用的空间了。
1. | 现在您可以删除临时表信息: @drop_sales_dec01.sql> DROP TABLE sales_dec01; |
使用外部表处理这个简单的加载和转换过程,可以使您将加载和转换相结合,从而简化和加快了该过程。
另外,外部表不需要在数据库中临时存放最初的数据,因此节省了空间。 外部表与 SQL*Loader 相比,外部数据量越大,您节省的临时空间和处理时间就越多。
将十二月份的数据成功加载到 SALES 数据表的第四季度分区之后,该分区会经受很少甚至不会遇到 DML 操作了。 这样就使得表分区成为使用在 Oracle9i Release 2 中引入的 Oracle 的表压缩功能进行存储的最佳选择。随着企业需要的信息越来越多,存储在关系数据库中的数据也在不断增加。 保存大量数据的很大一部分成本是磁盘系统的成本以及管理这些数据所使用资源的成本。 Oracle9i Release 2 企业版引入了一种独特的方法来处理这种成本,即压缩存储在关系表中数据,且基本不会对该数据的查询时间造成负面影响,从而节省了可观的成本。
商用关系数据库系统还没有对在关系表中存储的数据大量使用压缩技术。 其中一个原因是,压缩时间和空间之间的权衡对于关系数据库来说并非总是那样地具有吸引力。 通常的压缩技术可能节省空间,但是需要付出数据查询时间大大延长的代价。 另外,很多标准技术甚至不能保证数据大小在压缩之后不会增加。
Oracle 数据库 10g 企业版提供了一种独特的压缩技术,它对大型数据仓库来说具有非常大的吸引力。 它在很多种方面都是那么独特。 它节省的磁盘空间要显著多于标准压缩算法,这是因为它针对关系数据进行了优化。 这种方法对压缩后数据的查询性能基本没有负面影响;对于访问大量数据的查询以及数据管理操作(如备份和恢复)它还可能产生明显的正面影响。 它可以确保压缩后的数据决不会比未压缩的数据大。
为了衡量表压缩的巨大优点,我们首先要确定近期的分区没有启用压缩以及它的大小。 您可以运行 part_before_compression.sql script 或复制下列 SQL 来完成该任务:
很多数据仓库都维护着一个数据的滚动窗口。 例如,该数据仓库存储着最近 12 个月的销售数据。 正如可以将新分区添加到 SALES 表中一样,也可以将旧分区快速地(且单独地)从 SALES 表中删除。 分区功能为这些操作提供了理想的框架。 与添加分区一样,删除分区同样具有两个优点(降低资源使用,对最终用户影响最小)。
要执行滚动窗口操作的步骤和了解 Oracle 数据库 10g 中的性能增强,执行下列步骤:
1. |
滚动窗口操作,第 1 部分: 准备一个包含新数据的独立表 |
2. |
滚动窗口操作,第 2 部分: 向事实表添加新数据 |
3. | 滚动窗口操作,第 3 部分: 从事实表删除旧数据 |
4. |
Oracle 数据库 10g 为本地索引维护提供的性能增强: 联机本地索引维护将最近的季度分区分割为月分区,这是 Oracle 数据库 10g 的一个新功能 |
5. | 使用 Oracle 的全局索引维护: 使用 Oracle9i 为全局索引维护提供的性能增强。 |
要执行滚动窗口操作,您需要创建和加载一个包含新数据的独立表,通过下列步骤可完成上述操作。 请注意,您将要使用已经定义的外部表,但是现在要指向不同的外部文件:
1.1 | |
1.2 | |
1.3 | 加载该表。 |
1.4 | |
1.5 |
在本节中,您要使用已经定义的那个外部表。 但是这次将使用另一个外部文件:第一季度销售数据。 因此,您必须修改该外部表的位置属性,指向这个新的数据文件。
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。 |
您将为新的第一季度销售数据创建一个空表。 该表稍后将被添加到已经存在分区的 SALES 表中。
1. | 运行该 SQL 创建该表: @create_stage_table.sql |
要加载该表,执行下列步骤:
1. | 在登录到 SH 模式的 SQL*Plus 会话中,执行下列语句: @load_stage_table2.sql INSERT /*+ APPEND */ INTO sales_delta 加载了 sales_delta 表中,收集该新创建表的统计信息。 |
2. |
在登录到 SH 模式的 SQL*Plus 会话中,执行下列语句: @gather_stat_stage_table.sql Rem gather statistics for the table |
因为您稍后要将该独立表与 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 ; 请注意,这些索引的统计信息将作为索引创建的一部分进行创建。 |
与 SALES 表的现有约束相同。
1. |
在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询: @create_constraints.sql set echo on Rem ***** ALTER TABLE channels MODIFY CONSTRAINT CHANNELS_PK RELY; |
执行滚动窗口操作的下一个任务是将新加载和新索引的数据添加到事实表中。 为此,执行下列步骤:
2.1 | |
2.2 | |
2.3 | 从分区进行选择以保证成功。 |
2.4 | 分割近期的分区以确保(业务)数据完整性。 |
您需要创建一个新的空分区。 您可以使用一个不同的上边界来创建新分区,也可以选择关键字 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); |
现在您要执行 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 |
现在您可以从这个新添加和交换的分区中选择,以体验插入几千行的速度有多快。
请注意,您必须添加到已分区事实表的数据越多,使用这个仅元数据操作节省的时间就越多,您将体验到最低甚至零用户影响的优点就越多。
您需要逻辑分区,如范围分区;散列分区不能用于这种非常常见的滚动窗口操作。
SALES 表中的所有索引都要进行维护,并且都可使用。
正如上面提到的那样,您决定将数据加载到一个没有固定上边界的分区以避免任何可能的错误。 为了识别任何可能的违例,您要分割近期的分区,因此创建两个分区,其中一个具有固定上边界(使用 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 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 |
执行滚动窗口操作的下一个任务是从事实表删除旧数据。 您只希望分析最近三年的最新数据。 因此,因为您添加了 Q1-2002,所以必须删除 Q1-1998 的数据。
如果没有范围分区,您则必须对该表执行 DML 操作。 使用分区,您则可以再次使用 PARTITION EXCHANGE 命令从事实表中删除这些数据。 像添加新数据一样,散列分区仍然无法在此处使用。
请注意,您并不是要删除数据。 而是要将 SALES 事实表中包含这些数据的分区与具有相同逻辑结构的空独立表进行交换(逻辑替换)。 然后,您可以将这些数据存档,或者删除交换后的分区,具体取决于您的业务需求。
3.1 | |
3.2 | |
3.3 | 为该表创建约束。 |
3.4 | 显示交换前分区中的数据。 |
3.5 | 将这个新的空表与现有的 Q1-1998 分区进行交换。 |
3.6 | 显示交换后分区中的数据。 |
您需要创建一个空表,以在其中存储旧的 1998 年的数据。
1. |
在登录到 SH 模式的 SQL*Plus 会话中,执行下列语句: @create_empty_sat.sql DROP TABLE sales_old_q1_1998; CREATE TABLE sales_old_q1_1998 NOLOGGING COMPRESS |
现在创建本地索引。
1. |
在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询: @create_ndx.sql set echo on Rem ***** |
现在创建约束。
1. |
在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询: @create_constraints_old.sql Rem ***10g*** |
执行交换之前,您希望看一眼将要在该分区中过时的 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); |
您现在需要将该空表与现有的 Q1-1998 分区进行交换。 为此,执行下列步骤:
1. |
在登录到 SH 模式的 SQL*Plus 会话中,执行下列 SQL 语句: @exchange_old_partition.sql Rem ***** 请注意,您可以使用一个 DROP PARTITION 来代替。 表 sales_old_q1_1998 现在存储了 1998 年第一季度的所有数据。您可以删除该表以从系统完全删除这些数据。 |
执行交换之后,您想看一眼分区中的数据。
1. |
在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询: @count_sales.sql PROMPT show the actual content of the partition to be aged out AFTER exchange 与执行 EXCHANGE 命令之前不同,该独立表现在存储着几千行数据,而 SALES 的相应分区为空。 |
2. |
本地索引未受影响。 @show_sales_idx_status.sql PROMPT INDEXES ARE MAINTAINED SELECT ui.index_name, DECODE(uip.status,null,ui.status,uip.status) |
从 Oracle10g 开始,执行所有分区维护操作均不会对其可用性产生任何影响。 本地索引维护允许您把分区表的本地索引当作任何原子分区维护操作的一部分来保持最新,从而在进行维护操作时,任何索引都可以使用且影响它的使用。
Oracle 扩展了分区维护操作的 SQL 语法来控制所有受影响本地索引结构的物理属性,如索引放置。
步骤:
4.1 | |
4.2 | |
4.3 | 清理。 |
成功加载了 2002 年第一季度的数据之后,DBA 小组认识到,由于不断变化的业务需求,查询模式发生了变化;现在很多业务用户不再重点关注季度分析,而是开始依赖于月报表和月分析。
为了满足这个变化的业务需求以及优化查询性能,DBA 小组决定利用 Oracle 分区,将最近一个季度的分区分割为月分区。 这个任务必须在不影响联机可用性的情况下完成。
本地索引维护的联机可用性不会明确显现;但是全局索引维护的联机可用性将显现,其工作方式与本地索引完全相同。
您需要创建一个新的空分区。 您可以使用一个不同的上边界来创建新分区,也可以选择关键字 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 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 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'); |
您还要将上一季度分区的剩余部分分割为一月分区和二月分区。 为了进行演示,在 SYSAUX 表空间中创建其中一个新分区,并显式命名一些索引。
1. |
在登录到 SH 模式的 SQL*Plus 会话中,执行下列 SQL 语句,分割剩余的分区,其中包括本地索引维护 split2_10g.sql: @split2_10g.sql ALTER TABLE sales SPLIT PARTITION sales_1_2_2002 |
2. |
您可以看到到新索引分区与表分区同处一处,并且索引分区命名沿袭了分区命名。 @see_split2.sql SELECT segment_name, partition_name, tablespace_name |
现在我们来进行清理,离开 SYSAUX 表空间,返回标准命名(约定)。
1. |
在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询: @cleanup_split_10g.sql PROMPT bring them back in shape (into 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.1 | |
5.2 | |
5.3 | 交换带有全局索引维护的分区,并体验它对全局索引的影响。 |
5.4 | 交换不带全局索引维护的分区,并体验它对全局索引的影响。 |
5.5 | 删除全局索引,并交换回来(清理)。 |
将三月份的数据交换到存在全局索引的分区表中。 首先,必须先构建必要的基础架构:
1. |
在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询: @prep4_global_index.sql 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 ; |
要演示全局索引功能,首先需要创建一个全局索引。 为此,执行下列步骤:
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 |
要演示分区维护操作对并发联机访问的影响,您需要两个会话,因此需要两个窗口。 执行下面的操作之前,请先仔细阅读下一节的内容。
在窗口一中,您将执行下列操作:
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 还会保证这种情况下的“读取一致性”,并且可以提供最高效的分区表和索引维护操作,而不会对联机使用造成限制。 |
在窗口二中,执行下列操作:
再次交换新的 Q1-2001 销售数据。 全局索引是作为 PARTITION EXCHANGE 命令的一部分维护的,因此不会影响任何联机使用。
现在,您将研究 Oracle9i 之前没有全局索引维护的古老行为。
要演示该功能,您需要两个窗口。 亲自执行下面的操作之前,请先仔细阅读下一节的内容。
在窗口一中,您将执行下列操作:
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 |
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); |
要清理您的环境,您需要执行下列操作:
1. | 在登录到 SH 模式的 SQL*Plus 会话中,执行下列查询,进行模块特定的修改: @cleanup_mod1.sql Rem drop unique index sales_pk. you do not need it, and it 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 分区执行滚动窗口操作 |
![]() |
要了解关于 Oracle 数据库 10g 的更多信息,请参考 OTN 网站上的其他 OBE。 | |
![]() |
要了解其他商务智能产品的详细信息,请参阅位于此处的 BI OBE。 | |
![]() |
有关本 OBE 教程的问题,请在 OBE 论坛上提问。 |