Optimizing brio query with Oracle 8.1.6

[kira123]

Hi,
I have a brio query which takes a long time to retrieve records. I modified the query by using a different optimizer in Oracle but cannot implement it in Brio as I do not know how to change the request line in the Custom sql.
I have to add select /*+ rule */ ename..... from..
to the query. But Brio only allows us to modify the where clause in the custom sql.

Also, any idea how to speed up the retrieval process.
Ana

 

[mpcohen]

This is a problem that I have also run into. One thing that you can try is to create a view in Oracle with the desired query and then just reference the view in BRIO. Sometimes this works.

If this does not work, another approach is to have a stored procedure insert data into some other table and then have BRIO run the stored procedure and process a query off of the report table.

You have to be real careful with this second approach if you use this method and there is a possibility of two people running the stored procedure at the same time. One approach is to add a user id to the report table and provide the stored procedure with a user id parameter. Come up with some way of assigning a user id, pass it to the stored procedure and then run the query using the user id.

 

[largepizza]

I assume this will work in Brio 6.5, as it has worked in all previous versions I have used:

Add a computed item as the first field on the request line. I call the field Hint for documentation purposes.

The definintion of the item should be, e.g.
/*+ Ordered */1

You need to include the 1 at the end. It will generate a column that has a value of 1, which you can hide, but it also comes in handy sometimes when counting things. Oracle will recognize the Hint text and include it in the query. You can confirm this by looking at the Custom SQL.