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

Michael Austin skrev:

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

Well, Q is the query, not the table.
I intentionally left the specific query out, that wasn't really the issue.

I was just wondering, if you make a long-running query, and then store the results in a Table or you store it in a Mat View -- how can the second case take ca 500% longer time to execute? I thought the oracle-engine would do more or less the same operations in both cases?
-- grz01 Received on Mon Nov 13 2006 - 02:00:06 CST