Re: 64K limit on fast refresh MVs?

From: Vladimir Begun <Vladimir.Begun_at_oracle.com>
Date: Thu, 10 Feb 2005 09:51:15 -0800
Message-ID: <420B9F13.3080506@oracle.com>

> has no one else run into this problem

I have. Try to squeeze your query. As an example:

SELECT col1

      , col2
      , COUNT(col1) cnt1
      , COUNT(col2) cnt2
      , COUNT(*) cnt

   FROM table

to

SELECT col1,col2,COUNT(col1),COUNT(col2),COUNT(*)FROM table

Please do not ask me why there is a limitation. :)

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

Jesse, Rich wrote:
> Hey all,
> Trying to implement a fast-refresh complex MV in 9.2.0.5.0,


 > but I'm running into an apparent bug where the fast refresh

 > blows up with an ORA-600 because the MV query is >64KB

 > (shouldn't that have been fixed since 8.0?).

> 
> The query is four SELECTs joined by a union all, each with


 > the required MV marker, and 52 aggregate columns, each with

 > it's required corresponding COUNT.  And each SELECT requires

 > CASE statements for each column.  So, this thing is huge.

> 
> I've got a TAR opened (there's apparently no fix), but has
> no one else run into this problem?  The first MV I write to


 > help get us into even thinking in a DW/DM mindset and I'm

 > already running into limitations.  I'm sounding like a broken

 > record when I tell folks "That feature/fix is only available

 > in [8.x|9.x|10x]"...


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 10 2005 - 12:55:45 CST