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

[bhooma]

query:

SELECT
COUNT(*)
FROM
dwdbo.FNL_DW_COM_ACCT_D a
WHERE
a.ACCT_REPORT_DT = '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_acct_nbr, '888') > 0
OR INSTR(a.psr_id, '888') > 0
OR INSTR(a.pms_id, '888') > 0
OR INSTR(a.camra_id, '888') > 0
OR INSTR(a.reporting_id, '888') > 0
OR INSTR(a.regent_id, '888')> 0
OR INSTR(a.epace_acct_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.