explain plan
set statement_id='exam_sheet_master3'
for
select count (*)
from exam_sheet_master , pats_in_hospital
where exam_sheet_master.pat_id =pats_in_hospital.patient_id
and exam_sheet_master.visit_id =pats_in_hospital.visit_id
and exam_sheet_master.REQ_DEPT_CODE =pats_in_hospital.dept_code
and exam_sheet_master.scheduled_date_time >= to_date('2004-10-18 16:20:36','yyyy-mm-dd 24hh:mi:ss')
and exam_sheet_master.scheduled_date_time <= to_date('2004-10-18 16:20:36','yyyy-mm-dd 24hh:mi:ss') + 3
and (PROCEDURE_STATUS = '30'
or PROCEDURE_STATUS = '20'
or PROCEDURE_STATUS = '25'
or PROCEDURE_STATUS = '40')
AND CANCEL_FLAG ='0'
AND REQ_DEPT_CODE ='030111'
and ward_exec ='0' ;
set statement_id='exam_sheet_master3'
for
select count (*)
from exam_sheet_master , pats_in_hospital
where exam_sheet_master.pat_id =pats_in_hospital.patient_id
and exam_sheet_master.visit_id =pats_in_hospital.visit_id
and exam_sheet_master.REQ_DEPT_CODE =pats_in_hospital.dept_code
and exam_sheet_master.scheduled_date_time >= to_date('2004-10-18 16:20:36','yyyy-mm-dd 24hh:mi:ss')
and exam_sheet_master.scheduled_date_time <= to_date('2004-10-18 16:20:36','yyyy-mm-dd 24hh:mi:ss') + 3
and (PROCEDURE_STATUS = '30'
or PROCEDURE_STATUS = '20'
or PROCEDURE_STATUS = '25'
or PROCEDURE_STATUS = '40')
AND CANCEL_FLAG ='0'
AND REQ_DEPT_CODE ='030111'
and ward_exec ='0' ;
or PROCEDURE_STATUS = '20'
or PROCEDURE_STATUS = '25'
or PROCEDURE_STATUS = '40') 改成用in 时间改成用between试试
呵呵,我就是从IN改过来的。
to lialin(阿林)
PROCEDURE_STATUS列上有一个索引,是不是有问题?表的部分结构如下:
CREATE INDEX IND_EXAM_PROCEDURE_STATUS ON EXAM_SHEET_MASTER
(PROCEDURE_STATUS)
LOGGING
TABLESPACE TSP_PACS
PCTFREE 20
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 512K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
or PROCEDURE_STATUS = '20'
or PROCEDURE_STATUS = '25'
or PROCEDURE_STATUS = '40') 改成
(trim(PROCEDURE_STATUS) in ('20','30','25','40'))如果PROCEDURE_STATUS在'20'到'40'之间只有'25'、'30',可以考虑改为
(PROCEDURE_STATUS between '20' and '40')