New to query tuning - question on subquery

Hi group,

I am new to query tuning activities and here is my question. I have a certain complex query, which takes so much time when given a condition in the where clause. I dont know why is this behaviour since I did not have time to dig into the query (created by someone else. This is my new job) since it was a matter of urgency. To my surprise the query performs well when the condition is taken off. So I tried the following

SELECT VALUE1, VALUE2.... FROM
(...query...)
WHERE VALUE1 = VALUE2; The where clause which was previously inside the query was taked out. But this even fails to complete. What I thought was Oracle first would execute the subquery and then do the filter. Is this because Oracle rearranges the query altogether ? I use 8i with CBO enabled. All tables involved are analyzed.

One more question - will hints given inside a view be ignored by the optimizer if another hint is given in the main query using this view. If so how do I override this behavior ?

Thanks in advance.

Pramod R Received on Wed Jun 02 2004 - 09:16:12 CDT