Oracle - materialized view or table
I have a table (in Oracle 12c) with 22 million records so far and plus 10,000 records inserted every day. We need calculations based on this table such as:
select col1, col2, count(*) cnt from my_table group by col1, col2;
This query returns at least 30 rows, and the combination col1
, col2
will be unique.
Our application needs to check the value frequently CNT
, but the approximate values CNT
are good enough. This means that we can create a materialized view and update it every 10-20 minutes.
Is a materialized view a good choice for this requirement or should I create a regular table for it?
Thanks in advance!
source to share
There are at least three different ways to achieve this goal:
-
Refreshing a Materialized View Quickly Refreshing with Refreshing is probably the ideal solution. 10,000 inserted rows will have a small amount of overhead, but then there is no need to rebuild anything; new totals are available immediately after every commit, and getting new totals is incredibly fast. The downside is that quickly updated materialized views are difficult to set up and have a lot of weird bugs. They work well with your sample design, but may not work well with a more complex scenario.
Example circuit
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;
Rewrite query
The sample query is silently modified to use a small materialized view instead of a 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 | --------------------------------------------------------------------------------------------
-
Compressed tree index B * If there are only 30 unique values, the index should compact well and not take up much space. Then the index can be used in a quick scan of the full index and act like a skinny table. This method requires that at least one value is not null. If both values can be null, then the index function might be useful here.
create index my_table_idx on my_table(col1, col2) compress;
-
Bitmap Index Bitmap indexes are small and fast when there are few distinct values. However, they can introduce catastrophic blocking issues for some DML types.
create bitmap index my_table_idx on my_table(col1, col2);
source to share
Depending on how approximate your method is, you can also try the SAMPLE suggestion:
select col1,
col2,
count(*) cnt
from my_table sample(1)
group by col1, col2;
Depending on the distribution of these values, this may give a reasonable estimate. You can check how high you need the number to give a reasonable result, but it's rarely worth going through 4 or 5 unless you use the block clause:
select col1,
col2,
count(*) cnt
from my_table sample block(10)
group by col1, col2;
source to share