[Edit: - Cindy - Moving this cool suggestion to the Designer forum]
I know that you where only using the addition of Hints as an example of your point but for what it is worth you can add hints in your generated SQL. You will want to create a dimension variable in the universe something like this:
/*+ the hint */ ‘’
the two ’ at the end are important.
You then want to make sure that you use the created dimension object is the first object in your results objects selection within the query panel.
This is not elegant but it will put the hint in the resulting generated SQL.
I think that the distinction above is important only in that when a person sees the generated SQL code they might not see the , causing the problem because it would be after the hint.
This might not be worth mentioning but I think that the reason the ‘’ are necessary is not because of the way Oracle parses the string (although Oracle will be the reporter of the bad news ) but because of the way that Business Objects builds the SQL string. If we put an object into the query panel Business Objects can only interpret the object as a select field. That is why I said originally that this is not an elegant solution. If Business Objects could interpret the contents of the object as a comment/hint or if there was an object type of comment and/or hint then the SQL builder would understand that we did not pass a select variable and would not put the , after it. I do think that this is a very subtle difference but in one interpretation we get mad at Oracle and in the other we get mad at Business Objects . The other difference is where we might spend our energies attempting to optimize a query. In this particular case we could spend a lot of time figuring out how to change Oracle when, in fact our efforts would need to be focused on Business Objects.
That being said… If you always use the SAME hint, you are supposed to be able to apply hints at the Designer Level, through the connection.
If you look at the Connection Parameters, then the CUSTOM tab, you will see that you can enter a value in for the HINT.
This is a hint that will always occur, so it’s not very useful, unless you were trying to enforce something like parallelism for all queries. I’m not sure the syntax that it’s looking for, but a few experiments should resolve that.
The dimension object for hints - /*+ Hint / ‘’ it helps to generate a simple sql. What about sqls generated with a GROUP BY caluse. /+ Hint */’’ , appears on the GROUP BY part of the SQL also. Have anyone thought of any trick to get around that problem ?
Yep, you’re correct. But does it cause a problem - does your SQL not parse? I’ve just tested this and sure enough the hint sits in the group by clause as well as at the top of the select clause. But my Island-Resorts-with-hint query came back just fine.
The reason it should not cause a problem in the group by is it is essentially a text constant. So it would have the same impact as if you had an object with ‘Some Text’ as the select.
The only time this would be a problem is for databases that don’t allow grouping by a constant (some versions of DB2, I think) but since the hint syntax we’re talking about is for Oracle, it should always work.
Which begs the question, is this hint syntax limited to Oracle? Are there equivalent options for other databases?
Good question! A while ago I created a generic hints universe. The idea is that it contains a class of hint objects for a variety of databases which you could potentially include in a master universe. At worst its a good place to keep this sort of info. So for oracle there are things like an ‘All Rows’ object with syntax /+ ALL_ROWS/ ‘’ etc.
We also have a small amount of SQL Server databases so I did a bit of reading on this. I didn’t find anything directly comparable to hints but I did find this:
SET FORCEPLAN ON processes tables in order which can make things go fast. My object simply has a select statement of : SET FORCEPLAN ON
Or you could try OPTION(FAST number_of_rows) - another speed things up option.
Not strictly a hint but if you want users to be able to run SQL Server Business Objects reports and just return a few rows - without going into options in the query panel - then they can use an object with this select statement:
TOP @Prompt(‘Enter the number of rows you want returned’,‘N’,) ‘’
Hope this helps and that there are non-oracle db gurus out there who can add more. I’d like to know about any Informix style hints…
I have been looking at using hints against tables that have multiple indexes and in some cases concat primary key strings with index. Have you created the index hints with the index names in your hint universe?
Did you find that they were used correctly by the end user report developers?
Did you use oracles explain plan to see the results of the hints as some times the hint will cause oracle to take much longer.
[quote:488aa33fb1=“Chris Wilson-Marquis”]Have you created the index hints with the index names in your hint universe?
[/quote]
No, I just tried a few of the simple ones, all rows, first rows - that kind of thing
I’ve never rolled them out to our end users
No, it was very much a case of look at query exection times in Data Manager. If we take this forward we’ll have to benchmark it properly - and we’ll definitely compare the different explain plans generated.
Please forgive me but I think I’m missing a major point about using hints in BusinessObjects.
I don’t see how I could anticapate what combinations of columns, tables or joins are going to be built from the objects selected by the user in the Query Panel. I would have to hope that the hint is always faster/better then what the optimizer figures out from the user’s query and the table statistics provided by ANALYZE statement. It seems like an impossible situation, let alone an incredible amount of intuition.
I could understand using hints on canned queries or reports that users can not modify but not on reports that could be modified or custom built by a user.
From my understanding, hints would be used in canned SQL that is impossible for a user to change like in Java, Oracle Forms, C, or Oracle Reports. Sorry, but am I missing something fundamental about the use of hints and or the use of hints in BusinessObjects? It doesn’t sound like a good idea to use them in BusinessObjects.
I think you make a fair point that hints are better suited to canned reports. However, if you have users who are prepared to write a report then use hint1 and see how it runs compared to how it runs using hint2…what do you lose? I must admit out of my user community of 150plus users I wouldn’t let more than half a dozen loose with hints and adhoc reports. But then maybe if I was a better trainer…
I have found that, at times, Oracle either choices not to use an index or choices an index that I do not think is the optimal choice. I am not sure but I think that (depending on the amount of data the user is going to pull) “Hinting” to Oracle to use an index in the worse case will break even and in the best case should improve performance. My thought is that if you “Hint” to Oracle to use an index and the user pulls back every record in the table then you have gained nothing (ok you potentially might lose a little performance) but if they make a reasonable request then the index could help. The question then comes up as to if Oracle would have figured it out on its own or if it needed our help
This is not elegant but it will put the hint in the resulting generated SQL.
(BOB member since 2002-06-06)
(BOB member since 2002-08-15)
This might not be worth mentioning but I think that the reason the ‘’ are necessary is not because of the way Oracle parses the string (although Oracle will be the reporter of the bad news 
) but because of the way that Business Objects builds the SQL string. If we put an object into the query panel Business Objects can only interpret the object as a select field. That is why I said originally that this is not an elegant solution. If Business Objects could interpret the contents of the object as a comment/hint or if there was an object type of comment and/or hint then the SQL builder would understand that we did not pass a select variable and would not put the , after it. I do think that this is a very subtle difference but in one interpretation we get mad at Oracle 
and in the other we get mad at Business Objects 
