Re: Views and performance

Kylian Wrzesinski (Kylian_Wrzesinski_at_csg.mot.com) wrote:

: I have a two part question dealing with views:
 

: 1) If I have a report that repeatedly deletes and recreates views, am I
: going to cause fragmentation or other "bad" things to happen to my the
: system tablespace that will eventually degrade overall performance?

a single view creation takes up some space which amounts to a few rows in the base data dictionary tables. Unless you are creating and dropping thousands of views, i don't see any fragmentation. there may be a little perf degradation in accessing base data dictionary, but i would count that as so negligible as to be nearly non-existant.

: 2) If a do a join/subselect with a view, as opposed to the base table,
: will I see a performance increase? Does oracle create a temporary table
: during the query, and drop it when it finishes? (It seems to behave this
: way, but I am not certain). I would think that this could yield a
: performance increase on joins with non-indexed tables; you'd only have to
: do a single full table scan, as opposed to nested loops of the fulle
: table. (Doing a nested loop of the view that only contains relavant data
: would be fine, as every piece of data is what I want) I realize that when
: a view is created, only the text of the query is stored by oracle, I'm
: only curious about it's behaviour for the duration of a single select
: statement execution.

oracle creates no temporary table when accessing a view, hence you are not going to see any perf increase in the use of a view. the view construct is meant for data partitioning/slicing of base table data...not perf improvement.

Johnny Chan
Independent Oracle Specialist Received on Wed May 24 1995 - 00:00:00 CEST