首页 文章

Oracle - 物化视图或表

提问于
浏览 次
3

我有一个表（在Oracle 12c中）到目前为止有2200万条记录，每天还有10,000条记录 . 我们需要基于此表的计数，例如：

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

此查询将返回少于30行， col1 ， col2 的组合将是唯一的 .

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

物化视图是否适合此要求，还是应该为其创建常规表？

提前致谢！！！

oracle

2 回答

  • 1

    至少有三种不同的方法可以实现这一目标：

    • Fast Refresh Materialized View 快速刷新物化视图可能是理想的解决方案 . 插入10,000行将产生少量开销，但不需要重建任何东西;每次提交后立即可以使用新的总计，并且检索新的总计将非常快 . 缺点是快速刷新物化视图很难设置，并且有很多奇怪的陷阱 . 它们适用于您的示例模式，但可能无法用于更复杂的方案 .

    示例模式

    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;
/

    创建物化视图日志和物化视图

    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;

    查询重写

    静默修改示例查询以使用小物化视图而不是大表 .

    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 |
--------------------------------------------------------------------------------------------
    • Compressed B*Tree Index 如果只有30个唯一值，则索引应该压缩得很好并且不会占用太多空间 . 然后索引可以在快速全索引扫描中使用，并且像瘦表一样 . 此方法至少需要一个值不为null . 如果两者都可以为null，那么基于函数的索引在这里可能很有用 .
    create index my_table_idx on my_table(col1, col2) compress;
    • Bitmap index 当存在少量不同值时，位图索引很小且很快 . 但是，它们可能会为某些类型的DML引入灾难性的锁定问题 .
    create bitmap index my_table_idx on my_table(col1, col2);
    回复于
  • 0

    根据您的方法的近似程度，您还可以尝试SAMPLE子句：

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

    根据值的数据分布，这可能会给出合理的估计 . 您可以测试您需要多少数量以获得合理的结果，但除非使用block子句，否则很少值得超过4或5：

    select col1,
       col2,
       count(*) cnt
from   my_table sample block(10)
group by col1, col2;
    回复于

相关问题