Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Implicit Passing of "Parameters" to Views

Re: Implicit Passing of "Parameters" to Views

From: Jurij Modic <jmodic_at_src.si>
Date: Mon, 30 Aug 1999 21:27:55 GMT
Message-ID: <37d2f714.27517841@news.siol.net>


On Mon, 30 Aug 1999 15:00:01 -0400, "Tim Theis" <ttheis_at_dytn.veridian.com> wrote:

>I have a view set up like...
>
>Create View View1 as
>Select A.field1,A.field2,sum(A.field3)
>From
> Table1 A
>
>I now want to issue a select statement on this view similar to the
>following...
>
>Select * from View1 where field2 = 'PARAMETER'
>
>and have the underlying view1 know to restrict the query to only those
>records where field2='Parameter'. All of my experimentation seems to prove
>that Oracle creates a temporary table of all possible values first, and then
>restricts the rows in the temporary table to those meeting the condition.
>Since this is a very large table, it is very inefficient.
>
>Any help would be appreciated. I am using Oracle 7.3.

Oracle may choose to perform the query one way or another. It may full-scan the Table1, sort the rows and then return only the rows where field2 = 'PARAMETER'. Or, if appropriate index is available, it may range-scan the index for the rows with field2 = 'PARAMETER' and perform sort only on those rows to return grouped result. Or even if the index is available, it may choose that table scan is more efficient and it might still go with the first method.

In your case, either there is no index with the leading column being FIELD2 or the cost based optimizer chooses full table scan despite the index. If the latter is true and you feel that FIELD2 is restrictive enough that the index range scan would be more efficient, either re-analyze the table and the index (with COMPUTE, if possible) or use a hint to force the index usage.

>Tim Theis
>ttheis_at_dytn.veridian.com

HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)



The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Aug 30 1999 - 16:27:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US