OPTIMIZE USING 'outlinename'

[Iain75]

Afternoon

Using BO 5.1.7 on an Oracle DB

We are wanting to add an 'optimize using' statement onto the end of a query without having to restrict BO from recreating the SQL

eg

OPTIMIZE USING 'outlinename'

Obviously, we cannot create this as an object and use it in the conditions as it will require an "and" or an "or" - which is not syntactically correct.

Any ideas gratefully received.

 

[blom0344]

You can define optimizing hints for Oracle at universe level. (or add them to the .SBO file)
However, these are hints used directly after the SELECT clause. (like /*+FIRST_ROWS*/)

Are you trying to address an existing outline in the database?

If not, then perhaps you should look for an alternative from the list of regular hints:

check:

http://www.psoug.org/reference/hints.html

(But you are perhaps working with a very old Oracle version)

Ties Blom

 

[Iain75]

Ties

The Database is actually an Oracle RDB Database rather than a true Oracle DB

The Outline has been created within the DB and we're trying to reference this with an OPTIMIZE USING statement at the end of the SQL.

Cheers

 

[blom0344]

Just a thought. What happens if you create a dummy object that just holds the 'OPTIMIZE USING .... ' phrase and add this as the last object in query panel?

Ties Blom

 

[skrandel]

I dont' think this is possible in V5. In 6 on up, there is an End SQL parameter which may do it.

You might be able to add the End SQL to the appropriate PRM or SBO file, but I'm not sure.

Steve Krandel
Intuit

 

[Iain75]

Thanks to all for your help.

I know we can't implement Ties suggestion - we gave that a go before submitting the original post.

Just another tick in our column in trying to get our paymasters to upgrade!!!

 

[Iain75]

Just one last question, in relation SKandrel's post - does anyone know if in version 6, this a single parameter for the pc it's running on or is it a variable for each report?

 

[blom0344]

Not really certain, Steve will know for sure. My guess is that it is a universe property. Regular hints for Oracle have always been situated in Connection Parameters (under the Custom tab of the connection properties).

If the same goes for End sql, then it should neither apply to a PC or report, but more general for universe/connection.

Ties Blom

 

[skrandel]

In V6 it's a universe parameter.

Steve Krandel
Intuit