首页
技术基础
备份恢复
SQL优化
诊断案例
BLOG
留言板
服务
生活
网摘
阅读
下载
墨天轮
English
eygle.com - 留言簿 - 发表留言 - Powered by eYgLe.Com
返回留言簿
删 除 留 言
管理密码:
请输入管理密码, 只有版主才能删除留言.
编 辑 留 言
管理密码:
请输入管理密码, 只有版主才能编辑留言.
昵称:
性别:
帅哥
美女
不告诉你
Email:
来自:
QQ:
主页:
信息图标: ( 随机 )
BB 代码:
字体
宋体
黑体
隶书
楷体
幼圆
Arial
颜色
天蓝
品蓝
蓝色
深蓝
橙色
橙红
深红
红色
砖红
暗红
绿色
灰绿
海绿
粉红
果红
桃红
紫色
紫蓝
棕木
沙褐
土黄
深褐
土绿
灰色
大小
1号
2号
3号
4号
5号
留言给(谁?):
内容:
老大,又来麻烦您了,嘿嘿!先谢谢! 我现在系统奇慢,仔细看后发现是个查询导致的,脚本如下: select /*+ ORDERED */ --qv.lj, 'v' "BJ", bso.department_id department_id, bso.standard_operation_id process_id, ppf.person_id person_id, qv.CHARACTER31 "BC", cih.workshop_name "CJ", 'N' "SFBZ", trunc(to_date(qv.CHARACTER17, 'YYYY/MM/DD')) "CLRQ", qv.CHARACTER27 "YG" , nvl(qv.CHARACTER30,qv.CHARACTER25) "CHP", nvl(qv.CHARACTER14,'A') "LJGXDM", decode(qv.CHARACTER14,NULL,nvl(qv.CHARACTER26,qv.CHARACTER20),nvl(nvl(qv.CHARACTER29,qv.CHARACTER26),qv.CHARACTER20) ) "GXDM", qv.CHARACTER2 "SZCG", qv.WIP_ENTITY_ID "JOB", decode(cih.inspection_code,'HGP',round(round(to_number(qv.CHARACTER7, '9999999999999999999999999999999.999999')*cpp1.per_price,8)/cpp2.per_price,8),0) "HGP", decode(cih.inspection_code,'CP',round(round(to_number(qv.CHARACTER7, '9999999999999999999999999999999.999999')*cpp1.per_price,8)/cpp2.per_price,8),0) "CP", decode(cih.inspection_code,'FP',round(round(to_number(qv.CHARACTER7, '9999999999999999999999999999999.999999')*cpp1.per_price,8)/cpp2.per_price,8),0) "FEP", decode(cih.inspection_code,'SF',round(round(to_number(qv.CHARACTER7, '9999999999999999999999999999999.999999')*cpp1.per_price,8)/cpp2.per_price,8),0) "SAOF", decode(cih.inspection_code,'FS',round(round(to_number(qv.CHARACTER7, '9999999999999999999999999999999.999999')*cpp1.per_price,8)/cpp2.per_price,8),0) "FANS", decode(cih.inspection_code,'FG',round(round(to_number(qv.CHARACTER7, '9999999999999999999999999999999.999999')*cpp1.per_price,8)/cpp2.per_price,8),0) "FANG", decode(cih.inspection_code,'LT',round(round(to_number(qv.CHARACTER7, '9999999999999999999999999999999.999999')*cpp1.per_price,8)/cpp2.per_price,8),0) "LIANT", decode(cih.inspection_code,'SSBLCP',round(round(to_number(qv.CHARACTER7, '9999999999999999999999999999999.999999')*cpp1.per_price,8)/cpp2.per_price,8),0) "SSCP", decode(cih.inspection_code,'SSBLFP',round(round(to_number(qv.CHARACTER7, '9999999999999999999999999999999.999999')*cpp1.per_price,8)/cpp2.per_price,8),0) "SSFP", decode(cih.inspection_code,'DCZT',round(round(to_number(qv.CHARACTER7, '9999999999999999999999999999999.999999')*cpp1.per_price,8)/cpp2.per_price,8),0) "DACZT", decode(cih.inspection_code,'SHF',round(round(to_number(qv.CHARACTER7, '9999999999999999999999999999999.999999')*cpp1.per_price,8)/cpp2.per_price,8),0) "SHUAIF", decode(cih.inspection_code,'SH',round(round(to_number(qv.CHARACTER7, '9999999999999999999999999999999.999999')*cpp1.per_price,8)/cpp2.per_price,8),0) "SUNH" from qa_results qv, cux_inspection_headers cih, cux_inspection_lines cil, cux_process_prices cpp1, cux_process_prices cpp2, bom_standard_operations bso, per_people_f ppf where qv.CHARACTER8 = cil.reason_code and cih.header_id = cil.header_id and qv.CHARACTER28 is null and qv.CHARACTER27 is not null and qv.CHARACTER8 is not null and qv.plan_id=101 --and nvl(qv.lj,'无') = '无' and decode(qv.CHARACTER14,NULL,nvl(qv.CHARACTER26,qv.CHARACTER20),nvl(nvl(qv.CHARACTER29,qv.CHARACTER26),qv.CHARACTER20) ) = cih.process_code 注意最后一个条件,如果不加这个条件就会很快,如果加上就会很慢。估计是这个函数反复比较导致的,但是我这个函数还必须用,有什么办法优化一下呢? qa_results的脚本如下: -- Create table create table QA.QA_RESULTS ( COLLECTION_ID NUMBER not null, OCCURRENCE NUMBER not null, LAST_UPDATE_DATE DATE not null, QA_LAST_UPDATE_DATE DATE not null, LAST_UPDATED_BY NUMBER not null, QA_LAST_UPDATED_BY NUMBER not null, CREATION_DATE DATE not null, QA_CREATION_DATE DATE not null, CREATED_BY NUMBER not null, QA_CREATED_BY NUMBER not null, LAST_UPDATE_LOGIN NUMBER, REQUEST_ID NUMBER, PROGRAM_APPLICATION_ID NUMBER, PROGRAM_ID NUMBER, PROGRAM_UPDATE_DATE DATE, TRANSACTION_NUMBER NUMBER, TXN_HEADER_ID NUMBER, ORGANIZATION_ID NUMBER not null, PLAN_ID NUMBER not null, SPEC_ID NUMBER, TRANSACTION_ID NUMBER, DEPARTMENT_ID NUMBER, TO_DEPARTMENT_ID NUMBER, RESOURCE_ID NUMBER, QUANTITY NUMBER, ITEM_ID NUMBER, UOM VARCHAR2(3), REVISION VARCHAR2(3), SUBINVENTORY VARCHAR2(10), LOCATOR_ID NUMBER, LOT_NUMBER VARCHAR2(30), SERIAL_NUMBER VARCHAR2(30), COMP_ITEM_ID NUMBER, COMP_UOM VARCHAR2(3), COMP_REVISION VARCHAR2(3), COMP_SUBINVENTORY VARCHAR2(10), COMP_LOCATOR_ID NUMBER, COMP_LOT_NUMBER VARCHAR2(30), COMP_SERIAL_NUMBER VARCHAR2(30), WIP_ENTITY_ID NUMBER, LINE_ID NUMBER, TO_OP_SEQ_NUM NUMBER, FROM_OP_SEQ_NUM NUMBER, VENDOR_ID NUMBER, RECEIPT_NUM VARCHAR2(30), PO_HEADER_ID NUMBER, PO_RELEASE_ID NUMBER, PO_LINE_NUM NUMBER, PO_SHIPMENT_NUM NUMBER, CUSTOMER_ID NUMBER, SO_HEADER_ID NUMBER, RMA_HEADER_ID NUMBER, PROJECT_ID NUMBER, TASK_ID NUMBER, STATUS NUMBER, TRANSACTION_DATE DATE, CHARACTER1 VARCHAR2(150), CHARACTER2 VARCHAR2(150), CHARACTER3 VARCHAR2(150), CHARACTER4 VARCHAR2(150), CHARACTER5 VARCHAR2(150), CHARACTER6 VARCHAR2(150), CHARACTER7 VARCHAR2(150), CHARACTER8 VARCHAR2(150), CHARACTER9 VARCHAR2(150), CHARACTER10 VARCHAR2(150), CHARACTER11 VARCHAR2(150), CHARACTER12 VARCHAR2(150), CHARACTER13 VARCHAR2(150), CHARACTER14 VARCHAR2(150), CHARACTER15 VARCHAR2(150), CHARACTER16 VARCHAR2(150), CHARACTER17 VARCHAR2(150), CHARACTER18 VARCHAR2(150), CHARACTER19 VARCHAR2(150), CHARACTER20 VARCHAR2(150), CHARACTER21 VARCHAR2(150), CHARACTER22 VARCHAR2(150), CHARACTER23 VARCHAR2(150), CHARACTER24 VARCHAR2(150), CHARACTER25 VARCHAR2(150), CHARACTER26 VARCHAR2(150), CHARACTER27 VARCHAR2(150), CHARACTER28 VARCHAR2(150), CHARACTER29 VARCHAR2(150), CHARACTER30 VARCHAR2(150), CHARACTER31 VARCHAR2(150), CHARACTER32 VARCHAR2(150), CHARACTER33 VARCHAR2(150), CHARACTER34 VARCHAR2(150), CHARACTER35 VARCHAR2(150), CHARACTER36 VARCHAR2(150), CHARACTER37 VARCHAR2(150), CHARACTER38 VARCHAR2(150), CHARACTER39 VARCHAR2(150), CHARACTER40 VARCHAR2(150), CHARACTER41 VARCHAR2(150), CHARACTER42 VARCHAR2(150), CHARACTER43 VARCHAR2(150), CHARACTER44 VARCHAR2(150), CHARACTER45 VARCHAR2(150), CHARACTER46 VARCHAR2(150), CHARACTER47 VARCHAR2(150), CHARACTER48 VARCHAR2(150), CHARACTER49 VARCHAR2(150), CHARACTER50 VARCHAR2(150), CHARACTER51 VARCHAR2(150), CHARACTER52 VARCHAR2(150), CHARACTER53 VARCHAR2(150), CHARACTER54 VARCHAR2(150), CHARACTER55 VARCHAR2(150), CHARACTER56 VARCHAR2(150), CHARACTER57 VARCHAR2(150), CHARACTER58 VARCHAR2(150), CHARACTER59 VARCHAR2(150), CHARACTER60 VARCHAR2(150), CHARACTER61 VARCHAR2(150), CHARACTER62 VARCHAR2(150), CHARACTER63 VARCHAR2(150), CHARACTER64 VARCHAR2(150), CHARACTER65 VARCHAR2(150), CHARACTER66 VARCHAR2(150), CHARACTER67 VARCHAR2(150), CHARACTER68 VARCHAR2(150), CHARACTER69 VARCHAR2(150), CHARACTER70 VARCHAR2(150), CHARACTER71 VARCHAR2(150), CHARACTER72 VARCHAR2(150), CHARACTER73 VARCHAR2(150), CHARACTER74 VARCHAR2(150), CHARACTER75 VARCHAR2(150), CHARACTER76 VARCHAR2(150), CHARACTER77 VARCHAR2(150), CHARACTER78 VARCHAR2(150), CHARACTER79 VARCHAR2(150), CHARACTER80 VARCHAR2(150), CHARACTER81 VARCHAR2(150), CHARACTER82 VARCHAR2(150), CHARACTER83 VARCHAR2(150), CHARACTER84 VARCHAR2(150), CHARACTER85 VARCHAR2(150), CHARACTER86 VARCHAR2(150), CHARACTER87 VARCHAR2(150), CHARACTER88 VARCHAR2(150), CHARACTER89 VARCHAR2(150), CHARACTER90 VARCHAR2(150), CHARACTER91 VARCHAR2(150), CHARACTER92 VARCHAR2(150), CHARACTER93 VARCHAR2(150), CHARACTER94 VARCHAR2(150), CHARACTER95 VARCHAR2(150), CHARACTER96 VARCHAR2(150), CHARACTER97 VARCHAR2(150), CHARACTER98 VARCHAR2(150), CHARACTER99 VARCHAR2(150), CHARACTER100 VARCHAR2(150), LPN_ID NUMBER, CONTRACT_ID NUMBER, CONTRACT_LINE_ID NUMBER, DELIVERABLE_ID NUMBER ) tablespace QAD pctfree 10 pctused 40 initrans 10 maxtrans 255 storage ( initial 16K next 4M minextents 1 maxextents unlimited pctincrease 0 ); -- Create/Recreate indexes create index QA.QA_RESULTS_CUX_N1 on QA.QA_RESULTS (CHARACTER14, CHARACTER20, CHARACTER25, CHARACTER26, CHARACTER28, CHARACTER29, CHARACTER30) tablespace QAX pctfree 10 initrans 2 maxtrans 255 storage ( initial 40K next 43152K minextents 1 maxextents 505 pctincrease 0 ); create index QA.QA_RESULTS_CUX_N2 on QA.QA_RESULTS (CHARACTER14, CHARACTER20, CHARACTER26, CHARACTER25, CHARACTER27, CHARACTER29, CHARACTER30) tablespace QAX pctfree 10 initrans 2 maxtrans 255 storage ( initial 40K next 43152K minextents 1 maxextents 505 pctincrease 0 ); create index QA.QA_RESULTS_CUX_N3 on QA.QA_RESULTS (CHARACTER28) tablespace QAX pctfree 10 initrans 2 maxtrans 255 storage ( initial 40K next 12784K minextents 1 maxextents 505 pctincrease 0 ); create index QA.QA_RESULTS_CUX_N4 on QA.QA_RESULTS (CHARACTER27) tablespace QAX pctfree 10 initrans 2 maxtrans 255 storage ( initial 40K next 12784K minextents 1 maxextents 505 pctincrease 0 ); create index QA.QA_RESULTS_N1 on QA.QA_RESULTS (ITEM_ID, ORGANIZATION_ID) tablespace QAX pctfree 10 initrans 11 maxtrans 255 storage ( initial 16K next 256K minextents 1 maxextents unlimited pctincrease 0 ); create index QA.QA_RESULTS_N2 on QA.QA_RESULTS (SERIAL_NUMBER, ORGANIZATION_ID) tablespace QAX pctfree 10 initrans 11 maxtrans 255 storage ( initial 16K next 256K minextents 1 maxextents unlimited pctincrease 0 ); create index QA.QA_RESULTS_N3 on QA.QA_RESULTS (COMP_SERIAL_NUMBER, ORGANIZATION_ID) tablespace QAX pctfree 10 initrans 11 maxtrans 255 storage ( initial 16K next 256K minextents 1 maxextents unlimited pctincrease 0 ); create index QA.QA_RESULTS_N4 on QA.QA_RESULTS (LOT_NUMBER, ORGANIZATION_ID) tablespace QAX pctfree 10 initrans 11 maxtrans 255 storage ( initial 16K next 256K minextents 1 maxextents unlimited pctincrease 0 ); create index QA.QA_RESULTS_N5 on QA.QA_RESULTS (SO_HEADER_ID) tablespace QAX pctfree 10 initrans 11 maxtrans 255 storage ( initial 16K next 4M minextents 1 maxextents unlimited pctincrease 0 ); create index QA.QA_RESULTS_N6 on QA.QA_RESULTS (COLLECTION_ID) tablespace QAX pctfree 10 initrans 11 maxtrans 255 storage ( initial 16K next 256K minextents 1 maxextents unlimited pctincrease 0 ); create index QA.QA_RESULTS_N7 on QA.QA_RESULTS (TXN_HEADER_ID) tablespace QAX pctfree 10 initrans 11 maxtrans 255 storage ( initial 16K next 256K minextents 1 maxextents unlimited pctincrease 0 ); create index QA.QA_RESULTS_N8 on QA.QA_RESULTS (CONTRACT_ID, CONTRACT_LINE_ID, DELIVERABLE_ID) tablespace QAX pctfree 10 initrans 11 maxtrans 255 storage ( initial 16K next 256K minextents 1 maxextents unlimited pctincrease 0 ); create unique index QA.QA_RESULTS_U1 on QA.QA_RESULTS (PLAN_ID, COLLECTION_ID, OCCURRENCE) tablespace QAX pctfree 10 initrans 11 maxtrans 255 storage ( initial 16K next 256K minextents 1 maxextents unlimited pctincrease 0 ); 呵呵,脚本比较长,还请老大息怒,帮我仔细看看,感动阿....................
Copyright © 2003~2012
eygle.com
All Rights Reserved.
Powered by:
www.eygle.com