今天创建的一个视图,视图如下:
create or replace view vw_nfw_feeinvoice_sum as
select a.billid,sum(b.amount) as invoiceamount
from fe_fee a
inner join oc_billlink b on a.billid=b.pbillid
inner join fe_invoice c on b.billid=c.billid
where c.invoiceno is not null and nvl(iscancel,0)=0 and CREDITINVOICENO is null
group by a.billid;
查询这个视图,在测试服务器上跑的执行计划是这样的:
select * from VW_NFW_FEEinvoice_SUM
Plan Hash Value : 969507009
Description 对象所有者 对象名称 耗费 基数 字节
SELECT STATEMENT, GOAL = ALL_ROWS 6,635 3,777 1,227,525
HASH GROUP BY 6,635 3,777 1,227,525
HASH JOIN 6,634 3,777 1,227,525
NESTED LOOPS
NESTED LOOPS 3,110 3,777 982,020
TABLE ACCESS FULL BL8 FE_INVOICE 52 764 97,028
INDEX RANGE SCAN BL8 PK_LINKBILLID 2 5
TABLE ACCESS BY INDEX ROWID BL8 OC_BILLLINK 4 5 665
INDEX FAST FULL SCAN BL8 PK_FEEID 3,515 1,190,982 77,413,830
在正式服务器上,跑同样的查询语句,执行的结果是这样的:
SELECT STATEMENT, GOAL = ALL_ROWS 24724 22961 7646013
HASH GROUP BY 24724 22961 7646013
HASH JOIN 23084 22961 7646013
HASH JOIN 12716 22961 6153548
TABLE ACCESS FULL BL8 FE_INVOICE 87 4214 568890
TABLE ACCESS FULL BL8 OC_BILLLINK 12619 1283904 170759232
INDEX FAST FULL SCAN BL8 PK_FEEID 4447 1536129 99848385
求助大神,好几个查询都有这样的问题,测试服务器上很快,但是一到正式的拉稀了