Re: materialized view + count(distinct x)

Can wrote:

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

I recall you (or someone) posted this question recently. You've already checked the documentation for the MV logs to support fast refresh, thank you. For what it's worth, I quickly checked and it does look to me like you've followed "the rules" in the Oracle documenation.

Incidentally, just recently, it was posted that the word 'ACCESS' (your table name in the FROM clause) was an Oracle reserved word, so maybe that's your problem? Worth a quick test.

Two subsequent approachs come to mind:

1. set up a reproducible test case, with all the create statements, some sample data, and so on, so you or someone else can try it in some other database. If it's a reproducible bug, it should be logged with Metalink.
2. go for a workaround. Create your MV without the count(distinct x) expression but everything else (all the time-consuming work), then get your final count from a much simpler query on the MV (directly or via query re-write).

--Mark Bole Received on Tue Sep 14 2004 - 19:14:43 CDT