收到开发这边的确认请求,如下三个语句,执行计划的COST均一样,但为什么实际执行效果相差很大(1)的查询速度很慢,2)和3)都正常)。
1) Select * From (Select * From V_Question_Head Vquest0_ Where 1 = 1 Order By Vquest0_.State Asc, Vquest0_.Ask_Time Desc) Where Rownum <= 6; 2) Select * From (Select * From V_Question_Head Vquest0_ Where 1 = 1 And Rownum <= 6 Order By Vquest0_.State Asc, Vquest0_.Ask_Time Desc); 3) Select * From (Select * From Question_Head Vquest0_ Where 1 = 1 Order By Vquest0_.State Asc, Vquest0_.Ask_Time Desc) Where Rownum <= 6;
看如下执行计划,显然2速度快可以理解,因为行数只有6,而1和3为什么有那么大的速度差距,要命的是执行计划看起来没有差距。
SELECT STATEMENT, GOAL = ALL_ROWS Cost=1710 Cardinality=6 Bytes=27912 COUNT STOPKEY VIEW Object owner=GAZA Cost=1710 Cardinality=20237 Bytes=94142524 SORT ORDER BY STOPKEY Cost=1710 Cardinality=20237 Bytes=5949678 TABLE ACCESS FULL Object owner=TEST Object name=QUESTION_HEAD Cost=421 Cardinality=20237 Bytes=5949678 SELECT STATEMENT, GOAL = ALL_ROWS Cost=1710 Cardinality=6 Bytes=27912 VIEW Object owner=TEST Cost=1710 Cardinality=6 Bytes=27912 SORT ORDER BY Cost=1710 Cardinality=6 Bytes=1764 COUNT STOPKEY TABLE ACCESS FULL Object owner=TEST Object name=QUESTION_HEAD Cost=421 Cardinality=20237 Bytes=5949678 SELECT STATEMENT, GOAL = ALL_ROWS Cost=1710 Cardinality=6 Bytes=28590 COUNT STOPKEY VIEW Object owner=TEST Cost=1710 Cardinality=20237 Bytes=96429305 SORT ORDER BY STOPKEY Cost=1710 Cardinality=20237 Bytes=5949678 TABLE ACCESS FULL Object owner=TEST Object name=QUESTION_HEAD Cost=421 Cardinality=20237 Bytes=5949678