Applying Oracle SQL Hint in OBIEE 12c

Writen by Chai Biscuit 2:27 AM - 2 Comments

What are Hints?:

1.       Hints are the instructions that we place within sql statement  which tells  data source query optimizer to choose the most efficient way to execute the query.

2.       Hint override the optimizer’s execution plan ,so we can use hints to improve performance by forcing the optimizer to use more efficient plan.

3.       Hints are only supported for Oracle database sources.

Hints are of two types.

1. Index Hint

2 Leading Hint

Index hint:   

Index Hint instructs the optimizer to scan a specified index rather than the table.

For Instance:

We find queries against REVENUE_ORDERS table to be slow. We review the execution plan of the query optimizer  and find the FAST_INDEX is not being used.

 Create an Index hint to force the optimizer to scan the FAST_INDEX index rather than the ORDER_ITEMS table.

Syntax:

index(table_name, index_name)

2. Leading Hint:

The leading Hint forces the optimizer to build the join order of a query with a specified table

Syntax:

leading(Location)

Note:

Location : Specified table Name

CREATING HINTS

To create a hint:

In the Administration Tool, go to one of the following dialogs:

• ·         Physical Table—General tab
• ·         Physical Foreign Key
• ·         Complex Join

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

Creating Hint  in Physical table:

Creating hint in physical foreign key

• Facebook
• Tweeter
• Google+
• Whatsapp Pinterest