Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Why is Join Index feature so restricted!
 
From:   Dieter Noeth

Narayan Murthy wrote:

  Today I was trying to create join indexes which were fairly simple - as seen below - but none of them got created - I don't understand why - why so many restrictions - Any answers!  


     > 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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019