选择不同...内连接与选择...其中 id in (...)

Question

我正在尝试创建表的子集（作为物化视图），定义为在另一个物化视图中具有匹配记录的记录。

例如，假设我有一个包含 user_id 和 name 列的用户表，以及一个包含entry_id、user_id、activity 和 timestamp 列的日志表。

首先，我创建日志表的物化视图，仅选择时间戳 > 的那些行。某个日期。现在我想要日志表快照中引用的用户的物化视图。我可以创建它

select * from Users where user_id in (select user_id from Log_mview)

或我可以这样做

select distinct u.* from Users u inner join Log_mview l on u.user_id = l.user_id

（需要不同的以避免来自具有多个日志条目的用户的多次点击）。

前者看起来更干净、更优雅，但需要更长的时间。我错过了什么吗？有更好的方法吗？

编辑： where contains 子句有很大帮助，除了条件使用 OR 的情况。例如，假设上面的日志表也有一个 user_name 列，将日志条目与用户记录匹配的正确方法是当任一列（用户 ID 或用户名）匹配时。我发现这

select distinct u.* from Users u
    inner join Log_mview l
        on u.user_id = l.user_id or u.name = l.user_name

快得多吗

select * from Users u where exists
    (select id from Log_mview l 
        where l.user_id = u.user_id or l.user_name = u.name)

比有帮助

？ （关于解释计划......让我努力清理它，或者它们，而不是......我会在一段时间后发布它们。）

编辑：解释计划： 对于使用内连接的查询：

Plan hash value: 436698422

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                     |  4539K|   606M|       |   637K  (3)| 02:07:25 |
|   1 |  HASH UNIQUE                    |                     |  4539K|   606M|  3201M|   637K  (3)| 02:07:25 |
|   2 |   CONCATENATION                 |                     |       |       |       |            |          |
|*  3 |    HASH JOIN                    |                     |  4206K|   561M|    33M|   181K  (4)| 00:36:14 |
|   4 |     BITMAP CONVERSION TO ROWIDS |                     |   926K|    22M|       |  2279   (1)| 00:00:28 |
|   5 |      BITMAP INDEX FAST FULL SCAN| I_M_LOG_MVIEW_4     |       |       |       |            |          |
|*  6 |     TABLE ACCESS FULL           | USERS               |    15M|  1630M|       | 86638   (6)| 00:17:20 |
|*  7 |    HASH JOIN                    |                     |  7646K|  1020M|    33M|   231K  (4)| 00:46:13 |
|   8 |     BITMAP CONVERSION TO ROWIDS |                     |   926K|    22M|       |  2279   (1)| 00:00:28 |
|   9 |      BITMAP INDEX FAST FULL SCAN| I_M_LOG_MVIEW_4     |       |       |       |            |          |
|  10 |     TABLE ACCESS FULL           | USERS               |    23M|  2515M|       | 87546   (7)| 00:17:31 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("U"."NAME"="L"."USER_NAME")
   6 - filter("U"."NAME" IS NOT NULL)
   7 - access("U"."USER_ID"=TO_NUMBER("L"."USER_ID"))
       filter(LNNVL("U"."NAME"="L"."USER_NAME") OR LNNVL("U"."NAME" IS NOT NULL))

Note
-----
   - dynamic sampling used for this statement

对于使用 where isn't 的查询：

Plan hash value: 2786958565

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     1 |   114 |    21M  (1)| 70:12:13 |
|*  1 |  FILTER                       |                     |       |       |            |          |
|   2 |   TABLE ACCESS FULL           | USERS               |    23M|  2515M| 87681   (7)| 00:17:33 |
|   3 |   BITMAP CONVERSION TO ROWIDS |                     |  7062 |   179K|     1   (0)| 00:00:01 |
|*  4 |    BITMAP INDEX FAST FULL SCAN| I_M_LOG_MVIEW_4     |       |       |            |          |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "MYSCHEMA"."LOG_MVIEW" 
              "LOG_MVIEW" WHERE ("USER_NAME"=:B1 OR TO_NUMBER("USER_ID")=:B2) AND 
              ("USER_NAME"=:B3 OR TO_NUMBER("USER_ID")=:B4) AND ("USER_NAME"=:B5 OR 
              TO_NUMBER("USER_ID")=:B6)))
   4 - filter("USER_NAME"=:B1 OR TO_NUMBER("USER_ID")=:B2)

Note
-----
   - dynamic sampling used for this statement

更改数据库对象名称以保护无辜者。 ：p

Answer 1

尝试一下

select * from Users u
where exists 
   ( select user_id 
     from Log_mview l
     where l.user_id = u.user_id )
/

如果子查询返回大量行，WHERE EXISTS 可能比 WHERE ... IN 快得多。

Answer 2

这取决于您拥有的数据，但在联接中使用 Distinct 可以提高您的性能：

Select u.*
From Users u
Join ( Select Distinct user_id
       From log_mview ) l On u.user_id = l.user_id

Answer 3

第二个查询可能比第一个查询（join+distinc）更多地使用硬盘。

第一个查询可能会翻译成这样：

for each row in table Log find corresponding row in table User (in memory).

The database is probably smart enough to create in memory structures for table User that is probably much smaller than Log table.

I believe that query one (join+distinct) will require only one pass on table Log.

The distinct is probably executed in memory.

第二个查询可能会强制数据库对表 Log 进行多次完整读取。

所以在第二个查询中你可能会得到：

对于表中的每一行，用户读取表 Log 中的所有行（从磁盘）以便匹配条件。

您还必须考虑到，由于内存可用性、负载和表增加的变化，某些查询可能会出现速度上的巨大差异。