Subquery nesting causes SQL to slow down

The original SQL statement is as follows. It takes about 4 minutes to run.

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 |      TABLE ACCESS BY INDEX ROWID| T_TO_ORDER_TICKETS         |    17 |  1003 |       |     6   (0)| 00:00:01 ||*  6 |       INDEX RANGE SCAN          | IDX_T_TO_ORDER_TICKETS_D_T |    17 |       |       |     1   (0)| 00:00:01 ||*  7 |      INDEX RANGE SCAN           | IND_TO_INS_ORDER_ID        |   253K|  1483K|       |     1   (0)| 00:00:01 ||   8 |     TABLE ACCESS BY INDEX ROWID | T_TO_ORDER_INFO            |     1 |    24 |       |     1   (0)| 00:00:01 ||*  9 |      INDEX UNIQUE SCAN          | PK_T_TO_ORDER_INFO         |     1 |       |       |     1   (0)| 00:00:01 ||  10 |   VIEW                          | VW_NSO_1                   |  1120K|    13M|       | 26017   (7)| 00:05:13 ||* 11 |    FILTER                       |                            |       |       |       |            |          ||* 12 |     HASH JOIN                   |                            |  1120K|    33M|    39M| 26017   (7)| 00:05:13 ||* 13 |      TABLE ACCESS FULL          | T_TO_INSURANCE             |  1120K|    26M|       | 16255   (7)| 00:03:16 ||  14 |      INDEX FAST FULL SCAN       | PK_T_TO_ORDER_TICKETS      |  4981K|    28M|       |  3144   (8)| 00:00:38 |---------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------    1 - access("T_TO_ORDER_TICKETS"."GUEST_ID"="$nso_col_1")   2 - filter(("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))   6 - access("T_TO_ORDER_TICKETS"."DEPARTURE_TIME">TO_DATE('2012-6-4 10:48:00','yyyy/MM/DD/ HH24:MI:SS') AND               "T_TO_ORDER_TICKETS"."DEPARTURE_TIME"<=TO_DATE('2012-6-4 14:48:00','yyyy/MM/DD/ HH24:MI:SS'))   7 - access("T_TO_ORDER_INSURANCE"."ORDER_ID"="T_TO_ORDER_TICKETS"."ORDER_ID")   9 - access("T_TO_ORDER_TICKETS"."ORDER_ID"="T_TO_ORDER_INFO"."ORDER_ID"(+))  11 - filter(TO_DATE('2012-6-4 14:48:00','yyyy/MM/DD/ HH24:MI:SS')>TO_DATE('2012-6-4 10:48:00','yyyy/MM/DD/               HH24:MI:SS'))  12 - access("T"."GUEST_ID"="I"."GUEST_ID")  13 - filter("I"."INSURANCE_TYPE_ID"<>U'2' AND "I"."INSURANCE_BUY_TYPE"=1)

The problem is obvious. Check that there is a problem with the execution plan 10th.

Result 1 second after hints is added

Optimized: Select guest_id, sequence, sequence, guest_name, guest_idno, departure_time, trip_num, arrival_time from t_to_order_tickets left join t_to_order_info on sequence = direction where (sequence = 5 or second = 6) and (between is null or between = 0) and not exists (select * From t_to_order_insurance where between = t_to_order_tickets.order_id) and departure_time <= to_date ('2017-6-4 14:48:00 ', 'yyyy/MM/DD/hh24: MI: ss') and departure_time> to_date ('2017-6-4 10:48:00 ', 'yyyy/MM/DD/hh24: MI: SS ') and guest_id in (select/* + no_unnest */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: 504857027 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | period | 0 | SELECT statement | 1 | 89 | 36 (0) | 00:00:01 | * 1 | filter | * 2 | filter | * 3 | filter | 4 | nested loops outer | 11 | 979 | 25 (0) | 00:00:01 | 5 | nested loops anti | 11 | 715 | 18 (0) | 00:00:01 | 6 | table access by index rowid | t_to_order_tickets | 17 | 1003 | 6 (0) | 00:00:01 | * 7 | index range scan | idx_t_to_order_tickets_d_t | 17 | 1 (0) | 00:00:01 | * 8 | index range scan | ind_to_ins_order_id | 253k | 1483k | 1 (0) | 00:00:01 | 9 | table access by index rowid | t_to_order_info | 1 | 24 | 1 (0) | 00:00:01 | * 10 | index unique scan | pk_t_to_order_info | 1 | 1 (0) | 00:00:01 | 11 | nested loops | 1 | 31 | 2 (0) | 00:00:01 | * 12 | index unique scan | pk_t_to_order_tickets | 1 | 6 | 1 (0) | 00:00:01 | * 13 | table access by index rowid | t_to_insurance | 1 | 25 | 1 (0) | 00:00:01 | * 14 | index range scan | insurance_record_id_fk | 1 | 1 (0) | 00:00:01 | descripredicate information (identified by Operation ID ): limit 1-filter (exists (select/* + no_unnest */0 from "t_to_insurance" "I", "t_to_order_tickets" "T" where "T ". "guest_id" =: B1 and "I ". "guest_id" =: B2 and "I ". "insurance_type_id" <> u'2' and "I ". "insurance_buy_type" = 1) 2-filter (to_date ('2017-6-4 14:48:00 ', 'yyyy/MM/DD/hh24: MI: ss')> to_date ('2017-6-4 10:48:00 ', 'yyyy/MM/DD/hh24: MI: ss') 3-filter ("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) 7-access ("t_to_order_tickets ". "departure_time"> to_date ('2017-6-4 10:48:00 ', 'yyyy/MM/DD/hh24: MI: ss') and "t_to_order_tickets ". "departure_time" <= to_date ('2017-6-4 14:48:00 ', 'yyyy/MM/DD/hh24: MI: ss') 8-access ("t_to_order_insurance ". "order_id" = "t_to_order_tickets ". "order_id") 10-access ("t_to_order_tickets ". "order_id" = "t_to_order_info ". "order_id" (+) 12-access ("T ". "guest_id" =: B1) 13-filter ("I ". "insurance_type_id" <> u'2' and "I ". "insurance_buy_type" = 1) 14-access ("I ". "guest_id" =: B1)

Summary:

Observe step 1 of the original SQL Execution Plan
| 10 | View | vw_nso_1 | 1120k | 13m | 26017 (7) | 00:05:13 |
Vw_nso_1 indicates that Oracle has performed subquery decoding nesting (which is unlocked by default,

This is where the problem is. If you don't want him to solve it, you can test it.
And guest_id in (select/* + no_unnest */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 ')
Remove this section. The result set contains only eight rows of databases, and there is a lot of data in this subquery.

When there is a big difference between the two sets, it is better to solve the problem by default.