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.
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.
MV vs. report-ready table. There are trade-offs depending on the number of rows that are needed (i.e. raw data versus rolled-up data) and other actual requirements.
See my last reply in this thread from earlier today.