子查询解嵌套导致SQL变慢

最新推荐文章于 2021-05-01 16:29:09 发布

wadekobe9

最新推荐文章于 2021-05-01 16:29:09 发布

阅读量3.4k

点赞数

分类专栏： oracle SQL优化文章标签： sql access filter loops nested date

本文链接：https://blog.csdn.net/wadekobe9/article/details/7712363

版权

原SQL中存在子查询解嵌套问题，导致执行时间长达4分钟。通过添加特定的Hints禁止Oracle数据库进行子查询解嵌套，执行时间降低到1秒。分析表明，当子查询返回数据量较大，且与外部查询结果集差距显著时，不解嵌套能显著提升查询效率。

摘要由CSDN通过智能技术生成

原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