Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Posts
    8

    Question Unanswered: materialized view on external table

    I,
    i'm working with the latest 9.02 Oracle version. Did somebody have tried to create a materialized view on external table ? Can it be automatically synchronise, for example, when the file changes ? Did the materialized view were improved compared to the preceding version because it was a pain to use ?

    thanks for your advice

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you can't create indexes or views on external; tables since Oracle has no idea when the data in these tables changes.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Location
    Germantown, MD
    Posts
    34
    To answer your question Problem, yes, it can be automatically syncronized. You can specify a refresh interval... However, a refresh can have a substantial performance hit. So, you may want to use the DBMS_MVIEW.REFRESH procedure to refresh when you need it.

    During the MV creation try adding the following lines to your create command:

    refresh fast on commit
    enable query rewrite

    If you want to manually refresh MV's try: exec DBMS_MVIEW.REFRESH('mv1','mv2') or exec DBMS_MVIEW.REFRESH_ALL.

    You can also make "refresh groups" and refresh a bunch of MV's all at once as a group.

    IHTH,
    Rip
    Last edited by the Oracle; 08-26-04 at 10:50.
    "The world is a dangerous place, not because of those who do evil, but because of those who look on and do nothing. "
    - Albert Einstein

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    Hey Einstein, are all people in Maryland as stupid as you?

    An external table does not refresh when the file changes, which is what the orginal question concerned. So, create a view or an mview, it doesn't help since the external table still doesn't refresh. Also, maybe you should finish school before you start ranting. Materialized views are not just for remote refreshes. They are also used to create aggregates and complex joins. Now, shut up until you learn what you're talking about.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    Jan 2004
    Location
    Germantown, MD
    Posts
    34
    Ouch. Sorry. I thought he was talking about another database when he said external table. I feel like I was slapped in the face with a fish.

    My mistake.
    "The world is a dangerous place, not because of those who do evil, but because of those who look on and do nothing. "
    - Albert Einstein

  6. #6
    Join Date
    Jan 2004
    Location
    Germantown, MD
    Posts
    34
    Trying to redeem myself...

    If you want to sync the external table with the database table, I think you could do this:

    MERGE INTO tt1
    USING external_table et ON ( et.pk_field = tt1.pk_field )
    WHEN MATCHED THEN UPDATE SET tt1.field1 = et.field1
    WHEN NOT MATCHED THEN
    INSERT (pk_field, field1, field2, field3)
    VALUES (et.pk_field, et.field1, et.field2, et.field3);


    * DISCLAIMER *
    This is a rough guess at doing something I haven't studied in some time... Please do not beat me up over this... It is merely a suggestion to point the questioner in a certain (hopefully helpful) direction.

    -Rip
    Last edited by the Oracle; 08-26-04 at 16:26.
    "The world is a dangerous place, not because of those who do evil, but because of those who look on and do nothing. "
    - Albert Einstein

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    *grabs fish to smack the_oracle*
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jan 2004
    Location
    Germantown, MD
    Posts
    34
    *turns the other cheek*

    Quote Originally Posted by The_Duck
    *grabs fish to smack the_oracle*
    "The world is a dangerous place, not because of those who do evil, but because of those who look on and do nothing. "
    - Albert Einstein

  9. #9
    Join Date
    Apr 2004
    Posts
    246
    still can't be done. external tables only read the file when a select is done. so, if somebody changes the file from the o/s nothing will cause the database to re-select the table (keeping in mind that it isn't really a table). so you would need to use your merge within a scheduled job which runs every minute or so. then of course, you still need to account for deletes.

    So, external tables should be used to get data into the database, not to serve as a database themselves. That's the point of a database - data in one place, locking, concurrent users.... Why go back in technology to using flat files.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  10. #10
    Join Date
    May 2004
    Posts
    8

    thanks

    thanks for your advice,i thought i could help ETL developper saving some time. I'll create table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •