View on View problem

Database
Enthusiast

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

Tags (2)
1 REPLY
Enthusiast

Re: View on View problem

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.