materialized view + count(distinct x)

From: Can <no_at_spam.net>
Date: Mon, 13 Sep 2004 20:38:53 GMT
Message-ID: <xzn1d.282341$vG5.218429@news.chello.at>

Hi,

I've a simple query:

SELECT link, count(DISTINCT IP) as distinctip, count(*) as allcount FROM access
GROUP BY link

...and am trying to create a mview with this but keep getting ORA-12015 error (too complex query).
The oracle 10g manual claims a "count (distinct x)" would be fast refreshable, but on my server it doesn't work.

I've created an index on the column "link", and "ip". When creating the m.view log, I specified "nocache", "with rowid (...all columns...)", "sequence" and "including new values".

Refering to the oracle documentation, the materialized view logs must:
"Contain all columns from the table referenced in the materialized view" (=>
"link" and "ip")
"Specify with ROWID and INCLUDING NEW VALUES"
"Specify the SEQUENCE clause if the table is expected to have a mix of
inserts/direct-loads, deletes, and updates"
"COUNT(*) must be specified"
"The SELECT list must contain all GROUP BY columns"

I've followed these rules - without success. What am I doing wrong? Or is it impossible to create a fast refreshable materialized view on the given query?

thanks,
can oezdemir Received on Mon Sep 13 2004 - 15:38:53 CDT