先看正常秒出的sql
SELECT
很多列
FROM NBA_WELCOME_SELLS A
LEFT JOIN NBA_WELCO_DICTIONARY B ON A.PRINT_FLG = B.DATA_ID AND B.DIC_ID = 'PRINT_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY C ON A.MARK_FLG = C.DATA_ID AND C.DIC_ID = 'MARK_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY D ON A.AUTO_FLG = D.DATA_ID AND D.DIC_ID = 'YESorNO'
LEFT JOIN NBA_WELCO_DICTIONARY E ON A.DELI_TYPE = E.DATA_ID AND E.DIC_ID = 'DELI_TYPE'
LEFT JOIN NBA_WELCO_DICTIONARY F ON A.PAYMENT_ID = F.DATA_ID AND F.DIC_ID = 'PAYMENT_TYPE'
LEFT JOIN NBA_WELCO_DICTIONARY G ON A.COD_FLG = G.DATA_ID AND G.DIC_ID = 'YESorNO'
LEFT JOIN NBA_WELCO_DICTIONARY H ON A.INVOICE_FLG = H.DATA_ID AND H.DIC_ID = 'YoNInvoice'
LEFT JOIN NBA_WELCO_DICTIONARY I ON A.INVOICE_TYPE = I.DATA_ID AND I.DIC_ID = 'INVOICE_TYPE'
LEFT JOIN NBA_WELCO_DICTIONARY J ON A.LOGISTICS_FLG = J.DATA_ID AND J.DIC_ID = 'LOGISTICS_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY K ON A.SALE_FLG = K.DATA_ID AND K.DIC_ID = 'SALE_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY L ON A.STATUS_FLG = L.DATA_ID AND L.DIC_ID = 'ORDER_STATUS'
LEFT JOIN NBA_WELCO_DICTIONARY O ON A.PAUSE_FLG = O.DATA_ID AND O.DIC_ID = 'PAUSE_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY P ON A.ASSIGN_FLG = P.DATA_ID AND P.DIC_ID = 'YESorNO'
LEFT JOIN NBA_WELCO_DICTIONARY Q ON A.ABNORMAL_FLG = Q.DATA_ID AND Q.DIC_ID = 'ABNORMAL_TYPE'
LEFT JOIN NBA_WELCO_DICTIONARY V ON A.STORE_ID = V.DATA_ID AND V.DIC_ID = 'STORE_ID'
LEFT JOIN (SELECT STAFF_ID, MAX(STAFF_NAME) AS STAFF_NAME FROM NBA_WELC_STAFF GROUP BY STAFF_ID) T ON A.ASSIGN_STAFF = T.STAFF_ID
LEFT JOIN (SELECT SALE_ID, SUM(QTY) AS QTY, SUM(SENT_QTY) AS SENT_QTY
FROM NBA_WELCOME_SELLS_DTL
WHERE SALE_ID IN ('123456','345688','567567567')
AND DPET_ID = '10001'
GROUP BY SALE_ID) R ON A.SALE_ID = R.SALE_ID
LEFT JOIN DBA_WELCOME_EXPRESS S ON A.LOGISTICS_COMPANY = S.CODE AND A.DPET_ID = S.DPET_ID
LEFT JOIN DBA_WELCOME_EXPRESS U ON A.INVOICE_LOGISTICS_CP = U.CODE AND A.DPET_ID = U.DPET_ID
WHERE A.SALE_ID IN ('123456','345688','567567567')
AND A.DPET_ID = '10001';
乍一看这个sql，很多的join但是实际上是4个表，因为能秒出，所以sale_id这个字段的选择度肯定很高，NBA_WELCOME_SELLS十有八九是驱动表，其他表上跟sale_id关联的列有索引的话，秒出不是问题 继续看执行计划：
------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11776 | 48 (3)| 00:00:01 | | 1 | NESTED LOOPS OUTER | | 1 | 11776 | 48 (3)| 00:00:01 | | 2 | NESTED LOOPS OUTER | | 1 | 11750 | 47 (3)| 00:00:01 | |* 3 | HASH JOIN RIGHT OUTER | | 1 | 11724 | 46 (3)| 00:00:01 | | 4 | VIEW | | 1 | 83 | 4 (0)| 00:00:01 | | 5 | HASH GROUP BY | | 1 | 37 | 4 (0)| 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCOME_SELLS_DTL | 15 | 555 | 4 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | NBA_WELCOME_SELLS_DTL#3 | 2 | | 3 (0)| 00:00:01 | | 8 | VIEW | | 1 | 11641 | 42 (3)| 00:00:01 | | 9 | HASH GROUP BY | | 1 | 2568 | 42 (3)| 00:00:01 | | 10 | NESTED LOOPS OUTER | | 1 | 2568 | 41 (0)| 00:00:01 | | 11 | NESTED LOOPS OUTER | | 1 | 2453 | 38 (0)| 00:00:01 | | 12 | NESTED LOOPS OUTER | | 1 | 2338 | 35 (0)| 00:00:01 | | 13 | NESTED LOOPS OUTER | | 1 | 2223 | 32 (0)| 00:00:01 | | 14 | NESTED LOOPS OUTER | | 1 | 2108 | 29 (0)| 00:00:01 | | 15 | NESTED LOOPS OUTER | | 1 | 1993 | 26 (0)| 00:00:01 | | 16 | NESTED LOOPS OUTER | | 1 | 1878 | 23 (0)| 00:00:01 | | 17 | NESTED LOOPS OUTER | | 1 | 1763 | 21 (0)| 00:00:01 | | 18 | NESTED LOOPS OUTER | | 1 | 1648 | 19 (0)| 00:00:01 | | 19 | NESTED LOOPS OUTER | | 1 | 1533 | 17 (0)| 00:00:01 | | 20 | NESTED LOOPS OUTER | | 1 | 1418 | 15 (0)| 00:00:01 | | 21 | NESTED LOOPS OUTER | | 1 | 1303 | 13 (0)| 00:00:01 | | 22 | NESTED LOOPS OUTER | | 1 | 1188 | 11 (0)| 00:00:01 | | 23 | NESTED LOOPS OUTER | | 1 | 1073 | 9 (0)| 00:00:01 | | 24 | NESTED LOOPS OUTER | | 1 | 958 | 7 (0)| 00:00:01 | | 25 | NESTED LOOPS OUTER | | 1 | 843 | 5 (0)| 00:00:01 | |* 26 | TABLE ACCESS BY INDEX ROWID BATCHED| NBA_WELCOME_SELLS | 1 | 826 | 4 (0)| 00:00:01 | |* 27 | INDEX RANGE SCAN | NBA_WELCOME_SELLS#1 | 1 | | 3 (0)| 00:00:01 | |* 28 | INDEX RANGE SCAN | NBA_WELC_STAFF#3 | 1 | 17 | 1 (0)| 00:00:01 | |* 29 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 2 (0)| 00:00:01 | |* 30 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 | |* 31 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 2 (0)| 00:00:01 | |* 32 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 | |* 33 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 2 (0)| 00:00:01 | |* 34 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 | |* 35 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 2 (0)| 00:00:01 | |* 36 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 | |* 37 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 2 (0)| 00:00:01 | |* 38 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 | |* 39 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 2 (0)| 00:00:01 | |* 40 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 | |* 41 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 2 (0)| 00:00:01 | |* 42 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 3 | | 1 (0)| 00:00:01 | |* 43 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 2 (0)| 00:00:01 | |* 44 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 4 | | 1 (0)| 00:00:01 | |* 45 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 2 (0)| 00:00:01 | |* 46 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 4 | | 1 (0)| 00:00:01 | |* 47 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 3 (0)| 00:00:01 | |* 48 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | 1 (0)| 00:00:01 | |* 49 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 3 (0)| 00:00:01 | |* 50 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | 1 (0)| 00:00:01 | |* 51 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 3 (0)| 00:00:01 | |* 52 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | 1 (0)| 00:00:01 | |* 53 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 3 (0)| 00:00:01 | |* 54 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | 1 (0)| 00:00:01 | |* 55 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 3 (0)| 00:00:01 | |* 56 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | 1 (0)| 00:00:01 | |* 57 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | 3 (0)| 00:00:01 | |* 58 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | 1 (0)| 00:00:01 | |* 59 | TABLE ACCESS BY INDEX ROWID BATCHED | DBA_WELCOME_EXPRESS | 1 | 26 | 1 (0)| 00:00:01 | |* 60 | INDEX RANGE SCAN | DBA_WELCOME_EXPRESS1# | 1 | | 0 (0)| 00:00:01 | |* 61 | TABLE ACCESS BY INDEX ROWID BATCHED | DBA_WELCOME_EXPRESS | 1 | 26 | 1 (0)| 00:00:01 | |* 62 | INDEX RANGE SCAN | DBA_WELCOME_EXPRESS1# | 1 | | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 70 consistent gets --非常低的逻辑读 0 physical reads 0 redo size 10847 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed
果然印证了猜想，NBA_WELCOME_SELLS是驱动表其中，NBA_WELCOME_SELLS和 NBA_WELCOME_SELLS_DTL 是两个大表， 分别是1400w和2800w，其他是几百行的小表，虽然join多，但是先忽略不计， 综合来看sql不算复杂，因为选择度高所以是在非常小的结果集当中进行计算 -------------------------------问题分割线----------------------------- 开发说SALE_ID当中IN的条件传入的参数会是'char,char,char'的形式， 所以使用了SPLITSTR进行处理，目的是去掉逗号，一行变多行， 比如从 'char,char,char'变成 char char char
SELECT
很多列
FROM NBA_WELCOME_SELLS A
LEFT JOIN NBA_WELCO_DICTIONARY B ON A.PRINT_FLG = B.DATA_ID AND B.DIC_ID = 'PRINT_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY C ON A.MARK_FLG = C.DATA_ID AND C.DIC_ID = 'MARK_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY D ON A.AUTO_FLG = D.DATA_ID AND D.DIC_ID = 'YESorNO'
LEFT JOIN NBA_WELCO_DICTIONARY E ON A.DELI_TYPE = E.DATA_ID AND E.DIC_ID = 'DELI_TYPE'
LEFT JOIN NBA_WELCO_DICTIONARY F ON A.PAYMENT_ID = F.DATA_ID AND F.DIC_ID = 'PAYMENT_TYPE'
LEFT JOIN NBA_WELCO_DICTIONARY G ON A.COD_FLG = G.DATA_ID AND G.DIC_ID = 'YESorNO'
LEFT JOIN NBA_WELCO_DICTIONARY H ON A.INVOICE_FLG = H.DATA_ID AND H.DIC_ID = 'YoNInvoice'
LEFT JOIN NBA_WELCO_DICTIONARY I ON A.INVOICE_TYPE = I.DATA_ID AND I.DIC_ID = 'INVOICE_TYPE'
LEFT JOIN NBA_WELCO_DICTIONARY J ON A.LOGISTICS_FLG = J.DATA_ID AND J.DIC_ID = 'LOGISTICS_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY K ON A.SALE_FLG = K.DATA_ID AND K.DIC_ID = 'SALE_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY L ON A.STATUS_FLG = L.DATA_ID AND L.DIC_ID = 'ORDER_STATUS'
LEFT JOIN NBA_WELCO_DICTIONARY O ON A.PAUSE_FLG = O.DATA_ID AND O.DIC_ID = 'PAUSE_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY P ON A.ASSIGN_FLG = P.DATA_ID AND P.DIC_ID = 'YESorNO'
LEFT JOIN NBA_WELCO_DICTIONARY Q ON A.ABNORMAL_FLG = Q.DATA_ID AND Q.DIC_ID = 'ABNORMAL_TYPE'
LEFT JOIN NBA_WELCO_DICTIONARY V ON A.STORE_ID = V.DATA_ID AND V.DIC_ID = 'STORE_ID'
LEFT JOIN (SELECT STAFF_ID, MAX(STAFF_NAME) AS STAFF_NAME FROM NBA_WELC_STAFF GROUP BY STAFF_ID) T ON A.ASSIGN_STAFF = T.STAFF_ID
LEFT JOIN (SELECT SALE_ID, SUM(QTY) AS QTY, SUM(SENT_QTY) AS SENT_QTY
FROM NBA_WELCOME_SELLS_DTL
WHERE
SALE_ID IN (SELECT T.* FROM TABLE(SPLITSTR('123456,345688,567567567',',')) T)
AND DPET_ID = '10001'
GROUP BY SALE_ID) R ON A.SALE_ID = R.SALE_ID
LEFT JOIN DBA_WELCOME_EXPRESS S ON A.LOGISTICS_COMPANY = S.CODE AND A.DPET_ID = S.DPET_ID
LEFT JOIN DBA_WELCOME_EXPRESS U ON A.INVOICE_LOGISTICS_CP = U.CODE AND A.DPET_ID = U.DPET_ID
WHERE
A.SALE_ID IN (SELECT T.* FROM TABLE(SPLITSTR('123456,345688,567567567',',')) T)
AND A.DPET_ID = '10001';
改了之后变成了需要2分多种才能执行完，执行计划如下：
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 6210K(100)| |
|* 1 | HASH JOIN OUTER | | 1 | 11778 | | 6210K (1)| 00:04:03 |
| 2 | NESTED LOOPS OUTER | | 1 | 11695 | | 6209K (1)| 00:04:03 |
| 3 | NESTED LOOPS OUTER | | 1 | 11669 | | 6209K (1)| 00:04:03 |
|* 4 | HASH JOIN RIGHT SEMI | | 1 | 11643 | | 6209K (1)| 00:04:03 |
| 5 | COLLECTION ITERATOR PICKLER FETCH | SPLITSTR | 8168 | 16336 | | 29 (0)| 00:00:01 |
| 6 | VIEW | | 11M| 119G| | 6209K (1)| 00:04:03 |
| 7 | HASH GROUP BY | | 11M| 26G| 27G| 6209K (1)| 00:04:03 |
|* 8 | HASH JOIN RIGHT OUTER | | 11M| 26G| | 316K (1)| 00:00:13 |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 9 | 1035 | | 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 9 | | | 1 (0)| 00:00:01 |
|* 11 | HASH JOIN RIGHT OUTER | | 10M| 25G| | 316K (1)| 00:00:13 |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 20 | 2300 | | 5 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 20 | | | 1 (0)| 00:00:01 |
|* 14 | HASH JOIN RIGHT OUTER | | 10M| 23G| | 316K (1)| 00:00:13 |
| 15 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 15 | 1725 | | 4 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 15 | | | 1 (0)| 00:00:01 |
|* 17 | HASH JOIN RIGHT OUTER | | 10M| 22G| | 316K (1)| 00:00:13 |
| 18 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 14 | 1610 | | 4 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 14 | | | 1 (0)| 00:00:01 |
|* 20 | HASH JOIN RIGHT OUTER | | 10M| 21G| | 316K (1)| 00:00:13 |
| 21 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 8 | 920 | | 3 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 8 | | | 1 (0)| 00:00:01 |
|* 23 | HASH JOIN RIGHT OUTER | | 10M| 20G| | 316K (1)| 00:00:13 |
| 24 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 7 | 805 | | 3 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 7 | | | 1 (0)| 00:00:01 |
|* 26 | HASH JOIN RIGHT OUTER | | 10M| 19G| | 316K (1)| 00:00:13 |
| 27 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 4 | 460 | | 2 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 4 | | | 1 (0)| 00:00:01 |
|* 29 | HASH JOIN RIGHT OUTER | | 10M| 18G| | 316K (1)| 00:00:13 |
| 30 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 4 | 460 | | 2 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 4 | | | 1 (0)| 00:00:01 |
|* 32 | HASH JOIN RIGHT OUTER | | 10M| 16G| | 315K (1)| 00:00:13 |
| 33 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 3 | 345 | | 2 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 3 | | | 1 (0)| 00:00:01 |
| 35 | NESTED LOOPS OUTER | | 10M| 15G| | 315K (1)| 00:00:13 |
|* 36 | HASH JOIN RIGHT OUTER | | 10M| 14G| | 315K (1)| 00:00:13 |
| 37 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 2 | 230 | | 2 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | | 1 (0)| 00:00:01 |
| 39 | NESTED LOOPS OUTER | | 10M| 13G| | 315K (1)| 00:00:13 |
|* 40 | HASH JOIN RIGHT OUTER | | 10M| 12G| | 315K (1)| 00:00:13 |
| 41 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 2 | 230 | | 2 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | | 1 (0)| 00:00:01 |
|* 43 | HASH JOIN RIGHT OUTER | | 10M| 10G| | 315K (1)| 00:00:13 |
| 44 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 2 | 230 | | 2 (0)| 00:00:01 |
|* 45 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | | 1 (0)| 00:00:01 |
| 46 | NESTED LOOPS OUTER | | 10M| 9G| | 315K (1)| 00:00:13 |
| 47 | NESTED LOOPS OUTER | | 10M| 8833M| | 315K (1)| 00:00:13 |
|* 48 | TABLE ACCESS FULL | NBA_WELCOME_SELLS | 10M| 8655M| | 315K (1)| 00:00:13 |
|* 49 | INDEX RANGE SCAN | NBA_WELC_STAFF#3 | 1 | 17 | | 1 (0)| 00:00:01 |
|* 50 | TABLE ACCESS BY INDEX ROWID BATCHED| NBA_WELCO_DICTIONARY | 1 | 115 | | 3 (0)| 00:00:01 |
|* 51 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | | 1 (0)| 00:00:01 |
|* 52 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | | 3 (0)| 00:00:01 |
|* 53 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | | 1 (0)| 00:00:01 |
|* 54 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 115 | | 3 (0)| 00:00:01 |
|* 55 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | | 1 (0)| 00:00:01 |
|* 56 | TABLE ACCESS BY INDEX ROWID BATCHED | DBA_WELCOME_EXPRESS | 1 | 26 | | 1 (0)| 00:00:01 |
|* 57 | INDEX RANGE SCAN | DBA_WELCOME_EXPRESS1# | 1 | | | 0 (0)| |
|* 58 | TABLE ACCESS BY INDEX ROWID BATCHED | DBA_WELCOME_EXPRESS | 1 | 26 | | 1 (0)| 00:00:01 |
|* 59 | INDEX RANGE SCAN | DBA_WELCOME_EXPRESS1# | 1 | | | 0 (0)| |
| 60 | VIEW | | 302 | 25066 | | 1051 (1)| 00:00:01 |
| 61 | HASH GROUP BY | | 302 | 11778 | | 1051 (1)| 00:00:01 |
| 62 | NESTED LOOPS | | 302 | 11778 | | 1050 (1)| 00:00:01 |
| 63 | NESTED LOOPS | | 302 | 11778 | | 1050 (1)| 00:00:01 |
| 64 | SORT UNIQUE | | 8168 | 16336 | | 29 (0)| 00:00:01 |
| 65 | COLLECTION ITERATOR PICKLER FETCH | SPLITSTR | 8168 | 16336 | | 29 (0)| 00:00:01 |
|* 66 | INDEX RANGE SCAN | NBA_WELCOME_SELLS_DTL#9 | 1 | | | 3 (0)| 00:00:01 |
| 67 | TABLE ACCESS BY INDEX ROWID | NBA_WELCOME_SELLS_DTL | 1 | 37 | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."SALE_SO_ID"="R"."SALE_SO_ID")
4 - access("from$_subquery$_034"."SALE_SO_ID"=VALUE(KOKBF$))
8 - access("A"."SALE_FLG"="K"."DATA_ID")
10 - access("K"."DIC_ID"=:SYS_B_74)
11 - access("A"."STATUS_FLG"="L"."DATA_ID")
13 - access("L"."DIC_ID"=:SYS_B_75)
14 - access("A"."PAYMENT_ID"="F"."DATA_ID")
16 - access("F"."DIC_ID"=:SYS_B_69)
17 - access("A"."ABNORMAL_FLG"="Q"."DATA_ID")
19 - access("Q"."DIC_ID"=:SYS_B_78)
20 - access("A"."LOGISTICS_FLG"="J"."DATA_ID")
22 - access("J"."DIC_ID"=:SYS_B_73)
23 - access("A"."INVOICE_FLG"="H"."DATA_ID")
25 - access("H"."DIC_ID"=:SYS_B_71)
26 - access("A"."STORE_ID"="V"."DATA_ID")
28 - access("V"."DIC_ID"=:SYS_B_79)
29 - access("A"."PAUSE_FLG"="O"."DATA_ID")
31 - access("O"."DIC_ID"=:SYS_B_76)
32 - access("A"."DELI_TYPE"="E"."DATA_ID")
34 - access("E"."DIC_ID"=:SYS_B_68)
36 - access("A"."INVOICE_TYPE"="I"."DATA_ID")
38 - access("I"."DIC_ID"=:SYS_B_72)
40 - access("A"."AUTO_FLG"="D"."DATA_ID")
42 - access("D"."DIC_ID"=:SYS_B_67)
43 - access("A"."MARK_FLG"="C"."DATA_ID")
45 - access("C"."DIC_ID"=:SYS_B_66)
48 - filter("A"."DPET_ID"=:SYS_B_85)
49 - access("A"."ASSIGN_STAFF"="STAFF_ID")
50 - filter("B"."DIC_ID"=:SYS_B_65)
51 - access("A"."PRINT_FLG"="B"."DATA_ID")
52 - filter("G"."DIC_ID"=:SYS_B_70)
53 - access("A"."COD_FLG"="G"."DATA_ID")
54 - filter("P"."DIC_ID"=:SYS_B_77)
55 - access("A"."ASSIGN_FLG"="P"."DATA_ID")
56 - filter("from$_subquery$_034"."QCSJ_C000000000300004"="S"."DPET_ID")
57 - access("from$_subquery$_034"."LOGISTICS_COMPANY"="S"."CODE")
58 - filter("from$_subquery$_034"."QCSJ_C000000000300004"="U"."DPET_ID")
59 - access("from$_subquery$_034"."INVOICE_LOGISTICS_CP"="U"."CODE")
66 - access("SALE_SO_ID"=VALUE(KOKBF$) AND "DPET_ID"=:SYS_B_82)
不出所料， NBA_WELCOME_SELLS和 NBA_WELCOME_SELLS_DTL两个大表都变成了全表扫， 也就是 SPLITSTR的导致了不走索引，到此我在想有什么办法可以让 SPLITSTR变成一个正常的参数传入 想到了with as先对 SPLITSTR进行处理，再传入in，改写如下：
WITH temp AS (SELECT COLUMN_VALUE AS id FROM TABLE(SPLITSTR('1401652766778673639',',')) )
SELECT
很多列
FROM NBA_WELCOME_SELLS A
LEFT JOIN NBA_WELCO_DICTIONARY B ON A.PRINT_FLG = B.DATA_ID AND B.DIC_ID = 'PRINT_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY C ON A.MARK_FLG = C.DATA_ID AND C.DIC_ID = 'MARK_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY D ON A.AUTO_FLG = D.DATA_ID AND D.DIC_ID = 'YESorNO'
LEFT JOIN NBA_WELCO_DICTIONARY E ON A.DELI_TYPE = E.DATA_ID AND E.DIC_ID = 'DELI_TYPE'
LEFT JOIN NBA_WELCO_DICTIONARY F ON A.PAYMENT_ID = F.DATA_ID AND F.DIC_ID = 'PAYMENT_TYPE'
LEFT JOIN NBA_WELCO_DICTIONARY G ON A.COD_FLG = G.DATA_ID AND G.DIC_ID = 'YESorNO'
LEFT JOIN NBA_WELCO_DICTIONARY H ON A.INVOICE_FLG = H.DATA_ID AND H.DIC_ID = 'YoNInvoice'
LEFT JOIN NBA_WELCO_DICTIONARY I ON A.INVOICE_TYPE = I.DATA_ID AND I.DIC_ID = 'INVOICE_TYPE'
LEFT JOIN NBA_WELCO_DICTIONARY J ON A.LOGISTICS_FLG = J.DATA_ID AND J.DIC_ID = 'LOGISTICS_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY K ON A.SALE_FLG = K.DATA_ID AND K.DIC_ID = 'SALE_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY L ON A.STATUS_FLG = L.DATA_ID AND L.DIC_ID = 'ORDER_STATUS'
LEFT JOIN NBA_WELCO_DICTIONARY O ON A.PAUSE_FLG = O.DATA_ID AND O.DIC_ID = 'PAUSE_FLG'
LEFT JOIN NBA_WELCO_DICTIONARY P ON A.ASSIGN_FLG = P.DATA_ID AND P.DIC_ID = 'YESorNO'
LEFT JOIN NBA_WELCO_DICTIONARY Q ON A.ABNORMAL_FLG = Q.DATA_ID AND Q.DIC_ID = 'ABNORMAL_TYPE'
LEFT JOIN NBA_WELCO_DICTIONARY V ON A.STORE_ID = V.DATA_ID AND V.DIC_ID = 'STORE_ID'
LEFT JOIN (SELECT F.STAFF_ID, MAX(F.STAFF_NAME) AS STAFF_NAME FROM NBA_WELC_STAFF F,NBA_WELCOME_SELLS S
WHERE S.sale_so_id in (SELECT ID FROM TEMP ) AND S.ASSIGN_STAFF = F.STAFF_ID
GROUP BY STAFF_ID) T ON A.ASSIGN_STAFF = T.STAFF_ID
LEFT JOIN (SELECT SALE_SO_ID, SUM(QTY) AS QTY, SUM(SENT_QTY) AS SENT_QTY, SUM(RETURN_QTY) AS RETURN_QTY, SUM(ITEM_DIS_AMT) AS ITEM_DIS_AMT, SUM(ADJUST_AMT) AS ADJUST_AMT
FROM NBA_WELCOME_SELLS_DTL DTL
WHERE SALE_SO_ID in (SELECT ID FROM TEMP )
AND DPET_ID = '81'
GROUP BY SALE_SO_ID) R ON A.SALE_SO_ID = R.SALE_SO_ID
LEFT JOIN DBA_WELCOME_EXPRESS S ON A.LOGISTICS_COMPANY = S.CODE AND A.DPET_ID = S.DPET_ID
LEFT JOIN DBA_WELCOME_EXPRESS U ON A.INVOICE_LOGISTICS_CP = U.CODE AND A.DPET_ID = U.DPET_ID
WHERE A.SALE_SO_ID in (SELECT ID FROM TEMP)
aND A.DPET_ID = '81';
结果秒出，应该是达到效果了：
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3479 | 31427 (1)| 00:00:02 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9DF8B8_3B27946F | | | | |
| 3 | COLLECTION ITERATOR PICKLER FETCH | SPLITSTR | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 4 | HASH JOIN OUTER | | 1 | 3479 | 31398 (1)| 00:00:02 |
|* 5 | HASH JOIN OUTER | | 1 | 3443 | 6883 (1)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 3360 | 3460 (1)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 3328 | 3458 (1)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 1 | 3296 | 3456 (1)| 00:00:01 |
| 9 | NESTED LOOPS OUTER | | 1 | 3264 | 3454 (1)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 1 | 3232 | 3452 (1)| 00:00:01 |
| 11 | NESTED LOOPS OUTER | | 1 | 3200 | 3449 (1)| 00:00:01 |
| 12 | NESTED LOOPS OUTER | | 1 | 3168 | 3447 (1)| 00:00:01 |
| 13 | NESTED LOOPS OUTER | | 1 | 3136 | 3444 (1)| 00:00:01 |
| 14 | NESTED LOOPS OUTER | | 1 | 3104 | 3442 (1)| 00:00:01 |
| 15 | NESTED LOOPS OUTER | | 1 | 3072 | 3439 (1)| 00:00:01 |
| 16 | NESTED LOOPS OUTER | | 1 | 3040 | 3436 (1)| 00:00:01 |
| 17 | NESTED LOOPS OUTER | | 1 | 3008 | 3433 (1)| 00:00:01 |
| 18 | NESTED LOOPS OUTER | | 1 | 2976 | 3431 (1)| 00:00:01 |
| 19 | NESTED LOOPS OUTER | | 1 | 2944 | 3429 (1)| 00:00:01 |
| 20 | NESTED LOOPS OUTER | | 1 | 2912 | 3426 (1)| 00:00:01 |
| 21 | NESTED LOOPS OUTER | | 1 | 2880 | 3424 (1)| 00:00:01 |
| 22 | NESTED LOOPS OUTER | | 1 | 2854 | 3423 (1)| 00:00:01 |
| 23 | NESTED LOOPS | | 1 | 2828 | 3422 (1)| 00:00:01 |
| 24 | VIEW | VW_NSO_2 | 8168 | 15M| 3 (0)| 00:00:01 |
| 25 | HASH UNIQUE | | 1 | 15M| | |
| 26 | VIEW | | 8168 | 15M| 3 (0)| 00:00:01 |
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF8B8_3B27946F | 8168 | 16336 | 3 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID BATCHED| NBA_WELCOME_SELLS | 1 | 826 | 3 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | NBA_WELCOME_SELLS#18 | 1 | | 2 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID BATCHED | DBA_WELCOME_EXPRESS | 1 | 26 | 1 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | DBA_WELCOME_EXPRESS1# | 1 | | 0 (0)| 00:00:01 |
|* 32 | TABLE ACCESS BY INDEX ROWID BATCHED | DBA_WELCOME_EXPRESS | 1 | 26 | 1 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | DBA_WELCOME_EXPRESS1# | 1 | | 0 (0)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 2 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 4 | | 1 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 3 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | 1 (0)| 00:00:01 |
|* 38 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 2 (0)| 00:00:01 |
|* 39 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 |
|* 40 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 2 (0)| 00:00:01 |
|* 41 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 4 | | 1 (0)| 00:00:01 |
|* 42 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 3 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | 1 (0)| 00:00:01 |
|* 44 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 3 (0)| 00:00:01 |
|* 45 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | 1 (0)| 00:00:01 |
|* 46 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 3 (0)| 00:00:01 |
|* 47 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | 1 (0)| 00:00:01 |
|* 48 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 2 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 |
|* 50 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 3 (0)| 00:00:01 |
|* 51 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | 1 (0)| 00:00:01 |
|* 52 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 2 (0)| 00:00:01 |
|* 53 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 |
|* 54 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 3 (0)| 00:00:01 |
|* 55 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | 1 (0)| 00:00:01 |
|* 56 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 2 (0)| 00:00:01 |
|* 57 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 3 | | 1 (0)| 00:00:01 |
|* 58 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 2 (0)| 00:00:01 |
|* 59 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 |
|* 60 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 2 (0)| 00:00:01 |
|* 61 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 |
|* 62 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | 2 (0)| 00:00:01 |
|* 63 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 |
| 64 | VIEW | | 1 | 83 | 3424 (1)| 00:00:01 |
| 65 | HASH GROUP BY | | 1 | 2039 | 3424 (1)| 00:00:01 |
| 66 | NESTED LOOPS | | 1 | 2039 | 3423 (1)| 00:00:01 |
| 67 | NESTED LOOPS | | 1 | 2039 | 3423 (1)| 00:00:01 |
| 68 | VIEW | VW_NSO_1 | 8168 | 15M| 3 (0)| 00:00:01 |
| 69 | HASH UNIQUE | | 1 | 15M| | |
| 70 | VIEW | | 8168 | 15M| 3 (0)| 00:00:01 |
| 71 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF8B8_3B27946F | 8168 | 16336 | 3 (0)| 00:00:01 |
|* 72 | INDEX RANGE SCAN | NBA_WELCOME_SELLS_DTL#9 | 1 | | 3 (0)| 00:00:01 |
| 73 | TABLE ACCESS BY INDEX ROWID | NBA_WELCOME_SELLS_DTL | 1 | 37 | 4 (0)| 00:00:01 |
| 74 | VIEW | | 1 | 36 | 24515 (1)| 00:00:01 |
| 75 | HASH GROUP BY | | 1 | 2055 | 24515 (1)| 00:00:01 |
| 76 | NESTED LOOPS | | 1 | 2055 | 24514 (1)| 00:00:01 |
| 77 | NESTED LOOPS | | 1 | 2038 | 24513 (1)| 00:00:01 |
| 78 | VIEW | | 8168 | 15M| 3 (0)| 00:00:01 |
| 79 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF8B8_3B27946F | 8168 | 16336 | 3 (0)| 00:00:01 |
|* 80 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCOME_SELLS | 1 | 36 | 3 (0)| 00:00:01 |
|* 81 | INDEX RANGE SCAN | NBA_WELCOME_SELLS#18 | 1 | | 2 (0)| 00:00:01 |
|* 82 | INDEX RANGE SCAN | NBA_WELC_STAFF#3 | 1 | 17 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."ASSIGN_STAFF"="T"."STAFF_ID"(+))
5 - access("A"."SALE_SO_ID"="R"."SALE_SO_ID"(+))
29 - access("A"."SALE_SO_ID"="ID" AND "A"."DPET_ID"='10001')
30 - filter("U"."DPET_ID"(+)='10001')
31 - access("A"."INVOICE_LOGISTICS_CP"="U"."CODE"(+))
32 - filter("S"."DPET_ID"(+)='10001')
33 - access("A"."LOGISTICS_COMPANY"="S"."CODE"(+))
34 - filter("A"."STORE_ID"="V"."DATA_ID"(+))
35 - access("V"."DIC_ID"(+)='STORE_ID')
36 - filter("Q"."DIC_ID"(+)='ABNORMAL_TYPE')
37 - access("A"."ABNORMAL_FLG"="Q"."DATA_ID"(+))
38 - filter("A"."ASSIGN_FLG"="P"."DATA_ID"(+))
39 - access("P"."DIC_ID"(+)='YESorNO')
40 - filter("A"."PAUSE_FLG"="O"."DATA_ID"(+))
41 - access("O"."DIC_ID"(+)='PAUSE_FLG')
42 - filter("L"."DIC_ID"(+)='ORDER_STATUS')
43 - access("A"."STATUS_FLG"="L"."DATA_ID"(+))
44 - filter("K"."DIC_ID"(+)='SALE_FLG')
45 - access("A"."SALE_FLG"="K"."DATA_ID"(+))
46 - filter("J"."DIC_ID"(+)='LOGISTICS_FLG')
47 - access("A"."LOGISTICS_FLG"="J"."DATA_ID"(+))
48 - filter("A"."INVOICE_TYPE"="I"."DATA_ID"(+))
49 - access("I"."DIC_ID"(+)='INVOICE_TYPE')
50 - filter("H"."DIC_ID"(+)='YoNInvoice')
51 - access("A"."INVOICE_FLG"="H"."DATA_ID"(+))
52 - filter("A"."COD_FLG"="G"."DATA_ID"(+))
53 - access("G"."DIC_ID"(+)='YESorNO')
54 - filter("F"."DIC_ID"(+)='PAYMENT_TYPE')
55 - access("A"."PAYMENT_ID"="F"."DATA_ID"(+))
56 - filter("A"."DELI_TYPE"="E"."DATA_ID"(+))
57 - access("E"."DIC_ID"(+)='DELI_TYPE')
58 - filter("A"."AUTO_FLG"="D"."DATA_ID"(+))
59 - access("D"."DIC_ID"(+)='YESorNO')
60 - filter("A"."MARK_FLG"="C"."DATA_ID"(+))
61 - access("C"."DIC_ID"(+)='MARK_FLG')
62 - filter("A"."PRINT_FLG"="B"."DATA_ID"(+))
63 - access("B"."DIC_ID"(+)='PRINT_FLG')
72 - access("SALE_SO_ID"="ID" AND "DPET_ID"='10001')
80 - filter("S"."ASSIGN_STAFF" IS NOT NULL)
81 - access("S"."SALE_SO_ID"="ID")
82 - access("S"."ASSIGN_STAFF"="F"."STAFF_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
Statistics
----------------------------------------------------------
2 recursive calls
9 db block gets
88 consistent gets
1 physical reads
772 redo size
9816 bytes sent via SQL*Net to client
553 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
逻辑读88，达到了之前的效果，但是。。。。。开发说最终的sql不是这样，这只是简写，sale_id还有个union。。。。（重点来了）with as要改成,如下：
with temp as (SELECT T.COLUMN_VALUE AS ID FROM TABLE(SPLITSTR('14039',',')) T
UNION
SELECT SALE_SO_ID AS ID FROM NBA_WELCOME_SELLS
WHERE COMBINE_ID IN (SELECT T.* FROM TABLE(SPLITSTR('140165',',')) T))
结果一执行，又变慢了。。。
---------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | ---------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 2836K(100)| | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | | 2 | LOAD AS SELECT | | | | | | | | 3 | SORT UNIQUE | | 5434K| 238M| 166M| 274K (1)| 00:00:11 | | 4 | UNION-ALL | | | | | | | | 5 | COLLECTION ITERATOR PICKLER FETCH | SPLITSTR | 8168 | 16336 | | 29 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 5426K| 119M| | 237K (1)| 00:00:10 | | 7 | NESTED LOOPS | | 5426K| 119M| | 237K (1)| 00:00:10 | | 8 | COLLECTION ITERATOR PICKLER FETCH | SPLITSTR | 8168 | 16336 | | 29 (0)| 00:00:01 | |* 9 | INDEX RANGE SCAN | NBA_WELCOME_SELLS#19 | 318 | | | 2 (0)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID | NBA_WELCOME_SELLS | 664 | 13944 | | 29 (0)| 00:00:01 | |* 11 | HASH JOIN RIGHT OUTER | | 4248K| 13G| | 2562K (1)| 00:01:41 | | 12 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 9 | 288 | | 3 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 9 | | | 1 (0)| 00:00:01 | |* 14 | HASH JOIN RIGHT OUTER | | 4239K| 13G| | 2562K (1)| 00:01:41 | |* 15 | TABLE ACCESS FULL | DBA_WELCOME_EXPRESS | 57 | 1482 | | 4 (0)| 00:00:01 | |* 16 | HASH JOIN RIGHT OUTER | | 4239K| 13G| | 2562K (1)| 00:01:41 | |* 17 | TABLE ACCESS FULL | DBA_WELCOME_EXPRESS | 57 | 1482 | | 4 (0)| 00:00:01 | |* 18 | HASH JOIN RIGHT OUTER | | 4239K| 13G| | 2562K (1)| 00:01:41 | | 19 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 20 | 640 | | 5 (0)| 00:00:01 | |* 20 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 20 | | | 1 (0)| 00:00:01 | |* 21 | HASH JOIN RIGHT OUTER | | 4239K| 13G| | 2562K (1)| 00:01:41 | | 22 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 15 | 480 | | 4 (0)| 00:00:01 | |* 23 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 15 | | | 1 (0)| 00:00:01 | |* 24 | HASH JOIN RIGHT OUTER | | 4239K| 13G| | 2562K (1)| 00:01:41 | | 25 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 14 | 448 | | 4 (0)| 00:00:01 | |* 26 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 14 | | | 1 (0)| 00:00:01 | |* 27 | HASH JOIN RIGHT OUTER | | 4239K| 13G| | 2562K (1)| 00:01:41 | | 28 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 8 | 256 | | 3 (0)| 00:00:01 | |* 29 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 8 | | | 1 (0)| 00:00:01 | |* 30 | HASH JOIN RIGHT OUTER | | 4239K| 12G| | 2561K (1)| 00:01:41 | | 31 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 7 | 224 | | 3 (0)| 00:00:01 | |* 32 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 7 | | | 1 (0)| 00:00:01 | |* 33 | HASH JOIN RIGHT OUTER | | 4239K| 12G| | 2561K (1)| 00:01:41 | | 34 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 4 | 128 | | 2 (0)| 00:00:01 | |* 35 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 4 | | | 1 (0)| 00:00:01 | |* 36 | HASH JOIN RIGHT OUTER | | 4239K| 12G| | 2561K (1)| 00:01:41 | | 37 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 4 | 128 | | 2 (0)| 00:00:01 | |* 38 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 4 | | | 1 (0)| 00:00:01 | |* 39 | HASH JOIN RIGHT OUTER | | 4239K| 12G| | 2561K (1)| 00:01:41 | | 40 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 3 | 96 | | 2 (0)| 00:00:01 | |* 41 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 3 | | | 1 (0)| 00:00:01 | | 42 | NESTED LOOPS OUTER | | 4239K| 12G| | 2561K (1)| 00:01:41 | |* 43 | HASH JOIN RIGHT OUTER | | 4239K| 12G| | 2561K (1)| 00:01:41 | | 44 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 2 | 64 | | 2 (0)| 00:00:01 | |* 45 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | | 1 (0)| 00:00:01 | | 46 | NESTED LOOPS OUTER | | 4239K| 12G| | 2561K (1)| 00:01:41 | |* 47 | HASH JOIN RIGHT OUTER | | 4239K| 12G| | 2561K (1)| 00:01:41 | | 48 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 2 | 64 | | 2 (0)| 00:00:01 | |* 49 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | | 1 (0)| 00:00:01 | |* 50 | HASH JOIN RIGHT OUTER | | 4239K| 11G| | 2561K (1)| 00:01:41 | | 51 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 2 | 64 | | 2 (0)| 00:00:01 | |* 52 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | | 1 (0)| 00:00:01 | | 53 | NESTED LOOPS OUTER | | 4239K| 11G| | 2561K (1)| 00:01:41 | |* 54 | HASH JOIN RIGHT OUTER | | 4239K| 11G| | 2561K (1)| 00:01:41 | | 55 | VIEW | | 1 | 83 | | 969K (1)| 00:00:38 | | 56 | HASH GROUP BY | | 1 | 2039 | | 969K (1)| 00:00:38 | |* 57 | HASH JOIN | | 6433K| 12G| 777M| 968K (1)| 00:00:38 | |* 58 | TABLE ACCESS FULL | NBA_WELCOME_SELLS_DTL | 16M| 586M| | 408K (1)| 00:00:16 | | 59 | VIEW | | 5434K| 10G| | 4611 (1)| 00:00:01 | | 60 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF80C_3B27946F | 5434K| 114M| | 4611 (1)| 00:00:01 | |* 61 | HASH JOIN RIGHT OUTER | | 4239K| 11G| | 1592K (1)| 00:01:03 | | 62 | VIEW | | 1 | 36 | | 319K (1)| 00:00:13 | | 63 | HASH GROUP BY | | 1 | 2055 | | 319K (1)| 00:00:13 | |* 64 | HASH JOIN | | 1 | 2055 | | 319K (1)| 00:00:13 | | 65 | NESTED LOOPS | | 1 | 53 | | 315K (1)| 00:00:13 | |* 66 | TABLE ACCESS FULL | NBA_WELCOME_SELLS | 1 | 36 | | 315K (1)| 00:00:13 | |* 67 | INDEX RANGE SCAN | NBA_WELC_STAFF#3 | 1 | 17 | | 1 (0)| 00:00:01 | | 68 | VIEW | | 5434K| 10G| | 4611 (1)| 00:00:01 | | 69 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF80C_3B27946F | 5434K| 114M| | 4611 (1)| 00:00:01 | |* 70 | HASH JOIN | | 4239K| 11G| 8781M| 1273K (1)| 00:00:50 | |* 71 | TABLE ACCESS FULL | NBA_WELCOME_SELLS | 10M| 8655M| | 315K (1)| 00:00:13 | | 72 | VIEW | | 5434K| 10G| | 4611 (1)| 00:00:01 | | 73 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF80C_3B27946F | 5434K| 114M| | 4611 (1)| 00:00:01 | |* 74 | TABLE ACCESS BY INDEX ROWID BATCHED| NBA_WELCO_DICTIONARY | 1 | 32 | | 3 (0)| 00:00:01 | |* 75 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | | 1 (0)| 00:00:01 | |* 76 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | | 3 (0)| 00:00:01 | |* 77 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | | 1 (0)| 00:00:01 | |* 78 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 1 | 32 | | 3 (0)| 00:00:01 | |* 79 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#1 | 3 | | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------------------------------------
with as的cost达到了5434K所以没有走索引关联，很难受，这里实际的cost是很低的既然目前明确知道cost很低，是cost计算的问题，那么自然想到了CARDINALITY 大招，手动指定就可以了 但是。。。hint写在什么位置研究了很久，最终如下改写：
with temp as (SELECT T.COLUMN_VALUE AS ID FROM TABLE(SPLITSTR('14039',',')) T
UNION
SELECT SALE_SO_ID AS ID FROM NBA_WELCOME_SELLS
WHERE COMBINE_ID IN (SELECT /*+ CARDINALITY(T 10) */ T.* FROM TABLE(SPLITSTR('140165',',')) T))
再查看执行计划
------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6380 | 21M| 81831 (1)| 00:00:04 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9DF8FA_3B27946F | | | | | | 3 | SORT UNIQUE | | 8169 | 32712 | 62 (4)| 00:00:01 | | 4 | UNION-ALL | | | | | | | 5 | COLLECTION ITERATOR PICKLER FETCH | SPLITSTR | 8168 | 16336 | 29 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 1 | 20 | 31 (0)| 00:00:01 | | 7 | COLLECTION ITERATOR PICKLER FETCH | SPLITSTR | 1 | 2 | 29 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | NBA_WELCOME_SELLS#1 | 1 | 18 | 2 (0)| 00:00:01 | |* 9 | HASH JOIN RIGHT OUTER | | 6380 | 21M| 81769 (1)| 00:00:04 | | 10 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 9 | 288 | 3 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 9 | | 1 (0)| 00:00:01 | |* 12 | HASH JOIN RIGHT OUTER | | 6368 | 20M| 81765 (1)| 00:00:04 | | 13 | VIEW | | 9671 | 783K| 32687 (1)| 00:00:02 | | 14 | HASH GROUP BY | | 9671 | 18M| 32687 (1)| 00:00:02 | | 15 | NESTED LOOPS | | 9671 | 18M| 32686 (1)| 00:00:02 | | 16 | NESTED LOOPS | | 9671 | 18M| 32686 (1)| 00:00:02 | | 17 | VIEW | | 8169 | 15M| 3 (0)| 00:00:01 | | 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF8FA_3B27946F | 8169 | 16338 | 3 (0)| 00:00:01 | |* 19 | INDEX RANGE SCAN | NBA_WELCOME_SELLS_DTL#9 | 1 | | 3 (0)| 00:00:01 | | 20 | TABLE ACCESS BY INDEX ROWID | NBA_WELCOME_SELLS_DTL | 1 | 37 | 4 (0)| 00:00:01 | |* 21 | HASH JOIN RIGHT OUTER | | 6368 | 20M| 49079 (1)| 00:00:02 | |* 22 | TABLE ACCESS FULL | DBA_WELCOME_EXPRESS1 | 57 | 1482 | 4 (0)| 00:00:01 | |* 23 | HASH JOIN RIGHT OUTER | | 6368 | 20M| 49075 (1)| 00:00:02 | |* 24 | TABLE ACCESS FULL | DBA_WELCOME_EXPRESS1 | 57 | 1482 | 4 (0)| 00:00:01 | |* 25 | HASH JOIN RIGHT OUTER | | 6368 | 20M| 49071 (1)| 00:00:02 | | 26 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 20 | 640 | 5 (0)| 00:00:01 | |* 27 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 20 | | 1 (0)| 00:00:01 | |* 28 | HASH JOIN RIGHT OUTER | | 6368 | 19M| 49066 (1)| 00:00:02 | | 29 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 15 | 480 | 4 (0)| 00:00:01 | |* 30 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 15 | | 1 (0)| 00:00:01 | |* 31 | HASH JOIN RIGHT OUTER | | 6368 | 19M| 49062 (1)| 00:00:02 | | 32 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 14 | 448 | 4 (0)| 00:00:01 | |* 33 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 14 | | 1 (0)| 00:00:01 | |* 34 | HASH JOIN RIGHT OUTER | | 6368 | 19M| 49058 (1)| 00:00:02 | | 35 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 8 | 256 | 3 (0)| 00:00:01 | |* 36 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 8 | | 1 (0)| 00:00:01 | |* 37 | HASH JOIN RIGHT OUTER | | 6368 | 19M| 49055 (1)| 00:00:02 | | 38 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 7 | 224 | 3 (0)| 00:00:01 | |* 39 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 7 | | 1 (0)| 00:00:01 | |* 40 | HASH JOIN RIGHT OUTER | | 6368 | 19M| 49051 (1)| 00:00:02 | | 41 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 4 | 128 | 2 (0)| 00:00:01 | |* 42 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 4 | | 1 (0)| 00:00:01 | |* 43 | HASH JOIN RIGHT OUTER | | 6368 | 18M| 49049 (1)| 00:00:02 | | 44 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 4 | 128 | 2 (0)| 00:00:01 | |* 45 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 4 | | 1 (0)| 00:00:01 | |* 46 | HASH JOIN RIGHT OUTER | | 6368 | 18M| 49047 (1)| 00:00:02 | | 47 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 3 | 96 | 2 (0)| 00:00:01 | |* 48 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 3 | | 1 (0)| 00:00:01 | |* 49 | HASH JOIN RIGHT OUTER | | 6368 | 18M| 49045 (1)| 00:00:02 | | 50 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 2 | 64 | 2 (0)| 00:00:01 | |* 51 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 | |* 52 | HASH JOIN RIGHT OUTER | | 6368 | 18M| 49043 (1)| 00:00:02 | | 53 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 2 | 64 | 2 (0)| 00:00:01 | |* 54 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 | |* 55 | HASH JOIN RIGHT OUTER | | 6368 | 18M| 49041 (1)| 00:00:02 | | 56 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 2 | 64 | 2 (0)| 00:00:01 | |* 57 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 | |* 58 | HASH JOIN RIGHT OUTER | | 6368 | 17M| 49039 (1)| 00:00:02 | | 59 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 2 | 64 | 2 (0)| 00:00:01 | |* 60 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 | |* 61 | HASH JOIN RIGHT OUTER | | 6368 | 17M| 49037 (1)| 00:00:02 | | 62 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 2 | 64 | 2 (0)| 00:00:01 | |* 63 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 | |* 64 | HASH JOIN RIGHT OUTER | | 6368 | 17M| 49035 (1)| 00:00:02 | | 65 | TABLE ACCESS BY INDEX ROWID BATCHED | NBA_WELCO_DICTIONARY | 2 | 64 | 2 (0)| 00:00:01 | |* 66 | INDEX RANGE SCAN | NBA_WELCO_DICTIONARY#2 | 2 | | 1 (0)| 00:00:01 | |* 67 | HASH JOIN RIGHT OUTER | | 6368 | 17M| 49033 (1)| 00:00:02 | | 68 | VIEW | | 1 | 36 | 24518 (1)| 00:00:01 | | 69 | HASH GROUP BY | | 1 | 2055 | 24518 (1)| 00:00:01 | | 70 | NESTED LOOPS | | 1 | 2055 | 24517 (1)| 00:00:01 | | 71 | NESTED LOOPS | | 1 | 2038 | 24516 (1)| 00:00:01 | | 72 | VIEW | | 8169 | 15M| 3 (0)| 00:00:01 | | 73 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF8FA_3B27946F | 8169 | 16338 | 3 (0)| 00:00:01 | |* 74 | TABLE ACCESS BY INDEX ROWID BATCHED| NBA_WELCOME_SELLS | 1 | 36 | 3 (0)| 00:00:01 | |* 75 | INDEX RANGE SCAN | NBA_WELCOME_SELLS#18 | 1 | | 2 (0)| 00:00:01 | |* 76 | INDEX RANGE SCAN | NBA_WELC_STAFF#3 | 1 | 17 | 1 (0)| 00:00:01 | | 77 | NESTED LOOPS | | 6368 | 17M| 24515 (1)| 00:00:01 | | 78 | NESTED LOOPS | | 8169 | 17M| 24515 (1)| 00:00:01 | | 79 | VIEW | | 8169 | 15M| 3 (0)| 00:00:01 | | 80 | TABLE ACCESS FULL | SYS_TEMP_0FD9DF8FA_3B27946F | 8169 | 16338 | 3 (0)| 00:00:01 | |* 81 | INDEX RANGE SCAN | NBA_WELCOME_SELLS#18 | 1 | | 2 (0)| 00:00:01 | | 82 | TABLE ACCESS BY INDEX ROWID | NBA_WELCOME_SELLS | 1 | 826 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 2 recursive calls 8 db block gets 104 consistent gets 1 physical reads 772 redo size 9816 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed
with as的cost恢复到了之前的8000，同时逻辑读达到104， CARDINALITY牛皮！之后我将执行计划绑定，以防万一，另外使用with as属于简单粗暴的解决办法，最好还是开发将结果处理完再传入sql
![20201215]记录工作中的错误.txt](/d/file/efpub/2026/03-03/small11b68f5890cfe5dcaa64689aa1133fb71772525877.jpg "20201215]记录工作中的错误.txt")
