Re: Efficient Views..

In article <jdennisCqupqn.2K0_at_netcom.com> jdennis_at_netcom.com (John Dennis) writes:
>
>Hello,
>
>I'm creating a view between two tables A and B. Is there overhead to
>defining this view with *all* the columns in A and B compared to *just*
>the columns the people accessing it will need. The join is straight
>forward (i.e. A.x = B.x).
>
>The reason for the question is that, currently, I don't really (nor they
>me thinks) know what columns from A and B they need -- only that the join
>is necessary because some columns will be used from both tables.
>

If the A.x = B.x JOIN is the main/only one that links these two tables, and performance is a consideration, have you considered CLUSTERing them. This glues the two tables together, and they share ONE copy of the x column. Supposedly perfomance benefits. I've never done proper tests on it, can any one else verify all this ?

One other word of warning, creating a VIEW using a SELECT * FROM ... has all sorts of possible problems if the underlying table changes, since the effective value of '*' changes.

[Query optimisation is done when ?]

There is (or at least was in Sun OS Oracle 6.0.32) a facility to find out how the RDBMS was going to process a query. You had to create a special table and then do a query on it. I can't remember exactly how, and I can't find it now in the 7 or so manuals I've just looked thorugh, but I don't think I dreamed it.

>
>Thanks for any input ...
>
I just hope it generates soem ouptu.

-- 
			David McCann

Computer Science Yr3                   Faculty Rep: All Comp.Sci Undergrads
Students Union Court Member (elect)    Board of Studies Rep: Comp.Sci 3
Tel: 0956 237670 (any time)            Student Council Rep: Comp.Sci