Archives of the TeradataForum

Message Posted: Tue, 05 Apr 2005 @ 15:40:53 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Narayan Murthy wrote:

     > create join index j12 as
     >  sel t1.a,t2.a from t1, t2;
     >  *** Failure 5464 Error in Join Index DDL, One of the tables
     >  does not partic ipate in any join or the join type is not
     >  allowed.

A Join Index is usefull only for large tables, so why do you want to CROSS JOIN those tables?

     > create join index j12 as
     >  sel t1.a,t2.a from t1, t2 where t1.a=t2.a;
     >  *** Failure 5464 Error in Join Index DDL, Ambiguous name
     >  specified for a join index.

A Join Index is similar to a View, you can't create a view with a duplicate column name, too.

     > create join index j12_n as
     >  sel t1.a as col1,t2.a as col2 from t1 , t2 where col1>col2;
     >  *** Failure 5464 Error in Join Index DDL, Only satisfiable
     >  conditions that have constant and/or inequality conditions
     >  anded to at least one equality join between columns from
     >  different tables which are of the same type are allowed in
     >  the WHERE clause.

Similar to #1, inequality join conditions result in a CROSS JOIN.

     > create join index j12_n as
     >  sel t1.a as col1,t2.a as col2 from t1 right outer join t2 on col1=2;
     >  *** Failure 5464 Error in Join Index DDL, All selected columns
     >  of an inner table are nullable.

At least on column of the inner table must be NOT NULL to be able to distinguish if a row has been NULLed because of the outer join. Otherwise the Join Index might not be used for an Inner join.

So IMHO your examples are no real restriction...

On the other hand there are real restrictions if you compare Join Indexes to Oracle's Materialized Views :-)

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>