Re: Speed considerations with joins in views

I have had a similar problem when using views. I have used 2 ways of solving it and can think of a third way of solving it.

1. Instead of using the syntax 'CREATE VIEW AS SELECT ... etc', try doing a 'CREATE TABLE AS SELECT ...'. This will create a table which can then be indexed on fields you are particularly interested in. When you select from this temporary table, performance will be much better. You will need to regularly drop and re-create the table (nightly?) to re-populate the data (dont forget to add the indexes again).
2. I have used Business Objects (a third party tool) to use as a query tool. This needs to do a similar thing to a view, except that it builds the select statement dynamically dependant on the data required.
3. Create a temporary table as in option 1. However, instead of re-populating the table as in 1 by dropping and re-creating, use database triggers to populate the data as it is created.

In article <3mucq5$m0j_at_access1.digex.net>, cobrien_at_access1.digex.net says...
>
>In article <3ls4vo$5oi_at_news.fmso.navy.mil>,
> <James_F_Davis_at_NSLC.FMSO.NAVY.MIL> wrote:
>>We have a number of tables which contain codes representing other data.
 To
>>make it easy on users, we want to make a view which adds the
 descriptions of
>>these codes. It will outer-join a table to lookup tables containing
 the descriptions.
>>If a user accesses a view, but does not select a field from a joined
 table, does
>>Oracle still perform the join? Does this hurt performance?
Received on Tue Apr 18 1995 - 00:00:00 CEST