not in和or 出的问题
来源：互联网 发布：买个便宜货知乎 编辑：程序博客网 时间：2024/05/16 17:57
开发发来一条SQL，出了几行数据，要跑40多秒，让优化下
原SQL如下
SQL> explain plan for Select Orderinfo.Order_Id, Guest_Num, Guest_Name, Pnr, Orderinfo.Order_Flow_Id, Orderinfo.Deliver_Type_Id, Orderinfo.Deliver_Employee_Name, Orderinfo.Assign_Time From (Select t_To_Order_Info.Order_Id, Guest_Num, Pnr, t_To_Order_Info.Order_Flow_Id, (Select De.Deliver_Employee_Name From t_To_Deliver_Assign_Record Dar Inner Join t_Tp_Employee_Deliver De On Dar.Deliver_Employee_Id = De.Deliver_Employee_Id Where Dar.Assign_Id = (Select Max(t.Assign_Id) From t_To_Deliver_Assign_Record t Where t.Order_Id = t_To_Order_Info.Order_Id)) Deliver_Employee_Name, (Select Dar.Deliver_Type_Id 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 = t_To_Order_Info.Order_Id)) Deliver_Type_Id, (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 = t_To_Order_Info.Order_Id)) Assign_Time, (Select Guest_Name From t_To_Order_Tickets Where t_To_Order_Tickets.Order_Id = t_To_Order_Info.Order_Id And Rownum <= 1) || Case Guest_Num When 1 Then '' Else ' ...' End Guest_Name From t_To_Order_Info Inner Join t_To_Deliver_Assign_Record Dd On t_To_Order_Info.Order_Id = Dd.Order_Id Where (t_To_Order_Info.Pay_Type In (11, 12) And t_To_Order_Info.Guest_Pymt_Sts_Id = 1 And Dd.Assign_Id = (Select Max(Assign_Id) From t_To_Deliver_Assign_Record) And Dd.Deliver_Status = 2 And Dd.Order_Type = 1 Or Dd.Order_Type = 2) And t_To_Order_Info.Partner_Id In (Select Partner_Id From t_Tp_Casher Where Employee_Id = 2422)) Orderinfo Where Orderinfo.Order_Id Not In (Select t.Order_Id From t_To_Order_Deal t Where t.Deal_Status = 2) And Orderinfo.Assign_Time >= To_Date('2012-03-01 00:00:00', 'yyyy-MM-dd HH24:MI:SS') And Orderinfo.Assign_Time <= To_Date('2012-04-17 23:59:59', 'yyyy-MM-dd HH24:MI:SS') Order By Orderinfo.Order_Id, Orderinfo.Assign_Time Desc SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3419959878-------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------------------------------------| 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虽然长，不过不用一行行看，出问题的地方比较明显，有一下2处：And Dd.Deliver_Status = 2 And Dd.Order_Type = 1 Or Dd.Order_Type = 2)Orderinfo.Order_Id Not In (Select t.Order_Id From t_To_Order_Deal t Where t.Deal_Status = 2)改写后的SQL如下，运行时间不到1秒钟
SQL> explain plan for Select Orderinfo.Order_Id, Guest_Num, Guest_Name, Pnr, Orderinfo.Order_Flow_Id, Orderinfo.Deliver_Type_Id, Orderinfo.Deliver_Employee_Name, Orderinfo.Assign_Time From (Select t_To_Order_Info.Order_Id, Guest_Num, Pnr, t_To_Order_Info.Order_Flow_Id, (Select De.Deliver_Employee_Name From t_To_Deliver_Assign_Record Dar Inner Join t_Tp_Employee_Deliver De On Dar.Deliver_Employee_Id = De.Deliver_Employee_Id Where Dar.Assign_Id = (Select Max(t.Assign_Id) From t_To_Deliver_Assign_Record t Where t.Order_Id = t_To_Order_Info.Order_Id)) Deliver_Employee_Name, (Select Dar.Deliver_Type_Id 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 = t_To_Order_Info.Order_Id)) Deliver_Type_Id, (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 = t_To_Order_Info.Order_Id)) Assign_Time, (Select Guest_Name From t_To_Order_Tickets Where t_To_Order_Tickets.Order_Id = t_To_Order_Info.Order_Id And Rownum <= 1) || Case Guest_Num When 1 Then '' Else ' ...' End Guest_Name From t_To_Order_Info Inner Join t_To_Deliver_Assign_Record Dd On t_To_Order_Info.Order_Id = Dd.Order_Id Where (t_To_Order_Info.Pay_Type In (11, 12) And t_To_Order_Info.Guest_Pymt_Sts_Id = 1 And Dd.Assign_Id = (Select Max(Assign_Id) From t_To_Deliver_Assign_Record) And Dd.Deliver_Status = 2 And (Dd.Order_Type = 1 Or Dd.Order_Type = 2)) And t_To_Order_Info.Partner_Id In (Select Partner_Id From t_Tp_Casher Where Employee_Id = 2422)) Orderinfo Where Not Exists (Select t.Order_Id From t_To_Order_Deal t Where Orderinfo.Order_Id = t.Order_Id And t.Deal_Status = 2) And Orderinfo.Assign_Time >= To_Date('2012-03-01 00:00:00', 'yyyy-MM-dd HH24:MI:SS') And Orderinfo.Assign_Time <= To_Date('2012-04-17 23:59:59', 'yyyy-MM-dd HH24:MI:SS') Order By Orderinfo.Order_Id, Orderinfo.Assign_Time Desc;SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 763589270----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------------------------| 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
总结：要熟悉SQL的写法，要快速的读出长SQL哪里有问题，这里SQL这么长，
执行计划也这么长，但是问题很明显，就没有必须一行行去分析执行计划
快速看一眼SQL就要发现问题出现在哪里，开发的经常可能搞出这种事情，
可能是因为SQL写的不熟，或者是不仔细，自己一定要熟悉这些常见瓶颈点
不要在这种SQL上面纠结浪费过多的时间。
- not in和or 出的问题
- 最近遇到的curl问题和libcurl问题（Protocol https not supported or disabled in libcurl）
- oracle not available 和 oracle initialization or shutdown in process问题
- mysql中or和in的效率问题
- mysql中or和in的效率问题
- mysql中or和in的效率问题
- mysql中or和in的效率问题
- mysql中or和in的效率问题
- mysql中or和in的效率问题
- mysql中or和in的效率问题
- mysql中or和in的效率问题
- mysql中or和in的效率问题
- mysql中or和in的效率问题
- The type or namespace name 'Zhaopin' does not exist in the namespace 'LMSoft' 问题的解决
- http not supported or disabled in libcurl 和 未将对象引用到实例的解决办法
- SQLSERVER not in 的问题
- Mysql Not in的问题
- JSTL的问题(According to TLD or attribute directive in tag file, attribute value does not accept any expressions)
- android:textAppearance的应用
- Android 4.1 Jelly Bean 相关资源
- 基于boost/asio，采用Half-Sync/Half-Async模式的跨平台服务器框架
- 关于wince6.0 升级全年包的注意事项
- 转：面试经验
- not in和or 出的问题
- Sencha Touch开发实例：记事本应用(二)
- Android --- layoutAnimation应用
- jsp 生僻字 显示
- 题目：学员日常管理—分数排名安排日常上课座位系统
- 数据结构 - 二叉树的广度优先遍历算法（C++）
- IOS--Do Not Backup属性
- axis2 webservice jar包使用情况
- 可重入与不可重入
