不好意思,没写清楚,不怎么支持的意思是支持但支持地不很理想,我将Oracle的 资料联结到ACCESS里,在access里建立了同样的查询速度比Oracle里的View快了1倍,很伤自尊啊,不知道在Oracle里做View有啥诀窍,另外,用Function做怎么样,该如何下手呢. View_B CREATE OR REPLACE VIEW RPT_MN_PDQ ( MN, FAC_NO, EMP_NO, MFG_NO, SEC_NO, PCS ) AS SELECT to_char(prodq.prod_date,'YYYY/MM') as MN, prodq.fac_no as fac_no, prodq.emp_no as EMP_NO, prodq.mfg_no mfg_no, prodq.sec_no as sec_no, prodq.pcs as pcs FROM prodq prodqView_C CREATE OR REPLACE VIEW RPT_MN_MIN ( MN, MFG_NO, SEC_NO, MIN_PRI ) AS SELECT to_char(sprice.start_dt,'YYYY/MM') MN, sprice.mfg_no as mfg_no, sprice.sec_no sec_no, Min(sprice.prod_pri) as min_pri FROM sprice sprice GROUP BY to_char(sprice.start_dt,'YYYY/MM'), sprice.mfg_no, sprice.sec_noView_A CREATE OR REPLACE VIEW RPT_MN_EMPPRI ( MN, FAC_NO, DEP_FUNC, EMP_NO, PAY_TYPE, XINZI ) AS SELECT tb_.mn, tb_.fac_no, dept.dep_func, tb_.emp_no, emp.pay_type,sum(tb_.pcs * min.min_pri) as xinzi FROM RPT_mn_pdq tb_, emp emp, dept dept, RPT_mn_min min WHERE ( (tb_.emp_no = emp.no(+)) AND (tb_.fac_no = emp.fac_no(+)) AND (emp.dep_no = dept.no(+)) AND (emp.fac_no = dept.fac_no(+)) AND (tb_.mn = MIN.mn(+)) AND (tb_.mfg_no = MIN.mfg_no(+)) AND (tb_.sec_no = MIN.sec_no(+)) ) GROUP BY tb_.mn, tb_.fac_no, dept.dep_func, emp.pay_type, tb_.emp_noprodq里有13万笔资料,执行View_A大概13分钟,没这么离谱吧,服务器跟工作站都是P4的,内存也是大大的有.
View_B
CREATE OR REPLACE VIEW RPT_MN_PDQ ( MN,
FAC_NO, EMP_NO, MFG_NO, SEC_NO,
PCS ) AS SELECT to_char(prodq.prod_date,'YYYY/MM') as MN, prodq.fac_no as fac_no, prodq.emp_no as EMP_NO, prodq.mfg_no mfg_no, prodq.sec_no as sec_no,
prodq.pcs as pcs
FROM prodq prodqView_C
CREATE OR REPLACE VIEW RPT_MN_MIN ( MN,
MFG_NO, SEC_NO, MIN_PRI ) AS SELECT to_char(sprice.start_dt,'YYYY/MM') MN, sprice.mfg_no as mfg_no, sprice.sec_no sec_no, Min(sprice.prod_pri) as min_pri
FROM sprice sprice
GROUP BY to_char(sprice.start_dt,'YYYY/MM'), sprice.mfg_no, sprice.sec_noView_A
CREATE OR REPLACE VIEW RPT_MN_EMPPRI ( MN,
FAC_NO, DEP_FUNC, EMP_NO, PAY_TYPE,
XINZI ) AS SELECT tb_.mn, tb_.fac_no, dept.dep_func, tb_.emp_no, emp.pay_type,sum(tb_.pcs * min.min_pri) as xinzi
FROM RPT_mn_pdq tb_, emp emp, dept dept, RPT_mn_min min
WHERE ( (tb_.emp_no = emp.no(+))
AND (tb_.fac_no = emp.fac_no(+))
AND (emp.dep_no = dept.no(+))
AND (emp.fac_no = dept.fac_no(+))
AND (tb_.mn = MIN.mn(+))
AND (tb_.mfg_no = MIN.mfg_no(+))
AND (tb_.sec_no = MIN.sec_no(+))
)
GROUP BY tb_.mn, tb_.fac_no, dept.dep_func, emp.pay_type, tb_.emp_noprodq里有13万笔资料,执行View_A大概13分钟,没这么离谱吧,服务器跟工作站都是P4的,内存也是大大的有.