This discussion is archived
1 2 Previous Next 28 Replies Latest reply: Dec 19, 2012 7:18 PM by user13328581 RSS

creating your own materialized view utlizing tables and a job

user13328581 Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    visit http://asktom.oracle.com

    do SEARCH on 'MATERIALIZED VIEW'
  • 4. Re: creating your own materialized view utlizing tables and a job
    user13328581 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points