Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2008
    Posts
    1

    Unanswered: Oracle Query Rewrite

    Problem description

    A problem with Cognos query Studio in combination with Oracle and “Query rewrite” functionality.

    We have:
    - fact table FACT
    Columns: dim_key , total
    - dimensie table DIM
    Columns: dim_key, dim_description
    - Materialized view MV

    Materialized view:

    Create materialized view MV
    Build immediate
    refresh fast
    Enable query rewrite
    AS
    Select fact.dim_key
    , sum(fact.total)
    From FACT
    Group by fact.dim_key


    The next query will be generated by Cognos Query Studio:

    select DIM.dim_key
    , min( dim.description)
    , sum(fact.total)
    from FACT
    inner join DIM
    On DIM.dim_key = FACT.dim_key
    group by DIM.dim_key

    Because the use of the MIN-function oracle doesn’t rewrite the query.
    What should be done without changing the above query to have Oracle’s optimizer rewrite the query.
    Last edited by uptodata; 02-07-08 at 10:11.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Just a shot in the dark, but what happens if you add this?
    Code:
    Create materialized view MV_DIM
    Build immediate
    refresh fast
    Enable query rewrite
    AS
    Select dim.dim_key
    , min( dim.description)
    From dim
    Group by dim.dim_key

  3. #3
    Join Date
    Feb 2007
    Posts
    8
    We tried that (uptodata works at the same site as I do).
    We also defined an aggregate materialized view that included the min(dim.desc), but it didn't do the job.
    For some reason Cognos not only sums the measures, but also the dimension attributes, although there's only one per dim.key by definition.

    Kind of frustrating: with 'normal' queries as a human would write them the response time = 32 milliseconds, Cognos generated query: 4 minutes

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Are you saying that once you submit the query to Cognos, it rewrites the query ?

  5. #5
    Join Date
    Feb 2007
    Posts
    8
    No, I mean that while running reports Cognos generates SQL that causes our trouble. Literally 'injecting' my own query works fine.
    But the thing is, I don't wat to supply those queries for every possible combination one could select in Cognos.

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I would have to use Cognos to fully understand the problem then, as I am not able to "digest" what the real problem is.

  7. #7
    Join Date
    Feb 2007
    Posts
    8
    I'm not really impartial, because I'm thinking quite 'database centric', but every time I see the capabilities of very expensive reporting tools I'm not impressed.
    Fancy front end, not so fancy back end.

  8. #8
    Join Date
    May 2008
    Posts
    4
    Hi,

    Did you get a solution this problem of cognos and oracle rewrite. please let me know I am in the same situation.

    thanks
    koi

  9. #9
    Join Date
    Jul 2010
    Posts
    1

    create a dimention in oracle with determines

    Quote Originally Posted by andrewst View Post
    Just a shot in the dark, but what happens if you add this?
    Code:
    Create materialized view MV_DIM
    Build immediate
    refresh fast
    Enable query rewrite
    AS
    Select dim.dim_key
    , min( dim.description)
    From dim
    Group by dim.dim_key

    In order to help CBO for rewriting your query you have to create a dimention in oracle
    Code:
    CREATE DIMENSION DIM
      LEVEL DIM                        IS 
        (DIM.DIM_KEY)
      ATTRIBUTE DIM DETERMINES 
        (DIM.DESCRIPTION);

Posting Permissions

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