Use Hints in SQL Statements
Hints are instructions that you place within a SQL statement that tell the data source query optimizer the most efficient way to run the statement.
Hints override the optimizer's processing plan, so you can use hints to improve performance by forcing the optimizer to use a more efficient plan. Hints are only supported for Oracle Database data sources.
Using the Model Administration Tool, you can add hints to a repository, in both online and offline modes, to optimize the performance of queries. When you add a hint to the repository, you associate it with Physical layer objects. When the object associated with the hint is queried, the Oracle BI Server inserts the hint into the SQL statement.
The table shows the physical objects with which you can associate hints. It also shows the Model Administration Tool dialog that corresponds to the physical object. Each of these dialogs contains a Hint field, into which you can type a hint to add it to the repository.
| Database Object | Dialog |
|---|---|
|
Complex join |
Complex Join |
|
Physical foreign key |
Physical Foreign Key |
|
Physical table |
Physical Table - General tab |
Hints are only supported when the Table Type is set to Physical Table. For other table types, the hint text is ignored. For physical tables with a table type of Select, you can provide the hint text as part of the SQL statement entered in the Default Initialization String field.
How to Use Oracle Hints
Review these topics about using Oracle hints with the Oracle BI Server.
See Oracle hints in the SQL reference guide for the version of the Oracle Database that you use.
This section contains the following topics:
About the Index Hint
The Index hint explains how the optimizer scans a specified index rather than a table.
If queries against the ORDER_ITEMS table are slow, you can review the processing plan of the query optimizer. If the FAST_INDEX wasn't used, you can create an Index hint to force the optimizer to scan the FAST_INDEX rather than the ORDER_ITEMS table. The syntax for the Index hint is as follows:
index(table_name,index_name)
To add this hint to the repository, open the Physical Table dialog in the Model Administration Tool, and type the following text in the Hint field:
index(ORDER_ITEMS, FAST_INDEX)
About the Leading Hint
The Leading hint forces the optimizer to build the join order of a query with a specified table.
The syntax for the Leading hint is leading(table_name). If you were creating a foreign key join between the Products table and the Sales Fact table and wanted to force the optimizer to begin the join with the Products table, you would go to the Physical Foreign Key dialog in the Model Administration Tool and type the following text in the Hint field:
leading(Products)
About Performance Considerations for Hints
Hints that are well researched and planned can result in significantly better query performance.
However, hints can also negatively affect performance if they result in a suboptimal processing plan.
Follow these guidelines to create hints to optimize query performance:
-
Only add hints to a repository after you've tried to improve performance in the following ways such as:
-
Adding physical indexes or other physical changes to the Oracle Database.
-
Makin modeling changes within the server.
-
-
Avoid creating hints for physical table and join objects that are queried often. If you drop or rename a physical object that's associated with a hint, you must also alter the hints accordingly.