Querry running very slow due to NOT IN clause.
Please help to run this querry faster.
SELECT 'ALTER TABLE '
|| table_owner
|| '.'
|| table_name
|| ' DROP SUBPARTITION '
|| subpartition_name
|| ';' drop_code
--, TO_NUMBER(NVL(SUBSTR(SUBPARTITION_NAME, 12 , 4), 9999),0) BATCH_GID
,
SUBSTR (subpartition_name, 3, 8) PARTITION,
SUBSTR (subpartition_name, 12, 4) batch_id, table_owner,
tablespace_name
FROM all_tab_subpartitions sp
WHERE table_owner IN ('SCHEMA1', 'SCHEMA2')
AND SUBSTR (subpartition_name, 12, 1) <> '0'
AND high_value_length IN (3, 4, 5)
AND DECODE (TRANSLATE (SUBSTR (subpartition_name, 12, 4),
'012345678',
'999999999'
),
'9999', TO_NUMBER (SUBSTR (subpartition_name, 12, 4)),
-1
) NOT IN (470,480,716,666,713,715,652,476,741,620,557,585,667,645,499,646,722,731,668,724,305,
728,654,637,498,570,584,669,566,583,504,590,595,727,505,670,725,653,555,591,648,624,
647,596,520,730,513,593,643,644,714,638,671,639,729,565,598,672,723,599,600,390,466,
413,673,393,394,395,402,396,400,397,398,399,732,726,661,601,717,579,602,606,721,635,
627,634,628,626,542)
ORDER BY 4, 2, 3;
thanks
Edited by: 482 on Dec 4, 2009 10:54 AM