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.

Could you split your current table into two -- a parent table containing the first two columns and a child table that would contain the key-value pairs? It could simplify the query quite a bit (not needing to guess which of the 4 column pairs is holding the data). — Mick Mnemonic, Dec 04 '16 at 22:19

Konstantin Sorokin · Answer 1 · 2016-12-07T14:33:38.210

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;

that sound good, but wat if the user input key="name",value="Orange" and key="age", value="22", will this return 2 record of my example. — snofty, Dec 06 '16 at 04:18
@snofty why this should return both records? Requested name=Orange and age=22 has nothing in common with record 1 of your example. My solution relies on assumption that user knows number of column where requested key-value pair is stored. If it's wrong assumption you should search through every column pair for every key-value pair requested by user (16 predicates instead of 4). Or better consider Mick Mnemonic suggestion to use [EAV](https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) — Konstantin Sorokin, Dec 06 '16 at 14:50
Not both the records, its 2nd record... Yes user doesn't know about the number of column.. — snofty, Dec 06 '16 at 18:31

Faster Search query with dynamic where columns on oracle db

1 Answers1