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 -> Re: Performance of Create table vs. Create materialized view ?

Re: Performance of Create table vs. Create materialized view ?

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Mon, 13 Nov 2006 03:16:52 GMT
Message-ID: <EsR5h.13324$B31.1296@newssvr27.news.prodigy.net>


grz01_at_spray.se wrote:
> 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
>

and you expect someone to read your mind on what table Q looks like? Size? Storage? Platform (hardware and OS)? Version? Tablespace definitions (locally managed vs. dictionary managed)? Extent Management?

The real question is: what are your requirements?

-- 
Michael Austin.
Received on Sun Nov 12 2006 - 21:16:52 CST

Original text of this message

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