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 ?
I've just seen this exact same issue. Peformance is better with the full qualified version of the SQL.
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.