本来要测试一个功能的并行处理能力
select v_time,
round(WANGJIANYINGDA_NUM1 / WANGJIANYINGDA_NUM2, 2)
WANGJIANYINGDA_NUM1,
WANGJIANYINGDA_NUM2
from (SELECT /*+ PARALLEL(ZXCDR_ii, 4) */ SUBSTR(IAM_DATE, 1, 13) as v_time,
COUNT(case
when anm_date is null then
1
end) AS WANGJIANYINGDA_NUM1,
COUNT(1) AS WANGJIANYINGDA_NUM2
FROM ZXCDR_ii t
WHERE t.iam_date between
to_date('-04-08 10:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('-04-08 19:00:00', 'yyyy-mm-dd hh24:mi:ss') and
OPC IN ('11-FF-19', '11-FF-42')
AND DPC IN ('11-0D-37', '11-27-45')
AND process_flg = 0
group by SUBSTR(IAM_DATE, 1, 13));
指定并行处理,
SELECT STATEMENT, GOAL = ALL_ROWS 7 7 1 65
SORT GROUP BY 7 7 1 65
SORT GROUP BY 7 7 1 65
PARTITION RANGE ALL
INLIST ITERATOR
TABLE ACCESS BY LOCAL INDEX ROWID HLHT ZXCDR_II 3 3 1 65
INDEX RANGE SCAN HLHT IDX_OPC 2 2 6659 "T"."OPC"='11-FF-19' OR "T"."OPC"='11-FF-42'
但结果总是不对.但自己检查表的并行度,使用的索引idx_opc并行度,都没有问题.[@more@]
想不明白为什么.
只好打开sql_trace ,看看系统的内部处理,
通过分析oracle的trace结果,如下:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=65)
1 0 SORT* (GROUP BY) (Cost=7 Card=1 Bytes=65) :Q692001
2 1 SORT* (GROUP BY) (Cost=7 Card=1 Bytes=65) :Q692000
3 2 PARTITION RANGE* (ALL) :Q692000
4 3 INLIST ITERATOR* :Q692000
5 4 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'ZXCDR_II' (Cost=3 Card=1 Byte :Q692000
s=65)
6 5 INDEX* (RANGE SCAN) OF 'IDX_OPC' (NON-UNIQUE) (Cost=2 Card=6659) :Q6920001 PARALLEL_TO_SERIAL SELECT /*+ CIV_GB */ A1.C0,COUNT(SYS_OP_CSR(
A1.C1,0)),COUNT(SYS_OP_CSR(A1.C1,1)) FROM :Q
692000 A1 GROUP BY A1.C0
2 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ SUBSTR(A1.C3,1,13) C0,S
YS_OP_MSR(COUNT(*),COUNT(CASE WHEN A1.C4 IS
NULL THEN 1 END )) C1 FROM (SELECT /*+ NO_E
XPAND INDEX(A2 "IDX_OPC") */ A2.ROWID C0,A2.
"OPC" C1,A2."DPC" C2,A2."IAM_DATE" C3,A2."AN
M_DATE" C4,A2."PROCESS_FLG" C5 FROM "ZXCDR_I
I" PX_GRANULE(0, PARTITION, DYNAMIC) A2 WHE
RE (A2."OPC"='11-FF-19' OR A2."OPC"='11-FF-4
2') AND A2."IAM_DATE">=TO_DATE('-04-08 1
0:00:00', 'yyyy-mm-dd hh24:mi:ss') AND A2."I
AM_DATE"<=TO_DATE('-04-08 19:00:00', 'yy
yy-mm-dd hh24:mi:ss') AND (A2."DPC"='11-0D-3
7' OR A2."DPC"='11-27-45') AND A2."PROCESS_F
LG"=0) A1 GROUP BY SUBSTR(A1.C3,1,13)
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_PARENT
6 PARALLEL_COMBINED_WITH_PARENT
这才发现,实际上的执行结果,在数据库端并没有错.而是
PL/SQL DEVELOPER工具显示的有问题.
