Using 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 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.

How to Use Oracle Hints

Review theses topics about using Oracle hints with the Oracle BI Server.

See Oracle hints in the SQL reference guide for the version of theOracle 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 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:


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:


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 Administration Tool and type the following text in the Hint field:


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 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.

Creating Hints

You can add hints to the repository using the Oracle BI Administration Tool.

  1. In the Administration Tool, go to one of the following dialogs:
    • Physical Table—General tab

    • Physical Foreign Key

    • Complex Join

  2. Type the text of the hint in the Hint field and click OK.

    For a description of available Oracle hints and hint syntax, see SQL reference for the version of the Oracle Database that you use.


    Although hints are identified using SQL comment markers (/* or --), do not type SQL comment markers when you type the text of the hint. The Oracle BI Server inserts the comment markers when the hint is executed.