Faster Search query with dynamic where columns on oracle db

Question

I have a table(ResponseData) with columns RESPONSE_ID,RESPONSEDATA,KEY1,KEY2,KEY3,KEY4,VALUE1,VALUE2,VALUE3,VALUE4 user can insert data any of below category.

• 1,"my response one","name",null,null,null,"Apple",null,null,null
• 2, "my response two","name","age",null,null,"Apple","22",null,null

Later in different page when user request for Responsedata with name="Apple",age="32" should return record 1 because it has matching property name if user request with name="Apple" age="22" should return record 1 and 2 because it matches with record 1 by name and record 2 by name and age.

how can we form search query in this scenario. I was try with utl_match.jaro_winkler_similarity as below utl_match.jaro_winkler_similarity(upper(VALUE1|VALUE2|VALUE3|VALUE4),(USERINPUTREQUEST)) by fetching the top matching records in all existing records.but it is giving delayed response on firing query with more number records on table. Appreciated for your inputs.

Answer 1

declare
  cursor c(k1 varchar2,
           k2 varchar2,
           k3 varchar2,
           k4 varchar2,
           v1 varchar2,
           v2 varchar2,
           v3 varchar2,
           v4 varchar2)
   is(select *
        from (select ResponseData.*,
                     case
                       when    (k1 = key1 and v1 <> value1)
                            or (k1 = key2 and v1 <> value2)
                            or (k1 = key3 and v1 <> value3)
                            or (k1 = key4 and v1 <> value4)
                            or (k2 = key1 and v2 <> value1)
                            or (k2 = key2 and v2 <> value2)
                            or (k2 = key3 and v2 <> value3)
                            or (k2 = key4 and v2 <> value4)
                            or (k3 = key1 and v3 <> value1)
                            or (k3 = key2 and v3 <> value2)
                            or (k3 = key3 and v3 <> value3)
                            or (k3 = key4 and v3 <> value4)
                            or (k4 = key1 and v4 <> value1)
                            or (k4 = key2 and v4 <> value2)
                            or (k4 = key3 and v4 <> value3)
                            or (k4 = key4 and v4 <> value4)
                       then 'not match'
                       else 'match'
                     end ok
                from ResponseData)
       where ok = 'match'
          or ok = 'not match');  -- debug mode
begin
  for r in c('name', null, null, 'age', 'Apple', null, null, '22') loop
    dbms_output.put_line(r.id||': ('||r.key1||'=>'||r.value1||'), ('||r.key2||'=>'||r.value2||'), ('
                                    ||r.key3||'=>'||r.value3||'), ('||r.key4||'=>'||r.value4||') - '||r.ok);
  end loop;
end;