DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: doubts

  1. #1
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    doubts

    1. Let's suppose I truncate a table and insert 1 million rows (it had 1 million rows before truncating). Do I need to rebuild indexes and analyze the table?

    2. How do I update a materialized view if it containts more than one table in from clause ?


    Thanks

    F.

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: doubts

    Originally posted by Mnemonical
    1. Let's suppose I truncate a table and insert 1 million rows (it had 1 million rows before truncating). Do I need to rebuild indexes and analyze the table?


    Rebild Index is not required...Though Drop Index during bulk load and recreate them wud be good choice...

    Analyze - Might be (Not sure) if it really helps in improving performance.

    Originally posted by Mnemonical
    2. How do I update a materialized view if it containts more than one table in from clause ?
    RTM
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    As far as I know, materialized views aren't updatable when you have more than one table in the definition...

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I would definately analyze the table and indexes.

    index rebuild probably not required except for bitmap indexes.

    I don't understand what the problem is in refreshing a materialized view defined on multiple tables -- use dbms_mview.refresh (as i recall).
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: doubts

    Originally posted by Mnemonical
    2. How do I update a materialized view if it containts more than one table in from clause ?
    Define Update. Update as in UPDATE sql statement or Update as in refresh?
    Jeff Hunter

  6. #6
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282
    Define update: update a materialized view. I dont want to update the base tables, I want to update the mv, and it's formed by several tables (that's the why I dont want to use dbms_refresh), I use 'for update' clause and oracle does not allow it.

    So, whe I truncate a table and insert lots of rows using APPEND hint I need to analyze the table, ok, I'll do it. I'm going to truncate the table, insert /*+ append */ as select, then analyze table compute statistics.


    thanks.

    F.

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    analyze table compute statistics.
    you might look at dbms_stats.gather_table_stats as a better alternative.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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


Click Here to Expand Forum to Full Width