Bookshelf Home | Contents | Index | Search | PDF

Siebel Analytics Server Administration Guide > Working in a Repository's Physical Layer >

Using Database Hints

---

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. Siebel Analytics Server 7.5 supports hints only for Oracle 8i and 9i servers.

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 Analytics server inserts the hint into the SQL statement.

Table 13 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 enter a hint to add it to the repository.

Table 13.  Database Objects That Accept Hints

Database Object	Dialog Box
Physical table - object type of None	Physical Table - General tab
Physical table - object type of Alias	Physical Table - General tab
Physical foreign key	Physical Foreign Key
Physical complex join	Physical Join - Complex Join

Usage Examples

This section provides a few examples of how to use Oracle hints in conjunction with the Siebel Analytics Server. For information about Oracle hints, see the following Oracle documentation: Oracle8i Tuning for reference information, and Oracle8i SQL Reference for descriptions about Oracle hints and hint syntax.

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 enter the following text in the Hint field:

index(ORDER_ITEMS, FAST_INDEX)

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 navigate to the Administration Tool's Physical Foreign Key dialog box and enter the following text in the Hint field:

leading(Products)

Performance Considerations

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:

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

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

Creating Hints

The following procedure provides the steps to add hints to the repository using the Administration Tool.

To create a hint

Navigate to one of the following dialog boxes:
Physical Table—General tab
Physical Foreign Key
Physical Join—Complex Join
Enter the text of the hint in the Hint field and click OK.

For a description of available Oracle hints and hint syntax, see Oracle8i SQL Reference.

NOTE:  Do not enter SQL comment markers (/* or --) when you enter the text of the hint. The Siebel Analytics Server inserts the comment markers when the hint is executed.

Bookshelf Home | Contents | Index | Search | PDF