Oracle 11g PL/SQL查询背靠背运行，结果不同（不返回所有行）

Question

我不认为问题是查询，但我认为我会包括它来帮助隔离。

SELECT stuff
FROM   datamartdbo.position_details e10010
       INNER JOIN datamartdbo.fund_master e10002
          ON (e10010.dmart_fund_id = e10002.dmart_fund_id)
       INNER JOIN datamartdbo.security_details e10013
          ON (    e10010.security_alias = e10013.security_alias
              AND e10002.effective_date = e10013.effective_date
              AND e10013.security_id NOT LIKE 'INTEREST ON CASHUSD%'
              AND e10002.snapshot_id = e10013.snapshot_id)
       LEFT OUTER JOIN
       (SELECT   fm.entity_id,
                 t.security_alias,
                 SUM (t.security_gain) - SUM (t.security_loss) tgainloss
        FROM     datamartdbo.fund_master fm
                 JOIN datamartdbo.trade_details t
                    ON fm.dmart_fund_id = t.dmart_fund_id
        WHERE        t.dmart_currency = 'BASE'
                 AND fm.entity_id IN (' an ENTITY ID here')
                 AND fm.effective_date BETWEEN 'Start DD-MON-YYYY'
                                           AND 'End DD-MON-YYYY'
        GROUP BY fm.entity_id, t.security_alias) gl
          ON     e10002.entity_id = gl.entity_id
             AND e10010.security_alias = gl.security_alias
WHERE      e10010.dmart_currency = 'BASE'
       AND (e10010.mkt_val <> 0 OR UPPER (e10013.investment_type) = 'FUTURES')
       AND e10010.local_book_value IS NOT NULL
       AND e10002.entity_id IN ('an ENTITY ID here')
       AND e10002.effective_date = 'End DD-MON-YYYY'
       AND e10002.snapshot_id = 'DEFAULT'

Answer 1

最有可能的情况是，您正在查看更大结果集中的行子集。当ORACLE向客户机发送结果时，它会以chanks（可以配置）的形式执行，比如100、1000、10000条记录。

我的猜测是，你看了第一个（或前几个）这样的chanks，并看到它们在不同的运行之间是不同的。如果获取整个resultset，则cas会检查它们并看到它们是相同的，但是如果没有设置ORDER BY，ORACLE可以自由地以任何顺序检索记录。

试着通过constuction添加顺序，我希望它能解决这个问题。