语句在没有rownum的情况执行3.5秒左右,加了rownum后(取前10行)就要10秒多,为什么加了rownum就怎么慢呢?
ORACLE10G
SQL语句如下:
select *
from (select ejwh0_.EJWHXH as col_0_0_,
cljbxx1_.cpys as col_1_0_,
cljbxx1_.cphm as col_2_0_,
cljbxx1_.yyzz as col_3_0_,
cljbxx1_.yyzh as col_4_0_,
yhxx3_.yhmc as col_5_0_,
xkxx5_.xkzz as col_6_0_,
xkxx5_.xkzh as col_7_0_,
yzjg7_.jgmc as col_8_0_,
yzry6_.xm as col_9_0_,
ejwh0_.WXDWMC as col_10_0_,
ejwh0_.WHRQ as col_11_0_,
ejwh0_.XCWHRQ as col_12_0_,
ejwh0_.WHLC as col_13_0_,
ejwh0_.XCWHLC as col_14_0_,
ejwh0_.HGZH as col_15_0_,
ejwh0_.ZJY as col_16_0_,
ejwh0_.BZ as col_17_0_
from T_SCJG_EJWH ejwh0_
left outer join T_XZXK_CL_JBXX cljbxx1_ on ejwh0_.FK_CLXH =
cljbxx1_.clxh
left outer join T_XZXK_YH_YHXX yhxx3_ on cljbxx1_.FK_YHXH =
yhxx3_.yhxh
left outer join T_XZXK_YHXK_XKXX xkxx5_ on cljbxx1_.FK_YHXKXH =
xkxx5_.yhxkxh
left outer join T_YZRY_JBXX yzry6_ on ejwh0_.FK_LRR = yzry6_.ryxh
left outer join T_YZJG_JBXX yzjg7_ on ejwh0_.FK_YZJG =
yzjg7_.yzjgxh
left outer join T_YZRY_JBXX yzry8_ on ejwh0_.FK_XGR = yzry8_.ryxh
where cljbxx1_.FK_YHXKXH = xkxx5_.yhxkxh
and xkxx5_.xkzz = '锦'
and (cljbxx1_.cphm like '%A%')
and cljbxx1_.yyzz = '锦'
order by ejwhxh)
where rownum <= 10
执行计划:
SELECT STATEMENT, GOAL = ALL_ROWS Cost=2046 Cardinality=10 Bytes=7380
COUNT STOPKEY
VIEW Object owner=TEPS_DB Cost=2046 Cardinality=10 Bytes=7380
NESTED LOOPS
NESTED LOOPS Cost=2046 Cardinality=10 Bytes=1740
NESTED LOOPS OUTER Cost=2026 Cardinality=10 Bytes=1530
NESTED LOOPS OUTER Cost=2006 Cardinality=10 Bytes=1380
NESTED LOOPS OUTER Cost=1996 Cardinality=10 Bytes=1220
NESTED LOOPS Cost=1986 Cardinality=10 Bytes=1100
TABLE ACCESS BY INDEX ROWID Object owner=TEPS_DB Object name=T_SCJG_EJWH Cost=220 Cardinality=2402260 Bytes=163353680
INDEX FULL SCAN Object owner=TEPS_DB Object name=PK_EJWHXHCost=5 Cardinality=882
TABLE ACCESS BY INDEX ROWID Object owner=TEPS_DB Object name=T_XZXK_CL_JBXX Cost=2 Cardinality=1 Bytes=42
INDEX UNIQUE SCAN Object owner=TEPS_DB Object name=PK_T_XZXK_CL_JBXX Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=TEPS_DB Object name=T_YZRY_JBXX Cost=1 Cardinality=1 Bytes=12
INDEX UNIQUE SCAN Object owner=TEPS_DB Object name=PK_T_YZRY_JBXX Cost=0 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=TEPS_DB Object name=T_YZJG_JBXX Cost=1 Cardinality=1 Bytes=16
INDEX UNIQUE SCAN Object owner=TEPS_DB Object name=PK_T_YZJG_JBXX Cost=0 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=TEPS_DB Object name=T_XZXK_YH_YHXX Cost=2 Cardinality=1 Bytes=15
INDEX UNIQUE SCAN Object owner=TEPS_DB Object name=PK_T_XZXK_YH_YHXX Cost=1 Cardinality=1
INDEX UNIQUE SCAN Object owner=TEPS_DB Object name=PK_XZXK_YHXK_XKXX Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=TEPS_DB Object name=T_XZXK_YHXK_XKXX Cost=2 Cardinality=1 Bytes=21
Statistics:
bytes received via SQL*Net from client 3701
bytes sent via SQL*Net to client 2108
consistent gets 2819932
consistent gets - examination 1825569
consistent gets direct 0
consistent gets from cache 2819932
db block gets 0
db block gets direct 0
db block gets from cache 0
lob reads 0
lob writes 0
parse count (failures) 1
parse count (hard) 1
parse count (total) 1
physical read bytes 0
physical reads 0
recursive calls 1
redo size 0
sorts (disk) 0
sorts (memory) 0
sorts (rows) 0