Dear Friends,

How can we better tune this query.
ci_custmast (table size 6GB,datarows locking scheme)
in this query one subquery is used which is using datarows locking
scheme,
ubquery returns 4,38,000 rows out of 4,50,000 rows )


select convert(char(28),a.cod_cust_id) CUSTID,
a.nam_cust_shrt NAMECUSTSHRT,
a.txt_cust_prefix PREFIX,
convert(char(8),a.dat_birth_cust,112) BIRTHDATE,
substring(a.txt_custadr_add1,1,35) ADD1,
substring(a.txt_custadr_add2,1,35) ADD2,
substring(a.txt_custadr_add3,1,20) ADD3,
substring(a.nam_custadr_city,1,25) CITY,
substring(a.txt_custadr_zip,1,10) ZIP,
substring(a.ref_cust_phone,1,20) RESIPHONE,
substring(a.ref_cust_phone_off,1,20) OFFPHONE,
substring(a.ref_cust_fax,1,20) FAX,
substring(a.nam_cust_spouse,1,20) SPOUSENAME from
ci_custmast a
where
convert(char(8),a.dat_addr_change,112)= '20040108'
--convert(char,dateadd(dd,-8,getdate()),112) and
convert(char,getdate(),112)
and
convert(char(8),a.dat_cust_open,112) < '20040108'
and a.cod_cust_id in (select cod_cust_id from ci_direct_banking
where flg_mnt_status='A' and flg_tb_new_req='Y')
and a.ctr_updat_srlno > 1
and a.flg_mnt_status = 'A'
go

Kindly suggest creating indexes on which table will be best or how can
we reduce the overhead of a query