Views

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;

		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 Fri Mar 24 1995 - 19:04:24 CET