--你的select 语句肯定有优化的余地,而且没有order by或group by select * from ( select rownum rn,--前100行是这么获取的 z.bank_name, z.branchbank_name, x.bank_cardid, x.check_amount, x.check_description, x.GYSbank_acct_name, x.vendor_bank_cardid, y.sheetrow_type, u.meaning, y.payable_voucher_id from ( select a.checkID, b.bank_cardid, a.check_amount, a.check_description, c.bank_acct_name as GYSbank_acct_name, a.vendor_bank_cardid, a.bank_acctid, b.branchbankID from p_e_payablepayment a, P_E_BANKACCOUNT b, (select bank_acctID, bank_acct_name, bank_cardid from P_E_BANKACCOUNT) c where a.bank_acctid = b.bank_acctid and a.vendor_bank_cardidid = c.bank_acctid) x, P_E_PAYABLESHEETROW y, p_e_bank z, (select type, code, meaning from P_E_SPEEDCODEDIC where type = 'AE LINE TYPE') u where x.branchbankID = z.branchbankid and x.checkId = y.source_tableID and y.sheetrow_type = u.code ) where rn <= 100
多谢解答, 按照这种命别名的方式,确实会更快些; 只是为什么必须取子句的rownum,取外层的rownum为什么不行呢? 而且我试了下:select * from ( select * from data_b001 t ) where rownum < 100像这样子句较简单的语句,使用的也是外层的rownum,执行起来也挺快的; 好像是子句嵌套层数太多了,rownum的计算就会有效率问题。你说呢?
select * from ( --SELECT SQL HERE ) where rownum <= 100一般的话,很少直接用ROWNUM,都用别名,这样用select * from ( select *,rownum rn from ( --SELECT SQL HERE ) ) where rn <= 100
COUNT STOPKEY
HASH JOIN
TABLE ACCESS FULL
HASH JOIN
TABLE ACCESS FULL
HASH JOIN
TABLE ACCESS FULL
NESTED LOOPS
HASH JOIN
TABLE ACCESS FULL
TABLE ACCESS FULL
TABLE ACCESS FULL
另外,不用嵌套这么多层,一层就行了,
没有排序,rownum条件放在里面就行了。
--》
内部子句执行后,记录数大概是27000;耗时:0.2秒;无索引;
我的程序中,外面的select...rownum<=100这部分必须套在最外层;仅子句是可变的;我怎么也想不明白,为什么加个rownum<=100,就处于无限等待状态了,痛苦中。。
--你的select 语句肯定有优化的余地,而且没有order by或group by
select *
from (
select rownum rn,--前100行是这么获取的
z.bank_name,
z.branchbank_name,
x.bank_cardid,
x.check_amount,
x.check_description,
x.GYSbank_acct_name,
x.vendor_bank_cardid,
y.sheetrow_type,
u.meaning,
y.payable_voucher_id
from (
select a.checkID,
b.bank_cardid,
a.check_amount,
a.check_description,
c.bank_acct_name as GYSbank_acct_name,
a.vendor_bank_cardid,
a.bank_acctid,
b.branchbankID
from p_e_payablepayment a,
P_E_BANKACCOUNT b,
(select bank_acctID, bank_acct_name, bank_cardid
from P_E_BANKACCOUNT) c
where a.bank_acctid = b.bank_acctid
and a.vendor_bank_cardidid = c.bank_acctid) x,
P_E_PAYABLESHEETROW y,
p_e_bank z,
(select type, code, meaning
from P_E_SPEEDCODEDIC
where type = 'AE LINE TYPE') u
where x.branchbankID = z.branchbankid
and x.checkId = y.source_tableID
and y.sheetrow_type = u.code
)
where rn <= 100
别名,懂吗?好象是pl/sql几个最主要的关注点。
不过既然每层查询都会有rownum,我现在并不是要引用内层的rownum,用的就是最外层的rownum;
难道我写在最外层的rownum,oracle会认为是对内的引用?
多谢解答,
按照这种命别名的方式,确实会更快些;
只是为什么必须取子句的rownum,取外层的rownum为什么不行呢?
而且我试了下:select * from (
select * from data_b001 t
)
where rownum < 100像这样子句较简单的语句,使用的也是外层的rownum,执行起来也挺快的;
好像是子句嵌套层数太多了,rownum的计算就会有效率问题。你说呢?
from (
--SELECT SQL HERE
)
where
rownum <= 100一般的话,很少直接用ROWNUM,都用别名,这样用select * from (
select *,rownum rn
from (
--SELECT SQL HERE
)
)
where
rn <= 100