模式管理
通过显著加快并简化许多常见操作的新功能，更高效地管理数据库对象。
Oracle Database 11g 包括大量特性，这些特性不仅能够简化作业，在某些情况下，还可以将某些常见的耗时操作缩减为实际上的一行代码。在本文中，您将了解其中一些特性。
DDL Wait 选项
Jill 是 Acme Retailers 的 DBA，她尝试更改名为 SALES 的表，为其添加一列 TAX_CODE。这是很常见的任务；她执行以下 SQL 语句：
SQL> alter table sales add (tax_code varchar2(10));
但是，她收到了以下消息，而非“Table altered”之类的内容：
alter table sales add (tax_code varchar2(10))
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
错误消息描述的是：该表目前可能正由一个事务使用，因此要获得该表的独占锁定不太可能。当然，表的行不会永远锁定。当会话执行提交动作后，会释放对这些行的锁定，但在此之前，由于解除锁定期间很长，其他会话可能会更新表的其他行 — 这样，获得表的独占锁定的时机又消失了。在典型的业务环境中，以独占方式锁定表的窗口会定期打开，但 DBA 可能无法恰好在那时执行 alter 命令。
当然，Jill 也可以反复键入相同的命令，直到获得独占锁定或者失败（两者取其先）。
在 Oracle Database 11g 中，Jill 有更好的选择：DDL Wait 选项。她可以执行以下命令：
SQL> alter session set ddl_lock_timeout = 10;
Session altered.
现在，如果会话中的 DDL 语句没有获得独占锁定，也不会显示错误消息。相反，它将等待 10 秒钟。在这 10 秒钟内，它将不断重试 DDL 操作，直到成功或超时（两者取其先）。如果执行以下命令：
SQL> alter table sales add (tax_code varchar2(10));
该语句将挂起，并且不会显示错误消息。这样，Jill 就将重复尝试操作外包给了 Oracle Database 11g（就像电话通过程序重试繁忙号码），而不必反复尝试以获得难以捉摸的独占锁定可用时机。
现在，Jill 十分喜欢这个特性，并与其他所有 DBA 一起分享这个特性。由于在系统繁忙期间更改表时，每个人都遇到过相同的问题，他们都发现这个新特性非常有帮助。因此，Jill 很想知道是否可以将该行为设为默认行为，这样就不需要每次都执行 ALTER SESSION 语句？
是的，可以。如果您执行 ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 10，会话将在 DDL 操作期间自动等待该时间段。与任何其他 ALTER SYSTEM 语句一样，该语句可被 ALTER SESSION 语句覆盖。
添加具有默认值的列
尽管对该特性感到满意，Jill 还在考虑与第一个问题相关的另一个问题。她希望添加 TAX_CODE 列，但该列不能为空。显然，当她向非空表添加非空列时，还必须指定默认值“XX”。因此，她编写了以下 SQL：
alter table sales add tax_code varchar2(20) default 'XX' not null;
但她在这里停下了。SALES 表十分巨大，大约有 4 亿行。她知道，在执行该语句时，Oracle 会立即添加该列，但在将控制权返回给她之前将更新所有行中的值“XX”。更新 4 亿行不仅要花费很长时间，还要填充撤销段、生成大量重做任务并产生极大的性能开销。因此，Jill 必须在“安静时段”（即，停机期间）才能进行此更改。Oracle Database 11g 中有更好的方法吗？
当然有。上述语句将不会对表中的所有记录执行更新。尽管对于列值将自动设为“XX”的新记录来说，这不是问题，但当用户选择现有记录的该列时，这将返回 NULL，是吗？
实际上并非如此。当用户选择现有记录的列时，Oracle 将从数据字典获取默认值并将其返回给用户。这样，您就实现了一箭双雕：可以将一个新列定义为非空并具有默认值，同时不会导致任何重做和撤销开销。真棒！
虚拟列
Acme 的数据库包含了一个名为 SALES 的表，如前所示。该表的结构如下：
| SALES_ID |
NUMBER |
| CUST_ID |
NUMBER |
| SALES_AMT |
NUMBER |
某些用户希望添加一个名为 SALE_CATEGORY 的列，以便根据销售量和当前客户来标识销售的类型：LOW、MEDIUM、HIGH 和 ULTRA。该列将帮助他们识别相应动作的记录，并将记录路由给相关人员以进行处理。以下是列值的逻辑：
| 如果 sale_amt 大于： |
且 sale_amt 小于或等于： |
则 sale_category 为 |
| 0 |
1000 |
LOW |
| 10001 |
100000 |
MEDIUM |
| 100001 |
1000000 |
HIGH |
| 1000001 |
无限 |
ULTRA |
尽管该列是重要的业务需求，但开发团队不希望更改代码来创建必要的逻辑。当然，您可以在表中添加一个名为 sale_category 的新列，然后编写一个触发器以使用上述逻辑填充该列 — 一个相当简单的操作。但是，由于与触发器代码的上下文切换，可能会导致性能问题。
在 Oracle Database 11g 中，您不需要编写任何触发器代码。您只需添加一个虚拟列。虚拟列为您提供了灵活性，可以添加传达商业意识的列，而不增加任何复杂性或性能影响。
以下是创建该表的方法：
SQL> create table sales
2 (
3 sales_id number,
4 cust_id number,
5 sales_amt number,
6 sale_category varchar2(6)
7 generated always as
8 (
9 case
10 when sales_amt <= 10000 then 'LOW'
11 when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM'
12 when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
13 else 'ULTRA'
14 end
15 ) virtual
16 );
注意 6-7 行；该列被指定为“generated always as”，这意味着，列值在运行时生成，而非作为表的一部分进行存储。该子句的后面是在详细的 CASE 语句中计算值的方法。最后，在第 15 行，指定了“virtual”以加强这是一个虚拟列的事实。现在，如果您插入一些记录：
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,102,1500);
1 row created.
SQL>insert into sales (sales_id, cust_id, sales_amt) values (3,102,100000);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from sales;
SALES_ID CUST_ID SALES_AMT SALE_C
---------- ---------- ---------- ------
1 1 100 LOW
2 102 1500 LOW
3 102 100000 MEDIUM
3 rows selected.
虚拟列值都将照常填充。即使该列未存储，您也可以将其视为表的任何其他列，甚至可以在其上创建索引。
SQL> create index in_sales_cat on sales (sale_category);
Index created.
其结果将是一个基于函数的索引。
SQL> select index_type
2 from user_indexes
3 where index_name = 'IN_SALES_CAT';
INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL
SQL> select column_expression
2 from user_ind_expressions
3 where index_name = 'IN_SALES_CAT';
COLUMN_EXPRESSION
--------------------------------------------------------------------------------
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">=101 AND "CUS
T_ID"<=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">=101
AND "CUST_ID"<=200) THEN 'HIGH' ELSE 'ULTRA' END ELSE 'ULTRA' END
您甚至可以在该列上分区，如本系列的分区一文中所述。但是，您不能为该列输入值。如果您尝试输入值，很快就会收到错误消息：
insert into sales values (5,100,300,'HIGH','XX')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
不可见的索引
您常常感到疑惑，索引是否真的有利于用户的查询？它可能有利于一个查询，但会影响 10 个其他查询。索引肯定会对 INSERT 语句造成负面影响，也会执行潜在的删除和更新操作，这取决于 WHERE 条件是否在索引中包括该列。
一个相关的问题是，使用索引时，如果该索引被删除，会对查询性能造成什么影响？当然，您可以删除索引并查看对查询的影响，但说起来容易做起来难。索引实际上如何有助于查询？您必须重新定义索引，为此，需要进行重新创建。完全重新创建之后，就没有人能使用它了。重新创建索引也是一个昂贵的过程；它会占用许多有用的数据库资源。
您有办法创建一种对特定查询不可用同时又不会影响其他查询的索引吗？在 Oracle Database 11g 之前，不推荐使用 ALTER INDEX ...UNUSABLE，因为它会使表上的所有 DML 失败。但现在，您可以通过不可见的索引 精确使用该选项。简言之，您可以使索引对优化器“不可见”，这样就没有查询会使用它了。如果查询希望使用索引，则必须将其显式指定为提示。
下面是一个例子。假设有一个名为 RES 的表，并且您创建了如下所示的索引：
SQL> create index in_res_guest on res (guest_id);
分析完该表和索引后，如果您执行
SQL> select * from res where guest_id = 101;
将发现正在使用该索引：
Execution Plan
----------------------------------------------------------
Plan hash value: 1519600902
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RES | 1 | 28 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IN_RES_GUEST | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GUEST_ID"=101)
现在，使索引不可见：
SQL> alter index in_res_guest invisible;
Index altered.
现在，将显示以下内容：
SQL> select * from res where guest_id = 101
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3824022422
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 140 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| RES | 1 | 28 | 140 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("GUEST_ID"=101)
未使用索引。要使优化器再次使用索引，您必须在提示中显式命名索引：
SQL> select /*+ INDEX (res IN_RES_GUEST) */ res_id from res where guest_id = 101;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RES | 1 | 28 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IN_RES_GUEST | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
立刻！优化器再次使用了索引。
或者，您也可以设置会话级参数以使用不可见的索引：
SQL> alter session set optimizer_use_invisible_indexes = true;
如果您无法修改代码（如第三方应用程序中的代码），该特性将十分有用。创建索引时，可以在末尾追加子句 INVISIBLE，将索引构建为对优化器不可见。您还可以使用字典视图 USER_INDEXES 查看索引的当前设置。
SQL> select visibility
2 from user_indexes
3 where index_name = 'IN_RES_GUEST';
VISIBILITY
---------
INVISIBLE
注意，如果您重新构建该索引，则该索引将变为可见。您必须再次将其显式设为不可见。
那么，该索引到底对“什么”不可见？当然，它不会对用户不可见。它只是对优化器不可见。常规数据库操作（如插入、更新和删除）将继续更新索引。创建不可见索引时应注意这一点；由于该索引，您将不会再次查看性能，但同时您在 DML 操作期间可能会付出一些代价。
只读表
Robin 是 Acme 数据仓库系统的开发人员，他正在考虑一个典型问题。作为 ETL 流程的一部分，几个表的更新周期有所不同。在更新时，表会按业务规则对用户开放，即使用户不应修改它们。但是，取消用户对这些表的 DML 权限不是一个可选方法。
Robin 需要一个能够充当开关角色的功能，可以允许或不允许更新表。要实现这个听起来简单的操作，实际上相当困难。Robin 有哪些选择呢？
一个选择是，在表上创建一个触发器，以针对 INSERT、DELETE 和 UPDATE 引发异常。执行触发器会涉及上下文切换，这会影响性能。另一个选择是，创建一个虚拟专用数据库 (VPD) 策略，始终返回 false 字符串（如“1=2”）。如果表上的 VPD 策略使用该函数，它就会返回 FALSE，并且 DML 会失败。这可能比使用触发器具有更好的性能，但用户肯定不愿意使用，因为会看到“policy function returned error”之类的错误消息。
然而，在 Oracle Database 11g 中，您可以通过一个更好的方法来实现这个目标。您只需将表设为只读，如下所示：
SQL> alter table TRANS read only;
Table altered.
现在，当用户尝试执行如下所示的 DML 时：
SQL> delete trans;
Oracle Database 11
g 就会立即抛出错误：
delete trans
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TRANS"
错误消息不会将操作反映到代码中，但会有目的地传递消息，而无需触发器或 VPD 策略的开销。
如果您希望表可更新，则需要将其设为读/写，如下所示：
SQL> alter table trans read write;
Table altered.
现在，DML 就没有问题了：
SQL> update trans set amt = 1 where trans_id = 1;
1 row updated.
当表仅处于只读模式时，不允许执行 DML；但您可以执行所有 DDL 操作（创建索引、维护分区等）。因此，这个特性的一个非常有用的应用就是表维护。您可以将表设为只读，执行必要的 DDL，然后再将其设为读/写。
要查看表的状态，请在数据字典视图 dba_tables 中查找 read_only 列。
SQL> select read_only from user_tables where table_name = 'TRANS';
REA
---
NO
细粒度依赖跟踪
该特性可以通过一个示例得到很好的解释。考虑一个名为 TRANS 的表，按如下方式创建：
create table trans
(
trans_id number(10),
trans_amt number(12,2),
store_id number(2),
trans_type varchar2(1)
)
用户不应直接从该表中获取数据；他们通过视图 VW_TRANS 获取数据，该视图的创建如下所示：
create or replace view vw_trans
as
select trans_id, trans_amt
from trans;
现在，视图 VW_TRANS 依赖于表 TRANS。可以使用以下查询查看依赖性：
elect d.referenced_name, o.status
from user_dependencies d, user_objects o
where d.name = o.object_name
and d.name = 'VW_TRANS'
/
REFERENCED_NAME STATUS
---------------------------------------------------------------- -------
TRANS VALID
如上所示，视图 VW_TRANS 的状态为 VALID。然后，以某种方式（如添加一列）修改基础表：
alter table trans
add (trans_date date);
由于该视图所依赖的表发生了更改，因此该视图目前在 Oracle Database 10g 和早期版本中变为无效。现在，可以使用上述查询检查依赖性和状态：
REFERENCED_NAME STATUS
---------------------------------------------------------------- -------
TRANS INVALID
状态显示为 INVALID。因为基本上没有发生导致该视图永久无效的任何更改，所以可通过以下命令轻松地重新编译该视图：
alter view vw_trans compile;
那么，该视图为什么无效？答案很简单：当父对象更改时，会自动检查子对象，因为其中的某些内容也可能需要更改。但在该案例中，所作的更改是添加了一个新列。而该视图并未使用这个新列，为什么会无效呢？
在 Oracle Database 11g 中，该视图不会无效。当然，依赖性仍然设为 TRANS，但现在的状态不再是 INVALID，而是 VALID！
REFERENCED_NAME STATUS
---------------------------------------------------------------- -------
TRANS VALID
由于该视图并未无效，因此该视图的所有依赖对象（如另一个视图或程序包及过程）也不会无效。该行为大大提高了应用程序的可用性，从而增强了整个体系的总体可用性。进行某些数据库更改时，无需停止应用程序。
如果您更改了该视图中使用的某列（如 TRANS_AMT），该视图便会无效。这也是我们所希望的，因为所更改的列影响到了该视图。
但是视图和表也需要具有高可用性，因为您还需要将它们用于其他存储对象（像过程和程序包）。考虑如下所示的程序包：
create or replace package pkg_trans
is
procedure upd_trans_amt
(
p_trans_id trans.trans_id%type,
p_trans_amt trans.trans_amt%type
);
end;
/
create or replace package body pkg_trans
is
procedure upd_trans_amt
(
p_trans_id trans.trans_id%type,
p_trans_amt trans.trans_amt%type
) is
begin
update trans
set trans_amt = p_trans_amt
where trans_id = p_trans_id;
end;
end;
/
现在，假设您要编写一个可使事务量按指定百分比增加的函数。该函数使用程序包 pkg_trans。
create or replace function adjust
(
p_trans_id number,
p_percentage number
)
return boolean
is
l_new_trans_amt number(12);
begin
select trans_amt * (1 + p_percentage/100)
into l_new_trans_amt
from trans
where trans_id = p_trans_id;
pkg_trans.upd_trans_amt (
p_trans_id,
p_percentage
);
return TRUE;
exception
when OTHERS then
return FALSE;
end;
/
如果您查看该函数的状态，其应为 VALID：
select status
from user_objects
where object_name = 'ADJUST'
/
STATUS
-------
VALID
假设您要修改程序包 pkg_trans，即添加一个新过程以更新 vendor_name 列。下面是新程序包的定义：
create or replace package pkg_trans
is
procedure upd_trans_amt
(
p_trans_id trans.trans_id%type,
p_trans_amt trans.trans_amt%type
);
procedure upd_vendor_name
(
p_trans_id trans.trans_id%type,
p_vendor_name trans.vendor_name%type
);
end;
/
create or replace package body pkg_trans
is
procedure upd_trans_amt
(
p_trans_id trans.trans_id%type,
p_trans_amt trans.trans_amt%type
) is
begin
update trans
set trans_amt = p_trans_amt
where trans_id = p_trans_id;
end;
procedure upd_vendor_name
(
p_trans_id trans.trans_id%type,
p_vendor_name trans.vendor_name%type
) is
begin
update trans
set vendor_name = p_vendor_name
where trans_id = p_trans_id;
end;
end;
重新编译该程序包后，函数 ADJUST 的状态将会怎样呢？在 Oracle Database 10g 及更低版本中，该函数（为依赖函数）将无效，如以下状态所示：
STATUS
-------
INVALID
但在 Oracle Database 11g 中，如果可以通过 alter function ... recompile 轻松编译，则该函数不会无效：
STATUS
-------
VALID
这可极大提高可用性。函数 adjust 不会调用程序包 pkg_trans 的已更改部分，因此不需要使该函数无效，所以该函数在 Oracle Database 11g 中不会无效。
但情况并不总是这样。如果通过在该程序包末尾添加新子组件来修改程序包，如上述示例中所示，则依赖存储代码不会无效。如果在程序包开头添加子组件，如下所示：
create or replace package pkg_trans
is
procedure upd_vendor_name ...
procedure upd_trans_amt ...
end;
依赖存储代码 ADJUST 便会无效，与 Oracle Database 10g 及更低版本中一样。之所以出现此情况是因为，在现有过程之前插入的新过程会更改程序包中的存储区号，从而导致失效。在现有过程之后插入过程时，存储区号不会更改；只是添加了一个新的存储区号。
下面是减少与依赖性有关的失效的一些一般性指导原则。
而且，如果您之前使用了联机重定义，可能会看到重定义使某些依赖对象无效。Oracle Database 11g 中不会再出现这种情况。现在，如果对象引用的列具有相同的名称和类型，联机重定义不会使对象无效。如果在重定义期间删除了某列，但过程并未使用该列，则过程不会无效。
注意：在 Oracle Database 11g 第 2 版中，上述的这种失效行为有所不同。为了进行说明这一区别，我们在第 1 版数据库中创建一个表：
create table trans
(
trans_id number(10),
trans_amt number(12,2),
store_id number(2),
trans_type varchar2(1)
)
接下来，创建一个触发器：
create trigger tr_trans
before insert on trans
for each row
declare
l_store_id number(2);
begin
l_store_id := :new.store_id;
end;
/
查看触发器的状态：
SQL> select status from user_objects where object_name = 'TR_TRANS';
STATUS
-------
VALID
以某种方式修改表：
SQL> alter table trans add (col1 number);
Table altered.
现在，如果您查看触发器的状态：
SQL> select status from user_objects where object_name = 'TR_TRANS';
STATUS
-------
INVALID
在第 1 版中，触发器无效，即使它与表修改无关。但在第 2 版中，触发器
不会 无效，因为触发器不依赖于表的修改。（只是添加了一个新列；而现有触发器永远不会引用它。）
如果我们在第 2 版数据库上重新创建该实例并查看状态：
SQL> select status from user_objects where object_name = 'TR_TRANS';
STATUS
-------
VALID
1 row selected.
SQL> alter table trans add (col1 number);
Table altered.
SQL> select status from user_objects where object_name = 'TR_TRANS';
STATUS
-------
VALID
触发器仍有效。当然，如果进行的更改影响到触发器，则情况会有所不同。例如，
SQL> alter table trans modify (store_id number(3));
Table altered.
SQL> select status from user_objects where object_name = 'TR_TRANS';
STATUS
-------
INVALID
因此，在许多情况下，表的修改（如添加新列）不会使依赖对象无效，从而提高了数据库可用性。
虚拟列上的外键（仅限第 2 版）
在 11g 第 1 版中，我们看到引入了两个非常有用的重要特性。一个是前面介绍的虚拟列。另一个是基于引用完整性约束的分区（即所谓的 REF 分区），该特性允许您完全按照父表的方式对子表进行分区，即使分区列不在子表中。
这两个特性具有截然不同的好处：虚拟列允许在不造成存储列开销或不对应用程序进行更改就实现新列合并的情况下操作表；而 REF 分区允许您对表进行分区以在父子关系中利用分区修剪，而无需将这些列添加到子表中。但是，如果您要在多组相同的表上同时利用这两个特性，该怎么办呢？在 11g 第 2 版中，您可以轻松做到这一点。
下面就是一个例子：表 CUSTOMERS 具有两个虚拟列 CUST_ID 和 CATEGORY，前者也用作主键，后者是分区列。表 SALES 是 CUSTOMERS 表的子表，通过 CUST_ID 实现联接。我们来看一下实际的代码。
create table customers
(
cust_id number(14)
generated always as
(
DECODE(plan_id, 'MEDICAL',100, 'DENTAL',200, 'HOSPITAL ONLY',300, 999)
|| ssn ||
DECODE(member_type, 'SELF','01', 'SPOUSE','02', 'CHILD','03', '99')
) virtual,
cust_name varchar2(20),
ssn varchar(9),
plan_id varchar2(15),
member_type varchar2(10),
category varchar2(1)
generated always as
(case
when member_type = 'SELF' then
case when plan_id = 'MEDICAL' then 'A' else 'B' end
when member_type = 'SPOUSE' then
case when plan_id = 'MEDICAL' then 'B' else 'C' end
when member_type = 'CHILD' then 'C' else 'X'
end) virtual,
constraint pk_customers primary key (cust_id)
)
partition by list (category)
(
partition A values ('A'),
partition B values ('B'),
partition C values ('C'),
partition DEF values (default)
)
/
我们来插入一些行，注意不要为虚拟列分配具体值。我们希望生成虚拟列。
insert into insert into customers (cust_name, ssn, plan_id, member_type) values ('Jill','123456789','MEDICAL','SELF')
/
insert into customers (cust_name, ssn, plan_id, member_type) values
('John','123456789','MEDICAL','SPOUSE')
/
insert into customers (cust_name, ssn, plan_id, member_type) values
('Charlie','123456789','MEDICAL','CHILD')
/
虚拟列会正确返回数据吗？我们通过选择表中的行来检查：
select * from customers;
CUST_ID CUST_NAME SSN PLAN_ID MEMBER_TYP C
-------------- -------------------- --------- --------------- ---------- -
10012345678901 Jill 123456789 MEDICAL SELF A
10012345678902 John 123456789 MEDICAL SPOUSE B
10012345678903 Charlie 123456789 MEDICAL CHILD C
现在父表已经准就绪，我们来创建子表：
create table sales
(
sales_id number primary key,
cust_id number not null,
sales_amt number,
constraint fk_sales_01
foreign key (cust_id)
references customers
)
partition by reference (fk_sales_01)
/
在 11g 第 1 版中，该操作会失败，显示以下错误：
ERROR at line 6:
ORA-14663: reference partitioning parent key is not supported
在 11g 第 2 版中，该操作可实现；上述语句将创建子表。使用这些特性，您可以利用 Oracle 中两个非常有用特性的强大功能来构建更好的数据模型。
JDBC 中的 IPv6 格式设置（仅限第 2 版）
近几年，IP 寻址方式经历了重大改进。传统的寻址方式是一组以句点隔开的四个数字，如 192.168.1.100。该模式称为 IPv4，是 32 位寻址模式，所允许的 IP 地址数量非常有限。随着对 IP 地址的需求激增（这种需求不仅来自网站，而且也来自 IP 电话和 PDA 之类的设备），该模式会在很短的时间内用完 IP 地址。为解决此问题，引入了被称作 IPv6 的新一代 IP 寻址方式。它是 128 位寻址，能够处理更多的地址。
在 Oracle Database 11g 第 2 版中，您可以开始使用 IPv6 模式。为了弄清楚 IPv6 地址，下面是一个简单的示例命令（从 Linux 命令行执行）
# /sbin/ifconfig -a
eth0 Link encap:Ethernet HWaddr 00:19:21:BB:9A:A5
inet addr:10.14.104.253 Bcast:10.14.107.255 Mask:255.255.252.0
inet6 addr: fe80::219:21ff:febb:9aa5/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
… output truncated …
注意传统 IP 地址（以“inet addr”为头显示）：10.14.104.253。您可以使用 EZNAMES 寻址模式连接到默认 1521 端口上运行的数据库 D112D1：
SQL> connect arup/arup@10.14.104.253/D112D1
Connected.
注意 ifconfig 命令的输出。除了 IPv4 之外，您还可以看到 IPv6 寻址模式（以“inet6 addr”为头显示）：fe80::219:21ff:febb:9aa5。您可以使用该地址来代替 IPv4 地址。您必须将 IPv6 地址括在方括号内。
SQL> connect arup/arup@[fe80::219:21ff:febb:9aa5]/D112D1
Connected.
IPv6 支持不仅限于 SQL*Plus。您还可以使用 JDBC，如下所示：
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=[fe80::219:21ff:febb:9aa5]) (PORT=1526))
(CONNECT_DATA=(SERVICE_NAME=D112D1)))
当然，您既可以使用 IPv6，也可以使用 IPv4。确保将 IPv6 地址括在方括号内。
无段对象（仅限第 2 版）
考虑这样一种情况：第三方应用程序甚至您自己的应用程序部署数千个表。每个表至少是一个段，即使所有表均为空，它们也会在各自的段中至少占用一个区。其中多个表在某个时候可能已填充，也可能未填充，因此现在预先分配所有空间毫无意义。这会使整个数据库所占的空间变得很大，同时增加了应用程序安装时间。您可以延迟表的创建，但这会妨碍您正确部署相关对象（如过程和视图）。
在 11g 第 2 版中，提供了一个非常优秀的解决方案。在此版本中，不在创建表时默认创建段，而是在插入第一个数据时创建。我们来看一个示例：
SQL> create table test (col1 number);
Table created.
SQL> select bytes from user_segments where segment_name = 'TEST';
no rows selected
新创建的表没有段。现在，在表中插入一行：
SQL> insert into test values (1);
1 row created.
SQL> select bytes from user_segments where segment_name = 'TEST';
BYTES
----------
65536
创建了段，包含初始区。这是一个不可恢复的过程；即使回滚操作，该区也会存在。
SQL> roll
Rollback complete.
SQL> /
BYTES
----------
65536
但是，您可能不需要该默认行为 — 例如，您希望在创建表时就创建段。可以使用 deferred_segment_creation 参数控制该行为。要在创建表时创建段，请将该参数设为 FALSE。您甚至可以在会话级别控制该行为：
SQL> alter session set deferred_segment_creation = false;
Session altered.
SQL> create table test1 (col1 number);
Table created.
SQL> select bytes from user_segments where segment_name = 'TEST1';
BYTES
----------
65536
一旦创建了段，它便会保持在那里。如果您截断表，也不会删除段。注意，该特性不适用于 LOB 段，因为即使不创建表段，也会创建 LOB 段。
SQL> create table testlob (col1 clob);
Table created.
SQL> select segment_name from user_lobs where table_name = 'TESTLOB';
SEGMENT_NAME
------------------------------
SYS_LOB0000079350C00001$$
但是，如果您查看表段：
SQL> select bytes from user_segments where segment_name = 'TESTLOB';
no rows selected
您可以看到未创建表段。
在第 1 版中，延迟段创建仅适用于非分区对象。第 2 版解除了对分区表的限制，因此您现在也可以针对分区对象使用该功能。
如前所述，对各种对象类型仍有多种限制，但幸运的是，SQL 语言参考指南 中介绍了这些限制。
不可用索引不占用空间（仅限第 2 版）
现在，您了解了延迟段创建的强大功能，可能总想到处执行该行为。但是，如果您查看了上述文档，您会看到无法对索引声明延迟段创建属性。
对此的合理解释如下：作为辅助类结构 — 您在创建索引之前总是先需要一个表 — 索引只遵循表的属性。如果您使用延迟段创建创建一个空表，则索引也具有了延迟段创建功能。您插入第一个记录，结果是，您将获取同时适用于表和索引的段。
考虑以下示例：我们在包含数据的表上创建索引（或示例中禁用了延迟段创建）。
SQL> create index in_test on test (col1);
Index created.
SQL> select bytes from user_segments where segment_name = 'IN_TEST';
BYTES
----------
65536
创建了段。但是，要将该数据块存储在数据库的什么位置？您无法再访问该数据，无法使用它进行重新构建，除了占用了空间它无法用于任何操作 — 该数据是无用的。
11g 第 2 版中提供的一个特性在此证明非常有用（实际上利用了延迟段创建）。在此版本中，当您使索引不可用 时，对应的无用段会被删除：
SQL> alter index in_test unusable;
Index altered.
SQL> select bytes from user_segments where segment_name = 'IN_TEST';
no rows selected
当您重新构建索引（显然要开始使用它）时，会创建段：
SQL> alter index in_test rebuild;
Index altered.
SQL> select bytes from user_segments where segment_name = 'IN_TEST';
BYTES
----------
5536
该特性在分区中尤其有用，您可以选择有选择性地让索引不可用，从而节省空间。我们来看一个示例 — SH 模式中的 SALES 表。
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
SALES_1995 TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_1996 TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_H1_1997 TO_DATE(' 1997-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_H2_1997 TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q1_1998 TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_1998 TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_1998 TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q4_1998 TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q1_1999 TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_1999 TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_1999 TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q4_1999 TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q1_2000 TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_2000 TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_2000 TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q4_2000 TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q1_2001 TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_2001 TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_2001 TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q4_2001 TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q1_2002 TO_DATE(' 2002-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_2002 TO_DATE(' 2002-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_2002 TO_DATE(' 2002-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q4_2002 TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q1_2003 TO_DATE(' 2003-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_2003 TO_DATE(' 2003-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_2003 TO_DATE(' 2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q4_2003 TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
我们将这个表的索引等分为两个分区：
SQL> select index_name, partitioning_type
2 from user_part_indexes
3 where table_name = 'SALES';
INDEX_NAME PARTITIONING_TYPE
------------------------------ -----------------
SALES_PROD_BIX RANGE
SALES_CUST_BIX RANGE
SALES_TIME_BIX RANGE
SALES_CHANNEL_BIX RANGE
SALES_PROMO_BIX RANGE
选择一个具体索引，例如 SALES_CUST_BIX，检查其分区以及分区占用的空间：
SQL> select partition_name, bytes
2 from user_segments
3 where segment_name = 'SALES_CUST_BIX';
PARTITION_NAME BYTES
----------------------- -----------------
SALES_1995 65536
SALES_1996 65536
SALES_H1_1997 65536
SALES_H2_1997 65536
SALES_Q1_1998 327680
SALES_Q1_2000 327680
SALES_Q1_2001 327680
SALES_Q1_2002 65536
SALES_Q1_2003 65536
SALES_Q2_1998 262144
SALES_Q2_1999 327680
SALES_Q2_2000 327680
SALES_Q2_2001 327680
SALES_Q2_2002 65536
SALES_Q2_2003 65536
SALES_Q3_1998 327680
SALES_Q3_1999 327680
SALES_Q3_2000 327680
SALES_Q3_2001 327680
SALES_Q3_2002 65536
SALES_Q3_2003 65536
SALES_Q4_1998 327680
SALES_Q4_1999 327680
SALES_Q4_2000 327680
SALES_Q4_2001 327680
SALES_Q4_2002 65536
SALES_Q4_2003 65536
该索引有多个分区，一直到 1995 年。在典型的应用程序中，非常旧的数据（如 1995 年的数据）可能很少被访问；因此，即使曾经使用，该索引分区也可能很少使用。无论如何，它会占用大量空间。如果可以删除该分区，则空间将被回收。但是，您无法删除该分区，因为数据需要放在那里。
同样，在第 2 版中，解决方案也非常简单：使该索引分区不可用（这使该段消失），同时保持表分区完整：
SQL> alter index SALES_CUST_BIX modify partition SALES_1995 unusable;
Index altered.
SQL> select partition_name, bytes
2 from user_segments
3 where segment_name = 'SALES_CUST_BIX';
PARTITION_NAME BYTES
----------------------- -----------------
SALES_1996 65536
SALES_H1_1997 65536
… output truncated …
注意：不再有名为 SALES_1995 的段。该段被删除，因为其索引分区变得不可用。如果您针对几个索引中的多个较旧分区执行同样操作，您可能会回收大量空间，而不会丢失任何 数据（旧数据或新数据）。
您可能很想知道，如果您使分区不可用，而某人对该分区中的数据执行查询，将会发生什么情况。查询会失败吗？我们来看一个示例。
下面是访问可用索引分区的查询的优化计划：
set autot on explain
select count(1)
from sales
where cust_id = 611
and time_id between to_date('01-jan-1999','dd-mon-yyyy') and to_date('01-mar-1999','dd-mon-yyyy')
Output: Execution Plan
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE SINGLE | | 19 | 247 | 5 (0)| 00:00:01 | 9 | 9 |
| 3 | BITMAP CONVERSION COUNT | | 19 | 247 | 5 (0)| 00:00:01 | | |
| 4 | BITMAP AND | | | | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE| SALES_CUST_BIX | | | | | 9 | 9 |
| 6 | BITMAP MERGE | | | | | | | |
|* 7 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | | | | | 9 | 9 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("CUST_ID"=611)
7 - filter("CUST_ID"=611 AND "TIME_ID"<=TO_DATE(' 1999-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TIME_ID"<=TO_DATE(' 1999-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
现在，对已删除的 分区执行相同的查询：
select count(1)
from sales
where cust_id = 611
and time_id between to_date('01-jan-1995','dd-mon-yyyy') and to_date('01-mar-1995','dd-mon-yyyy')
Execution Plan
----------------------------------------------------------
Plan hash value: 642363238
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 36 (3)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE SINGLE| | 19 | 247 | 36 (3)| 00:00:01 | 9 | 9 |
|* 3 | TABLE ACCESS FULL | SALES | 19 | 247 | 36 (3)| 00:00:01 | 9 | 9 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("CUST_ID"=611 AND "TIME_ID"<=TO_DATE(' 1995-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TIME_ID"<=TO_DATE(' 1995-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
查询运行良好；优化器没有返回错误。由于索引分区不可用，因此查询仅恢复到全表扫描。
对于法律上要求长时间保存记录的那些数据库来说，这是一个非常有用的特性。它可帮助您维持所需最小的存储，从而节省空间和资金。
总结
正如您所看到的，这些特性不仅大大简化了以前费力的命令，在某些情况下，还可以根据日常操作的执行开发全新的方式。
在我的职业生涯中，经历了 Oracle 数据库功能的许多更改，其中有一些标志性特性重新定义了业务的完成过程。这里描述的特性就属于这一类特性。
免费注册
发表于 2011-12-22 08:54