All Forums Database
20 Oct 2015
View on View problem

We are using Views rather much. Data flows from tables trough several Views before they are used.
To simplify:
I have the following construct (the actual table and views are much more complicated, and there are a lot of data in the table):

CREATE MULTISET TABLE T1 (Val INT) PRIMARY INDEX (Val);
CREATE VIEW V1 AS SELECT Val FROM T1;
CREATE VIEW V2 AS SELECT Val FROM V1;
SELECT ---
  FROM TABLE
  JOIN V2
  JOIN TABLE
  ---

When I use the V2 view in a rather complicated Query, with a lot of joins it runs very slow: in the actual case it runs for nearly 4 minutes.

SELECT ---
  FROM TABLE
  JOIN V1
  JOIN TABLE
  ---

When I then substitute the V2 with the V1 the same complicated Query runs in only 10 to 15 seconds.
When I make an explain with both V2 and V1, the V1 explain are much smaller than the explained produced when using the V2.
The Views do nothing to the data, only pass it trough.
Why is using the Version V2 making my Queries run much slower than when using the V1 view?
We are on Teradata 14.10
 
Peter Schwennesen

19 Nov 2015

Now we hade another example her in the produktion, where it looks like it is the same problem. A joined Index was used when looking trough 1 view but not when looking trough 2.

You must sign in to leave a comment.