我正在尝试创建表的子集(作为物化视图),定义为在另一个物化视图中具有匹配记录的记录。
例如,假设我有一个包含 user_id 和 name 列的用户表,以及一个包含entry_id、user_id、activity 和 timestamp 列的日志表。
首先,我创建日志表的物化视图,仅选择时间戳 > some_date 的行。现在我想要日志表快照中引用的用户的物化视图。我可以将其创建为
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 exists
子句有很大帮助,除了条件使用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 exists
:
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