Re: Select performance against a view

From: Christian Bantzer <bantzerc_at_orca.akctr.noaa.gov>
Date: 1998/04/02
Message-ID: <Pine.SGI.3.95.980402083349.2492A-100000@orca.akctr.noaa.gov>#1/1

If id is a unique key, the two queries are qualitatively different as viewed by the optimizer, id= 1 return single row

                         id in (1,2) return multiple rows

The two have a different optimization values, which might lead the optimizer to coose a different execution plan.

you can check the execution plan with the explain statement, and force the optimizer to use the appropriate index with a hint, if necessary.

---

Christian Bantzer                                   PSMFC-PacFIN F/AKC
christian_bantzer_at_psmfc.org                         Bldg. 4 Rm. 2066   
phone (206) 526 4057                                7600 SandPoint Wy. NE
fax   (206) 526 4074                                Seattle, WA 98115-0070

On Wed, 1 Apr 1998, Neal Nachtigall wrote:

:)Can someone explain why we are encountering such a difference in response
:)from a select statement against a view.
:)
:)If we do a simple select:
:)
:) select id from big_nasty_view
:) where id = 1;
:)
:) it returns quickly, but if will include the values
:)
:) select id from big_nasty_view
:) where id = 1 or id = 2;
:)
:) it takes forever, even if we try
:)
:) select id from big_nasty_view
:) where id in (1,2);
:)
:)It appears that all of the fields that are referenced in the where clause of
:)the view are indexed(and valid) on their respective tables.
:)
:)Anyone have any ideas.
:)
:)Thanks,
:)Neal
:)
:)
:)
:)
Received on Thu Apr 02 1998 - 00:00:00 CST