Oracle 11g新特性之分区

[收藏此页] [打印]

作者：Arup Nanda 2008-03-26

内容导航：

系统分区

第1页：通过分区进行优化第2页：系统分区

系统分区

尽管 Oracle 对此特性的实际用途期望很少，但我还是希望至少描述一下此特性，因为它真的很酷。

下面是一个很少见但并非不可能发生的用例：假设您有一个无法用任何逻辑方法分区的表。结果将是一个巨大的、单一的表，这会导致需要扩展的索引维护和其他操作等问题。

因此，开发人员提出一个解决方案：他们承诺，如果该表不能以某种方式分区，他们可以通过一种智能方式写入分区。这样，应用程序就可以控制特定记录进入哪个分区。DBA 只需定义分区。示例如下：

create table sales3
(
   sales_id   number,
   product_code number,
   state_code   number
)
partition by system
(
   partition p1 tablespace users,
   partition p2 tablespace users
);

注意，没有任何分区键或边界。因此，以物理方式将表分成两个段，但仍然是一个逻辑表。以这种方式定义之后，数据库会为该表创建两个段，而不是只有一个单一的表。您可以通过以下语句对其进行检查：

SQL> select partition_name
  2  from user_segments
  3  where segment_name = 'SALES3';

PARTITION_NAME
------------------------------
P1
P2

创建本地索引时，也是以相同的方式对其进行分区。

SQL> create index in_sales3_state on sales3 (state_code) local;

Index created.

SQL
Index created.
SQL> select partition_name
  2  from user_segments
  3  where segment_name = 'IN_SALES3_STATE';

PARTITION_NAME
------------------------------
P1
P2

可以通过检查 user_part_tables 来检查分区的类型：

SQL> select partitioning_type

2  from user_part_tables
  3  where table_name = 'SALES3';

PARTITION
---------
SYSTEM

这显示为 SYSTEM，当然，指示系统分区。需要注意的一点是，这些表类型的 high_value 列值为空。

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES3';

PARTITION_NAME HIGH_VALUE
-------------- ---------------------
P1
P2

下面是一个有趣的问题：如果没有分区键或方案（如范围、列表或散列），Oracle 如何知道传入的记录应进入哪个分区？答案是：Oracle 不知道。下面的示例显示了如果您向表中插入一条记录时将发生的情况：

SQL> insert into sales3 values (1,101,1);
insert into sales3 values (1,101,1)
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable
must be used for DMLs on tables partitioned by the
System method

分区边界是未知的，因此应用程序必须在插入数据的同时使用分区敏感语法来提供该信息。您需要将该语句重新编写为：

SQL> insert into sales3 partition (p1) values (1,101,1);

1 row created.

删除时，您不必提供分区敏感语法，但是记住，没有分区边界的概念。因此，当您发出如下语句时：

SQL> delete sales3 where state_code = 1;

Oracle 必须扫描所有分区，以查看该行所在的位置。为了避免出现这种情况，您应将其重新编写为：

SQL> delete sales3 partition (p1) where state_code = 1;

更新也是一样的。这限制了搜索该记录的分区范围。

如果一个表不能以任何逻辑方式进行分区，那么系统分区将提供惊人的优势。它们使您能够利用分区的优势，同时允许开发人员自由决定记录将进入哪个分区。

单个分区的表空间传输

在 Oracle 数据库的早期版本中，您能够传输表空间，并且稍后能将其插入不同数据库或同一个数据库中。该过程涉及复制数据文件，因此它是跨数据库传输数据的最快方法。然而直到现在，您仍然无法传输单个分区的表空间，然后再将其插回数据库中。在 Oracle 数据库 11g 中，您可以这么做。

假设您有一个名为 SALES5 的表，它包括了几个名为 CT、NY 等的分区。

SQL> select partition_name, tablespace_name
  2  from user_tab_partitions
  3  where table_name = 'SALES5';


PARTITION_NAME TABLESPACE_NAME
-------------- ---------------
CT             TS1
NY             TS2

现在，您可以使用如下命令传输 CT 分区：$ expdp tables=scott.sales5:ct transportable=always directory=data_pump_dir dumpfile=p_ct.dmp Export:Release 11.1.0.4.0 - Beta on Sunday, 10 June, 2007 16:05:40 Copyright (c) 2003, 2005, Oracle.All rights reserved. Username:/ as sysdba

Connected to: Oracle Database 11g Enterprise Edition
Release 11.1.0.4.0 - Beta With the Partitioning, Oracle
Label Security, OLAP, Data Miningand Oracle Database
Vault optionsStarting "SYS"."SYS_EXPORT_TABLE_01":
  /**//******** AS SYSDBA tables=scott.sales5:ct transportable=
  always directory=data_pump_dir dumpfile=p_ct.dmp
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/
END_PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully
loaded/unloaded
***********************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/oracle/admin/PROBE2/dpdump/p_ct.dmp
**********************************************************
Datafiles required for transportable tablespace TS1:
  /home/oracle/oradata/PROBE2/PROBE2/ts1_01.dbf
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed
at 16:05:55

现在，可以将 p_ct.dmp 和 ts1_01.dmp 这两个文件传递到其他系统，然后尝试将其插入到数据库中。出于学习目的，我们尝试插入到相同的数据库中。首先，您需要删除该表，然后删除 ts1 表空间。

SQL> drop table scott.sales5;

Table dropped.

SQLTable dropped. SQL> drop tablespace ts1 including contents;
Tablespace dropped.

现在，将表空间插入到数据库中。但这里有个小问题：sales5 表不再存在，您最初只导出了一个分区 (ct)，并未出整个表。那么如何只导入不存在的表的一个分区呢？

在 Oracle 数据库 11g 中，Data Pump 导入中的一个名为 partition_options 的新命令行选项可实现此操作。如果指定了值 departition，Data Pump 将根据导出的分区创建一个新表。该方法以这种方式“拆分”分区，因此它相应地被命名为取消分区。我们来看一下它的工作原理。

$ impdp partition_options=departition dumpfile=p_ct.dmp
   transport_datafiles='/home/oracle/oradata/PROBE2/PROBE2
/ts1_01.dbf'

Import: Release 11.1.0.4.0 - Beta on Sunday, 10 June,
 2007 21:58:08

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition
Release 11.1.0.4.0 - Beta  With the Partitioning, Oracle
 Label Security, OLAP, Data Mining
and Oracle Database Vault options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_04"
successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_04":  /
******** AS SYSDBA partition_options=
departition dumpfile=p_ct.dmp transport_datafiles=
/home/oracle/oradata/PROBE2/PROBE2/ts1_01.dbf
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_04"
successfully completed at 21:58:23

该 SQL 创建了一个名为 sales5_ct 的表，这个表只是由先前可传输的表空间导出的 SALES5 表的 ct 分区。如您见，表名是原始表名和分区名的组合。可以通过检查 DBA_SEGMENTS 视图来确认段的存在。

SQL> select segment_name

2  from dba_segments
  3  where tablespace_name = 'TS1';

SEGMENT_NAME
-----------------
SALES5_CT

您可以使用单个分区的可传输表空间特性将一个表的单个分区插入到其他数据库中。插入之后，您可能希望执行换分区操作，以便将其作为一个分区置于某个表上。

根据虚拟列分区

我们来看另一个常见问题。在名为 sales 的表中，您具有以下列：

SQL> desc sales
 Name                                      Null?    Type
 ----------------------------------------- -------- ------
 SALES_ID                                  NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 SALES_AMT                                          NUMBER

假设您希望按照某个允许您进行清除的方案对该表进行分区，并且基于销售额进行存档。以下是销售的四个类别：

如果 sale_amt 为	且 cust_id 为	则 sale_category 为
0-10000	任何内容	LOW
10001-100000	0-100	LOW
10001-100000	101-200	MEDIUM
10001-100000	>200	HIGH
100001-1000000	0-100	MEDIUM
100001-1000000	101-200	HIGH
100001-1000000	>200	ULTRA
>1000000	任何内容	ULTRA

如果 sale_amt 为且 cust_id 为则 sale_category 为

0-10000 任何内容 LOW
10001-100000 0-100 LOW
10001-100000 101-200 MEDIUM
10001-100000 >200 HIGH
100001-1000000 0-100 MEDIUM
100001-1000000 101-200 HIGH
100001-1000000 >200 ULTRA
>1000000 任何内容 ULTRA

您希望根据 sale_category 列对该表进行分区，但有一个问题：没有名为 sale_category 的列。这是您从 sale_amt 列派生的列。那么您如何对该表进行分区呢？

在 Oracle 的早期版本中，您可能已经在表中插入了名为 sale_category 的新列，并使用一个触发器用表中所示的辑填充该列。但是由于触发器，这个新列的存在可能会导致其他性能影响。

在 Oracle 数据库 11g 中，一个称为虚拟列的新特性使您能够创建一个并不存储在表中的列，但在运行时将计算列。您还可以根据该列进行分区。使用此特性，对该表进行分区就变得轻而易举。

create table sales
(
   sales_id      number,
   cust_id       number,
   sales_amt     number,
   sale_category varchar2(6)
   generated always as
   (
      case
         when sales_amt <= 10000
            then 'LOW'
         when sales_amt > 10000
            and sales_amt <= 100000
            then case
               when cust_id < 101 then 'LOW'
               when cust_id between 101 and 200 then 'MEDIUM'
               else 'MEDIUM'
            end
         when sales_amt > 100000
            and sales_amt <= 1000000
            then case
               when cust_id < 101 then 'MEDIUM'
               when cust_id between 101 and 200 then 'HIGH'
               else 'ULTRA'
            end
         else 'ULTRA'
      end
    ) virtual
)
partition by list (sale_category)
(
   partition p_low values ('LOW'),
   partition p_medium values ('MEDIUM'),
   partition p_high values ('HIGH'),
   partition p_ultra values ('ULTRA')
)

现在，如果您插入以下行：

SQL> insert into sales (sales_id,cust_id,sales_amt) values (1,1,100);

1 row created.

SQL> insert into sales (sales_id,cust_id,sales_amt) values (2,1,1500);

1 row created.

SQL> insert into sales (sales_id,cust_id,sales_amt) values (3,102,1500);

1 row created.

SQL> insert into sales (sales_id,cust_id,sales_amt) values (4,102,10000);

1 row created.

SQL> commit;

Commit complete.

注意，您未输入 sale_category 的值。现在，如果您检查 p_low 中的记录，将看到正确的记录：

SQL> select * from sales partition (p_low);

  SALES_ID    CUST_ID  SALES_AMT SALE_C
---------- ---------- ---------- ------  1 1 100 LOW

该记录放在相应的分区中。

根据虚拟列分区使您能够创建对业务重要的分区，即使列本身是不存在的。这里，您已经对虚拟列使用了一个非简单的计算方法，但它也可以如您希望的那样复杂。在这些情况下，根据虚拟列进行分区将变得更有价值。

Partition Advisor

在设计分区方案的过程中，您考虑的最主要的问题可能是选择分区方案和分区列的决定。这个任务最好留给进行泛工作负载分析的经验丰富的专业人员，即使是他们可能也无法做出正确的决定。您可以在 Oracle 数据库 11g 中通过一个名为 Partition Advisor 的新顾问工具获得帮助，该工具可以分析数据、访问模式并建议分析方案。您可以在这一部分中了解有关该工具的更多信息。

结论
分区一直是 Oracle 数据库 11g 中最有用的工具之一，它将变得越来越有用：引用分区允许您对同一数据库中的关表进行均值分区，即使所有子表中都不存在相应的列。

间隔分区包括了非常有用的即发即弃功能 — 您定义一个时间间隔，Oracle 会永远进行维护。
将组合分区扩展到范围-范围、列表-范围、列表-散列和列表-列表可以提供更好的分区选择以及可管理性。
Data Pump 现在允许您传输和插入单个分区，该特性在存档和保留方面肯定非常有用。
最后，您可以设计最可能的分区策略，通过根据虚拟列进行分区来模拟业务流。
“分而治之”的策略从未提供过如此多的选择。可以将它们设想为另一套可以割下火鸡身上最好部分的锋利刀子！

1 2

【内容导航】

第1页：通过分区进行优化

第2页： 系统分区

[责任编辑：ITPUB编辑]