SQL優化--not in和or出的問題

wadekobe9發表於2012-04-19

原文網址 : http://blog.itpub.net/10678398/viewspace-721688/

SQL優化

開發發來一條SQL，出了幾行資料，要跑40多秒，讓優化下

原SQL如下

SQL> explain plan for

2 /* Formatted on 2012/04/19 14:11 (Formatter Plus v4.8.8) */

3 SELECT orderinfo.order_id, --訂單id

4 guest_num, --乘客數量

5 guest_name, --乘客姓名

6 pnr, --記錄編號

7 orderinfo.order_flow_id,

8 --退改簽

9 orderinfo.deliver_type_id, --配送方式

10 orderinfo.deliver_employee_name, --送票員

11 orderinfo.assign_time --配送時間

12 FROM (SELECT t_to_order_info.order_id, guest_num, pnr,

13 t_to_order_info.order_flow_id,

14 (SELECT de.deliver_employee_name

15 FROM t_to_deliver_assign_record dar INNER JOIN t_tp_employee_deliver de

16 ON dar.deliver_employee_id =

17 de.deliver_employee_id

18 WHERE dar.assign_id =

19 (SELECT MAX (t.assign_id)

20 FROM t_to_deliver_assign_record t

21 WHERE t.order_id = t_to_order_info.order_id))

22 deliver_employee_name,

23 (SELECT dar.deliver_type_id

24 FROM t_to_deliver_assign_record dar

25 WHERE dar.assign_id =

26 (SELECT MAX (t.assign_id)

27 FROM t_to_deliver_assign_record t

28 WHERE t.order_id = t_to_order_info.order_id))

29 deliver_type_id,

30 (SELECT dar.assign_time

31 FROM t_to_deliver_assign_record dar

32 WHERE dar.assign_id =

33 (SELECT MAX (t.assign_id)

34 FROM t_to_deliver_assign_record t

35 WHERE t.order_id =

36 t_to_order_info.order_id))

37 assign_time,

38 (SELECT guest_name

39 FROM t_to_order_tickets

40 WHERE t_to_order_tickets.order_id =

41 t_to_order_info.order_id

42 AND ROWNUM <= 1)

43 || CASE guest_num

44 WHEN 1

45 THEN ''

46 ELSE ' ...'

47 END guest_name

48 FROM t_to_order_info INNER JOIN t_to_deliver_assign_record dd

49 ON t_to_order_info.order_id = dd.order_id

50 WHERE ( t_to_order_info.pay_type IN (11, 12)

51 AND t_to_order_info.guest_pymt_sts_id = 1

52 AND dd.assign_id = (SELECT MAX (assign_id)

53 FROM t_to_deliver_assign_record)

54 AND dd.deliver_status = 2

55 AND dd.order_type = 1

56 OR dd.order_type = 2

57 )

58 AND t_to_order_info.partner_id IN (SELECT partner_id

59 FROM t_tp_casher

60 WHERE employee_id = 2422)) orderinfo

61 WHERE orderinfo.order_id NOT IN (SELECT t.order_id

62 FROM t_to_order_deal t

63 WHERE t.deal_status = 2)

64 AND orderinfo.assign_time >=

65 TO_DATE ('2012-03-01 00:00:00', 'yyyy-MM-dd HH24:MI:SS')

66 AND orderinfo.assign_time <=

67 TO_DATE ('2012-04-17 23:59:59', 'yyyy-MM-dd HH24:MI:SS')

68 ORDER BY orderinfo.order_id, orderinfo.assign_time DESC

69 ;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3419959878

-------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 980 | 52920 | | 9661 (3)| 00:01:56 |

|* 1 | COUNT STOPKEY | | | | | | |

| 2 | TABLE ACCESS BY INDEX ROWID | T_TO_ORDER_TICKETS | 1 | 16 | | 4 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | TORDERINFO_PORDER_FK | 1 | | | 3 (0)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 9 | | 3 (0)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | PK_T_TO_DELIVER_ASSIGN_RECORD | 1 | | | 2 (0)| 00:00:01 |

| 6 | SORT AGGREGATE | | 1 | 12 | | | |

| 7 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 12 | | 4 (0)| 00:00:01 |

|* 8 | INDEX RANGE SCAN | TORDERADDRESS_FK | 1 | | | 3 (0)| 00:00:01 |

| 9 | NESTED LOOPS | | 1 | 23 | | 4 (0)| 00:00:01 |

|* 10 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 8 | | 3 (0)| 00:00:01 |

|* 11 | INDEX UNIQUE SCAN | PK_T_TO_DELIVER_ASSIGN_RECORD | 1 | | | 2 (0)| 00:00:01 |

| 12 | SORT AGGREGATE | | 1 | 12 | | | |

| 13 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 12 | | 4 (0)| 00:00:01 |

|* 14 | INDEX RANGE SCAN | TORDERADDRESS_FK | 1 | | | 3 (0)| 00:00:01 |

| 15 | TABLE ACCESS BY INDEX ROWID | T_TP_EMPLOYEE_DELIVER | 1 | 15 | | 1 (0)| 00:00:01 |

|* 16 | INDEX UNIQUE SCAN | PK_T_TP_EMPLOYEE_DELIVER | 1 | | | 0 (0)| 00:00:01 |

| 17 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 14 | | 3 (0)| 00:00:01 |

|* 18 | INDEX UNIQUE SCAN | PK_T_TO_DELIVER_ASSIGN_RECORD | 1 | | | 2 (0)| 00:00:01 |

| 19 | SORT AGGREGATE | | 1 | 12 | | | |

| 20 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 12 | | 4 (0)| 00:00:01 |

|* 21 | INDEX RANGE SCAN | TORDERADDRESS_FK | 1 | | | 3 (0)| 00:00:01 |

| 22 | SORT ORDER BY | | 980 | 52920 | | 9661 (3)| 00:01:56 |

| 23 | CONCATENATION | | | | | | |

|* 24 | HASH JOIN RIGHT SEMI | | 979 | 52866 | | 8145 (3)| 00:01:38 |

|* 25 | TABLE ACCESS FULL | T_TP_CASHER | 3 | 24 | | 3 (0)| 00:00:01 |

| 26 | MERGE JOIN | | 30029 | 1348K| | 8141 (3)| 00:01:38 |

| 27 | TABLE ACCESS BY INDEX ROWID | T_TO_ORDER_INFO | 1602K| 42M| | 3654 (2)| 00:00:44 |

|* 28 | INDEX FULL SCAN | PK_T_TO_ORDER_INFO | 200 | | | 3497 (2)| 00:00:42 |

|* 29 | TABLE ACCESS FULL | T_TO_ORDER_DEAL | 1 | 9 | | 3 (0)| 00:00:01 |

| 30 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 14 | | 3 (0)| 00:00:01 |

|* 31 | INDEX UNIQUE SCAN | PK_T_TO_DELIVER_ASSIGN_RECORD | 1 | | | 2 (0)| 00:00:01 |

| 32 | SORT AGGREGATE | | 1 | 12 | | | |

| 33 | TABLE ACCESS BY INDEX ROWID| T_TO_DELIVER_ASSIGN_RECORD | 1 | 12 | | 4 (0)| 00:00:01 |

|* 34 | INDEX RANGE SCAN | TORDERADDRESS_FK | 1 | | | 3 (0)| 00:00:01 |

|* 35 | SORT JOIN | | 30029 | 527K| 1896K| 4487 (4)| 00:00:54 |

|* 36 | TABLE ACCESS FULL | T_TO_DELIVER_ASSIGN_RECORD | 30029 | 527K| | 4309 (4)| 00:00:52 |

| 37 | NESTED LOOPS SEMI | | 1 | 54 | | 8 (0)| 00:00:01 |

| 38 | NESTED LOOPS | | 1 | 46 | | 5 (0)| 00:00:01 |

|* 39 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 18 | | 3 (0)| 00:00:01 |

|* 40 | INDEX UNIQUE SCAN | PK_T_TO_DELIVER_ASSIGN_RECORD | 1 | | | 2 (0)| 00:00:01 |

| 41 | SORT AGGREGATE | | 1 | 6 | | | |

| 42 | INDEX FULL SCAN (MIN/MAX) | PK_T_TO_DELIVER_ASSIGN_RECORD | 1637K| 9596K| | 3 (0)| 00:00:01 |

|* 43 | TABLE ACCESS BY INDEX ROWID | T_TO_ORDER_INFO | 1 | 28 | | 2 (0)| 00:00:01 |

|* 44 | INDEX UNIQUE SCAN | PK_T_TO_ORDER_INFO | 1 | | | 1 (0)| 00:00:01 |

|* 45 | TABLE ACCESS FULL | T_TO_ORDER_DEAL | 1 | 9 | | 3 (0)| 00:00:01 |

| 46 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 14 | | 3 (0)| 00:00:01 |

|* 47 | INDEX UNIQUE SCAN | PK_T_TO_DELIVER_ASSIGN_RECORD | 1 | | | 2 (0)| 00:00:01 |

| 48 | SORT AGGREGATE | | 1 | 12 | | | |

| 49 | TABLE ACCESS BY INDEX ROWID| T_TO_DELIVER_ASSIGN_RECORD | 1 | 12 | | 4 (0)| 00:00:01 |

|* 50 | INDEX RANGE SCAN | TORDERADDRESS_FK | 1 | | | 3 (0)| 00:00:01 |

|* 51 | TABLE ACCESS FULL | T_TP_CASHER | 1 | 8 | | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=1)

3 - access("T_TO_ORDER_TICKETS"."ORDER_ID"=:B1)

5 - access("DAR"."ASSIGN_ID"= (SELECT /*+ */ MAX("T"."ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD" "T" WHERE

"T"."ORDER_ID"=:B1))

8 - access("T"."ORDER_ID"=:B1)

10 - filter("DAR"."DELIVER_EMPLOYEE_ID" IS NOT NULL)

11 - access("DAR"."ASSIGN_ID"= (SELECT /*+ */ MAX("T"."ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD" "T" WHERE

"T"."ORDER_ID"=:B1))

14 - access("T"."ORDER_ID"=:B1)

16 - access("DAR"."DELIVER_EMPLOYEE_ID"="DE"."DELIVER_EMPLOYEE_ID")

18 - access("DAR"."ASSIGN_ID"= (SELECT /*+ */ MAX("T"."ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD" "T" WHERE

"T"."ORDER_ID"=:B1))

21 - access("T"."ORDER_ID"=:B1)

24 - access("T_TO_ORDER_INFO"."PARTNER_ID"="PARTNER_ID")

25 - filter("EMPLOYEE_ID"=2422)

28 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T_TO_ORDER_DEAL" "T" WHERE "T"."DEAL_STATUS"=2 AND

LNNVL("T"."ORDER_ID"<>:B1)) AND (SELECT /*+ */ "DAR"."ASSIGN_TIME" FROM "T_TO_DELIVER_ASSIGN_RECORD" "DAR" WHERE

"DAR"."ASSIGN_ID"= (SELECT /*+ */ MAX("T"."ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD" "T" WHERE

"T"."ORDER_ID"=:B2))>=TO_DATE('2012-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND (SELECT /*+ */ "DAR"."ASSIGN_TIME"

FROM "T_TO_DELIVER_ASSIGN_RECORD" "DAR" WHERE "DAR"."ASSIGN_ID"= (SELECT /*+ */ MAX("T"."ASSIGN_ID") FROM

"T_TO_DELIVER_ASSIGN_RECORD" "T" WHERE "T"."ORDER_ID"=:B3))<=TO_DATE('2012-04-17 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))

29 - filter("T"."DEAL_STATUS"=2 AND LNNVL("T"."ORDER_ID"<>:B1))

31 - access("DAR"."ASSIGN_ID"= (SELECT /*+ */ MAX("T"."ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD" "T" WHERE

"T"."ORDER_ID"=:B1))

34 - access("T"."ORDER_ID"=:B1)

35 - access("DD"."ORDER_ID"="T_TO_ORDER_INFO"."ORDER_ID")

filter("DD"."ORDER_ID"="T_TO_ORDER_INFO"."ORDER_ID")

36 - filter("DD"."ORDER_TYPE"=2)

39 - filter("DD"."DELIVER_STATUS"=2 AND "DD"."ORDER_TYPE"=1 AND LNNVL("DD"."ORDER_TYPE"=2))

40 - access("DD"."ASSIGN_ID"= (SELECT /*+ */ MAX("ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD"

"T_TO_DELIVER_ASSIGN_RECORD"))

43 - filter("T_TO_ORDER_INFO"."GUEST_PYMT_STS_ID"=1 AND ("T_TO_ORDER_INFO"."PAY_TYPE"=11 OR

"T_TO_ORDER_INFO"."PAY_TYPE"=12))

44 - access("DD"."ORDER_ID"="T_TO_ORDER_INFO"."ORDER_ID")

filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T_TO_ORDER_DEAL" "T" WHERE "T"."DEAL_STATUS"=2 AND

LNNVL("T"."ORDER_ID"<>:B1)) AND (SELECT /*+ */ "DAR"."ASSIGN_TIME" FROM "T_TO_DELIVER_ASSIGN_RECORD" "DAR" WHERE

"DAR"."ASSIGN_ID"= (SELECT /*+ */ MAX("T"."ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD" "T" WHERE

"T"."ORDER_ID"=:B2))>=TO_DATE('2012-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND (SELECT /*+ */ "DAR"."ASSIGN_TIME"

FROM "T_TO_DELIVER_ASSIGN_RECORD" "DAR" WHERE "DAR"."ASSIGN_ID"= (SELECT /*+ */ MAX("T"."ASSIGN_ID") FROM

"T_TO_DELIVER_ASSIGN_RECORD" "T" WHERE "T"."ORDER_ID"=:B3))<=TO_DATE('2012-04-17 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))

45 - filter("T"."DEAL_STATUS"=2 AND LNNVL("T"."ORDER_ID"<>:B1))

47 - access("DAR"."ASSIGN_ID"= (SELECT /*+ */ MAX("T"."ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD" "T" WHERE

"T"."ORDER_ID"=:B1))

50 - access("T"."ORDER_ID"=:B1)

51 - filter("EMPLOYEE_ID"=2422 AND "T_TO_ORDER_INFO"."PARTNER_ID"="PARTNER_ID")

執行計劃和SQL都較長，不過這裡有兩個明顯的地方,我在sql中標紅了

=================================================== ===================================================

改寫後的SQL如下，執行時間不到1秒鐘，改動的地方已標紅

SQL> explain plan for

2 Select Orderinfo.Order_Id, --訂單id

3 Guest_Num, --乘客數量

4 Guest_Name, --乘客姓名

5 Pnr, --記錄編號

6 Orderinfo.Order_Flow_Id, --退改簽

7 Orderinfo.Deliver_Type_Id, --配送方式

8 Orderinfo.Deliver_Employee_Name, --送票員

9 Orderinfo.Assign_Time --配送時間

10 From (Select t_To_Order_Info.Order_Id,

11 Guest_Num,

12 Pnr,

13 t_To_Order_Info.Order_Flow_Id,

14 (Select De.Deliver_Employee_Name

15 From t_To_Deliver_Assign_Record Dar

16 Inner Join t_Tp_Employee_Deliver De

17 On Dar.Deliver_Employee_Id = De.Deliver_Employee_Id

18 Where Dar.Assign_Id =

19 (Select Max(t.Assign_Id)

20 From t_To_Deliver_Assign_Record t

21 Where t.Order_Id = t_To_Order_Info.Order_Id)) Deliver_Employee_Name,

22 (Select Dar.Deliver_Type_Id

23 From t_To_Deliver_Assign_Record Dar

24 Where Dar.Assign_Id =

25 (Select Max(t.Assign_Id)

26 From t_To_Deliver_Assign_Record t

27 Where t.Order_Id = t_To_Order_Info.Order_Id)) Deliver_Type_Id,

28 (Select Dar.Assign_Time

29 From t_To_Deliver_Assign_Record Dar

30 Where Dar.Assign_Id =

31 (Select Max(t.Assign_Id)

32 From t_To_Deliver_Assign_Record t

33 Where t.Order_Id = t_To_Order_Info.Order_Id)) Assign_Time,

34 (Select Guest_Name

35 From t_To_Order_Tickets

36 Where t_To_Order_Tickets.Order_Id = t_To_Order_Info.Order_Id

37 And Rownum <= 1) || Case Guest_Num

38 When 1 Then

39 ''

40 Else

41 ' ...'

42 End Guest_Name

43 From t_To_Order_Info

44 Inner Join t_To_Deliver_Assign_Record Dd

45 On t_To_Order_Info.Order_Id = Dd.Order_Id

46 Where (t_To_Order_Info.Pay_Type In (11, 12) And

47 t_To_Order_Info.Guest_Pymt_Sts_Id = 1 And

48 Dd.Assign_Id =

49 (Select Max(Assign_Id) From t_To_Deliver_Assign_Record) And

50 Dd.Deliver_Status = 2 And (Dd.Order_Type = 1 Or

51 Dd.Order_Type = 2))

52 And t_To_Order_Info.Partner_Id In

53 (Select Partner_Id From t_Tp_Casher Where Employee_Id = 2422)) Orderinfo

54 Where Not exists

55 (Select t.Order_Id From t_To_Order_Deal t Where Orderinfo.Order_Id=t.order_id And t.Deal_Status = 2)

56 And Orderinfo.Assign_Time >=

57 To_Date('2012-03-01 00:00:00', 'yyyy-MM-dd HH24:MI:SS')

58 And Orderinfo.Assign_Time <=

59 To_Date('2012-04-17 23:59:59', 'yyyy-MM-dd HH24:MI:SS')

60 Order By Orderinfo.Order_Id, Orderinfo.Assign_Time Desc

61 ;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 763589270

----------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 63 | 30 (7)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | TABLE ACCESS BY INDEX ROWID | T_TO_ORDER_TICKETS | 1 | 16 | 4 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | TORDERINFO_PORDER_FK | 1 | | 3 (0)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 9 | 3 (0)| 00:00:01 |

|* 5 | INDEX UNIQUE SCAN | PK_T_TO_DELIVER_ASSIGN_RECORD | 1 | | 2 (0)| 00:00:01 |

| 6 | SORT AGGREGATE | | 1 | 12 | | |

| 7 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 12 | 4 (0)| 00:00:01 |

|* 8 | INDEX RANGE SCAN | TORDERADDRESS_FK | 1 | | 3 (0)| 00:00:01 |

| 9 | NESTED LOOPS | | 1 | 23 | 4 (0)| 00:00:01 |

|* 10 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 8 | 3 (0)| 00:00:01 |

|* 11 | INDEX UNIQUE SCAN | PK_T_TO_DELIVER_ASSIGN_RECORD | 1 | | 2 (0)| 00:00:01 |

| 12 | SORT AGGREGATE | | 1 | 12 | | |

| 13 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 12 | 4 (0)| 00:00:01 |

|* 14 | INDEX RANGE SCAN | TORDERADDRESS_FK | 1 | | 3 (0)| 00:00:01 |

| 15 | TABLE ACCESS BY INDEX ROWID | T_TP_EMPLOYEE_DELIVER | 1 | 15 | 1 (0)| 00:00:01 |

|* 16 | INDEX UNIQUE SCAN | PK_T_TP_EMPLOYEE_DELIVER | 1 | | 0 (0)| 00:00:01 |

| 17 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 14 | 3 (0)| 00:00:01 |

|* 18 | INDEX UNIQUE SCAN | PK_T_TO_DELIVER_ASSIGN_RECORD | 1 | | 2 (0)| 00:00:01 |

| 19 | SORT AGGREGATE | | 1 | 12 | | |

| 20 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 12 | 4 (0)| 00:00:01 |

|* 21 | INDEX RANGE SCAN | TORDERADDRESS_FK | 1 | | 3 (0)| 00:00:01 |

| 22 | SORT ORDER BY | | 1 | 63 | 30 (7)| 00:00:01 |

|* 23 | HASH JOIN SEMI | | 1 | 63 | 12 (9)| 00:00:01 |

| 24 | NESTED LOOPS ANTI | | 1 | 55 | 8 (0)| 00:00:01 |

| 25 | NESTED LOOPS | | 1 | 46 | 5 (0)| 00:00:01 |

|* 26 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 18 | 3 (0)| 00:00:01 |

|* 27 | INDEX UNIQUE SCAN | PK_T_TO_DELIVER_ASSIGN_RECORD | 1 | | 2 (0)| 00:00:01 |

| 28 | SORT AGGREGATE | | 1 | 6 | | |

| 29 | INDEX FULL SCAN (MIN/MAX) | PK_T_TO_DELIVER_ASSIGN_RECORD | 1637K| 9596K| 3 (0)| 00:00:01 |

|* 30 | TABLE ACCESS BY INDEX ROWID | T_TO_ORDER_INFO | 1 | 28 | 2 (0)| 00:00:01 |

|* 31 | INDEX UNIQUE SCAN | PK_T_TO_ORDER_INFO | 1 | | 1 (0)| 00:00:01 |

| 32 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 14 | 3 (0)| 00:00:01 |

|* 33 | INDEX UNIQUE SCAN | PK_T_TO_DELIVER_ASSIGN_RECORD | 1 | | 2 (0)| 00:00:01 |

| 34 | SORT AGGREGATE | | 1 | 12 | | |

| 35 | TABLE ACCESS BY INDEX ROWID| T_TO_DELIVER_ASSIGN_RECORD | 1 | 12 | 4 (0)| 00:00:01 |

|* 36 | INDEX RANGE SCAN | TORDERADDRESS_FK | 1 | | 3 (0)| 00:00:01 |

|* 37 | TABLE ACCESS FULL | T_TO_ORDER_DEAL | 1 | 9 | 3 (0)| 00:00:01 |

|* 38 | TABLE ACCESS FULL | T_TP_CASHER | 3 | 24 | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=1)

3 - access("T_TO_ORDER_TICKETS"."ORDER_ID"=:B1)

5 - access("DAR"."ASSIGN_ID"= (SELECT /*+ */ MAX("T"."ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD" "T"

WHERE "T"."ORDER_ID"=:B1))

8 - access("T"."ORDER_ID"=:B1)

10 - filter("DAR"."DELIVER_EMPLOYEE_ID" IS NOT NULL)

11 - access("DAR"."ASSIGN_ID"= (SELECT /*+ */ MAX("T"."ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD" "T"

WHERE "T"."ORDER_ID"=:B1))

14 - access("T"."ORDER_ID"=:B1)

16 - access("DAR"."DELIVER_EMPLOYEE_ID"="DE"."DELIVER_EMPLOYEE_ID")

18 - access("DAR"."ASSIGN_ID"= (SELECT /*+ */ MAX("T"."ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD" "T"

WHERE "T"."ORDER_ID"=:B1))

21 - access("T"."ORDER_ID"=:B1)

23 - access("T_TO_ORDER_INFO"."PARTNER_ID"="PARTNER_ID")

26 - filter("DD"."DELIVER_STATUS"=2 AND ("DD"."ORDER_TYPE"=1 OR "DD"."ORDER_TYPE"=2))

27 - access("DD"."ASSIGN_ID"= (SELECT /*+ */ MAX("ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD"

"T_TO_DELIVER_ASSIGN_RECORD"))

30 - filter("T_TO_ORDER_INFO"."GUEST_PYMT_STS_ID"=1 AND ("T_TO_ORDER_INFO"."PAY_TYPE"=11 OR

"T_TO_ORDER_INFO"."PAY_TYPE"=12))

31 - access("DD"."ORDER_ID"="T_TO_ORDER_INFO"."ORDER_ID")

filter( (SELECT /*+ */ "DAR"."ASSIGN_TIME" FROM "T_TO_DELIVER_ASSIGN_RECORD" "DAR" WHERE

"DAR"."ASSIGN_ID"= (SELECT /*+ */ MAX("T"."ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD" "T" WHERE

"T"."ORDER_ID"=:B1))>=TO_DATE('2012-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND (SELECT /*+ */

"DAR"."ASSIGN_TIME" FROM "T_TO_DELIVER_ASSIGN_RECORD" "DAR" WHERE "DAR"."ASSIGN_ID"= (SELECT /*+ */

MAX("T"."ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD" "T" WHERE "T"."ORDER_ID"=:B2))<=TO_DATE('2012-04-17

23:59:59', 'yyyy-mm-dd hh24:mi:ss'))

33 - access("DAR"."ASSIGN_ID"= (SELECT /*+ */ MAX("T"."ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD" "T"

WHERE "T"."ORDER_ID"=:B1))

36 - access("T"."ORDER_ID"=:B1)

37 - filter("T"."DEAL_STATUS"=2 AND "T_TO_ORDER_INFO"."ORDER_ID"="T"."ORDER_ID")

38 - filter("EMPLOYEE_ID"=2422)

80 rows selected

===================================================================================================

這裡在or那裡新增了括號實際上出來的結果是不一樣的，我在詢問了開發人員的意思明白了這個地方確實是

需要新增括號才能做到要求的業務邏輯。

用soctt使用者做個實驗檢視一下兩者的差異

SQL> Select * From emp Where job='CLERK' And DEPTNO=20 Or deptno=30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

8 rows selected

--------------------------------------------------------------------------------------------------

SQL> Select * From emp Where job='CLERK' And (DEPTNO=20 Or deptno=30);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7876 ADAMS CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

--------------------------------------------------------------------------------------------------

(DEPTNO=20 Or deptno=30)的寫法當然就可以用in來代替了

SQL> Select * From emp Where job='CLERK' And DEPTNO in(20,30);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7876 ADAMS CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10678398/viewspace-721688/，如需轉載，請註明出處，否則將追究法律責任。

記錄一次SQL函式和優化的問題
2022-03-22
SQL函式優化
資料庫sql的優化問題的面試題
2018-04-16
資料庫SQL優化面試題
[20181119]使用sql profile優化問題.txt
2018-11-19
SQL優化
sql優化專題
2020-09-23
SQL優化
[20211229]toad下優化sql語句注意的問題.txt
2021-12-30
優化SQL
SQL優化案例-從執行計劃定位SQL問題（三）
2018-11-28
SQL優化
Navicat 匯出sql問題
2018-09-19
SQL
SQL最佳化問題
2022-07-29
SQL
Oracle 調優確定存在問題的SQL
2020-07-02
OracleSQL
凸優化問題
2020-07-05
優化
數值最優化—優化問題的解(二)
2020-09-24
優化
Oracle優化案例-從執行計劃定位SQL問題（三）
2018-07-01
Oracle優化SQL
go的編譯優化問題
2018-11-12
Go編譯優化
基於mysql資料庫關於sql優化的一些問題
2018-10-19
MySql資料庫優化
sql優化面試必問【簡答】
2020-11-26
SQL優化面試
斜率優化（凸包優化）DP問題acm
2020-10-09
優化ACM
Oracle優化案例-join列索引缺失導致的sql效能問題（二十六）
2020-03-06
Oracle優化索引SQL
[20211210]優化遇到的奇怪問題.txt
2021-12-14
優化
MySQL複製效能優化和常見問題分析
2018-11-09
MySql優化
SQL優化的方法論
2020-04-04
SQL優化
03-凸優化問題
2021-06-21
優化
sql優化之邏輯優化
2018-12-12
SQL優化
資料庫優化 - SQL優化
2020-01-11
資料庫優化SQL
SQL SERVER優化
2018-10-30
SQLServer優化
SQL優化指南
2018-12-01
SQL優化
iOS使用Instrument Time Profiler工具分析和優化效能問題
2019-04-04
iOS優化
關於vue的webpack打包優化問題
2018-03-30
VueWeb優化
SQL語句中的AND和OR執行順序問題
2020-11-26
SQL
優化GAN的分佈的梯度問題，WGAN
2020-12-22
優化梯度
SQL優化之統計資訊和索引
2018-12-17
SQL優化索引
【離散優化】覆蓋問題
2020-06-21
優化
記一個效能優化問題
2019-04-01
優化
N皇后問題（各種優化）
2020-12-02
優化
電腦出現常見問題卡頓，教你優化提速技巧
2021-10-27
優化
Oracle效能優化-SQL優化(案例一)
2020-02-17
Oracle優化SQL
Oracle效能優化-SQL優化(案例二)
2020-02-17
Oracle優化SQL
Oracle效能優化-SQL優化(案例三)
2020-02-18
Oracle優化SQL
Oracle效能優化-SQL優化(案例四)
2020-02-18
Oracle優化SQL
[20221008]sql profile最佳化失效問題.txt
2022-10-10
SQL

SQL優化--not in和or出的問題

相關文章