10 Replies Latest reply: Dec 20, 2012 8:55 PM by Hesh RSS

    MV as core table

    Hesh
      In my requirement, I have many calculated columns needs to be modeled for the fact table, I am thing using a Materialized view for all computed columns and expose this MV for reporting tools. Using Materialized view as a fact table to query is a good approach?

      Actual table columns
      ART_NO, ART_PRICE, PROFIT
      Table along with actual columns
      ART_NO, ART_PRICE, ART_PRICE_LAST_YEAR, ART_PRICE_TRIM, ART_PRICE_EURO, PROFIT, PROFIT_LAST_YEAR, PROFIT_TRIM, PROFIT_EURO
      Thanks,
      Hesh
        • 1. Re: MV as core table
          Dom Brooks
          There's certainly no reason why you shouldn't directly reference an MV.
          There is an underlying table after all.

          One thing to be aware of is how the refresh is done.
          For example a full refresh done with atomic=> false will truncate the table so there could be periods when it is empty.
          • 2. Re: MV as core table
            Hesh
            Thanks a lot Dom,

            I need them to refresh weekly once after the load is completed on the base/initial tables. I am thinking of making to allow only force refresh.


            -Hesh

            Edited by: Hesh on Dec 19, 2012 8:37 AM
            • 3. Re: MV as core table
              Mark Malakanov (user11181920)
              >
              In my requirement, I have many calculated columns needs to be modeled for the fact table, I am thing using a Materialized view for all computed columns and expose this MV for reporting tools. Using Materialized view as a fact table to query is a good approach?
              >

              Absolutely.
              Moreover, you can create bunch of MVs with pre-aggregated facts by popular dimensions and hierarchy levels. For example, you can make MVs:
              MV_PROFIT_BY_CATEGORY_YEAR for ART_CATEGORY, YEAR, sum(PROFIT);
              MV_PROFIT_BY_CATEGORY_MON for ART_CATEGORY, YEAR,MON, sum(PROFIT), sum(PROFIT_LAST_YEAR), avg(ART_PRICE), avg(ART_PRICE_LAST_YEAR);

              And enable Query Rewrite to use them when users issue queries based on these "group by"s and aggregations. Oracle will automatically get pre-calculated aggregations from these MVs instead of recalculating them for each query run.
              • 4. Re: MV as core table
                Hesh
                Thanks Mark,

                In my requirement, I don't need to fetch anything from base tables, as there is no aggregation involved in my MVs, all calculated columns are something like per-joined or self joined things and join operations taking time(business can not wait for 10min to execute the query).

                We load the data weekly once, do you have any suggestions for the proper approach to create these MVs, like a read somewhere we have to create the table first and assign MV to it.

                Thanks,
                Hesh
                • 5. Re: MV as core table
                  rp0428
                  >
                  In my requirement, I have many calculated columns needs to be modeled for the fact table, I am thing using a Materialized view for all computed columns and expose this MV for reporting tools. Using Materialized view as a fact table to query is a good approach?
                  . . .
                  In my requirement, I don't need to fetch anything from base tables, as there is no aggregation involved in my MVs, all calculated columns are something like per-joined or self joined things and join operations taking time(business can not wait for 10min to execute the query).

                  We load the data weekly once, do you have any suggestions for the proper approach to create these MVs, like a read somewhere we have to create the table first and assign MV to it.
                  >
                  Then why use an MV at all for this? All that is doing for you is storing the query you use to refresh the data.

                  You don't have to create a table first you can just create the MV. But you could also just create a table and a simple stored procedure that performs your truncate and load.
                  • 6. Re: MV as core table
                    Mark Malakanov (user11181920)
                    >
                    In my requirement, I don't need to fetch anything from base tables, as there is no aggregation involved in my MVs, all calculated columns are something like per-joined or self joined things and join operations taking time(business can not wait for 10min to execute the query).
                    >

                    Yes, these pre-aggregation views may be outside of your requirements.
                    May be someone who created the requirements was not aware about this Oracle feature that can improve performance dramatically. Especially if "business can not wait for 10min to execute the query".
                    The approach is generally called ROLAP. Oracle transparently uses it when Query Rewrite is enabled.
                    BTW, Query Rewrite can substitute joins as well. So users do not have to know about "hidden" MVs, they issue queries with joins and aggregations against base tables, QR will substitute them with pre-joined and pre-aggregated MVs transparently.

                    You can read more
                    http://docs.oracle.com/cd/B28359_01/server.111/b28313/qrbasic.htm
                    http://docs.oracle.com/cd/E11882_01/server.112/e25554/qradv.htm
                    http://www.oracle.com/technetwork/middleware/bi-foundation/twp-bi-dw-improve-perf-using-query--133436.pdf

                    >
                    We load the data weekly once, do you have any suggestions for the proper approach to create these MVs, like a read somewhere we have to create the table first and assign MV to it.
                    >
                    You create MVs once, then refresh them after you refresh your base tables and main fact MV.
                    • 7. Re: MV as core table
                      Hesh
                      Thanks Mark for pointing me to these very useful oracle features. I tried to model my requirement using query re-write feature, but did not understand how to do it. Biggest challenge in my requirement is to do a 'Full outer self-join' to get previous year-weeks Articles comparing current year-week Articles. Full outer is required as new Articles can be added during the Current year and few old Articles will no more exist in the current year-week.
                      Art_no     Sold_amt     Tim_id     yw
                      10001      20.5           700001     201101
                      10002      10.3           700001     201101
                      
                      10001      30.5           800001     201201
                      20001      50.2           800001     201201
                      Art_no      Sold_amt Tim_id          yw      Prev_Sold_Amt
                      10001      30.5      800001          201201           20.5
                      10002      null      800001          201201           10.3
                      20001      50.2      800001          201201          null
                      May be I am too deep into it!

                      Thanks,
                      Hesh
                      • 8. Re: MV as core table
                        Mark Malakanov (user11181920)
                        I believe you first need to create aggregation MV
                        Art_no, Tim_id, yw, sum(Sold_amt)

                        then you can create full self join MV from it.
                        • 9. Re: MV as core table
                          rp0428
                          >
                          I tried to model my requirement using query re-write feature, but did not understand how to do it.
                          >
                          Why did you do that? It doesn't sound like your use case requires it.

                          Query re-write comes into play when your users query the base tables and Oracle can determine that the query can be satifsifed using an existing materialized view. So if you have certain existing queries that have performance issues you could create an MV that Oracle could use 'without the users knowledge' to return the results from pre-accumulated data. Other queries would use the base tables directly.

                          But your users could also just write the queries against the materialized view table directly and not rely on query rewrite at all.
                          • 10. Re: MV as core table
                            Hesh
                            Thanks Mark and rp0428,
                            But your users could also just write the queries against the materialized view table directly and not rely on query rewrite at all.
                            Yes, sounds like a plan. My user never require to query base tables directly, they can directly query the self-joined MV.

                            Now I change my plan to not to do this using query re-write, just build an other table and populate the data using a store proc (just like MV)

                            Thanks,
                            Hesh