SQL statement for table quantum Query Optimization

The developer sends an SQL statement and generates 236 rows of data at 7 minutes 32 seconds 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 bytes | ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | time | period | 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 | 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) analysis: The conditions after where cannot enter the embedded view. No filtering conditions exist, let's get it in, here we want to rewrite the SQL ==================================================== ========================================================changed to subquery Results Optimized in 0.03 seconds 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 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | latency | 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 | 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)