收到
开发这边的确认
请求,如下三个
语句,执行计划的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
初步看看,无非1是查视图,3是直接查表的区别,但关键是去掉Order By Vquest0_.State Asc, vquest0_.ask_time Desc后两者的执行时间差不多,而加上排序后时间就有数十倍的差距,再来看看视图V_Question_Head
create or replace view v_question_head asSelect t.Accept_Id, t.Industry_Id, t.Dept_Id, t.Title, t.Question, t.Requester, t.Requester_Name, t.Ask_Time, t.Answer, t.Answer_Man, t.Answer_Time, t.Is_Hot, t.Check_Man, t.Check_Time, t.Satisfaction, t.Satis_Reason, t.Telphone, t.Is_Display, t.Hot_Sort, t.Check_State, t.State, t.Bbs_Id, t.Acceptor, t.Accept_Time, t.Is_Anonymous, t.Anonymous_Email, t.Ip_Address, t.Is_Requester_Read, (Select Count(r1.Accept_Id) From Answer_Remind r1 Where t.Accept_Id = r1.Accept_Id) As Reminded_Count, (Select (Case When Count(r2.Accept_Id) > 0 Then 1 Else 0 End) From Answer_Remind r2 Where t.Accept_Id = r2.Accept_Id) As Is_Reminded From Question_Head t Order By t.State Asc, t.Ask_Time Desc
通过如上视图,我们不难发现虽然执行计划中没有体现COUNT的代价,但其中有行级的COUNT,也就是主表有多少行就要COUNT多少次,而这时一个很恐怖的数字。显然这是表结构设计上的问题。而要优化就是要更改表结构,在主表中及上需要COUNT的字段。