Oracle - 物化 View 或表

Question

到目前为止，我有一个表(在 Oracle 12c 中)有 2200 万条记录，加上每天插入 10,000 条记录。我们需要基于此表的计数，例如:

select col1, col2, count(*) cnt from my_table group by col1, col2;

此查询将返回少于 30 行，并且 col1、col2 的组合将是唯一的。

我们的应用程序需要经常检查CNT 值，但是CNT 的近似值就足够了。这意味着我们可以创建一个物化 View 并每 10-20 分钟刷新一次。

物化 View 是满足此要求的不错选择，还是我应该为其创建一个常规表？

提前致谢!!!

Answer 1

至少有三种不同的方法可以实现这一点:

1. 快速刷新实体化 View 快速刷新实体化 View 可能是理想的解决方案。插入的 10,000 行会有少量开销，但不需要重建任何东西；新的总数在每次提交后立即可用，并且检索新的总数将非常快。缺点是快速刷新物化 View 很难设置，并且有很多奇怪的陷阱。它们适用于您的示例架构，但可能不适用于更复杂的场景。

   示例架构

   drop table my_table;
   
   create table my_table(
       id number not null,
       col1 number not null,
       col2 number not null,
       constraint my_table_pk primary key (id)
   );
   
   insert into my_table
   select level, mod(level, 30), mod(level+1, 30)
   from dual
   connect by level <= 100000;
   
   begin
       dbms_stats.gather_table_stats(user, 'MY_TABLE');
   end;
   /
   

   创建物化 View 日志和物化 View

   create materialized view log on my_table with rowid(col1, col2) including new values;
   
   create materialized view my_table_mv
   refresh fast on commit
   enable query rewrite as
   select col1, col2, count(*) total
   from my_table
   group by col1, col2;
   

   查询重写

   示例查询被静默修改为使用小物化 View 而不是大表。

   explain plan for
   select col1, col2, count(*) cnt
   from my_table
   group by col1, col2;
   
   select * from table(dbms_xplan.display);
   
   Plan hash value: 786752524
   
   --------------------------------------------------------------------------------------------
   | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
   --------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT             |             |    30 |   300 |     3   (0)| 00:00:01 |
   |   1 |  MAT_VIEW REWRITE ACCESS FULL| MY_TABLE_MV |    30 |   300 |     3   (0)| 00:00:01 |
   --------------------------------------------------------------------------------------------
   

2. 压缩的 B*Tree 索引 如果只有 30 个唯一值，索引应该压缩得很好并且不会占用太多空间。然后索引可以用在一个快速的全索引扫描，就像一个瘦表。此方法要求至少有一个值不为空。如果两者都可以为空，则基于函数索引在这里可能很有用。

   create index my_table_idx on my_table(col1, col2) compress;
   

3. 位图索引 当有少量不同的值时，位图索引小而快。但是它们会引入灾难性的锁定问题对于某些类型的 DML。

   create bitmap index my_table_idx on my_table(col1, col2);