Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 01 Feb 2005 @ 11:01:54 GMT


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


Subj:   Re: Any limits on IN LIST ?
 
From:   Victor Sokovin

  In oracle we do have a max limit of 1000 values in IN LIST clause (sel * from table where col in (values))...Is there any limit on Teradata too?  



The answer depends on the release. In V2R4, queries start failing when the total size of the IN LIST values approaches something like 1MB. I quote the limit from memory. It might depend on some system settings on your as well. A typical error message would indicate that the maximum number of plastic steps has been exceeded.

In V2R5, queries with long value lists are being rewritten by the optimizer. They don't fail but the execution plan might change significantly, and the queries might get slower.

There is a recent thread in the archives where somebody from NCR advised that in future releases a temporary table will be created on the fly and the query will be internally rewritten to have an extra join to it. Unfortunately, I cannot find the thread via Google. You might have more luck by browsing the thread titles over the last 3-4 months.


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: 15 Jun 2023