InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comWed, 03 Dec 2008 20:11:26 GMT20http://www.teradata.com/teradataforum/Topic11131-1-1.aspxIf you want to check if data exists, then better use a EXISTS instead of SAMPLE 1, this should always be sub-second instead of 15 seconds:SELECT 'exists'WHERE exists ( SELECT * FROM toutput WHERE field1_cd = 'code1' AND mese_cd = 200803 )The same for the volatile table:SELECT 'exists'WHERE EXISTS (SELECT * FROM toutput, vt WHERE vt.mese_cd = toutput.mese_cd AND toutput.field1_cd = 'code1')You should check explain for "enhanced by dynamic partition elimination" and "exit this join step after the first row is found" If you really need to return the mese_cd, just replace "SELECT 'exists' WHERE ..." with "SELECT mese_cd FROM vt WHERE ...", but don't remove vt from the subquery.DieterSat, 05 Apr 2008 08:33:19 GMTdnoethhttp://www.teradata.com/teradataforum/Topic11131-1-1.aspxTo eliminate partition, you must put constant constraint on partition column.when you use derived table, it will be treated as PPI and NPPI join without partitioning elimination.Is your purpose to limit the mese_cd dynamiclly?try it with macro parameters.Sat, 05 Apr 2008 05:36:57 GMTJianhttp://www.teradata.com/teradataforum/Topic11131-1-1.aspxHi all,I've a big problem with a "simple" select.I've the following table:ct toutput(mese_cd integer,field1_cd varchar(10), )UNIQUE PRIMARY INDEX XPKDT_OUTPUT ( MESE_CD , field1_cd , )PARTITION BY RANGE_N(MESE_CD BETWEEN 200401 AND 200412 EACH 1 ,200501 AND 200512 EACH 1 ,200601 AND 200612 EACH 1 ,200701 AND 200712 EACH 1 ,200801 AND 200812 EACH 1 );mese_cd is a field containing YYYYMM. The table contains about 500,000,000 recs.I need to verify if in that table exists data for a specific mese_cd.The query I need is something like:SELECT mese_cdFROM toutputWHERE field1_cd = 'code1'AND mese_cd = 200803SAMPLE 1This query uses partitions and is very fast (about 15 secs, 1/2 if the partition is empty).My problem is that the value 200803 is contained into a volatile table. I tried to modify it in this way:SELECT mese_cdFROM toutputWHERE field1_cd = 'code1'AND mese_cd = (SELECT min(mese_cd) FROM temporary_volatile_table)SAMPLE 1but it oirginates a full table scan on the toutput table (partitioning is not used).Also a simple test:SELECT mese_cdFROM toutputWHERE field1_cd = 'code1'AND mese_cd = (SELECT 200803)SAMPLE 1produces a full table scan..I tried also to create a working table partitioned like the toutput table, containing only the field mese_cd and only a row. I put these two tables in join but partitions are not used yet.Is there any easy suggestion to solve my problem? I've only to check if on the condition filed1_cd there are data available in a specific mese_cd.Any help will be apreciated :)Thu, 03 Apr 2008 07:16:23 GMTAlethesnake