• 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!

Oracle Materialized View Performance in this situation

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    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.

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

    Comment


      #3
      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?

      Comment


        #4
        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

        Comment


          #5
          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.

          Comment

          Working...
          X