Oracle Materialized View Performance in this situation

Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:

You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.

css_jay99 replied

7 March 2018, 17:21
Originally posted by BoggyMcCBoggyFace View Post

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.
Leave a comment:
BoggyMcCBoggyFace replied

7 March 2018, 16:56
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
Leave a comment:
css_jay99 replied

7 March 2018, 16:38
Originally posted by BoggyMcCBoggyFace View Post

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?
Leave a comment:
DimPrawn replied

6 March 2018, 19:54
The table is "on disk" so why would the materialised view be any faster?
Leave a comment:
BoggyMcCBoggyFace started a topic Oracle Materialized View Performance in this situation

6 March 2018, 14:19
Oracle Materialized View Performance in this situation

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.
Tags: None