Hints are instructions that you place within a SQL statement that tell the data source query optimizer the most efficient way to execute the statement.
Hints override the optimizer's execution 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 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 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.
Learn how to use Oracle hints with the Oracle BI Server.
For more information about Oracle hints, see SQL r eference guide for the version of the Oracle Database that you use.
This section contains the following topics:
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 execution plan of the query optimizer. If the FAST_INDEX was not 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 Administration Tool, and type the following text in the Hint field:
index(ORDER_ITEMS, FAST_INDEX)
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 Administration Tool and type the following text in the Hint field:
leading(Products)
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 execution plan.
Follow these guidelines to create hints to optimize query performance:
Only add hints to a repository after you have tried to improve performance in the following ways:
Added physical indexes or other physical changes to the Oracle Database.
Made 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 is associated with a hint, you must also alter the hints accordingly.