表量子查询优化SQL

开发发来条SQL，7分32秒出236行数据
 Select Ot.Guest_Id,
       Ot.Order_Id,
       Ot.Guest_Name,
       Oct.Guest_Name,
       Ot.Ticket_Price,
       Oct.Ticket_Price
  From t_To_Order_Tickets Ot
  Left Join (Select    Oct.*,
                    (Select Order_Id
                       From t_To_Order_Change Oc
                      Where Oct.Change_Order_Id = Oc.Change_Order_Id) Order_Id
               From t_To_Order_Change_Tickets Oct) Oct
    On Ot.Order_Id = Oct.Order_Id
   And Ot.Guest_Name = Oct.Guest_Name
    Where Ot.Order_Id > (Select Max(Order_Id) From t_To_Order_Info) - 200
    
Plan hash value: 3057350477
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |   249K|    30M|       | 14809   (2)| 00:02:58 |
|*  1 |  HASH JOIN OUTER              |                           |   249K|    30M|  9248K| 14808   (2)| 00:02:58 |
|   2 |   TABLE ACCESS BY INDEX ROWID | T_TO_ORDER_TICKETS        |   249K|  6323K|       | 10597   (1)| 00:02:08 |
|*  3 |    INDEX RANGE SCAN           | TORDERINFO_PORDER_FK      | 44832 |       |       |    41   (3)| 00:00:01 |
|   4 |     SORT AGGREGATE            |                           |     1 |     6 |       |            |          |
|   5 |      INDEX FULL SCAN (MIN/MAX)| PK_T_TO_ORDER_INFO        |  3736K|    21M|       |     1   (0)| 00:00:01 |
|   6 |   VIEW                        |                           |   408K|    40M|       |  1493   (6)| 00:00:18 |
|   7 |    TABLE ACCESS FULL          | T_TO_ORDER_CHANGE_TICKETS |   408K|  7575K|       |  1493   (6)| 00:00:18 |
-------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("OT"."GUEST_NAME"="OCT"."GUEST_NAME"(+) AND "OT"."ORDER_ID"="OCT"."ORDER_ID"(+))
   3 - access("OT"."ORDER_ID"> (SELECT MAX("ORDER_ID") FROM "T_TO_ORDER_INFO" "T_TO_ORDER_INFO")-200)

分析：where后面的条件进不了内嵌视图，过滤条件没有，要把它那进去，这里要改写SQL
=================================================================== 
改为子查询后0.03秒出结果

优化后的SQL
 Select Ot.Guest_Id,
       Ot.Order_Id,
       Ot.Guest_Name,
       Ot.Ticket_Price,
   (select Guest_Name from t_To_Order_Change t1,t_To_Order_Change_Tickets t2 
  where t1.Change_Order_Id=t2.Change_Order_Id 
    and Ot.Order_Id=T1.order_id 
    and Ot.Guest_Name=t2.Guest_Name) xxx,
   (select Ticket_Price from t_To_Order_Change t1,t_To_Order_Change_Tickets t2 
  where t1.Change_Order_Id=t2.Change_Order_Id 
    and Ot.Order_Id=T1.order_id 
    and Ot.Guest_Name=t2.Guest_Name) yyy
  From t_To_Order_Tickets Ot
  Where Ot.Order_Id > (Select Max(Order_Id) From t_To_Order_Info) - 200    
  
Plan hash value: 3136190503
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |   249K|  6323K| 10598   (1)| 00:02:08 |
|*  1 |  TABLE ACCESS BY INDEX ROWID  | T_TO_ORDER_CHANGE_TICKETS   |     1 |    15 |     1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |                             |     1 |    26 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_TO_ORDER_CHANGE           |     1 |    11 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | TO_RELATIONCHANGE_FK        |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | TORDERINFO_PORDER_CHANGE_FK |     1 |       |     1   (0)| 00:00:01 |
|*  6 |  TABLE ACCESS BY INDEX ROWID  | T_TO_ORDER_CHANGE_TICKETS   |     1 |    19 |     1   (0)| 00:00:01 |
|   7 |   NESTED LOOPS                |                             |     1 |    30 |     3   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID| T_TO_ORDER_CHANGE           |     1 |    11 |     2   (0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN          | TO_RELATIONCHANGE_FK        |     1 |       |     1   (0)| 00:00:01 |
|* 10 |    INDEX RANGE SCAN           | TORDERINFO_PORDER_CHANGE_FK |     1 |       |     1   (0)| 00:00:01 |
|  11 |  TABLE ACCESS BY INDEX ROWID  | T_TO_ORDER_TICKETS          |   249K|  6323K| 10597   (1)| 00:02:08 |
|* 12 |   INDEX RANGE SCAN            | TORDERINFO_PORDER_FK        | 44832 |       |    41   (3)| 00:00:01 |
|  13 |    SORT AGGREGATE             |                             |     1 |     6 |            |          |
|  14 |     INDEX FULL SCAN (MIN/MAX) | PK_T_TO_ORDER_INFO          |  3736K|    21M|     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T2"."GUEST_NAME"=:B1)
   4 - access("T1"."ORDER_ID"=:B1)
   5 - access("T2"."CHANGE_ORDER_ID"="T1"."CHANGE_ORDER_ID")
   6 - filter("T2"."GUEST_NAME"=:B1)
   9 - access("T1"."ORDER_ID"=:B1)
  10 - access("T2"."CHANGE_ORDER_ID"="T1"."CHANGE_ORDER_ID")
  12 - access("OT"."ORDER_ID"> (SELECT /*+ */ MAX("ORDER_ID") FROM "T_TO_ORDER_INFO" 
              "T_TO_ORDER_INFO")-200)