Execution is slow due to unreasonable ORACLE table structure design.

Today, I received a confirmation request from the development team. The following three statements have the same execution plan COST, but the actual execution results differ greatly (1) the query speed is very slow, 2) and 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;

 
Looking at the following execution plan, it is clear that 2 is fast and understandable, because the number of rows is only 6, and why is there such a large speed gap between 1 and 3? What's worse is that there seems to be no difference in the execution plan.
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
A preliminary look, nothing more than 1 is the View query, 3 is the difference between the direct Table query, but the key is to remove Order By Vquest0 _. state Asc, Vquest0 _. after Ask_Time Desc, the execution time of the two is almost the same, and the time difference after sorting is dozens of times. Let's look at the view V_Question_Head.
Create or replace view v_question_head
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
Through the above view, it is not difficult to find that although the execution plan does not reflect the cost of COUNT, there is a row-level COUNT, that is, the number of rows in the master table is counted several times, this is a terrible number. Obviously, this is a problem in table structure design. To optimize the table, you need to change the table structure, and set the COUNT field in the master table and on the table.
 
 
 
This article is from the "mgewu" blog