Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only
  • 1.  SQL Query Hints

    Posted Mon April 17, 2023 11:16 AM

    Are we able to add SQL Query hints to a report built on top of a data module?  

    I ask as I have a report that runs quick or slow depending on the prompts used. The Oracle explain plan shows the database tries to answer the query in very different ways depending on the prompts.  I need to be able to use Oracle hints to ensure bit map indexes on fact table within the query are always used.  

    Open to any suggestions!



    ------------------------------
    Marc Reed
    ------------------------------


  • 2.  RE: SQL Query Hints

    Posted Tue April 18, 2023 05:01 AM

    Hi Marc,

    It is not a best practice to add Oracle hint within Cognos.

    Taken from Oracle documentation:
    Hints were introduced in Oracle7, when users had little recourse if the optimizer generated suboptimal plans. Now Oracle provides a number of tools, including the SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to help you address performance problems that are not solved by the optimizer. Oracle strongly recommends that you use those tools rather than hints. The tools are far superior to hints, because when used on an ongoing basis, they provide fresh solutions as your data and database environment change.

    I know it is probably not the answer you are looking for. You want to check how the prompts are created.
    Do you use calculations (use Oracle functions when possible)?

    Best regards,



    ------------------------------
    Patrick Neveu
    Positive Thinking Company
    IBM Champion
    ------------------------------