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
------------------------------
Original Message:
Sent: Mon April 17, 2023 11:15 AM
From: Marc Reed
Subject: SQL Query Hints
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
------------------------------