Fast Refresh Materialized View A fast refresh materialized view is probably the ideal solution. The 10,000 rows inserted will have a small amount of overhead but then there is no need to rebuild anything; the new totals are available immediately after each commit and retrieving the new totals will be incredibly fast. The downside is that fast refresh materialized views are difficult to setup, and have lots of weird gotchas. They work well with your sample schema but may not work with a more complicated scenario.
Sample Schema
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 and materialized 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;
Query rewrite
The sample query is silently modified to use the small materialized view instead of the large table.
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 |
col1
和col2
上分别创建 BITMAP 索引。这样查询速度应该会更快。 - Wernfried Domscheit