Re: Views
Date: Tue, 28 Mar 1995 01:01:32 GMT
Message-ID: <Kenneth.D.Atkins.13.2F775FEC_at_tek.com>
I think that Oracle stores a 'Parsed' version of the view, so when you use the view, the SQL statement does not have to be parsed. If the Shared SQL area is working correctly, the non-view query should run as fast as the view query after the first time. You could also use tkprof to see how much of the 2.5 seconds is 'Parse' time.
This is just a guess, maybe someone who really knows the answer could reply.
Kenneth Atkins
Semi-Independent Oracle Consultant
Kenneth.D.Atkins_at_tek.com
In article <3kv1j8$ml_at_news.ais.net> dodd.vernon_at_aquila.com (Dodd M. Vernon)
writes:>From: dodd.vernon_at_aquila.com (Dodd M. Vernon)
>Subject: Views
>Date: 24 Mar 1995 18:04:24 GMT
>I have a question regarding the use of views in Oracle 7. The way I understand
>them, a view does not actually store any data. The data dictionary simply
>stores an SQL statement which defines the view. When a table is then accessed
>using this view, Oracle combines this stored SQL statement with the calling
>SQL and executes it.
>This would imply that executing a query using a view should take approx. the
>same amount of time as executing the equivalent query without the view.
>However, I have the following situation:
>I have a table which contains 200,000 rows called object_master, with three
>columns of data: object_id, atts_tbl, rec_cdate.
>I have defined a view called "view201" on object_master, which selects about
>5600 rows from object_master: "Select * from object_master where atts_tbl = 201"
>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);
>Both queries return 2 rows from the object_master table. It seems to me that
>they are essentially the same query, except for the use of the view. If anyone
>can tell me why the one executed using the view consistently runs more than
>twice as fast as the other, I would be grateful.
>Please reply either here or send email to dodd.vernon_at_aquila.com
Received on Tue Mar 28 1995 - 03:01:32 CEST