Archives of the TeradataForum

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

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

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