) 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_cd FROM toutput WHERE field1_cd = 'code1' AND mese_cd = 200803 SAMPLE 1
This 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_cd FROM toutput WHERE field1_cd = 'code1' AND mese_cd = (SELECT min(mese_cd) FROM temporary_volatile_table) SAMPLE 1
but it oirginates a full table scan on the toutput table (partitioning is not used).
Also a simple test:
SELECT mese_cd FROM toutput WHERE field1_cd = 'code1' AND mese_cd = (SELECT 200803) SAMPLE 1
produces 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.
Re: Query select..where..in on a partitioned table: partitions are not used
If 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.