1 2 Previous Next 28 Replies Latest reply: Dec 19, 2012 9:18 PM by user13328581 RSS

    creating your own materialized view utlizing tables and a job

    user13328581
      Dear expert;

      This is just for learning purposes. What if I would like to create my own materialized view utilizing only tables and a job...is this possible and a simple example would help. thank you
        • 1. Re: creating your own materialized view utlizing tables and a job
          sb92075
          user13328581 wrote:
          Dear expert;

          This is just for learning purposes. What if I would like to create my own materialized view utilizing only tables and a job...is this possible and a simple example would help. thank you
          I am sorry to learn that GOGGLE is broken for you.
          Please wait for repairs to be completed pending further notification.

          Handle:     user13328581
          Status Level:     Explorer (95)
          Registered:     Jul 5, 2010
          Total Posts:     712
          Total Questions:     194 (50 unresolved)
          • 2. Re: creating your own materialized view utlizing tables and a job
            user13328581
            and who told you i didnt google it....i have been searching and still not successful...thank you
            • 3. Re: creating your own materialized view utlizing tables and a job
              sb92075
              visit http://asktom.oracle.com

              do SEARCH on 'MATERIALIZED VIEW'
              • 4. Re: creating your own materialized view utlizing tables and a job
                user13328581
                Did u understand my question...I know how to create a materialized view...I am talking about designing one myself aka (creating my own pirated materialized view) using only tables, functions, job, stored procedures, etc.
                • 5. Re: creating your own materialized view utlizing tables and a job
                  SomeoneElse
                  There are some features of materialized views you can simulate with your own code but some you can not, like query rewrite.

                  Not sure what you'd learn by such an exercise. Mviews are a powerful feature of Oracle. You might be better off spending your time learning all you can about real materialized views and what they can (and can't) do.
                  • 6. Re: creating your own materialized view utlizing tables and a job
                    user13328581
                    What exactly in practical...Take for example I was using an old verison of oracle which doesn't have the object materialized view and I would like to create something that does a replica of it in this old verison of oracle...how can this be done and what will the limitation be...I will appreciate all the help i can get.

                    Thank you
                    • 7. Re: creating your own materialized view utlizing tables and a job
                      JustinCave
                      Materialized views have been around for a while (though in Oracle 7 and 8.0 they were known as snapshots). If you were using a version of Oracle that predated materialized views, that would mean that you were using Oracle 6, a product that is at least 12 major revisions out of date and hasn't been widely used for more than 15 years. If you're using something that old, I'd be impressed that you could manage to keep the ancient hardware running so long. But a version that old would undoubtedly have problems implementing the solution below-- I certainly wouldn't be shocked if jobs weren't available, for example.

                      If you're determined to relive the '80's, though, the simplest possible approach would be a job that runs a procedure that simply does something like
                      DELETE FROM name_of_pseudo_mv;
                      
                      INSERT INTO name_of_pseudo_mv( <<column list>> )
                        <<SELECT statement for materialized view>>
                      This would simulate a full refresh of a materialized view on a schedule that was not eligible to use query rewrite. That is a pretty minimal level of functionality for a materialized view but it meets the requirements you've laid out. Hard to imagine why you'd want to avoid using a proper materialized view, though, that offers much more functionality.

                      Justin
                      • 8. Re: creating your own materialized view utlizing tables and a job
                        user13328581
                        Hey Justin,

                        Thanks for the information so far...but that is a very simple concept which I have already implemented....however though, what is the best way to check if changes have occurred in a table...usually based on my implemented...i just scheduled a simple a job that runs hourly that deletes from the table and insert into the table. Is that good enough...and what else can add besides this feature that can further help emulate the materialized view.
                        • 9. Re: creating your own materialized view utlizing tables and a job
                          APC
                          user13328581 wrote:
                          Hey Justin,

                          Is that good enough
                          Given that this is an entirely spurious exercise in re-inventing the wheel, applying values such as "good" and "enough" is a philosophical question. not a technical one.
                          ...and what else can add besides this feature that can further help emulate the materialized view.
                          I thought you were supposed to be the one doing the learning. If you already know so much about materialized views that the only thing left to learn is how to hand-roll the functionality you should be able to figure out the next step for yourself. But if you really don't see what else you could do to improve your "simple concept" then perhaps you should be spending this time reading the documentation instead. [url http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#i1007299]Find out more.

                          Cheers, APC
                          • 10. Re: creating your own materialized view utlizing tables and a job
                            6363
                            user13328581 wrote:

                            and what else can add besides this feature that can further help emulate the materialized view.
                            Drop the whole thing and use a materialized view instead.
                            • 11. Re: creating your own materialized view utlizing tables and a job
                              user13328581
                              of course I can read the documentation guide. I just want to know ahead of time what the limitations would be from my fellow experts before i start implementing all the extra features so that i would waste time trying to implement a feature that can't be done.

                              THank you
                              • 12. Re: creating your own materialized view utlizing tables and a job
                                6363
                                user13328581 wrote:
                                of course I can read the documentation guide. I just want to know ahead of time what the limitations would be from my fellow experts before i start implementing all the extra features so that i would waste time trying to implement a feature that can't be done.
                                But wasting time implementing features that are already implemented is OK?
                                • 13. Re: creating your own materialized view utlizing tables and a job
                                  APC
                                  You appear to be labouring under the misapprehension that we have done this for ourselevs and have a wealth of experience to share with you.

                                  Well we haven't.

                                  Materialized Views work just fine. Whereas there are many other things which are missing or broken in the Oracle portfolio. Fixing those things is a better use of time than writing a half-baked MV implementation.

                                  Cheers, APC
                                  • 14. Re: creating your own materialized view utlizing tables and a job
                                    BluShadow
                                    user13328581 wrote:
                                    of course I can read the documentation guide. I just want to know ahead of time what the limitations would be from my fellow experts before i start implementing all the extra features so that i would waste time trying to implement a feature that can't be done.

                                    THank you
                                    Ahead of time? Why? Have you heard something we haven't? Are materialized views going to be scrapped in the next release of Oracle, and we've all got to figure out how to implement the same thing using lesser functionality of the database?

                                    If you want the functionality of a materialized view, then the answer is to use a materialized view.

                                    If you want something else... explain clearly what it is you are trying to achieve and what you have already done yourself. Don't waste people's time asking questions that serve no benefit to anyone.
                                    1 2 Previous Next