SQL优化問題
select count(*)into v_count
from MIT_BILL_DETAIL b,mit_bill a
where b.matno = 'AD0095001010'
and substr(b.billno, 1, 2) = 'WB'
AND substr(b.billno, 4, 1) in ('C', 'J', 'L', 'K')
and substr(b.billno,5,4)<=to_char(v_date,'YYMM')
and a.checked>0
and a.billno=b.billno;
IF NVL(V_COUNT,0)>0 THEN
select MAX(recprice)
into v_price
from MIT_BILL_DETAIL
where matno = c.matno
AND SUBSTR(billno,5,9) =
(select max(SUBSTR(b.billno,5,9))
from mit_bill_detail b,mit_bill a
where b.matno = 'AD0095001010'
and substr(b.billno, 1, 2) ='WB'
AND substr(b.billno, 4, 1) in ('C', 'J', 'L', 'K')
and substr(b.billno, 5, 4) <= to_char(v_date,'YYMM')
and a.checked>0
AND NVL(B.RECPRICE,0)>0
and a.billno=b.billno)
and substr(billno, 1, 2) ='WB'
AND substr(billno, 4, 1) in ('C', 'J', 'L', 'K');
以上語句如何調优 create table MI.MIT_BILL
(
BILLNO VARCHAR2(13) not null,
SUPPNO VARCHAR2(5),
FACNO VARCHAR2(11),
FACID VARCHAR2(1),
DEPTNO VARCHAR2(6),
INVOICE VARCHAR2(20),
CHECKED INTEGER default 0,
BILLNO1 VARCHAR2(13),
REMARK VARCHAR2(150),
USERNM VARCHAR2(30),
USERNM1 VARCHAR2(30),
BILLDATE DATE,
INSDATE DATE default sysdate,
UPDDATE DATE default sysdate,
CLASS NUMBER default 0
) create table MI.MIT_BILL_DETAIL
(
BILLNO VARCHAR2(13) not null,
SEQ NUMBER not null,
ITEMNO VARCHAR2(5),
MATNO VARCHAR2(12),
UNITNM VARCHAR2(10),
PLUS INTEGER default 1,
PQTY NUMBER(18,4) default 0,
LQTY NUMBER(18,4) default 0,
RECPRICE NUMBER(18,4) default 0,
PAYPRICE NUMBER(18,4) default 0,
DISCOUNT NUMBER(18,4) default 0,
CURNO VARCHAR2(4),
PAYMK INTEGER default 0,
PAYYM VARCHAR2(6),
POSITION VARCHAR2(20),
GROSS NUMBER(18,4) default 0,
SUTTLE NUMBER(18,4) default 0,
PWEIGHT NUMBER(18,6) default 0,
CTMATNO VARCHAR2(15),
CTNO VARCHAR2(15),
FACNO VARCHAR2(11),
FACID VARCHAR2(1),
REMARK VARCHAR2(60),
DELIDATE DATE,
TYPEID VARCHAR2(2)
)
页:
[1]