Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance of Create table vs. Create materialized view ?
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