Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 18 Jul 2002 @ 16:21:53 GMT


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


Subj:   Re: The Optimizer and Multi Inlists
 
From:   Dieter N�th

  Why not try building your list of values in a derived table and then joining the derived table to the table with the multi-part PI. I tried the following on a table (vedcuser04) which has two columns (col01 and col03) defined as the PI. My requiredment is to find rows where col01 in ('david','fred') and col04 = 1000102.  


  I coded it as:  


   sel *
   from vedcuser04
   where (col01,col03)
   in (select 'david' (char(30) ,character set latin ),1000102 (date)
        from sys_calendar.calendar
        where  calendar_date=date
        union all
       select 'fred' (char(30) ,character set latin ),1000102 (date)
        from sys_calendar.calendar
        where  calendar_date=date);

And if you don't want to access a real table [ maybe an Oracle-style dual ;-) ] just use derived tables:

in (select 'david' (char(30) ,character set latin ),1000102 (date) from
(select 1 as x) tmp
     union all
    select 'fred' (char(30) ,character set latin ),1000102 (date) from
(select 1 as x) tmp
    )

  7) We do an all-AMPs JOIN step from VEDB4NODE.vedcuser04 by way of an all-rows scan with no residual conditions, which is joined to Spool 4 (Last Use). VEDB4NODE.vedcuser04 and Spool 4 are joined using an inclusion merge join, with a join condition of ("(VEDB4NODE.vedcuser04.COL01 =) AND (VEDB4NODE.vedcuser04.COL03 =)").  


It's still a table lock/all-AMPs merge join instead of several UPI-accesses :-( But better than a FTS...


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