Can someone plz refer/recommend any document on query merging? I am working on a database sever. The response time, of view's query has become a challange to me. I have tried everything, the last hope left is query merging.
But I didnt find any docs/papers/books on it.
Are there those,
In this world of brave,
Who can tell me,
How should I behave,
When I am disgraced.
I'm not quite sure what you mean. Are you refering to the option _COMPLEX_VIEW_MERGING
This parameter is related to improving the SQL performance on complex views (including inline views). Oracle tries to merge the query criteria with the existing view criteria that would result in a faster single query. For example, if a view is created with a GROUP BY clause in it and a query is executed on the view having a where clause, Oracle tries to merge the two and create a single query that would run the where clause prior to grouping it, thus giving better performance. Default value is FALSE in Oracle 8i and TRUE in Oracle 9i.
If you are, then if you are running Oracle 9i or above, it is already being used.
You do not need a parachute to skydive. You only need a parachute to skydive twice.
Moving to 9i from 8i I had a similar problem with a number of complex views which retrieved large amounts of data. To fix the issue I did a number of things which may or may not also help you.
1. Look at the execution plan and find out what is wrong with it. If its something obvious like not using an appropriate index then see if hints in the query or view will help, you may need the NOMERGE hint to stop it merging the query and view and thus ignoring the hint.
2. Check you tables/indexes are analyzed. Remember there is more than one way to analyze and you may find a more detailed analysis helps you i.e. for all indexed columns size <n>.
3. In 9i you may need the NOMERGE hint to help your query.
4. If it still refuses to use a particular index you may need to change the optimizer parameters like dbfile_multiblock_read_count (sp?) or optimzer_index_cost_adj etc.
5. You may find the view is unnecessarily complex and simplifying it may help you without changing the actual data returned.
6. Maybe you need to add more indexes which will be more selective.
7. Look at stored outlines to make it execute in the way you require.
8. When selecting from the view use a more selective query, it may mean you have to execute multiple queries but it may run more efficiently despite this.
9. Always check on your production system as well as your testing environment.
10. In Oracle there is always one more thing you can try to improve performance
Let me clarify myself. Bill I was asking about the concept of Merging the queries. The same that COMPLEX_QUERY_MERGING does, but how? Simple condition clauses can be merged in each other, but how to merge the two queries (assume that the query is level 2 query ), which are having Group By clause or/and order by clause or/and having clause.