All Forums Database
amit.s 8 posts Joined 03/10
01 Nov 2012
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 ?

Harpreet Singh 101 posts Joined 10/11
02 Nov 2012

It will be great if you provide view defintion

Harpreet Singh 101 posts Joined 10/11
02 Nov 2012

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

KevinLynch 2 posts Joined 07/10
12 Dec 2012

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

Kevin Lynch

KevinLynch 2 posts Joined 07/10
12 Dec 2012

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.

Kevin Lynch

You must sign in to leave a comment.