Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 05 Aug 2014 @ 20:44:22 GMT


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


Subj:   Secondary Index usage (with Sub-select)
 
From:   Anomy Anom

<-- Anonymously Posted: Tuesday, August 05, 2014 09:30 -->

We have a source table indexed on a date and are experiencing some difficulty in 'encouraging' the parser to use the Secondary Index when a sub-select is used.

The sub-select will always produce a very short list of dates, but somehow the parser is unable to recognize that and does not use the index. The source table contains about 250 million records and the dates provided represents less than 1% of the records on the table. The sub-select below is just done for illustration purposes/simplicity, in reality our sub-select looks a bit different, but always produces only a max of 4 days.

--Uses Secondary Index

     SEL * FROM MyDb.MyIndexedTable
     WHERE MyStartDate IN ('2014-08-05','2014-08-04','2014-08-03');

--Doesnt use Secondary Index

     SEL * FROM MyDb.MyIndexedTable
     WHERE MyStartDate IN (SELECT CALENDAR_DATE FROM SYS_CALENDAR.CALENDAR SC
                           WHERE CALENDAR_DATE BETWEEN DATE - 2 AND DATE );

Any idea how I could reconstruct the sub-select in a way that will still enable the Index use?


Thanks!



     
  <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