1 Reply Latest reply: Oct 9, 2013 6:16 PM by rp0428 RSS

    materialized view use-case

    vxwo0owxv

      Greeting Gurus.

       

      Our BI request us the DB team for a function that would return a set based on some selection criteria for user analysis

      and this will be a table function

       

      e.g. This function select a set of customer_ID  who is male

       

      Nevertheless BI team suggests that it needs the business-user to have persistent on the analysis .

      And the BI team suggests to use a table to store the business-user temporary result sets for the analysis.


      e.g.

      if the business-user do a select the set of customre who is male on Day1

      the same user want the set of customer who buys shoes on Day2

      and the same user want the set of customer buys socks on Day3

      And on Day4 the same business user is doing some other criteria selection but still want the set from Day1,Day2,Day3

       

      We suggests that it would be more efficient to build materialized view and Build indexes  based on those frequently used criteria and just tag the business-user name along

      as the business-user has no relation to any of those selection criteria.

      It would be doing a lot of Delete/Insert on clearing and populating those sets that the business-user needed according to the BI team's suggestion.

      It is very unusual for a reporting DB has frequent Insert/Update/Delete rather than massaging/loading the data.

       

      Any suggestions are highly appreciated.