Re: Views , unions and performance
pablof@my-dejanews.com wrote:
>
> In article <19981112080904.25504.00000570@ng147.aol.com>,
> satriguy@aol.com (SaTriGuy) wrote:
[Lots of stuff SNIPPED]
> Hi Madison, Thank you for your answer. I want to remark a few things. The
> two tables involved in query do not have any row in common reason why the
> results are unique respect this index. On the other hand, I cannot create the
> view after to make the join, because this view is a user requirement. Now, in
> operation database, the view is really a unique table. After a study, we saw
> that users access frequently a part of the table, and the performance is very
> bad. So, we think to make a fragmentation in two tables, but whithout
> modifying the programs. The best result of all this is a view, and before
> this operation we're studying pros and cons. Then, we detected this problem.
> We think that the optimizer must be able to reach the same solution that you
> propose by its own means. Thank you.
No Pablo, Madison is correct. The engine does not know whether there
will be any rows returned from one part of a UNION that is identical to
some rows returned from another part of the UNION so the optimizer will
ALWAYS create a temp table containing the combined rows unless you were
to include the ALL option which will supress duplicate removal since
you know that there will be no dups. In this case the engine does not
care what order it returns the data in and so it will not sort at all
without an ORDER BY clause in the UNION ALL. So try making the VIEW
defined as:
CREATE VIEW vista (c1,c2,...)AS
SELECT * FROM t1
UNION ALL
SELECT * FROM t2;
That ought to do it.
Art S. Kagel