分页语句where条件中的子查询有or关键字优化

mac2022-06-30 85

背景

开发说：有段SQL语句，去掉order by很快，有order by之后，查询1小时都无法返回结果。我叫他把SQL扔给我看下。

SQL代码及执行计划

select * from (select d.*, rownum as num from (SELECT A.BILLNO, A.BILLCODE, A.GETDATE, A.GETUNITCODE, A.GETCODE, A.GETORGANCODE, A.USEORGANCODE, A.USEDATE, A.USEUNITCODE, A.USERCODE, A.CURRENCYCODE, A.AMOUNT, A.NAME, A.NOTES, A.STATUSCODE, A.IFPAGEONHOLE, A.OPCODE, A.OPUNITCODE, A.OPDATE, A.LOCKTIME, A.GETAGENTCODE, (SELECT D.AGENTNAME FROM SYN_MM_AGENTCODE_TC D WHERE D.AGENTCODE = A.GETAGENTCODE) AS GETAGENTNAME, A.USEAGENTCODE, A.OUTSTATUS, CASE A.BILLCODE WHEN 'B2010005' THEN A.FACTBILLCODE ELSE '' END FACTBILLCODE, A.SALES, A.FROMDATE, A.TODATE, (SELECT BILLNAME FROM BD_BILLCODE WHERE BILLCODE = A.BILLCODE) BILLNAME, (SELECT HANDLERNAME FROM BD_HANDLER WHERE HANDLERCODE = A.USERCODE) USERNAME, (SELECT HANDLERNAME FROM BD_HANDLER WHERE HANDLERCODE = A.GETCODE) GETERNAME, (SELECT NO3 FROM B_BILLDETAIL WHERE BILLNO = A.BILLNO AND BILLCODE = A.BILLCODE AND FACTBILLCODE = A.FACTBILLCODE) ONLINEINVOICENO, (SELECT NO4 FROM B_BILLDETAIL WHERE BILLNO = A.BILLNO AND BILLCODE = A.BILLCODE AND FACTBILLCODE = A.FACTBILLCODE) ONLINEINVOICECODE FROM B_BILL A WHERE 3 > 2 AND (3 > 2 OR 3 > 2 OR 3 > 2 OR 3 > 2 OR 3 > 2 OR 3 > 2) AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND ((TRIM(GETUNITCODE) = '013100' OR GETAGENTCODE IN (SELECT DISTINCT A.AGENTCODE FROM CIOD_IM.CHAGENTBASE A WHERE 1 = 1 AND TRIM(A.BRANCHCODE) = '013100'))) ORDER BY A.BILLNO) d where rownum <= 100) where num > 0 ; Plan hash value: 677599094 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 27792 | 11 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | CHAGENTBASE | 1 | 56 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_CHAGENTBASE_TEST | 1 | | 1 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID | BD_BILLCODE | 1 | 31 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_BD_BILLCODE | 1 | | 0 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID | T_EMPLOYEE_VIEW | 1 | 26 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | IND_T_EMPLOYEE_VIEW | 1 | | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | T_EMPLOYEE_VIEW | 1 | 26 | 2 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | IND_T_EMPLOYEE_VIEW | 1 | | 1 (0)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID | B_BILLDETAIL | 1 | 50 | 4 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | PK_B_BILLDETAIL_02 | 1 | | 3 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID | B_BILLDETAIL | 1 | 50 | 4 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | PK_B_BILLDETAIL_02 | 1 | | 3 (0)| 00:00:01 | |* 13 | VIEW | | 6 | 27792 | 11 (0)| 00:00:01 | |* 14 | COUNT STOPKEY | | | | | | | 15 | VIEW | | 6 | 27714 | 11 (0)| 00:00:01 | |* 16 | FILTER | | | | | | | 17 | TABLE ACCESS BY INDEX ROWID| B_BILL | 56M| 10G| 11 (0)| 00:00:01 | | 18 | INDEX FULL SCAN | PK_B_BILL_01 | 100 | | 4 (0)| 00:00:01 | |* 19 | TABLE ACCESS BY INDEX ROWID| CHAGENTBASE | 1 | 15 | 2 (0)| 00:00:01 | |* 20 | INDEX RANGE SCAN | IDX_CHAGENTBASE_TEST | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("D"."AGENTCODE"=:B1) 4 - access("BILLCODE"=:B1) 6 - access("A"."CODE"=:B1) 8 - access("A"."CODE"=:B1) 10 - access("BILLNO"=:B1 AND "BILLCODE"=:B2 AND "FACTBILLCODE"=:B3) 12 - access("BILLNO"=:B1 AND "BILLCODE"=:B2 AND "FACTBILLCODE"=:B3) 13 - filter("NUM">0) 14 - filter(ROWNUM<=100) 16 - filter(TRIM("GETUNITCODE")='013100' OR EXISTS (SELECT /*+ */ 0 FROM "CIOD_IM"."CHAGENTBASE" "A" WHERE "A"."AGENTCODE"=:B1 AND TRIM("A"."BRANCHCODE")='013100')) 19 - filter(TRIM("A"."BRANCHCODE")='013100') 20 - access("A"."AGENTCODE"=:B1)

优化

1）建立索引

create index IDX_B_BILL_TEST03 on B_BILL (GETAGENTCODE, BILLNO);

2）添加hint

/*+index_rs(a)*/

3）or改写成union

select * from (select d.*, rownum as num from (SELECT /*+index_rs(a)*/ A.BILLNO, A.BILLCODE, A.GETDATE, A.GETUNITCODE, A.GETCODE, A.GETORGANCODE, A.USEORGANCODE, A.USEDATE, A.USEUNITCODE, A.USERCODE, A.CURRENCYCODE, A.AMOUNT, A.NAME, A.NOTES, A.STATUSCODE, A.IFPAGEONHOLE, A.OPCODE, A.OPUNITCODE, A.OPDATE, A.LOCKTIME, A.GETAGENTCODE, (SELECT D.AGENTNAME FROM SYN_MM_AGENTCODE_TC D WHERE D.AGENTCODE = A.GETAGENTCODE) AS GETAGENTNAME, A.USEAGENTCODE, A.OUTSTATUS, CASE A.BILLCODE WHEN 'B2010005' THEN A.FACTBILLCODE ELSE '' END FACTBILLCODE, A.SALES, A.FROMDATE, A.TODATE, (SELECT BILLNAME FROM BD_BILLCODE WHERE BILLCODE = A.BILLCODE) BILLNAME, (SELECT HANDLERNAME FROM BD_HANDLER WHERE HANDLERCODE = A.USERCODE) USERNAME, (SELECT HANDLERNAME FROM BD_HANDLER WHERE HANDLERCODE = A.GETCODE) GETERNAME, (SELECT NO3 FROM B_BILLDETAIL WHERE BILLNO = A.BILLNO AND BILLCODE = A.BILLCODE AND FACTBILLCODE = A.FACTBILLCODE) ONLINEINVOICENO, (SELECT NO4 FROM B_BILLDETAIL WHERE BILLNO = A.BILLNO AND BILLCODE = A.BILLCODE AND FACTBILLCODE = A.FACTBILLCODE) ONLINEINVOICECODE FROM B_BILL A WHERE 3 > 2 AND (3 > 2 OR 3 > 2 OR 3 > 2 OR 3 > 2 OR 3 > 2 OR 3 > 2) AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND ((TRIM(GETUNITCODE) = '013100' /*OR GETAGENTCODE IN (SELECT A.AGENTCODE FROM CIOD_IM.CHAGENTBASE A WHERE 1 = 1 AND TRIM(A.BRANCHCODE) = '013100')*/ )) union SELECT A.BILLNO, A.BILLCODE, A.GETDATE, A.GETUNITCODE, A.GETCODE, A.GETORGANCODE, A.USEORGANCODE, A.USEDATE, A.USEUNITCODE, A.USERCODE, A.CURRENCYCODE, A.AMOUNT, A.NAME, A.NOTES, A.STATUSCODE, A.IFPAGEONHOLE, A.OPCODE, A.OPUNITCODE, A.OPDATE, A.LOCKTIME, A.GETAGENTCODE, (SELECT D.AGENTNAME FROM SYN_MM_AGENTCODE_TC D WHERE D.AGENTCODE = A.GETAGENTCODE) AS GETAGENTNAME, A.USEAGENTCODE, A.OUTSTATUS, CASE A.BILLCODE WHEN 'B2010005' THEN A.FACTBILLCODE ELSE '' END FACTBILLCODE, A.SALES, A.FROMDATE, A.TODATE, (SELECT BILLNAME FROM BD_BILLCODE WHERE BILLCODE = A.BILLCODE) BILLNAME, (SELECT HANDLERNAME FROM BD_HANDLER WHERE HANDLERCODE = A.USERCODE) USERNAME, (SELECT HANDLERNAME FROM BD_HANDLER WHERE HANDLERCODE = A.GETCODE) GETERNAME, (SELECT NO3 FROM B_BILLDETAIL WHERE BILLNO = A.BILLNO AND BILLCODE = A.BILLCODE AND FACTBILLCODE = A.FACTBILLCODE) ONLINEINVOICENO, (SELECT NO4 FROM B_BILLDETAIL WHERE BILLNO = A.BILLNO AND BILLCODE = A.BILLCODE AND FACTBILLCODE = A.FACTBILLCODE) ONLINEINVOICECODE FROM B_BILL A WHERE 3 > 2 AND (3 > 2 OR 3 > 2 OR 3 > 2 OR 3 > 2 OR 3 > 2 OR 3 > 2) AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND 3 > 2 AND (( /*TRIM(GETUNITCODE) = '013100' OR*/ GETAGENTCODE IN (SELECT A.AGENTCODE FROM CIOD_IM.CHAGENTBASE A WHERE 1 = 1 AND TRIM(A.BRANCHCODE) = '013100'))) ORDER BY BILLNO) d where rownum <= 100) where num > 0; Plan hash value: 215809904 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 452K| | 76511 (34)| 00:15:19 | |* 1 | VIEW | | 100 | 452K| | 76511 (34)| 00:15:19 | |* 2 | COUNT STOPKEY | | | | | | | | 3 | VIEW | | 570K| 2511M| | 76511 (34)| 00:15:19 | |* 4 | SORT UNIQUE STOPKEY | | 570K| 104M| 278M| 50812 (46)| 00:10:10 | | 5 | UNION-ALL | | | | | | | | 6 | TABLE ACCESS BY INDEX ROWID| B_BILL | 569K| 104M| | 3755 (1)| 00:00:46 | |* 7 | INDEX RANGE SCAN | IDX_B_BILL_TEST | 227K| | | 771 (1)| 00:00:10 | | 8 | TABLE ACCESS BY INDEX ROWID| B_BILL | 81 | 15552 | | 7742 (1)| 00:01:33 | | 9 | NESTED LOOPS | | 262 | 54234 | | 23093 (1)| 00:04:38 | |* 10 | TABLE ACCESS FULL | CHAGENTBASE | 3 | 45 | | 6 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | IDX_B_BILL_TEST03 | 1089 | | | 7672 (1)| 00:01:33 | -------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NUM">0) 2 - filter(ROWNUM<=100) 4 - filter(ROWNUM<=100) 7 - access(TRIM("GETUNITCODE")='013100') 10 - filter(TRIM("A"."BRANCHCODE")='013100') 11 - access("GETAGENTCODE"="A"."AGENTCODE") filter("GETAGENTCODE" IS NOT NULL)

优化后

1s能返回结果，性能大大提升。开发很开心地把SQL拿走了。

转载于:https://www.cnblogs.com/wanbin/p/9514614.html

最新回复(0)