如何让“不存在”变得存在快点

Question

我有一个问题。我的数据库是Oracle 9。这是我的SQL：

SELECT COUNT(distinct A.person_id)
        INTO p_record_count
        FROM A,
             B,
             F,
             T
       WHERE B.position_id = F.position_id **--Normal bussiness association**
         AND T.field2 = A.org_id           **--Normal bussiness association**
         AND F.Organization_Id = A.org_id  **--Normal bussiness association**
         AND A.person_id = F.person_id     **--Normal bussiness association**
         AND F.primary_flag = 'Y'          **--Normal bussiness association**
         and sysdate between F.effective_start_date and
             F.effective_end_date          **--Normal bussiness association**
         AND NOT EXISTS                    --very slow
                (SELECT log.person_id
                FROM cper.ehr_access_log log
               WHERE log.person_id = A.person_id
                 AND log.access_page = 'login.do'
                 AND trunc(log.access_time) BETWEEN
                     to_date(p_startDate, 'yyyy-mm-dd') AND
                     to_date(p_endDate, 'yyyy-mm-dd'))
         AND A.enable_flag = 'Y';            **--Normal bussiness association**

要求：获取未登录的人员。

逻辑：获取所有我能找到的人，然后减去登录的人。

我只有一张包含登录记录的表。

表cper.ehr_access_log有超过10M记录。这是一个日志表。此 SQL 大约需要 30 秒。

5s以内可以吗？谢谢你的帮助。

谢谢你~！我会努力的。周末愉快~ : )

Answer 1

表cper.ehr_access_log有超过10M的记录。它是一个日志
表。这个sql大约需要30秒。 5秒之内可能吗？

我想说，如果不添加一些索引，这是不可能的。但您可能想尝试以下建议

• 使用查询计划来识别热点。这可能是 ehr_access_log 所需的表扫描，您可以使用该参数来说服其他人您需要索引。

• 尝试按照其他人的建议用外连接替换子查询

• 您在 B 和 T 上有连接> 但不是它们的谓词。您在查询中真的需要它们吗？

• 您也许可以通过避免 trunc 调用来节省一些时间（不确定）。作为替代方案，将 p_startDate 修改为一天的开始，将 p_endDate 修改为一天的结束

Answer 2

查询的“不存在”部分称为相关子查询。如果重写为 OUTER 连接，通常可以获得更好的性能。

图案就

select a from b where not exists (select 1 from c where b.a = c.a)

变成了

select a from b left outer join c on b.a = c.a where c.a is null

Answer 3

为了获得更好的性能，您必须INDEX表cper.ehr_access_log

由于它是一个LOG表，因此每月计划根据此表进行INDEX在 person_id 上。这将减少一些查询时间。

Answer 4

我认为您可以通过仅选择 DISTINCT log.person_id 记录来优化查询，这会减少日志中的记录量。

您还可以尝试从第一部分中排除：

SELECT COUNT(distinct A.person_id)
    INTO p_record_count
    FROM A,
         B,
         F,
         T
   LEFT OUTER JOIN cper.ehr_access_log log
     ON log.person_id = A.person_id
        AND log.access_page = 'login.do'
        AND trunc(log.access_time) BETWEEN
        to_date(p_startDate, 'yyyy-mm-dd') AND
        to_date(p_endDate, 'yyyy-mm-dd')
   WHERE B.position_id = F.position_id **--Normal bussiness association**
     AND T.field2 = A.org_id           **--Normal bussiness association**
     AND F.Organization_Id = A.org_id  **--Normal bussiness association**
     AND A.person_id = F.person_id     **--Normal bussiness association**
     AND F.primary_flag = 'Y'          **--Normal bussiness association**
     and sysdate between F.effective_start_date and
         F.effective_end_date          **--Normal bussiness association**
     AND log.person_id IS null
     AND A.enable_flag = 'Y';            **--Normal bussiness association**