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
– Mick Mnemonic, Commented Dec 4, 2016 at 22:19

Konstantin Sorokin · Accepted Answer · 2016-12-07 14:33:38Z

0

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;

edited Dec 7, 2016 at 14:33

answered Dec 5, 2016 at 13:50

Konstantin Sorokin

4154 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

snofty Over a year ago

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.

Konstantin Sorokin Over a year ago

@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

snofty Over a year ago

Not both the records, its 2nd record... Yes user doesn't know about the number of column..

Konstantin Sorokin Over a year ago

@snofty changed my answer

Collectives™ on Stack Overflow

Faster Search query with dynamic where columns on oracle db

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related