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

Thread: A story of Oracle Support frustration

  1. #1
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    A story of Oracle Support frustration

    I'm not looking for a technical sol'n to a problem here, just relating a tale of frustration. Here we go ...

    I created a materialized view as an aggregation of a dimension and fact table joined together. Pretty standard procedure for data warehouse performance enhacement.

    During testing I saw that Query Rewrite (QR) wasn't available, and DBMS_MVIEW.EXPLAIN_MVIEW() told me that general query rewrite wasn't allowed because the MV query had a top-level WITH clause. Which it doesn't -- that would just be silly.

    I raised a TAR with Support, gave them the DDL for the objects, and the games began

    Oracle Support:
    query rewrite will not be used if any changes after the FROM clause has occurred that no longer matches the Materialized View (MVIEW) definition.
    Since ORDER BY clause is added to the query the partial query match rule will
    fail.
    Me: There is no ORDER BY in the MV definition, so the response makes no sense. What's this got to do with "WITH" clauses?

    Oracle Support:
    ROWID materialized views are provided for backwards compatibility only, and cannotbe used with aggregation queries
    Me: As the DDL shows, I am not trying to create a ROWID MV. If I were then the MV would not have successfully created, which it did.

    Oracle Support:
    You can't have an MV that is based on rowid MV log for one table and PK log for another.
    Me: Well, I just created one without any problem, and the documentation gives examples of doing exactly that.

    Oracle Support:
    One of the base tables doesn't have a PK -- this isn't a supported configuration
    Me: The documentation shows this in numerous places -- it makes no logical sense that the base table has to have a PK.

    Oracle Support:
    Documentation says that an MV is not updateable if the base table has no PK
    Me: As the DDL shows, I'm not trying to create an updateable MV. If I were then the MV would not have successfully created, which it did.

    Oracle Support:
    A materialized view base table must have a PK in order to use subqueries.
    Me: As the DDL shows, I am not trying to create an MV with a subquery. If I were then the MV would not have successfully created, which it did.

    Oracle Support:
    An MV that is going to be fast refreshed has to have primary key. Primary is set on MV by default.
    Me: It is not my aim to create a fast refreshable MV -- I am creating a Partition Change Tracking (PCT) fast-refreshable MV. However, as the DDL shows this is a PK-based MV anyway,and fast refresh also works.

    Oracle Support:
    It can't work because the table has to have a PK in order for the MV log to find the relevant data that needs to be propagated to the MV
    Me: The fast refresh process doesn't access the base table -- it only accesses the MV log.

    Oracle Support:
    No, it has to access the base table
    Me: I upload files that show successful fast refresh for ...
    i) a base table with no PK
    ii) a base table with no PK joined to a table with a PK

    Furthermore I upload an example of a fast refresh of a MV where the tablespace containing the base table is offline, yet the refresh was successful. Therefore fast refresh does not access the base table.

    I also point out that ...

    i) DBMS_MVIEW.EXPLAIN_MVIEW() says that fast refresh is available
    ii) I don't carewhether it'savailable or not, because i am never going to use it -- i use PCT refresh which doesn't work through MV logs.

    Oracle Support:
    The fact table in your examples does not have a primary key -- Oracle Support does not condone the use of non-PK'd tables, and we cannot support this scenario
    Me: OK, at this point i briefly lost my cool. However after recovering, I pointed out that the "Sales History" sample schema in Oracle 9i documentation does not have a PK, is also commented as ...
    Code:
    COMMENT ON TABLE sales
    IS 'facts table, without a primary key; all rows are uniquely identified by the
    combination of all foreign keys';
    ... and is used for all the MV examples, including fast refresh and PCT fast refresh, eg. here


    So, there we are then. I shall provide updates as they become available.
    Last edited by slimdave; 01-15-2004 at 01:55 PM.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  2. #2
    Join Date
    Nov 2002
    Posts
    80

    Unhappy

    after the first response I would have lost my cool.

  3. #3
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    Originally posted by onlysimon
    after the first response I would have lost my cool.
    IMHO, these days you can't do that (on the first response). *All* my tars replys are "first level" support person and the answers are canned. When the name of the support person in tar suddenly changes, the answers become precise. So, these days I think we need to wait until the third response to lose our cool.

    In Dave's case, he appeared to be pretty paient (and direct) through out the tar.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It's true -- I'm a saint, with my patience and all.

    Latest news ...
    Oracle Support:
    When multiple identical rows are inserted into the fact table, the result in the materialized view is incorrect, and fast refresh doesn't work
    Me: The result set shown in your test is correct, and when i run this modified test fast refresh does work.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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