Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 22 Jan 2004 @ 00:45:02 GMT


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


Subj:   Re: Misunderstanding of join index
 
From:   Victor Sokovin

  Let's explain my JI.  


  The table contains 500 000 000 rows for 8 years.  


  I try to do this small JI:  


CREATE JOIN INDEX DB.T1_JI AS
SELECT FIELD_1,
             FIELD_2,
             SUM(COST) (NAMED AGR_COST)
FROM DB.T1
WHERE PA_DAT > '2002-12-31'
GROUP BY 1,2
PRIMARY INDEX (FIELD_2);

Following Carrie's suggestion, I'd like to add a few remarks on the definition of the PI itself. Please note that this is a single-table join, which is a special case and some additional restrictions apply.

I quote from the "SQL Ref vol.4" on the search conditions in PI with my comments between the quotes:

1. "Data types for any columns used in a join condition must be drawn from the same domain because neither explicit nor implicit data type conversions are permitted."


In your condition there is an implicit conversion to DATE, which we see back in the explain plan.

2. "You can only specify a join condition using an inequality operator if you specify multiple join conditions and use the AND logical operator to connect the inequality join condition or conditions with an equality join condition"


It looks like this rule is violated.

3. "Do not specify a WHERE clause for a single-table join index."


Oops ...

All in all, it looks like the definition of the JI needs to be revisited before it can be considered by the optimizer.


Regards,

Victor



     
  <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: 27 Dec 2016