Tuesday, 21 February 2012

Using Database Hints

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
database objects. When the object associated with the hint is queried, the Oracle BI Server inserts
the hint into the SQL statement.

Below Table shows the database objects with which you can associate hints. It also shows
the Administration Tool dialog box that corresponds to the database object. Each of these dialog
boxes contains a Hint field, into which you can type a hint to add it to the repository.
Database Object Dialog Box
Physical complex join Physical Join - Complex Join
Physical foreign key Physical Foreign Key
Physical table - object type of Alias Physical Table - General tab
Physical table - object type of None Physical Table - General tab

Usage Examples

This section provides a few examples of how to use Oracle hints in conjunction with the Oracle BI
Server. For more information about Oracle hints, refer to the Oracle SQL Reference documentation
for the version of the Oracle server that you use.

Index Hint

The Index hint instructs the optimizer to scan a specified index rather than a table. The following
hypothetical example explains how you would use the Index hint. You find queries against the
ORDER_ITEMS table to be slow. You review the query optimizer’s execution plan and find the
FAST_INDEX index is not being used. You create an Index hint to force the optimizer to scan the
FAST_INDEX index rather than the ORDER_ITEMS table. The syntax for the Index hint is
index(table_name, index_name)

To add this hint to the repository, navigate to the Administration
Tool’s Physical Table dialog box and type the following text in the Hint field:
leading(table_name). If you were creating a foreign key join between
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
the Products table and the Sales Fact table and wanted to force the optimizer to begin the join with
the Products table, you would navigate to the Administration Tool’s Physical Foreign Key dialog box
and type the following text in the Hint field:

Performance Considerations

You should only add hints to a repository after you have tried to improve performance in the
following ways:


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.
The following guidelines are provided to help you create hints to optimize query performance:
hints accordingly.
Creating Hints
The following procedure provides the steps to add hints to the repository using the Administration

To create a hint

Physical Table—General tab
Physical Foreign Key

For a description of available Oracle hints and hint syntax, refer to

comment markers when you type the text of the hint. The Oracle BI Server inserts the comment
markers when the hint is executed.
Although hints are identified by using SQL comment markers (/* or --), do not type SQL
Oracle8i SQL Reference.
Physical Join—Complex Join
Navigate to one of the following dialog boxes:Type the text of the hint in the Hint field and click OK.
 Added physical indexes (or other physical changes) to the Oracle database.Made modeling changes within the server.

NOTE: If you drop or rename a physical object that is associated with a hint, you must also alter the

Leading Hint

Database hints are instructions placed within a SQL statement that tell the database 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.

NOTE: Hints are database specific. The Oracle BI Server supports hints only for Oracle 8i, 9i, and
10g servers.

No comments:

Post a Comment

Thanks to Comment