- 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 as
- Select 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的字段。