-1

i have a column that is varchar2 (2000 bytes), this is an existing column and is being used in many places, the issue is when i am trying to compare with this column the query is taking 30-40 seconds to execute. the query is as simple as below-

select response_id from t_response where desc_response = to_char(1000);

desc_response is that column.

i found that we have only one index as of now on this table and that is on response_id column.

APC
  • 144,005
  • 19
  • 170
  • 281
Dip
  • 1
  • 1

1 Answers1

2

"we have only one index as of now on this table and that is on response_id column."

So your query is executing a full table scan, reading every record in the table to inspect the value of desc_response.

You neglected to say how many rows the table has but this is simple physics. Tables with lots of rows take longer to search. So on the basis of 30-40 seconds, I guess this is a big table.

Build an index on desc_response and the execution time will drop. By how much depends on all sorts of factors.

APC
  • 144,005
  • 19
  • 170
  • 281