原SQL如下,跑4分钟左右
Select Guest_Id,
t_To_Order_Tickets.Order_Id,
t_To_Order_Info.Contact_Mobile,
Guest_Name,
Guest_Idno,
Departure_Time,
Trip_Num,
Arrival_Time
From t_To_Order_Tickets
Left Join t_To_Order_Info
On t_To_Order_Tickets.Order_Id = t_To_Order_Info.Order_Id
Where (t_To_Order_Info.Order_Status_Id = 5 Or
t_To_Order_Info.Order_Status_Id = 6)
And (t_To_Order_Info.Insurance_Status Is Null Or
t_To_Order_Info.Insurance_Status = 0)
And Not Exists
(Select *
From t_To_Order_Insurance
Where t_To_Order_Insurance.Order_Id = t_To_Order_Tickets.Order_Id)
And Departure_Time <=
To_Date('2012-6-4 14:48:00', 'yyyy/MM/DD/ HH24:MI:SS')
And Departure_Time >
To_Date('2012-6-4 10:48:00', 'yyyy/MM/DD/ HH24:MI:SS')
And Guest_Id In (Select t.Guest_Id
From t_To_Order_Tickets t, t_To_Insurance i
Where t.Guest_Id = i.Guest_Id
And i.Insurance_Buy_Type = 1
And i.Insurance_Type_Id <> '2')
Plan hash value: 2410550329
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 1122 | | 26081 (7)| 00:05:13 |
|* 1 | HASH JOIN SEMI | | 11 | 1122 | | 26081 (7)| 00:05:13 |
|* 2 | FILTER | | | | | | |
| 3 | NESTED LOOPS OUTER | | 11 | 979 | | 25 (0)| 00:00:01 |
| 4 | NESTED LOOPS ANTI | | 11 | 715 | | 18 (0)| 00:00:01 |
| 5 | TAB