Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Performance of Create table vs. Create materialized view ?

Performance of Create table vs. Create materialized view ?

From: <grz01_at_spray.se>
Date: 12 Nov 2006 17:27:35 -0800
Message-ID: <1163381255.186059.124240@h48g2000cwc.googlegroups.com>


Hi,

I have a query (Q) that takes some 10 to 15 minutes to execute.

What I discovered was that this query has very different performance in those 2 statements:

  1. create table T as Q;
  2. create materialized view MV refresh force on demand as Q;

Case 1 takes ca 10-15 minutes to execute, while case 2 takes between 1 and 2 hours to execute, even though the query Q is identical in the two statements.

Beforehand, I expected those queries to do essentially the same work -- execute the query, and put the result in new data-segments, associated with the table/mv.

I first though it must have been caused by some other loads affecting the disk-systems, but I then ran the same statements several times over, alternating between the 2 cases and could only confirm I get similar results every time.

Case 2 takes ca 5 times longer to execute than case 1.

Anyone here can explain what may cause this difference?

TIA,

-------------------------------------------grz01
Received on Sun Nov 12 2006 - 19:27:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US