Hi all, to an already existing report they want meto add a new condition. To verify one column department which should be one of the four departments. I used “department In List ‘A,B,C,D’”. After adding this condition it is taking like 4 to 5 minutes to analyse. Same report use to run like in lesser than 20 secs. Please suggest me if there is any other
alternative.
P.S. in the universe the employee and department table have a complex join which compares the substring of the dept id in both the tables.
Can you get the SQL that the old report generated, and compare it to the new SQL. I’d suggest that you get the help of your DBA or someone knowledgeable in database performance to help you analyze what’s happening.
What I would suspect is that the old query was able to make fact access to retrieve the proper database rows using a reporting index.
…And that the addition of your additional WHERE clause is causing the query optimizer to have to choose a different strategy, that is not able to make use of the index.
Anita is right
Have you include the optimizer as objects in the Universe?.
It improves a lot in the performance.
Better to use a Rule based optimizer
Be sure to include the optimizer as the first object in the query
Rule optimizer defined as long text and as dimension.
/+ Rule/ ‘Optimizer’
Cost optimizer defined as long text and as dimension object.
/+ Choose/ ‘Optimizer’
Have you include the optimizer as objects in the Universe?
I don’t know what you are talking about. I like to know more about it.
Please provide me with any link or something where I can find some.
I really appreciate it.
The Cost based optimizer.
It basically defines how the indexes r bein used.
In order to improve the query performance you can include this optimizers in you Universe.
To do this just try running Explain Plan against the query you will know how the optimizers r being used.
In my previous post I explained how you can define them in the universe.
Just include that code in the select statement of the object.
Rule optimizer defined as long text and as dimension.
/*+ Rule*/ 'Optimizer'
Cost optimizer defined as long text and as dimension object.
/*+ Choose*/ 'Optimizer'
(BOB member since 2002-06-17)
(BOB member since 2004-07-22)