Explain plans differ when object (view) names are fully qualified and when they are not

Database
Teradata Employee

Explain plans differ when object (view) names are fully qualified and when they are not

Does the optimizer treat a query differently when the object names in the query are fully qualified vs. when they are not fully qualified ?

I came across a query which gives one explain plan when the views referenced by the query are fully qualified i.e. <databasename>.<viewname>

and it generates an entirely different explain when the views are not fully qualified i.e., just the <viewname> with default database set to the desired <databasename>

Also, the query performance is much better with fully qualified names.

This does not look normal to me. I mean, the objects are same, the join conditions are same.

Can someone explain the reason behind this behaviour ?

4 REPLIES
Enthusiast

Re: Explain plans differ when object (view) names are fully qualified and when they are not

It will be great if you provide view defintion

Enthusiast

Re: Explain plans differ when object (view) names are fully qualified and when they are not

My mistake, I meant query which gives different plan, if possible

Re: Explain plans differ when object (view) names are fully qualified and when they are not

I've just seen this exact same issue. Peformance is better with the full qualified version of the SQL.

Re: Explain plans differ when object (view) names are fully qualified and when they are not

And the connection method is irrelevant...same behavior whether connecting via ODBC or Teradata. It tough to diagnose a fix for an issue that cannot be recreated because of what looks like a Teradata bug.