Re: 64K limit on fast refresh MVs?

  • From: Vladimir Begun <Vladimir.Begun@xxxxxxxxxx>
  • To: "ORACLE-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 10 Feb 2005 09:51:15 -0800

 > has no one else run into this problem

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

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


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,
 > 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]"...


Other related posts: