doubts

[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?

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


Thanks

F.

[abhaysk]

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

[efrijters]

As far as I know, materialized views aren't updatable when you have more than one table in the definition...

[slimdave]

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).

[marist89]

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?

[Mnemonical]

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.

[slimdave]

analyze table compute statistics.

you might look at dbms_stats.gather_table_stats as a better alternative.