Cognos Analytics with Watson

 View Only
Expand all | Collapse all

Optimizing Report on Oracle Partitioning

  • 1.  Optimizing Report on Oracle Partitioning

    Posted Tue August 25, 2020 05:08 PM
    Our reporting database uses date type columns in a partitioning scheme to improve query performance across tables with years of data retention.

    When importing the table into a data model, cognos converts these date columns (partitioned index columns) from date to to_timestamp in the resulting report query.

    Some 3rd party data warehouse tables also use an integer field for date/time behavior and partitioning.  

    Is there any way to prevent this behavior?  As a result of this conversion, the report query does not use the oracle optimizer when executing the query, greatly reducing performance.


    Dax Lawless


  • 2.  RE: Optimizing Report on Oracle Partitioning

    Posted Mon September 28, 2020 09:16 AM

    An ORACLE date type is really a form of timestamp (date and time component). When ORACLE introduced their timestamp type, it was differed from DATE and creates a few wrinkles re how ORACLE may handle implicit type casting etc.

    As of 10.2.2 an option was provided (CQM and DQM).  It is a global property when set.

    For predicates (i.e. equality, between , in), any timestamp literal which has a ZERO (all 0) time component will be expressed as an ORACLE date literal to the ORACLE JDBC driver. If you need further help, please contact support.

    Value assigned must be ORACLE:TRUE