oracle 每日一题-引用约束(1)、用约束改善性能(2)

原始出处：
http://www.plsqlchallenge.com/
一
作者：ChrisSaxon

运行环境：SQLPLUS, SERVEROUTPUT已打开

你正在创建一个电子商务应用。它把订单的信息存在这个表：

create table plch_orders ( 
  order_id           int not null primary key, 
  payment_status_id  varchar2(10) not null,
  shipment_status_id varchar2(10) not null
);

shipment_status_id可能的值为：

IN-TRANSIT
DELIVERED
RETURNED

payment_status_id 的可能值为:

PAID
REFUNDED
CANCELLED

哪些选项扩展了这个schema,使得shipment_status_id 和 payment_status_id分别只能够保存上述列表中的那些值？

(A) 
create table plch_statuses (
  status_id   varchar2(10) not null primary key,
  description varchar2(100) not null
);

alter table plch_orders add constraint plch_pay_fk
  foreign key ( payment_status_id )
  references plch_statuses ( status_id );

alter table plch_orders add constraint plch_ship_fk
  foreign key ( shipment_status_id )
  references plch_statuses ( status_id );

insert into plch_statuses 
values ('IN-TRANSIT', 'Shipment has been dispatched');
insert into plch_statuses 
values ('DELIVERED', 'Customer has received the items');
insert into plch_statuses 
values ('RETURNED', 'We have received the items back');

insert into plch_statuses 
values ('PAID', 'We have received funds');
insert into plch_statuses 
values ('REFUNDED', 'The customer has been reimbursed');
insert into plch_statuses 
values ('CANCELLED', 'Payment has been cancelled');

commit;

(B) 
create table plch_payment_statuses (
  status_id   varchar2(10) not null primary key,
  description varchar2(100) not null
);

create table plch_shipment_statuses (
  status_id   varchar2(10) not null primary key,
  description varchar2(100) not null
);

alter table plch_orders add constraint plch_pay_fk
  foreign key ( payment_status_id )
  references plch_payment_statuses ( status_id );

alter table plch_orders add constraint plch_ship_fk
  foreign key ( shipment_status_id )
  references plch_shipment_statuses ( status_id );

insert into plch_shipment_statuses 
values ('IN-TRANSIT', 'Shipment has been dispatched');
insert into plch_shipment_statuses 
values ('DELIVERED', 'Customer has received the items');
insert into plch_shipment_statuses 
values ('RETURNED', 'We have received the items back');

insert into plch_payment_statuses 
values ('PAID', 'We have received funds');
insert into plch_payment_statuses 
values ('REFUNDED', 'The customer has been reimbursed');
insert into plch_payment_statuses 
values ('CANCELLED', 'Payment has been cancelled');

commit;

(C) 
create table plch_statuses (
  status_id   varchar2(10) not null unique,
  status_type varchar2(10) not null
    check ( status_type in ( 'PAYMENT', 'SHIPMENT' ) ),
  description varchar2(100) not null,
  primary key ( status_type, status_id )
);

alter table plch_orders add (
  payment_status_type  varchar2(10) not null,
  shipment_status_type varchar2(10) not null
);

alter table plch_orders add constraint plch_pay_fk
  foreign key ( payment_status_type, payment_status_id )
  references plch_statuses ( status_type, status_id );

alter table plch_orders add constraint plch_ship_fk
  foreign key ( shipment_status_type, shipment_status_id )
  references plch_statuses ( status_type, status_id );

insert into plch_statuses 
values ('IN-TRANSIT', 'SHIPMENT', 'Shipment has been dispatched');
insert into plch_statuses 
values ('DELIVERED', 'SHIPMENT', 'Customer has received the items');
insert into plch_statuses 
values ('RETURNED', 'SHIPMENT', 'We have received the items back');

insert into plch_statuses 
values ('PAID', 'PAYMENT', 'We have received funds');
insert into plch_statuses 
values ('REFUNDED', 'PAYMENT', 'The customer has been reimbursed');
insert into plch_statuses 
values ('CANCELLED', 'PAYMENT', 'Payment has been cancelled');

commit;


(D) 
create table plch_statuses (
  status_id   varchar2(10) not null unique,
  status_type varchar2(10) not null
    check ( status_type in ( 'PAYMENT', 'SHIPMENT' ) ),
  description varchar2(100) not null,
  primary key ( status_type, status_id )
);

alter table plch_orders add (
  payment_status_type  varchar2(10) as ('PAYMENT'),
  shipment_status_type varchar2(10) as ('SHIPMENT')
);

alter table plch_orders add constraint plch_pay_fk
  foreign key ( payment_status_type, payment_status_id )
  references plch_statuses ( status_type, status_id );

alter table plch_orders add constraint plch_ship_fk
  foreign key ( shipment_status_type, shipment_status_id )
  references plch_statuses ( status_type, status_id );

insert into plch_statuses 
values ('IN-TRANSIT', 'SHIPMENT', 'Shipment has been dispatched');
insert into plch_statuses 
values ('DELIVERED', 'SHIPMENT', 'Customer has received the items');
insert into plch_statuses 
values ('RETURNED', 'SHIPMENT', 'We have received the items back');

insert into plch_statuses 
values ('PAID', 'PAYMENT', 'We have received funds');
insert into plch_statuses 
values ('REFUNDED', 'PAYMENT', 'The customer has been reimbursed');
insert into plch_statuses 
values ('CANCELLED', 'PAYMENT', 'Payment has been cancelled');

commit;


(E) 
alter table plch_orders add constraint plch_ship_ck check ( 
  payment_status_id in ('PAID', 'REFUNDED', 'CANCELLED')
);

alter table plch_orders add constraint plch_pay_ck check ( 
  shipment_status_id in ('IN-TRANSIT', 'DELIVERED', 'RETURNED')

);

A:　两个外键都指向PLCH_statuses。所以它可以在支付状态和运输状态列都保存同样的值！
B: （推荐）
这会把支付状态和运输状态保存在独立的表中。两个列会有外键指向适当的表。所以你只能够把运输状态存在shipment_status_id，把支付状态存在payment_status_id。
C: 这会把类型列添加到选项A的状态表。但是在PLCH_orders没有校验来确保你只能够在payment_status_type列保存PAYMENT类型的状态，在shipment_status_type保存SHIPMENT类型的状态。所以你仍然会在订单表保存不正确的值！
D: 这会把 *_type属性作为虚拟列添加到plch_orders。这样样会确保它们永远都有正确值。这可以避免选项C的问题，在选项C中你可以在payment_status_id列保存一个支付状态，反之亦然。

E: 检查约束确保你在每个列中只能保存提供列表中的值。

二

作者：        ChrisSaxon

运行环境：SQLPLUS, SERVEROUTPUT已打开

你正在建立一个员工工作时间跟踪应用。它在这个模式中存储着详细信息：

create table plch_employees (
  employee_id   int not null 
    constraint plch_employee_pk primary key ,
  employee_name varchar2(100) not null
);

create table plch_timesheets (
  employee_id        int not null,
  timesheet_datetime date not null,
  project_id         int not null,
  minutes_worked     int not null,
  constraint plch_timesheet_pk primary key ( 
    employee_id, timesheet_datetime 
  )
);

create or replace view plch_employee_timesheets_v as 
  select em.employee_name, ti.employee_id, 
         ti.timesheet_datetime, ti.project_id, ti.minutes_worked 
  from   plch_employees em
  join   plch_timesheets ti
  on     em.employee_id = ti.employee_id;

员工经常用如下的查询来检查他们记录了多少个小时的工作时间：

select employee_id, sum(minutes_worked)
from   plch_employee_timesheets_v
group  by employee_id;

这开始变慢了，因此你老板让你对它进行优化。你的分析显示执行计划正在访问plch_timesheets表以及plch_employees上的主键索引。例如：

------------------------------------------------
| Id  | Operation           | Name             |
------------------------------------------------
|   0 | SELECT STATEMENT    |                  |
|   1 |  HASH GROUP BY      |                  |
|   2 |   NESTED LOOPS      |                  |
|   3 |    TABLE ACCESS FULL| PLCH_TIMESHEETS  |
|   4 |    INDEX UNIQUE SCAN| PLCH_EMPLOYEE_PK |
------------------------------------------------

哪些选项修改了模式，使得查询仅仅访问PLCH_timesheets表？


例如执行计划：
----------------------------------------------
| Id  | Operation          | Name            |
----------------------------------------------
|   0 | SELECT STATEMENT   |                 |
|   1 |  HASH GROUP BY     |                 |
|   2 |   TABLE ACCESS FULL| PLCH_TIMESHEETS |
----------------------------------------------

(A) 
alter table plch_timesheets add constraint 
  plch_time_employee_fk foreign key ( employee_id ) 
  references plch_employees ( employee_id );

(B) 
alter table plch_timesheets add constraint 
  plch_time_employee_fk foreign key ( employee_id ) 
  references plch_employees ( employee_id ) novalidate ;

(C) 
alter table plch_timesheets add constraint 
  plch_time_employee_fk foreign key ( employee_id ) 
  references plch_employees ( employee_id ) rely novalidate ;


(D) 
alter table plch_timesheets add constraint 
  plch_time_employee_fk foreign key ( employee_id ) 
  references plch_employees ( employee_id ) enable validate ;

(E) 
alter table plch_timesheets add constraint 
  plch_time_employee_fk foreign key ( employee_id ) 
  references plch_employees ( employee_id ) disable;

A: 这会添加一个从plch_timesheets -> plch_employees的外键。它使得优化器知道这两表的连接会显示plch_timesheets的每行数据最多一次。在plch_timesheets.employee_id上还有一个非空约束。所以plch_timesheets里面的每一行都会指向plch_employees的一行。
查询仅仅选择了plch_timesheets的列。从上面讨论的外键以及非空约束，优化器知道查询会返回表中的每一行的一个副本。所以它可以从计划中去除plch_employees而不会丢失任何信息。

B: 当你指定了novalidate(不检验)选项，Oracle数据库不会检查已有数据。所以可能会有些数据违反约束。这样的话优化器就不能够再保证1:M的关系。所以它会在计划中访问plch_employee_pk。

注意，即使你创建约束的时候表是空的，结果也是如此！

C: Rely(可信)会告诉优化器，它可以相信数据满足约束条件。这甚至在约束被屏蔽或者未检验的情况下也是起作用的。
但是，为了创建一个rely外键，它必须指向一个rely的主键。缺省是norely。所以这个语句会报错：
"ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY" 

D:（推荐）
"Enable validate" 是你创建约束的缺省选项。所以这和A是等价的。

E:这会以屏蔽方式创建约束。所以表中的所有数据都可能违反约束！因此优化器不能够从使用外键的数据推导出任何事情。它仍然会在计划中访问plch_employee_pk