inlineview vs materialized view
basheer, July 09, 2003 - 2:53 am UTC
Thanks for your answer tom,
actually what i want is....
i am creating a mv,
CREATE MATERIALIZED VIEW MV_ACCOUNT_DELINQUENCY_CYCLE AS SELECT
B.PORTFOLIO_GROUP_ID,
A.SITE_ID,
A.ACCOUNT_ID,
A.CYCLE_NUMBER,
TRIM(C.CAL_MONTH_STR) REPORT_PERIOD,
LAG(A.DELINQUENCY_STATUS_ID) OVER (PARTITION BY
B.PORTFOLIO_GROUP_ID,
A.SITE_ID,
A.CYCLE_NUMBER,
TO_NUMBER(TO_CHAR(A.CYCLE_END_DATE, 'YYYYMM')) ORDER BY
B.PORTFOLIO_GROUP_ID,
A.SITE_ID,
A.CYCLE_NUMBER,
TO_NUMBER(TO_CHAR(A.CYCLE_END_DATE, 'YYYYMM')) ASC
) AS PRIOR_DELINQUENCY_STATUS_ID,
A.DELINQUENCY_STATUS_ID,
(CASE WHEN A.DELINQUENCY_STATUS_ID = 10 AND BANKRUPTCY_CHGOFF > 0 THEN
'Y'
ELSE 'N' END) BANKRUPTCY_FLAG,
NVL(A.CYCLE_CLOSING_BAL, 0) CLOSING_BAL
FROM CYCLE_SUMMARY_FACT A,
PORTFOLIOS_DIM B,
CCRT_CALENDAR_DIM C
WHERE A.PORTFOLIO_ID = B.PORTFOLIO_ID
AND A.CYCLE_END_DATE = C.CAL_DAY_DT
AND A.ACCOUNT_STATUS_ID NOT IN (1, 2);
from that MV i am selecting some records,
see the following select statement
SELECT A.PORTFOLIO_GROUP_ID,
A.SITE_ID,
A.CYCLE_NUMBER,
A.REPORT_PERIOD,
A.PRIOR_DELINQUENCY_STATUS_ID,
1000003 DELINQUENCY_STATUS_ID,
'GWO' DELINQUENCY_STATUS_DESC,
NVL(SUM(A.CYCLE_CLOSING_BAL),0) CLOSING_BAL,
COUNT(*) AS NUM_ACCOUNTS,
MAX(A.UPDATED_DATE) UPDATED_DATE
FROM MV_ACCOUNT_DELINQUENCY_CYCLE A,
delinquency_status_dim B
WHERE A.DELINQUENCY_STATUS_ID = B.DELINQUENCY_STATUS_ID
AND A.DELINQUENCY_STATUS_ID = 10
GROUP BY A.PORTFOLIO_GROUP_ID,
A.SITE_ID,
A.CYCLE_NUMBER,
A.REPORT_PERIOD,
A.PRIOR_DELINQUENCY_STATUS_ID,
1000003,
'GWO';
instead of selecting from MV can i go for INLINE VIEW?
or what i am doing is correct?
which one is faster?
thanks and regards
basheer
July 09, 2003 - 11:02 am UTC
Yes, you can go for an inline view -- however, a materialized view is just that -- the query already RAN some time ago. when you query a MV, you don't have to re-matierialize the view, it is there.
Using an inline view -- it would execute the query to gather the data at that point in time.
thanks
s.basheer, July 11, 2003 - 8:13 am UTC
thanks tom,
can you tell which one is faster?
in your previous followup you didnt' tell which one is faster.
you told to go for inline view.
ok i am going for inline view,
but i am eager to know which is faster.
s.basheer
July 11, 2003 - 10:41 am UTC
*think about it*
mv = we ran the query yesterday, you are just querying the results of the work we did yesterday.
inline view = we run the query right now, as you wait.
The answer to "which is faster" is IT DEPENDS, I can concoct examples that show the MV is faster (that is the *most likely* outcome). I can concoct examples that show the inline view is faster (this would be less likely in general)
What is a materialized view
Peter, July 11, 2003 - 11:10 am UTC
Basheer
Perhaps the word 'view' is giving rise to your confusion. A materialized view exists in exactly the same way as a table. It has size, it sits on the disk, it could be indexed, it could be partitioned and so on. The select statement in your example could just as easily start as
CREATE TABLE as SELECT ...
Your question could be restated as "what's faster - select from a view or select from a table?"
Perhaps you should ask "am I better off creating a MV (or table) with the risk that my data is not always up to date or run a slower query that is always in sync with the underlying data?"
MV
Rich, July 11, 2003 - 12:44 pm UTC
In general, I like regular views better than mvs. MVs are too complicated sometimes.
July 11, 2003 - 12:50 pm UTC
gee, thats like saying "you know, the create index syntax is pretty complicated - I don't like that so I'm not going to use them"
you cannot even being to remotely compare MV's with "regular views". total apples and oranges.
You can compare MV's to indexes -- MV's are like the indexes of your data warehouse. If you have a really big table and people do aggregates on it all day long -- which would you rather use:
o the really big table.
o a table with 500 rows in it that already contained the aggregated data.
MV versus tables
A reader, July 14, 2003 - 3:03 pm UTC
hi Tom
was wondering what the difference is if we use MVs
versus if we just precalculate the results and store
them in a different table.
I can think of following differences
1. MVs - apps dont need to know, tables - they need to know
2. You loose on the "intelligence" built into MV - for example, if you ude dimensions, you could get MVs to be used for queries that are not directly there in the MV itself.
3. You loose on automatic refresh options and control
over them?
Any other major differences? Would there be any difference
in performance - I am thinking not.
thanx!:)
July 15, 2003 - 1:01 am UTC
1) correct
2) correct
3) correct
it is all about manageability and usability.
do you want to have to "train" all of your applications to use some new MV you add tomorrow? No, you just want them to start using them.
materialized view
Nikunj, November 26, 2003 - 6:51 am UTC
Dear Tom,
I would like to understand more abt. materialized view.
i have below tables
SQL> desc invoice_mast
Name Null? Type
----------------------------------------- -------- ----------------------------
SRNO NOT NULL NUMBER(20)
INV_TYPE VARCHAR2(5)
INV_C_CODE VARCHAR2(5)
INV_N_CODE NUMBER(10)
INV_DATE DATE
CUSTCODE NOT NULL NUMBER(10)
G_PASS_NO NUMBER(10)
G_PASS_DATE DATE
INV_STATUS NOT NULL VARCHAR2(1)
VEH_NO VARCHAR2(20)
SQL> desc invoice_tran
Name Null? Type
----------------------------------------- -------- ----------------------------
SRNO NOT NULL NUMBER(20)
ITCODE NOT NULL VARCHAR2(60)
NO_OF_CARTON NOT NULL NUMBER(13,3)
U_PER_CARTON NOT NULL NUMBER(12,2)
KG_PER_CARTON NOT NULL NUMBER(13,3)
UNITRATE NOT NULL NUMBER(12,2)
ASS_VALUE NOT NULL NUMBER(12,2)
SQL> desc custmast
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTCODE NOT NULL NUMBER(10)
CUSTNAME NOT NULL VARCHAR2(80)
ADDRESS VARCHAR2(150)
CITY VARCHAR2(80)
DISTRICT VARCHAR2(80)
STATE VARCHAR2(80)
CSTNO VARCHAR2(80)
STNO VARCHAR2(80)
SQL> desc item
Name Null? Type
----------------------------------------- -------- ----------------------------
ITCODE NOT NULL VARCHAR2(60)
ITDES NOT NULL VARCHAR2(100)
BRAND NOT NULL VARCHAR2(20)
SILVER NOT NULL VARCHAR2(1)
SAFFRON NOT NULL VARCHAR2(1)
PACKAGING NOT NULL VARCHAR2(20)
UNITPACK NOT NULL NUMBER(12,2)
RATE NOT NULL NUMBER(12,2)
i had created mv as below
CREATE MATERIALIZED VIEW SALES_SUMMARY
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
select a.custcode,d.brand,d.unitpack,d.packaging,
to_date(to_char(inv_date,'Mon-YYYY'),'Mon-YYYY') inv_date,
sum(no_of_carton) no_of_carton
from invoice_mast a,invoice_tran b,custmast c,item d
where a.srno=b.srno and a.custcode=c.custcode and b.itcode=d.itcode
group by a.custcode,to_date(to_char(inv_date,'Mon-YYYY'),'Mon-YYYY'),
d.brand,d.unitpack,d.packaging;
Now i am executing below query.
SQL> select custname,d.brand,d.unitpack,d.packaging,
2 to_date(to_char(inv_date,'Mon-YYYY'),'Mon-YYYY'),sum(no_of_carton)
3 from ex2003.invoice_mast a,ex2003.invoice_tran b,ex2003.custmast c,ex2003.item d
4 where a.srno=b.srno and a.custcode=c.custcode and b.itcode=d.itcode
5 group by custname,to_date(to_char(inv_date,'Mon-YYYY'),'Mon-YYYY'),
6 d.brand,d.unitpack,d.packaging
7* order by 1,2;
my execution plan as below.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=81 Card=11820 Bytes=
2564940)
1 0 SORT* (ORDER BY) (Cost=81 Card=11820 Bytes=2564940) :Q112560
05
2 1 SORT* (GROUP BY) (Cost=81 Card=11820 Bytes=2564940) :Q112560
04
3 2 SORT* (GROUP BY) (Cost=81 Card=11820 Bytes=2564940) :Q112560
03
4 3 HASH JOIN* (Cost=17 Card=11820 Bytes=2564940) :Q112560
03
5 4 TABLE ACCESS* (FULL) OF 'CUSTMAST' (Cost=2 Card=93 :Q112560
8 Bytes=51590) 00
6 4 HASH JOIN* (Cost=15 Card=11820 Bytes=1914840) :Q112560
03
7 6 TABLE ACCESS* (FULL) OF 'INVOICE_MAST' (Cost=7 C :Q112560
ard=1952 Bytes=68320) 01
8 6 HASH JOIN* (Cost=8 Card=11820 Bytes=1501140) :Q112560
03
9 8 TABLE ACCESS* (FULL) OF 'ITEM' (Cost=1 Card=18 :Q112560
8 Bytes=12972) 02
10 8 TABLE ACCESS* (FULL) OF 'INVOICE_TRAN' (Cost=7 :Q112560
Card=11820 Bytes=685560) 03
1 PARALLEL_TO_SERIAL SELECT A1.C0 C0,A1.C1 C1,A1.C2 C2,A1.C3 C3,A
1.C4 C4,A1.C5 C5 FROM :Q11256004 A1
2 PARALLEL_TO_PARALLEL SELECT /*+ CIV_GB */ A1.C0 C0,A1.C1 C1,SUM(S
YS_OP_CSR(A1.C5,0)) C2,A1.C4 C3,A1.C
3 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ A1.C0 C0,A1.C4 C1,TO_DA
TE(TO_CHAR(A1.C2,'Mon-YYYY'),'Mon-YY
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_TO_PARALLEL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."CUSTCO
DE" C0,A1."CUSTNAME" C1 FROM "EX2003
6 PARALLEL_COMBINED_WITH_PARENT
7 PARALLEL_TO_PARALLEL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."SRNO"
C0,A1."CUSTCODE" C1,A1."INV_DATE" C2
8 PARALLEL_COMBINED_WITH_PARENT
9 PARALLEL_TO_PARALLEL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."ITCODE
" C0,A1."UNITPACK" C1,A1."BRAND" C2,
10 PARALLEL_COMBINED_WITH_PARENT
i would like to know why all tables are accessing full ?
And in my below query why only sales_summary mv is accessing ?
SQL> select a.custcode,d.brand,d.unitpack,d.packaging,
2 to_date(to_char(inv_date,'Mon-YYYY'),'Mon-YYYY') inv_date,
3 sum(no_of_carton) no_of_carton
4 from invoice_mast a,invoice_tran b,custmast c,item d
5 where a.srno=b.srno and a.custcode=c.custcode and b.itcode=d.itcode
6 group by a.custcode,to_date(to_char(inv_date,'Mon-YYYY'),'Mon-YYYY'),
7* d.brand,d.unitpack,d.packaging;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=4814 Bytes=15
8862)
1 0 TABLE ACCESS (FULL) OF 'SALE_SUMMARY' (Cost=4 Card=4814 By
tes=158862)
In oracle magazine of sept/oct 2003 there are the example on mv in there example if they select diff.
column of the same table still mv is accessing so why in my query it accessing all tables ?
Regards,
Nikunj
November 26, 2003 - 7:58 am UTC
your mv groups by custcode, .....
your query groups by custname, .......
There is no rule saying the results would be the SAME. It would be a bug if the MV was used when you grouped by a completely different set of columns.
If you group by a, b, c, d in the MV, you can group by
a,b,c,d
a,b,c
a,b
a,c
a,d
a
b,c,d
b,c
b,d
b
..... and so on in your queries and reuse the MV in many cases -- however, you CANNOT
group by Z,b,c,d
and expect the MV to be used!!! Z is "gone", not in available in the MV, the MV aggregated it away
ps:
this
to_date(to_char(inv_date,'Mon-YYYY'),'Mon-YYYY')
is a slow way to say
trunc(inv_date)
select trunc(created) from all_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2108 3.96 3.95 0 149340 0 31601
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2110 3.98 3.98 0 149340 0 31601
********************************************************************************
select to_date(to_char(created,'Mon-yyyy'),'Mon-yyyy') from all_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.06 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2108 4.91 4.74 0 149340 0 31601
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2110 4.92 4.81 0 149340 0 31601
just use TRUNC(), don't do date to string to date conversions.
what is the benefit of MV
A reader, March 18, 2004 - 5:55 pm UTC
Hi Tom
Can i infer from the discussion that the Materialized Views keep the results of a query ready before we fire a select on the MV.
i.e say if i have a select with complex joins and if i create it as a normal view and say that selects take around 2 minutes to return the results.
But now if i create the same normal view as a materialized view the query will be fired in advance(sometimes earlier) . So when i query the MV , i only view the results & the select actually does not fire on the base table when i query on the MV. SO i will retrieve the results faster. Do let me know if my inference is correct. If not do correct me.
Another point with MV is suppose if the query runs in the night and we view the results only at the time when we fire the query on hte MV. It is quiet possible i will be getting stale data. Say the changes made to the base table after the Materialized View query fired will not be reflected when we have a select on the MV
Bye
March 18, 2004 - 6:16 pm UTC
correct.
think of MVs as the "indexes of your data warehouse". Think of this MV:
select state, sum(sales), count(*), avg(sales), min(sales), max(sales)
from one_billion_row_table
group by state;
now, say your most popular queries ask for sum(sales) or count of sales for a given state.
do you want to query one billion rows, sum them up (hundreds of times a day) or..... just scan 50 or so records and get the answer straight up.
In a DW, the staleness is "not an issue" really. DW's are "somewhat static" for periods of time.
You can choose whether to accept stale data or not.
Check out:
</code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96520/toc.htm <code>
chapter on mv's
Oracle 9205
A reader, August 10, 2004 - 3:42 pm UTC
Tom,
I have a table t similar to all_objects which has 90 mill rec and on daily basis 1 mill records are inserted.
Our users fire count(*) group by many times. Is it a good idea if i create materialized view for this table or its gonna affect inserts.
Thanks.
August 10, 2004 - 4:09 pm UTC
how many times do you insert?
and how many times do they query?
soooooo -- one and many....
if you make the insert slower but make the queries light speed -- you win.
A reader, August 10, 2004 - 4:28 pm UTC
Thanks
View Vs MV
Manoj Nair, August 26, 2008 - 12:06 am UTC
As i read u said there is no comparison between view and MV. But i will give a scenario, say i am having a table having 1 billion records and 3300 records are inserted to the table every second. I want to transfer the data to a staging table to do some process. should i use a view and select the refreshed data using index on timestamp columns or should i create a MV (FAST refresh)and use that to do my staging table refreshment.
August 26, 2008 - 9:22 pm UTC
or should you just use a query and no view at all.
but, you do not say how you identify these 3,300 records and if the thing you identify them by is indexed or not, or if the billion row table is partitioned or anything really - so we cannot comment.
Inline Views Vs. Materialized Views
Manoj Nair, September 08, 2008 - 12:41 am UTC
The refreshment is based on the timestamp.I told them to use the MV to identify and transfer the refreshed data from the production DB to staging DB to do some process.But the client DBA is not happy on using the MV. The reason being that they had used it before and they had problems with that so thier DBA said that materialized view is not a good option to refresh the newly inserted data to staging DB. Now they told me to write java code to extract the newly inserted data from thier DB to our DB based on timestamp and do the process. I am not convinced.
Tom can u tell me which method is correct?
September 08, 2008 - 4:01 pm UTC
the only thing I can say is if this is all they said:
....
The reason being that
they had used it before and they had problems with that so thier DBA said that
materialized view is not a good option to refresh the newly inserted data to
staging DB.
......
then I don't like them very much. "i had a problem, don't like them, won't use them". I'll never get that attitude.
I have no idea what might be the best approach for you because I don't really know who you are or what you are trying to do?
If we look at my previous response to you - you should be able to see we have not progressed any further with our understanding of what you are really doing...
Manoj, September 10, 2008 - 6:38 am UTC
Sorry Tom ,
I am Manoj working as an oracle architect in a company
let me explain the scenario in detail.
There is a huge database (size in TB) in the client side. The DB contains 4 tables (relevant to us). everysecond 5000 record is inserted from the different sources to every 4 tables in the oracle database.They wanted us to extract the newly inserted/updated data from each table in realtime and put in our staging DB to do a process( generating a key based on the column values, joining tables and extracting values etc) and then generate on-demand report and dispatch it to their clients. We should store only 10 days data in the staging DB.
i suggested them that we will create date partitioned MV in our staging DB so that the refreshed data (inserted/updated) will be populated in the MV.
From the MV we will use a JAVA program to fetch the data and do process and write to file.
They are suggesting that we shouldn't use the MV , instead use the java program to hit the huge DB and fetch the data based on the timestamp.
there is no index in the "instime" column which is used to extract the data.
To me hitting huge DB and extracting the records based on timestamp; that also not indexed ; is a huge performance hit. Instead of it i told them oracle is providing the feature of finding the refreshed data; do it and then form the MV (low volume) data, we can do the process and write to file.
Hope this clarifies.
September 11, 2008 - 10:52 am UTC
ask them WHY and tell them "saying we had a problem in the past" doesn't count - that is not relevant to here, today, right now.
Getting things by timestamps - you do realized that with multi-versioning, it is problematic (IT IS A LOT MORE COMPLEX THAN YOU THINK) the algorithm cannot just be:
remember what time it is
query the time of the last refresh
pull all modified records since last refresh
Because you see, you will MISS records - and not only that, but this process ALWAYS has the high possibility of pulling the same record on two refreshes (you have to anticipate missing some records, you have to anticipate pulling the same row twice).
Make sure to read this entire article and understand it:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:16998677475837 Materialized views are still sounding "correct" here
well, assuming you actually have a real need to replicate this data - which you probably do not (most replicated data shouldn't be)
Inline Views Vs. Materialized Views
Manoj, September 10, 2008 - 6:38 am UTC
Sorry Tom ,
I am Manoj working as an oracle architect in a company
let me explain the scenario in detail.
There is a huge database (size in TB) in the client side. The DB contains 4 tables (relevant to us). everysecond 5000 record is inserted from the different sources to every 4 tables in the oracle database.They wanted us to extract the newly inserted/updated data from each table in realtime and put in our staging DB to do a process( generating a key based on the column values, joining tables and extracting values etc) and then generate on-demand report and dispatch it to their clients. We should store only 10 days data in the staging DB.
i suggested them that we will create date partitioned MV in our staging DB so that the refreshed data (inserted/updated) will be populated in the MV.
From the MV we will use a JAVA program to fetch the data and do process and write to file.
They are suggesting that we shouldn't use the MV , instead use the java program to hit the huge DB and fetch the data based on the timestamp.
there is no index in the "instime" column which is used to extract the data.
To me hitting huge DB and extracting the records based on timestamp; that also not indexed ; is a huge performance hit. Instead of it i told them oracle is providing the feature of finding the refreshed data; do it and then form the MV (low volume) data, we can do the process and write to file.
Hope this clarifies.
Materialised View -Any alternate solution?
Arch, April 17, 2009 - 1:04 pm UTC
Hi Tom,
Materialised View is just great but unfortunately it is available only in Enterprise edition and not in Standard edition.
We are using Standard edition so I would like to know if the next best thing to do is precalculate the results and store it in a table as one of the readers has posted.
I would like to know what is the next best thing that can be done.
Thanks and Regards,
Arch
April 17, 2009 - 4:00 pm UTC
materialized views are available in SE? I don't know what you mean.
query rewrite isn't available but materialized views certainly are.
Materialised View - An alternative
Arch, April 17, 2009 - 1:05 pm UTC
Sorry forgot to mention I am using 10g.
Thanks,
Arch
Materialised views within SE
A reader, April 20, 2009 - 7:58 am UTC
Hi Tom,
Thanks very much for getting back.
I referred to this link where it mentions that the below features are not available in standard edition or standard edition one
http://download.oracle.com/docs/cd/B13789_01/license.101/b13552/editions.htm Summary management Summary management consists of mechanisms to define materialized views and dimensions, refresh and query rewrite mechanisms, and a collection of materialized view analysis and advisory functions and procedures in the DBMS_OLAP package.
It appears my understanding of the above statement may be incorrect. Please advise.
Thanks and regards,
Arch
Performance of Materialized Views / Query performance over it
Gopal, April 20, 2009 - 10:46 am UTC
Hi Tom,
The performance of queries written over materialized views will performa faster unless the developer ends up writing complicated queries on materialzed views without appropriate indexes.
I have a question on the performance on building the materialized view. We have an application that consits of 5 modules which is modelled using 5 schemas. We have had to use the table from the other schema which are the driving tables for the entire application. By virtue of design they ended up storing current and historical data in the same table.
so if 1 record that has 10 changes
we have
-----------
col1 col2 ... coln latest_version
-----------
x x N
x x N
..
.. 9 TH ROW
x x Y -- 1OTH ROW
-- THE ONLY RECORD WE ARE INTERESTED IN
----------------
As a result we now have a table that keep growing heavily and all the queries written on these tables end up performing poorly.
Last year we tried to implement a change by having a copy of the same table on our schema but by only having the latest version of the record as we are not interested in historical versions of the same record.
When we implemented the change the data centre folks complained that there was tremendous amount of redo generated and we had to drop the materialized views and
ended up recreating them as tables
and doing truncate and insert with /*+append */ hints
Is there anyway to improve the performance of the recreation of materialized views when it has to recreate large volumes of data.
(more specifically can we supress redo generation while recreation of materialized views)
data recovery is not essential for the materialzed view as we can always recreate THE mv in case of failure
April 21, 2009 - 2:22 pm UTC
... The performance of queries written over materialized views will performa faster
unless the developer ends up writing complicated queries on materialzed views
without appropriate indexes.
....
you state that as fact, however, one of three things is always possible
a) it'll be faster
b) it'll be slower
c) it'll be the same with or without.
Always.
...
As a result we now have a table that keep growing heavily and all the queries
written on these tables end up performing poorly.
....
that would only be true if you are accessing the table via a full scan.. IF you are accessing the table via an index AND the amount of data you retrieve is not dependent on the number of history rows (eg: ONLY the current records are of interest) then in general, the history data is not relevant (presuming, assuming that your FLAG column is included in all of the relevant indexes)
The point is - to get 100 rows via an index from a table with
a) 100,000 records
b) 100,000,000 records
c) more than (b)
should be about the same - I don't care how big the underlying table is, if my index is constructed correctly, it'll take the same amount of work pretty much.
I'd say "FORGET THE MATERIALIZED VIEWS, FIX YOUR INDEXES" - it sounds like your indexes do not have this flag column in them OR the flag column is in the wrong place in the index (should be near the front of the list of columns since you always use equality against it, never >, <, LIKE and so one)
Or, I'd say "FORGET THE MATERIALIZED VIEW, USE PARTITIONING" - put old data in old partition, new data in new partition, use local indexes on each.