Geek Answers Handbook

Oracle - materialized view structure looks so slow

I have a huge view that I need to customize. This is a simple setup as I just add the NVL function to the select statement.

those. Original...

Select this, that.....

those. Changed

 Select NVL(this, orThat) as this, NVL(That, orThis) as that

The request takes 26 seconds to start, but due to the number of rows retrieved (2.3 million), it is dead slowly. He ran for almost 5 days in a row, and then I stopped him.

This is a problem, especially because I need to deliver this to the client, and they cannot run the script for 5+ days to create the MV.

Question: Is there a way to speed up MV change / restore? Would it be faster if I changed MV or it would be about the same as reset and recreate?

Oracle version = 10g

+4
source share
2 answers

You cannot change the definition of a query for a materialized view — you need to reset and recreate it. However, you can try this approach, it may be faster than re-creating the whole MV:

  • Retrieve the materialized view using the PRESERVE TABLE.
  • Refresh data in a table that was MV to reflect new column definitions.
  • Restore the materialized view using the ON PREBUILT TABLE clause.

If you have indexes on the view, it may be useful to disable and rebuild them.

+9
source

5+ days to build 2-3 million rows of MV? Thats waaaaay of punches, too much to be just bad SQL. I assume that you can be blocked by some other process (?). Not sure, but check this from another session after starting MV recovery:

 select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;

Just to guess. If you use Toad, you can also get this information (via Database-> monitor-> session browser). It will also show you the progress of Long Ops (table scan, etc.).

Edit: Oh, btw, building MV using nologging should help to do a little damage once you determine that you have no problems mentioned above.

+1
source

More articles:


All Articles