| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Implicit Passing of "Parameters" to Views
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)
 |
 |