Re: Indexing View Columns?
From: NewUser2008 <mcseven_at_gmail.com>
Date: Thu, 17 Mar 2011 05:41:10 -0700 (PDT)
Message-ID: <bbbc8798-f077-43bf-8ff4-ab48b6b7de2f_at_j13g2000yqj.googlegroups.com>
On 15 Mrz., 22:25, joel garry <joel-ga..._at_home.com> wrote:
> Well, you can upgrade. http://blogs.oracle.com/optimizer/2011/02/optimizer_transformations_j...
Ah, thank you very much for your input, however, even 11g will not solve my difficulties since they do not result from slow singluar queries. All the UNION ALL contents from above are in fact supported by appropriate indices; what makes the query slow is that the view will always be executed completely; if there are 2000 small, but fast queries being executed the whole result becomes slow anyway. So, I'd need a way to tell the DBMS only to execute the appropriate UNION ALL line(s) which by design is not possible because the required line number will first be available _after_ the execution of the statement.
Date: Thu, 17 Mar 2011 05:41:10 -0700 (PDT)
Message-ID: <bbbc8798-f077-43bf-8ff4-ab48b6b7de2f_at_j13g2000yqj.googlegroups.com>
On 15 Mrz., 22:25, joel garry <joel-ga..._at_home.com> wrote:
> Well, you can upgrade. http://blogs.oracle.com/optimizer/2011/02/optimizer_transformations_j...
Ah, thank you very much for your input, however, even 11g will not solve my difficulties since they do not result from slow singluar queries. All the UNION ALL contents from above are in fact supported by appropriate indices; what makes the query slow is that the view will always be executed completely; if there are 2000 small, but fast queries being executed the whole result becomes slow anyway. So, I'd need a way to tell the DBMS only to execute the appropriate UNION ALL line(s) which by design is not possible because the required line number will first be available _after_ the execution of the statement.
I thought that the explain plan somehow would give a hint on which part shall be executed, but apparently it does not. So unless there is a way to feed parts of a "WHERE" clause into a function parameter set automatically (maybe only via highly specialized triggers) I fear I cannot solve my dilemma...
Thanks again, NewUser2k8 Received on Thu Mar 17 2011 - 07:41:10 CDT