sql - 强制首先运行 SQL 子查询

Question

我有一个像这样的(简化的)查询:

SELECT DISTINCT this_.person_id AS y0_
  FROM coded_entity this_
 WHERE this_.code     = ?
   AND this_.tenant_id  = ?
   AND this_.person_id IN (SELECT person_id
                             FROM person that
                            WHERE that.date_voided IS NULL
                              AND that.ssn            = ?
                              AND that.tenant_id      = ? ) 

我希望能够强制 Oracle 始终首先执行最内层的子查询，因为我知道它总是更具选择性。 Code 只有几个值，而 ssn 有更多的值。每个表将具有相同的行数。

但是，在实际操作中，有时 Oracle CBO 会决定先执行 coded_entity 查询，从而导致运行速度变慢。

有没有办法在不将查询拆分为单独的调用的情况下强制执行此行为？

Answer 1

我的第一个想法(不考虑键)是试试这个:

SELECT DISTINCT this_.person_id AS y0_
  FROM coded_entity this_
 WHERE this_.code     = ?
   AND this_.tenant_id  = ?
   AND EXISTS (SELECT null
                 FROM person THAT_
                WHERE THAT_.date_voided IS NULL
                  AND THAT_.ssn            = ?
                  AND THAT_.tenant_id      = this_.tenant_id
                  AND THAT_.person_id      = this_.person_id) 

但是更好的方法(如果 person_id 是 person 的键并且每个人都有零个或多个编码实体)是这样的:

SELECT this_.person_id AS y0_
  FROM person_ this_
 WHERE this_.ssn        = ?
   AND this_.tenant_id  = ?
   AND this_.date_voided is null
   AND EXISTS (SELECT null
                 FROM coded_entity THAT_
                WHERE THAT_.code = ?
                  AND THAT_.tenant_id      = this_.tenant_id
                  AND THAT_.person_id      = this_.person_id)