Home » Server Options » Text & interMedia » Matching query (oracle 11g)
Matching query [message #664240] Mon, 10 July 2017 00:31 Go to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Table:
-----
Create table test(id number,name varchar2(250));
insert into test(1,'rajesh kumar reddy');
insert into test(2,'rajesh kumar mudimela');

question:I need a query which will give both records if i pass the string as "rajesh mudimela reddy".
if any minimum two exact words is matching then needs to give the result

my input parameter is "rajesh mudimela reddy"
output should be: 1,rajesh kumar reddy
2,rajesh kumar mudimela

[Updated on: Mon, 10 July 2017 00:32]

Report message to a moderator

Re: Matching query [message #664242 is a reply to message #664240] Mon, 10 July 2017 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is wrong to store multiple values in a single column.
Read Normalization.

Re: Matching query [message #664243 is a reply to message #664242] Mon, 10 July 2017 01:24 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
this was given by my business team..they need the data like that only.
Re: Matching query [message #664244 is a reply to message #664243] Mon, 10 July 2017 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This does not change the fact it is wrong and a source of problems like your current one.
If you normalize your data, there is no more problem and performances will be far better.

Wait for Barbara to help you with this problem as she did with your previous one (she is our expert in this domain).
I notice you did not feedback and thank her for the time and help she gave you.

[Updated on: Mon, 10 July 2017 01:29]

Report message to a moderator

Re: Matching query [message #664245 is a reply to message #664244] Mon, 10 July 2017 01:40 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Hi Michel,

I am extremely Sorry for not giving feedback/not saying thanks to Barbara for earlier issue.

Regards,
Rajesh.
Re: Matching query [message #664249 is a reply to message #664240] Mon, 10 July 2017 06:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> Create table test(id number,name varchar2(250))
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2  into test values(1,'rajesh kumar reddy')
  3  into test values(2,'rajesh kumar mudimela')
  4  into test values(3,'rajesh')
  5  into test values(4,'mudimela')
  6  into test values(5,'reddy')
  7  into test values(6,'rajesh mudimela reddy')
  8  into test values(7,'somebody else')
  9  select * from dual
 10  /

7 rows created.

SCOTT@orcl_12.1.0.2.0> create index test_name_idx on test (name) indextype is ctxsys.context
  2  /

Index created.

SCOTT@orcl_12.1.0.2.0> variable the_string varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :the_string := 'rajesh mudimela reddy'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> column name format a45
SCOTT@orcl_12.1.0.2.0> select matching_names, id, name
  2  from   (select sum (score(1)/100) as matching_names, test.id, test.name
  3  	     from   test,
  4  		    (select  regexp_substr (:the_string, '[^ ]+', 1, rownum) single_name
  5  		     from    dual
  6  		     connect by level <= regexp_count (:the_string || ' ', ' ')) names
  7  	     where  contains (test.name, 'definescore(' || names.single_name || ', discrete)', 1) > 0
  8  	     group  by test.id, test.name)
  9  where  matching_names >= 2
 10  order  by matching_names desc
 11  /

MATCHING_NAMES         ID NAME
-------------- ---------- ---------------------------------------------
             3          6 rajesh mudimela reddy
             2          1 rajesh kumar reddy
             2          2 rajesh kumar mudimela

3 rows selected.

Re: Matching query [message #664250 is a reply to message #664249] Mon, 10 July 2017 06:17 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara,

I will check and let you know the result.

Regards,
Rajesh.
Re: Matching query [message #664251 is a reply to message #664250] Mon, 10 July 2017 06:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The following is a bit simpler.

SCOTT@orcl_12.1.0.2.0> Create table test(id number,name varchar2(250))
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2  into test values(1,'rajesh kumar reddy')
  3  into test values(2,'rajesh kumar mudimela')
  4  into test values(3,'rajesh')
  5  into test values(4,'mudimela')
  6  into test values(5,'reddy')
  7  into test values(6,'rajesh mudimela reddy')
  8  into test values(7,'somebody else')
  9  select * from dual
 10  /

7 rows created.

SCOTT@orcl_12.1.0.2.0> create index test_name_idx on test (name) indextype is ctxsys.context
  2  /

Index created.

SCOTT@orcl_12.1.0.2.0> variable the_string varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :the_string := 'rajesh mudimela reddy'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> column name format a45
SCOTT@orcl_12.1.0.2.0> select count(*) as matching_names, test.id, test.name
  2  from   test,
  3  	    (select  regexp_substr (:the_string, '[^ ]+', 1, rownum) single_name
  4  	     from    dual
  5  	     connect by level <= regexp_count (:the_string || ' ', ' ')) names
  6  where  contains (test.name, names.single_name, 1) > 0
  7  group  by test.id, test.name
  8  having  count(*) >= 2
  9  order  by matching_names desc
 10  /

MATCHING_NAMES         ID NAME
-------------- ---------- ---------------------------------------------
             3          6 rajesh mudimela reddy
             2          1 rajesh kumar reddy
             2          2 rajesh kumar mudimela

3 rows selected.
Re: Matching query [message #664252 is a reply to message #664251] Mon, 10 July 2017 06:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
This could also be done without Oracle Text and contains, using instr instead. This would be fine for small amounts of data, but the text index and contains should be faster on large tables.

SCOTT@orcl_12.1.0.2.0> Create table test(id number,name varchar2(250))
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert all
  2  into test values(1,'rajesh kumar reddy')
  3  into test values(2,'rajesh kumar mudimela')
  4  into test values(3,'rajesh')
  5  into test values(4,'mudimela')
  6  into test values(5,'reddy')
  7  into test values(6,'rajesh mudimela reddy')
  8  into test values(7,'somebody else')
  9  select * from dual
 10  /

7 rows created.

SCOTT@orcl_12.1.0.2.0> variable the_string varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :the_string := 'rajesh mudimela reddy'

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> column name format a45
SCOTT@orcl_12.1.0.2.0> select count(*) as matching_names, test.id, test.name
  2  from   test,
  3  	    (select  ' ' || regexp_substr (:the_string, '[^ ]+', 1, rownum) || ' ' single_name
  4  	     from    dual
  5  	     connect by level <= regexp_count (:the_string || ' ', ' ')) names
  6  where  instr (' ' || test.name || ' ', names.single_name, 1) > 0
  7  group  by test.id, test.name
  8  having  count(*) >= 2
  9  order  by matching_names desc
 10  /

MATCHING_NAMES         ID NAME
-------------- ---------- ---------------------------------------------
             3          6 rajesh mudimela reddy
             2          1 rajesh kumar reddy
             2          2 rajesh kumar mudimela

3 rows selected.
Re: Matching query [message #664256 is a reply to message #664249] Mon, 10 July 2017 07:01 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara,

It is working fine for me.Is there any impact if I have Huge data(1 Billion records in table)??

Regards,
Rajesh.
Re: Matching query [message #664260 is a reply to message #664256] Mon, 10 July 2017 08:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
mvrkr44 wrote on Mon, 10 July 2017 05:01
Thanks Barbara,

It is working fine for me.Is there any impact if I have Huge data(1 Billion records in table)??

Regards,
Rajesh.
yes, obtaining row data ALWAYS has impact since you can not get something for nothing.
Re: Matching query [message #664265 is a reply to message #664256] Mon, 10 July 2017 11:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
mvrkr44 wrote on Mon, 10 July 2017 05:01
Thanks Barbara,

It is working fine for me.Is there any impact if I have Huge data(1 Billion records in table)??

Regards,
Rajesh.

Which of the 3 methods that I posted did you use? In general, with lots of records, any method that uses an index should be faster than any method that does not. When in doubt, run a few tests and compare.
Re: Matching query [message #664280 is a reply to message #664265] Mon, 10 July 2017 23:41 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks Barbara,

I used the below query,I will check today with huge sample data and let you know the performance

select count(*) as matching_names, test.id, test.name
from test,
(select ' ' || regexp_substr (:the_string, '[^ ]+', 1, rownum) || ' ' single_name
from dual
connect by level <= regexp_count (:the_string || ' ', ' ')) names
where instr (' ' || test.name || ' ', names.single_name, 1) > 0
group by test.id, test.name
having count(*) >= 2
order by matching_names desc
Re: Matching query [message #664282 is a reply to message #664280] Tue, 11 July 2017 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Don't you think Barbara's posts are easier and nicer to read?
Once more, Please read How to use [code] tags and make your code easier to read.

Re: Matching query [message #664286 is a reply to message #664282] Tue, 11 July 2017 01:34 Go to previous messageGo to next message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Sorry Michel.
I will format my code next time before replying.

Regards,
Rajesh.
Re: Matching query [message #664580 is a reply to message #664282] Mon, 24 July 2017 00:06 Go to previous message
mvrkr44
Messages: 132
Registered: December 2012
Senior Member
Thanks To All.

Regards,
Rajesh
Previous Topic: Full Text Search & ifilter for .docx
Next Topic: where to check context index sync status
Goto Forum:
  


Current Time: Tue Mar 19 02:58:16 CDT 2024