杭州软件ORACLE面试题

数据库实战
有三张表：
table dep（dep_id number(2), //部门编号
dep_name varchar2(10)) //部门名称
table emp (emp_id number(6), //员工编号
emp_name varchar2(20), //员工名称
dep_id number(2)) //部门编号
table busi(emp_id number(6), //员工编号
busi_id number(10), //业务编号
busi_status char(2), //业务状态 1 正常 2 未生效 3 失效 4 删除
busi_datetime date, //业务时间
busi_desc varchar2(10)) //业务描述
alter table busi add constraint PK_busi primary key (busi_id);
问题：
1． 完成一个 procedure，输入 dep_id, 输出该部门的员工数。并对该部门所有员工的业务记录，按下列条件更新：
busi_datetime 早于 20010101 12:00:00 的，busi_status 置为 ‘删除’
busi_datetime 早于 20020101 12:00:00 的，busi_status 置为 ‘失效’，
busi_datetime 在20020101 12:00:00 和 20030101 12:00:00 之间的，busi_datetime 时间修改为 原 busi_datetime ＋ 5 天
假设输入的部门ID变量是input_dep_id，存储过程如下：
create or replace procedure proc_test (input_dep_id in number,emp_count out number)
as
begin
select count(*) into emp_count
from emp
where dep_id=input_dep_id;
update busi
set busi_status=’4′
where busi_datetime emp_id in (select emp_id from emp where dep_id=input_dep_id) ;
update busi
set busi_status='3'
where busi_datetime emp_id in (select emp_id from emp where dep_id=input_dep_id);

update busi
set busi_datetime=busi_datetime+5
where busi_datetime busi_datetime>to_date(’2002-01-01 12:00:00′,’yyyy-mm-dd hh24:mi:ss’) and
emp_id in (select emp_id from emp where dep_id=input_dep_id);

end;

2． Table busi 有 1000000 万记录。有一句 sql：
select emp_id,busi_id,busi_datetime,busi_desc from busi where emp_id= :value1 and busi_status = :value2
执行异常频繁，效率很差，请解释该 SQL 的执行计划。用什么方法可以提高该 SQL 的执行效率？
上面的执行计划将进行全表扫描。
如果更新不频繁，可以考虑建立：emp_id的b树索引。（如果几乎是静态表还可以考虑为 busi_status建立位图索引。）
3．业务量增加，table busi 数据量急剧增加，每天增加 20 万数据，又删除该表的历史数据 10 万，二个月后，上面的SQL执行效率极差。用什么方法可以提高该 SQL 的执行效率？
应该将表exp出来，drop掉原来的表，再imp进去。这应该是考察高水位和碎片的处理。