Latest News

Wednesday, April 11, 2018

Applying Oracle SQL Hint in OBIEE 12c


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




  • Google+
  • Pinterest
« PREV
NEXT »

2 comments

  1. This comment has been removed by the author.

    ReplyDelete
  2. what's the version of the BI administration tool ??
    I have the 12.2.1.0.0 version and it doesn't the hint option.

    ReplyDelete