Archives of the TeradataForum
Message Posted: Tue, 05 Aug 2014 @ 20:44:22 GMT
<-- 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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|