--此语句查询出来的结果大概会有900多笔，
SELECT b.* FROM spe_register a,v_spe_his_sheet b
WHERE trunc(a.checkin_time) >= to_date('2008-11-01','yyyy-MM-dd') AND trunc(a.checkin_time) <= to_date('2008-11-14','yyyy-MM-dd')
--AND a.register_no = 295
AND a.register_no = b.register_no AND b.ext_system = 1而gdc.v_lis_report 中有一百万左右的数据，用LEFT JOIN连接，我把语句写成了如下格式，
--得到某个人的检验结果(LIS)
SELECT c.observation_sn,d.* FROM
(
SELECT b.* FROM spe_register a,v_spe_his_sheet b
WHERE trunc(a.checkin_time) >= to_date('2008-11-01','yyyy-MM-dd') AND trunc(a.checkin_time) <= to_date('2008-11-14','yyyy-MM-dd')
--AND a.register_no = 295
AND a.register_no = b.register_no AND b.ext_system = 1
) c
left JOIN gdc.v_lis_report d
ON d.observation_sn IN (c.observation_sn) AND c.observation_sn = d.observation_sn
可是，执行不动，如果将--AND a.register_no = 295这个条件给加上，大概需要5S左右，大家帮我看看，我的语句有没有什么问题
SELECT c.observation_sn, d.*
FROM (SELECT b.*
FROM spe_register a, v_spe_his_sheet b
WHERE a.checkin_time >= to_date('2008-11-01', 'yyyy-MM-dd')
AND a.checkin_time < to_date('2008-11-15', 'yyyy-MM-dd')
--AND a.register_no = 295
AND a.register_no = b.register_no
AND b.ext_system = 1) c,
gdc.v_lis_report d
where c.observation_sn = d.observation_sn(+)
and exists
(select 1 from c, d where c.observation_sn = d.observation_sn);;
还有你那a.checkin_time不需要用trunc，先关的关键字要建索引。查询会更快些
这种写法是很有问题的,除非在相应字段上建立函数索引,否则不能利用索引
如果再不行,列出该语句的执行计划来SELECT b.observation_sn,d.*
FROM spe_register a
JOIN v_spe_his_sheet b ON a.register_no = b.register_no AND b.ext_system = 1
LEFT JOIN gdc.v_lis_report d ON b.observation_sn = d.observation_sn
WHERE a.checkin_time >= to_date('2008-11-01','yyyy-MM-dd')
AND a.checkin_time < to_date('2008-11-15','yyyy-MM-dd')