eygle.com   eygle.com
eygle.com  
 
eygle.com - 留言簿 - 回复留言 - Powered by eYgLe.Com
返回留言簿
回 复 留 言
留言ID:
留言原文:
To: eygle
　　老大，又来麻烦您了，嘿嘿！先谢谢！
我现在系统奇慢，仔细看后发现是个查询导致的，脚本如下：
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
  );

呵呵，脚本比较长，还请老大息怒，帮我仔细看看，感动阿....................
From: remen
2005.11.04 04:28
管理密码: 请输入管理密码, 否则无法回复.
斑竹昵称:
信息图标: ( 随机 )

BB 代码:
内容:

如果想删除回复, 请点击最下面的删除回复按钮.
删 除 回 复
管理密码: 请输入管理员密码, 只有版主才能删除回复.
Copyright © 2003~2012 eygle.com All Rights Reserved.
Powered by: www.eygle.com