|
|
|
Forum Newbie
Group: Forum Members
Last Login: 4/3/2008 1:14:35 PM
Posts: 1,
Visits: 9
|
|
Hi 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_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.
Any help will be apreciated :)
...
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 5/4/2008 3:57:27 AM
Posts: 5,
Visits: 43
|
|
To 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.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 7:26:06 AM
Posts: 474,
Visits: 202
|
|
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.
Dieter
|
|
|
|