oracle parallel 排序,oracle 增加parallel后出现排序混乱问题

问题描述

在之前对项目中的一些查询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_ROWS51007120014360598490

PX COORDINATOR1

PX SEND QC (RANDOM)SYS:TQ2000251007120014360598492

HASH JOIN OUTER BUFFERED51007120014360598493

BUFFER SORT4

PX RECEIVE31006380012354563735

PX SEND HASHSYS:TQ2000031006380012354563736

VIEWTAQUERY31006380012354563737

COUNT STOPKEY8

FILTER9

PX COORDINATOR10

PX SEND QC (ORDER)SYS:TQ10001311975922123545637311

VIEWTAQUERY311975922123545637312

SORT ORDER BY311928679123545637313

PX RECEIVE211928679122661014

PX SEND RANGESYS:TQ10000211928679122661015

PX BLOCK ITERATOR211928679122661016

TABLE ACCESS FULLTABASETA_TFUNDINFO211928679122661017

PX RECEIVE217412514

PX SEND HASHSYS:TQ20001217412515

PX BLOCK ITERATOR217412516

TABLE ACCESS FULLTABASETA_TTAINFO217412517

发现有这么一项

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