Optimise this SQL?

[evild]

I am in the midst of major cleanouts of my database - using a total of 12 queries to get rid of specific data ensuring accurate copies are made.

I have the following query that I want to improve so that they run faster. At the moment the following query takes 40 minutes to run. Ideally I would like to cut this down.

SELECT *
FROM CCC50.Z303

where z303_field_3 like '%EXPIRED%'

AND substr(z303_field_3,1,8) < to_char(sysdate-30,'YYYYMMDD')

AND NOT rtrim(z303_rec_key) IN
(select rtrim(z36_id) from CCC50.z36)

AND NOT rtrim(z303_rec_key) IN
(select rtrim(substr(z31_rec_key,1,12))
from CCC50.z31
where z31_status = 'O')

group by rtrim(substr(z31_rec_key,1,12))
having (sum(decode(z31_credit_debit,'C',z31_sum,0)) - sum(decode(z31_credit_debit,'D',z31_sum,0))) < -500)

AND NOT NVL(substr(z303_field_1,1,2),'0') IN ('AT', '04', '05', '07')
AND NOT NVL(substr(z303_field_2,1,2),'0') IN ('AT', '04', '05', '07')
;

Thsi query is fine up until the sub queries are used, specifically:
AND NOT rtrim(z303_rec_key) IN
(select rtrim(substr(z31_rec_key,1,12))
from CCC50.z31
where z31_status = 'O')

the z31 table has 16 fields and currently there are 167873 records of this type.
the z303 table has 30 fields and currently there are 22301 records of this type.
Now when I do sub queries am I not correct in saying that this creates the catesian product of these two tables....which is huge!
Is there any way I can optimise the sub-query so that it does not take so long?

 

[swampBoogie]

What does this have to do with ANSI SQL?

It is better to post this in a forum dedicated to the DBMS that you are using. You should also describe the tables together with which keys and indexes that you have defined.