Oracle Materialized View Performance in this situation

[BoggyMcCBoggyFace]

Basically I already have a summary table built that I can report off still a lot of rows 400mil rows, for the report I don't need to join this table to anything the 1 table has all I need. Now I'm being told that I should pull that data into an Oracle Materialized view and it would be even fast 'as its on disk', note it takes 30mins to populate the MV, when I tested the MV vs. the summary table the summary table was actually faster.

Generally should an MV be faster in my circumstances, I don't buy it that it would and my testing shows this, just want to understand if what I'm being told is bollocks or not.

[DimPrawn]

The table is "on disk" so why would the materialised view be any faster?

[css_jay99]

note it takes 30mins to populate the MV, when I tested the MV vs. the summary table the summary table was actually faster.

Generally should an MV be faster in my circumstances, I don't buy it that it would and my testing shows this, just want to understand if what I'm being told is bollocks or not.

30 min to populate the MV, does it also take that long on a refresh?, how often do you have to refresh?

[BoggyMcCBoggyFace]

Thanks for replies.

Yeah takes 30mins to refresh the MV but only need to do it once a day and it will hold a rolling years worth of data, I'm still to tune it but I just need to get some in and can come back to tuning it later. I didn't see any real performance difference between summary table or the MV but in the end I went with the MV as if I need to change it its a lot easier and quicker to get released as I just generate the MV via an SP compared to doing it with a summary table, where it would need Table changes, ETL changes etc.

cheers

[css_jay99]

I went with the MV as if I need to change it its a lot easier and quicker to get released as I just generate the MV via an SP compared to doing it with a summary table, where it would need Table changes, ETL changes etc.

cheers

This is what they were getting at.