Impact of a view on Query Explain Plan

Database
Enthusiast

Impact of a view on Query Explain Plan

Hi,

 

This might be a strange observation (at least to me, based on my learning) for Teradata software. 

 

I have a code (with few jions and filters) that when I run against table (one particular table) it gives me a different explain plan as compare to when I run this against the view. Strangely enough the explain plan when going through view is more optimal than going through tables. 

 

The only change I see is, I am referring the tables 4 times in the code, ofcourse when referring the table directly it does not have "access lock" but when accessed through view it has access lock. But does that should change the query plan? Any help will be much appreciated. 

 

Also I am using aliases for each table/view reference in my code. Does it makes any difference in terms of table read by the DBMS when referred as table/view with an alias? 

 

Please note that there is no join any filter in the view - all columns are being selected in the view as well. The view definition is something like below; 

 

REPLACE VIEW PARTY_LOCATOR
(
a,

b,

c

)AS
LOCKING ROW FOR ACCESS
SELECT
a,

b,

c
FROM WHD01_EDW_STATED.PARTY_LOCATOR;

 

PS: TD v 16.20 on INFX. 

2 REPLIES 2
Teradata Employee

Re: Impact of a view on Query Explain Plan

Apart from locking, the execution plan for both select view and select table should be same.

 

# Table

SELECT
a,

b,

c
FROM WHD01_EDW_STATED.PARTY_LOCATOR;

 

# View

SELECT
a,

b,

c
FROM PARTY_LOCATOR_v;

 

Please share explain plan for the both queries to review. If possible, open incident with TSET pack file for select view query.

 

Teradata Employee

Re: Impact of a view on Query Explain Plan

Consider that in the parsing phase there are many steps, some of them about query re-writing processing in order to better optimize the access path to data.

Thus, some differences on plans using views insted of direct access to table can exists.

If you have real trouble/issues on you explain and you think is kinda wrong, please open an incident as previously indicated with all the details you can collect.

 

Have a nice day