performance is poor in this query where this table has got millions of records

[bhooma]

query:

SELECT
COUNT(*)
FROM
dwdbo.FNL_DW_CO M_ACCT_D a
WHERE
a.ACCT_REPORT_D T = '30/jun/2011' --v_report_dt
AND
(INSTR(TO_CHAR( a.ods_acct_id), '888') > 0 --v_search_attr
OR INSTR(a.acct_nm , '888') > 0
OR INSTR(a.client_ id, '888') > 0
OR INSTR(a.apace_a cct_nbr, '888') > 0
OR INSTR(a.psr_id, '888') > 0
OR INSTR(a.pms_id, '888') > 0
OR INSTR(a.camra_i d, '888') > 0
OR INSTR(a.reporti ng_id, '888') > 0
OR INSTR(a.regent_ id, '888')> 0
OR INSTR(a.epace_a cct_nbr, '888')> 0 )

[Rabbit]

Make sure you have the appropriate indexes. Also, using like might produce a quicker result. It may even be quicker to split out the individual where conditions and use a union.

[bhooma]

Like clause gave me the same result as Instr. i have 2 indexs on this table ie .ods_acct_id anf report_dt where its been used first in where clause.

can u pls give some some solution by rewriting this query

[Rabbit]

You're using more fields in your where clause than you have indexes on. Also, there's no way to optimize a query without knowing the query plan and the environment in which it is run.