dcsimg
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

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

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204

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

    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;

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Um, don't refresh fast?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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 ...
    Code:
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    That makes perfect sense. Thank you slimdave for taking the time to explain it and even provide a workaround.

    Ken
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by KenEwald
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width