Use of Database HINTS to OBIEE queries

Adding database HINTS to OBIEE queries

We had OBIEE queries that could be tuned by applying hints in physical layer tables. We can apply hint to use a specific index which helps in faster query performance. The syntax to apply this is :

index(table_name, index_name)

In oracle database the comma in hint syntax can be omitted however in BI this is required, although the explain plan with comma and without comma is exactly the same.

This approach works when we are tuning specific set of report, however if the subject area is used for multiple report types, the hint index gets applied to all queries and result in performance degradation in other queries. In those cases using a materialized view may be an better option.