是这两个SELECT语句执行时间长,如何优化.统计信息没有被删除.我想删除,但是没有删除对,现在暂时不删除.看看是不是可以优化,比如增加索引.现在我不知带有OR语句该如何优化.
select a.vys_code ,c.vywry_name,b.NSFXM_ID,b.nyf_ind,b.price,b.count,b.NTIMES,
a.dkf_time,e.dtk_time,decode(e.ncharge_id,2,'140(T)',a.Vks_id) as
vywks_code ,decode(e.ncharge_id,2,'特需门诊(T)',d.vks_name) vks_name,
e.NSCALE as sf_percent,e.VYX_FLAG ,e.dsk_time
from
c_prescription a ,c_prescription_detail b,r_emp c,r_dept d,c_receipt e where
a.vys_code=c.vywry_code and a.VZPSJ#=e.vSJ# and a.nyf_ind=b.nyf_ind and
a.VKS_ID = d.vks_id and ((e.dsk_time between to_date('2004-12-14 00:00:00',
'yyyy-mm-dd hh24:mi:ss') and to_date( '2004-12-14 23:59:59','yyyy-mm-dd
hh24:mi:ss')) or (e.dtk_time between to_date('2004-12-14 00:00:00',
'yyyy-mm-dd hh24:mi:ss') and to_date( '2004-12-14 23:59:59','yyyy-mm-dd
hh24:mi:ss'))) order by vywks_code,a.vys_code
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.46 0.49 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1598 309.174154491978.85 135092 86878 832 39906
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1600 309.644154491979.34 135092 86878 832 39906
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 112
Rows Row Source Operation
------- ---------------------------------------------------
39906 SORT ORDER BY (cr=86878 r=135092 w=69797 time=177956489 us)
39906 CONCATENATION (cr=86878 r=135092 w=69797 time=2293682736 us)
896 TABLE ACCESS BY INDEX ROWID C_PRESCRIPTION_DETAIL (cr=10459 r=2 w=0 time=32619744 us)
1073 NESTED LOOPS (cr=10311 r=1 w=0 time=32592317 us)
176 HASH JOIN (cr=9956 r=0 w=0 time=32546317 us)
176 HASH JOIN (cr=9947 r=0 w=0 time=32531990 us)
246 TABLE ACCESS FULL R_DEPT (cr=5 r=0 w=0 time=764 us)
176 HASH JOIN (cr=9942 r=0 w=0 time=32523373 us)
149 PARTITION RANGE ALL PARTITION: 1 27 (cr=168 r=0 w=0 time=3640 us)
149 TABLE ACCESS BY LOCAL INDEX ROWID C_RECEIPT PARTITION: 1 27 (cr=168 r=0 w=0 time=2806 us)
149 INDEX RANGE SCAN C_RECEIPT_IDX_002 PARTITION: 1 27 (cr=47 r=0 w=0 time=950 us)(object id 46319)
2433065 PARTITION RANGE ALL PARTITION: 1 26 (cr=9774 r=0 w=0 time=16601679 us)
2433065 TABLE ACCESS FULL C_PRESCRIPTION PARTITION: 1 26 (cr=9774 r=0 w=0 time=6591961 us)
503 TABLE ACCESS FULL R_EMP (cr=9 r=0 w=0 time=1770 us)
896 INDEX RANGE SCAN C_PRESCRIPTION_DETAIL_IDX_01 (cr=355 r=1 w=0 time=36907 us)(object id 46305)
39010 HASH JOIN (cr=76419 r=135090 w=69797 time=2260893167 us)
246 TABLE ACCESS FULL R_DEPT (cr=5 r=0 w=0 time=869 us)
39010 HASH JOIN (cr=76414 r=135090 w=69797 time=2260498779 us)
503 TABLE ACCESS FULL R_EMP (cr=9 r=0 w=0 time=1387 us)
39010 HASH JOIN (cr=76405 r=135090 w=69797 time=2260085676 us)
7568 TABLE ACCESS FULL C_RECEIPT PARTITION: 21 21 (cr=754 r=1 w=0 time=175486 us)
10849152 HASH JOIN (cr=75651 r=135089 w=69797 time=2189424493 us)
2433065 PARTITION RANGE ALL PARTITION: 1 26 (cr=9773 r=0 w=0 time=16783589 us)
2433065 TABLE ACCESS FULL C_PRESCRIPTION PARTITION: 1 26 (cr=9773 r=0 w=0 time=6788826 us)
10849152 TABLE ACCESS FULL C_PRESCRIPTION_DETAIL (cr=65878 r=65292 w=0 time=74547693 us)
********************************************************************************
select get_tc_qxksid(a.vks_id,c.NSFTC) as vQxKs_id,a.vks_name as vqxks_name,
e. NSFXM_ID,e.vSfxm_mc,c.VSFXM_ITEMNAME,c.price*c.count*c.NTIMES as
total_cash,g.vKS_Name ,d.dtk_time ,d.dsk_time ,d.NSCALE as sf_percent,
d.vYx_flag,d.NCHARGE_ID ,f.VYWRY_NAME
from
r_dept a, c_prescription b,c_receipt d,c_prescription_detail c,
c_charge_item e,r_emp f,r_dept g where a.vKs_id=c.QXKS_ID and b.VZPSJ#=
d.VSJ# and e.nSfxm_id=c.NSFXM_ID and b.vys_code=f.vywry_code and
f.vywks_code=g.vks_id and c.nyf_ind=b.nyf_ind and (d.dsk_time between
to_date('2004-12-14 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date(
'2004-12-14 23:59:59','yyyy-mm-dd hh24:mi:ss') or d.dtk_time between
to_date('2004-12-14 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date(
'2004-12-14 23:59:59','yyyy-mm-dd hh24:mi:ss'))order by a.vks_id,e.nSfxm_id,
c.NSFXM_ITEMNO,g.vks_id,f.VYWRY_CODE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 1.75 1.80 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1598 498.65 750.96 177437 91058 412 39906
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1600 500.40 752.77 177437 91061 412 39906
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 112
Rows Row Source Operation
------- ---------------------------------------------------
39906 SORT ORDER BY (cr=128006 r=177439 w=90135 time=754298003 us)
39906 HASH JOIN (cr=91041 r=177437 w=90135 time=533763416 us)
246 TABLE ACCESS FULL OBJ#(35729) (cr=5 r=0 w=0 time=772 us)
39906 HASH JOIN (cr=91036 r=177437 w=90135 time=533275825 us)
503 TABLE ACCESS FULL OBJ#(35732) (cr=9 r=0 w=0 time=1611 us)
39906 HASH JOIN (cr=91027 r=177437 w=90135 time=532764029 us)
246 TABLE ACCESS FULL OBJ#(35729) (cr=5 r=0 w=0 time=650 us)
39906 HASH JOIN (cr=91022 r=177437 w=90135 time=532268559 us)
17 TABLE ACCESS FULL OBJ#(35770) (cr=3 r=0 w=0 time=107 us)
39906 HASH JOIN (cr=91019 r=177437 w=90135 time=531778192 us)
7717 PARTITION RANGE ALL PARTITION: 1 27 (cr=15393 r=12377 w=0 time=18729991 us)
7717 TABLE ACCESS FULL OBJ#(35789) PARTITION: 1 27 (cr=15393 r=12377 w=0 time=18698302 us)
10849852 HASH JOIN (cr=75626 r=165060 w=90135 time=441776690 us)
2433213 PARTITION RANGE ALL PARTITION: 1 26 (cr=9782 r=9601 w=0 time=24233899 us)
2433213 TABLE ACCESS FULL OBJ#(35781) PARTITION: 1 26 (cr=9782 r=9601 w=0 time=14403215 us)
10849852 TABLE ACCESS FULL OBJ#(35788) (cr=65844 r=65324 w=0 time=79365305 us)
扫一扫
1750
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
