问题描述
在之前对项目中的一些查询sql进行优化时，为了减少主查询返回数据量，将主查询的排序分页逻辑移到sql内部。大致情况如下：
## 调整前sql
SELECT *
FROM (SELECT a.*, rownum AS row_id
FROM (SELECT a.*, b.*
FROM table_a a, table_b b
WHERE a.id = b.aid(+)
ORDER BY a.id) a
WHERE rownum <= 100) a
WHERE row_id > 0
###调整后sql
SELECT a.*, b.*
FROM (SELECT *
FROM (SELECT a.*, rownum AS row_id
FROM (SELECT /*+ parallel(8) */
*
FROM table_a
ORDER BY a.id) a
WHERE rownum <= 100) a
WHERE row_id > 0) a,
table_b b
WHERE a.id = b.aid(+)
调整后发现查询排序出现了混乱。（结果值正常，但结果值的排序不正常）
问题分析
查看调整后sql的执行计划
SELECT STATEMENT, GOAL = ALL_ROWS 5 100 71200 1 4 36059849 0
PX COORDINATOR 1
PX SEND QC (RANDOM) SYS :TQ20002 5 100 71200 1 4 36059849 2
HASH JOIN OUTER BUFFERED 5 100 71200 1 4 36059849 3
BUFFER SORT 4
PX RECEIVE 3 100 63800 1 2 35456373 5
PX SEND HASH SYS :TQ20000 3 100 63800 1 2 35456373 6
VIEW TAQUERY 3 100 63800 1 2 35456373 7
COUNT STOPKEY 8
FILTER 9
PX COORDINATOR 10
PX SEND QC (ORDER) SYS :TQ10001 3 119 75922 1 2 35456373 11
VIEW TAQUERY 3 119 75922 1 2 35456373 12
SORT ORDER BY 3 119 28679 1 2 35456373 13
PX RECEIVE 2 119 28679 1 2 26610 14
PX SEND RANGE SYS :TQ10000 2 119 28679 1 2 26610 15
PX BLOCK ITERATOR 2 119 28679 1 2 26610 16
TABLE ACCESS FULL TABASE TA_TFUNDINFO 2 119 28679 1 2 26610 17
PX RECEIVE 2 1 74 1 2 51 4
PX SEND HASH SYS :TQ20001 2 1 74 1 2 51 5
PX BLOCK ITERATOR 2 1 74 1 2 51 6
TABLE ACCESS FULL TABASE TA_TTAINFO 2 1 74 1 2 51 7
发现有这么一项
PX SEND QC (RANDOM)
QC (RANDOM)
The query coordinator (QC) consumes the input randomly. Used when the statement does not have an ORDER BY clause.
即外部没有排序时，查询协调器(QC)会将结果按随机顺序输出。
因此是由于将order by排序置于sql内侧，且增加了parallel并发执行指令所致。
解决方案
在sql外层增加相同规则排序（需要将排序字段查询至外层）
### 增加外层排序
SELECT a.*, b.*
FROM (SELECT *
FROM (SELECT a.*, rownum AS row_id
FROM (SELECT /*+ parallel(8) */
*
FROM table_a
ORDER BY a.id) a
WHERE rownum <= 100) a
WHERE row_id > 0) a,
table_b b
WHERE a.id = b.aid(+)
order by a.id