Re: [PERFORM] Q on views and performance

[Dean Gibson (DB Administrator)]

On 2008-02-23 08:21, Kynn Jones wrote:
...
    3. Why not write:

    CREATE VIEW txt AS
      SELECT a1.word AS word1, a1.type AS type1, a2.word AS word2,
    a2.type AS type2
        FROM T a1 [LEFT] JOIN T a2 USING( zipk );  -- Use "LEFT" if
    appropriate
    SELECT word1, word1
      FROM S JOIN txt ON word = word1
      WHERE type1 = <int1> AND type2 = <int2>;


This is would indeed produce the same results as Q1, but this approach 
would require defining about 10,000 views, one for each possible pair 
of int1 and int2

Why 10,000 views???  What's wrong with the ONE view above?  You DON'T 
want to be defining VIEWs based on actual tables VALUES;  leave that to 
the SELECT.  For that matter, what's wrong with the final SELECT I 
listed (below)?

SELECT a1.word AS word1, a2.word AS word2
 FROM S JOIN T a1 USING( word )
   LEFT JOIN T a2 USING( zipk )
 WHERE a1.type = <int1> AND a2.type = <int2>;

-- Dean

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.