Re: Views

From: Mahesh Vallampati <m0v5533_at_tam2000.tamu.edu>
Date: 24 Mar 1995 19:40:52 GMT
Message-ID: <3kv784$ora_at_news.tamu.edu>

In article <3kv1j8$ml_at_news.ais.net>,
Dodd M. Vernon <dodd.vernon_at_aquila.com> wrote:
>When I execute the following query using this view, it takes approx. 2.5
>seconds to run:
> select * from view201
> where object_id = 8200
> or object_id = 8214;
>
>However, when I execute the following query, it takes almost 6 seconds to run:
>
> select * from object_master
> where atts_tbl = 201
> and (object_id = 8200
> or object_id = 8214);
The Oracle SQL executes query parsing from right to left. In the second query which is slow, your query executed (object_id=8200 or object_id=8214) first and then executed atts_tbl=201 next. This is why the query was slower. The OR condition has to work on a bigger data set. Whereas in the view the atts_tbl=20 got executed first and then the OR condition was applied thus reducing the data set on which Or has to be applied.
To summarise:
Your first query was executed as

		select * from object_master
		where (object_id = 8200
		or object_id = 8214) and atts_tbl=201;
Your second query was:
		select * from object_master
		where atts_tbl = 201
		and (object_id = 8200
		or object_id = 8214);
	From a ssystem that execute from right to left, obviously the first

query will be faster.
Hope this helps.

---

Thanks
Mahesh Vallampati

Home:				   Office:
401, Stasney Street, #224	|  642, Texas A & M Headquarters Building
College Station, Texas 77840	|  College  Station, Texas 77840
PH : 409 846 3794 (H)		|  409 862 1070 (O)
e-mail address  : vallampa_at_ee.tamu.edu
WWW 		: http://tam2000.tamu.edu/~m0v5533/
Lab 		: 409 847 8609

M.S. In EE
Unix System Administrator and Oracle DBA for TexasAMP Research in Distributed Systems Lab on Multi-Threaded Systems. // In the Beginning there was Codd .... Received on Fri Mar 24 1995 - 20:40:52 CET