HINTS IN BO 2

[sreenathkm]

Hi ,

Can any one tell me what are all the Oracle Hints that can be used in BO, and how to enforce these hints.

Thanks in advance

Suresh

 

[jimbopalmer]

Hints are only hints, you cannot enforce them, they are suggestions only.

the "biggies" are

/*+ rule */ uses the older rule based optimizer rather than the newer cost based optimizer, my database is rule based by default so I don't ever use this.

/*+ all_rows */ this is great for reports, trys to make the entire report as fast as possible. my favorite.

/*+ first_rows */ this is usually better for forms as it trys to fetch the first rows as quick as possible.

there are many more hints which give increasing detailed instructions to the optimizer, but these three set the general tone of the optimizer. Try to find out which is the sefault for your instance. I tried to remain child-like, all I acheived was childish.

 

[srinathmk]

Hi,

Can you please enlighten as to where these hints can be enforced in Business Objects.

Thanks and Regards,
Srinath M.K

 

[blom0344]

Hello Shrinath,

Go into query panel, push query button, add the hint after the select and activate the 'do not generate SQL' option in order to preserve the hint. Be aware that each time you modify the SQL you will lose the hint and have to add it again........... T. Blom
Information analyst
tbl@shimano-eu.com

 

[jimbopalmer]

You can define the hint in the object, but the hint is only valid directly after the select, so you need it on quite a few objects.

if you have MYTABLE.MYCOLUMN as your object definition you can add /*+ ALL_ROWS*/ MYTABLE.MYCOLUMN and whereever MYCOLUMN is the first object chosen, you will use the ALL_ROWS hint automajically.

(hints are harmless anywhere but directly after the select)

another trick is to embed the hint in the definition of views, then any time the view is used you get that hint. I tried to remain child-like, all I acheived was childish.

 

[PAULSGS]

You could define objects with hints as their definition, they will not pass the "parse" check but they run fine when combined with other objects. Of course you will need to pick the hint object before any others so that it follows the select

 

[jimbopalmer]

Paul I am using BO 4.14 and they parse just fine, should I expect this to break as I go to BO 5? I tried to remain child-like, all I acheived was childish.

 

[skrandel]

You can also add the hint in the SBO file. Since most reports work well with the ALL ROWS hint, that's what you should use. Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[blom0344]

Steve,

Interesting option to add the hint in the sbo file. Were should we add it?

Thanks. T. Blom
Information analyst
tbl@shimano-eu.com

 

[skrandel]

I just found out that the Hint in the .sbo was only available in V4.

Actually, here are 2 tech notes that tell how to do it.
---------------------------------
Problem
How are hints and indexes applied in BusinessObjects 5.x ?
Resolution
*****CONFIGURATION******
BusinessObjects version 5.x.
Oracle databases.

***********SYMPTOMS*******
In BusinessObjects 4.x, you were able to optimize an Oracle query by modifying the .sbo file to have hints implemented. You want to know how to accomplish this in version 5.

**********RESOLUTION******
BusinessObjects 4.x had the optional parameter 'HINT' under the Oracle database engine section. This parameter is taken into account for all connections to the specified database.

BusinessObjects 5.x does not have this .sbo parameter as an option. Hints are implemented in the connection itself:
1. Go to the Connection Properties window.
2. Click on the Custom tab.
3. You will have the Hint entry under the Connection Parameters.
4. Enter your hint below and click on Set.
5. All queries using the connection will have this hint applied to it.

Notes:
The query panel SQL does not actually display the Hint. If you run a trace, you will see that the Hint is generated.

Indexes are not supported and the Hint parameter is an extra BusinessObjects feature available for connections to Oracle databases. There is no option currently available for indexes in other databases.

-------------------------------------------------

Problem
How do you enforce RULE-based optimization when you query an Oracle database from BusinessObjects?
Resolution
*****CONFIGURATION******
BusinessObjects version 5.1.x
Oracle versions 8.1.x and above

***********SYMPTOMS*******
You already know how to enforce RULE based-optimization when you query an Oracle database.
In SQL-Plus, you use the Hint "/*+RULE/".
You want to know how to enforce RULE-based optimization when you query an Oracle database from BusinessObjects.

**********RESOLUTION******
Hints are achieved through the connection itself. Follow these steps to implement a Hint:
1. In Designer, go to File > Parameters > Definition Tab > Edit > Custom tab. The Hint entry will appear under the Connection Parameters section.
2. In the text box at the bottom of the Connection Properties section, enter your Hint click "Set". The value for the Hint parameter is "/*+RULE*/" and all queries using the connection will have this Hint applied to them.

Note: The query panel SQL does not actually display the Hint. If you run a trace, you will see that the Hint is generated.

Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[blom0344]

Steve,

You are a guru if there ever was one...........

awesome... T. Blom
Information analyst
tbl@shimano-eu.com

 

[skrandel]

tblom,
How 'bout a "helpful post" vote. Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com