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>>

Attachments

Library

Quick Reference