Cognos Analytics

 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.

    Thanks


    ------------------------------
    Dax Lawless
    ------------------------------

    #CognosAnalyticswithWatson


  • 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.

    https://www.ibm.com/support/knowledgecenter/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cra.10.2.2.doc/r_uda_adv_settings_content.html

    Value assigned must be ORACLE:TRUE



    ------------------------------
    NIGEL CAMPBELL
    ------------------------------