How To Do A Count(distinct X) In Materialized Views?

[KenEwald]

On 9.2.0.6, an ORA-12015 'cannot create a fast refreshable materialized view from a complex query' is returned when trying to create a materialized view with an aggregate function in it "count(distinct aid)".

Do you know of a workaround?

CREATE MATERIALIZED VIEW ENTITIES_COUNT
BUILD IMMEDIATE
USING INDEX
TABLESPACE MY_TABLESPACE
REFRESH FAST
ON DEMAND
ENABLE QUERY REWRITE
AS
select client_id,
list_id,
entity_type_id,
status_id,
count(aid) count_aid,
count(distinct aid),
count(*)
from entities
group by client_id,
list_id,
entity_type_id,
status_id;

[marist89]

Um, don't refresh fast?

[slimdave]

The reason why you cannot do this is pretty straightforward.

If you insert a new row into the entities table with particular values for client_id, list_id, entity_type_id, status_id, and aid then the MV cannot be updated based only on that row's information. The count(aid) column can be incremented for the appropriate row of the MV if AID is not null (remembering that Count(AID) only counts rows with non-null values of AID), or a new row can be added if that combination of client_id, list_id, entity_type_id, and status_id does not already exist. However, in order to maintain the value for the count(distinct AID) column Oracle has to visit every row in the entities table that has that combination of the four key columns.

Hence, Count(distinct AID) precludes the use of fast refresh.

If you wanted a workaround it would be to add another MV as ...

CREATE MATERIALIZED VIEW ENTITIES_COUNT
BUILD IMMEDIATE
USING INDEX
TABLESPACE MY_TABLESPACE
REFRESH FAST
ON DEMAND
ENABLE QUERY REWRITE
AS
select client_id,
list_id,
entity_type_id,
status_id,
aid,
count(*)
from entities
group by client_id,
list_id,
entity_type_id,
status_id,
aid;

Then you can consider whether you still need the original MV. You might like to check how well clustered the MV is after a few fast refreshes, and consider whether there's any benefit to creating it in a multi-column hash cluster based on client_id, list_id, entity_type_id, and status_id to reduce the i/o associated with reading all values of AID for a particular combination of those four columns -- it'd need benchmarking for both MV update, storage, and selects from the MV though.

[KenEwald]

That makes perfect sense. Thank you slimdave for taking the time to explain it and even provide a workaround.

Ken

[slimdave]

That makes perfect sense. Thank you slimdave for taking the time to explain it and even provide a workaround.

Ken

Not my usual style at all, Ken. I really only did it to make Jeff look bad