Home » SQL & PL/SQL » SQL & PL/SQL » need two token matching query (oracle 11.2.0.4)
| need two token matching query [message #667094] |
Thu, 07 December 2017 01:50  |
 |
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
create table test_names(id number,firstname varchar2(100),secondname varchar2(100),thirdname varchar2(100),fourthname varchar2(100),fifthname varchar2(100));
insert into test_names values (1,'rajesh','kumar',null,null,null);
insert into test_names values (2,'rajesh',null,'rajesh',null,null);
insert into test_names values (3,'rajesh kumar',null,'rajesh',null,null);
insert into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy');
commit;
QUESTION:
My input name is 'rajesh kumar sachin'
Now i need to matchatleast two strings in the names columns
ouput :1,3,4 records i need to get
How can i achive this in sql query
Regards,
rajesh
|
|
|
|
| Re: need two token matching query [message #667104 is a reply to message #667094] |
Thu, 07 December 2017 08:12  |
 |
quirks
Messages: 82
Registered: October 2014
|
Member |
|
|
I deeply believe that there must be a better solution. But as long as no one comes around with a better one you could try this one:WITH
TEST_NAMES(ID, FIRSTNAME, SECONDNAME, THIRDNAME, FOURTHNAME
,FIFTHNAME)
AS
(SELECT 1, 'rajesh', 'kumar', NULL, NULL, NULL
FROM DUAL
UNION ALL
SELECT 2, 'rajesh', NULL, 'rajesh', NULL, NULL
FROM DUAL
UNION ALL
SELECT 3, 'rajesh kumar', NULL, 'rajesh', NULL, NULL
FROM DUAL
UNION ALL
SELECT 4, 'rajesh', NULL, 'rajesh kumar', NULL, 'reddy'
FROM DUAL),
PREPARE_NAMES
AS
(SELECT DISTINCT ID, TRIM(REGEXP_SUBSTR(FIRSTNAME || ' ' || SECONDNAME || ' ' || THIRDNAME || ' ' || FOURTHNAME || ' ' || FIFTHNAME, '[^ ]+', 1, LEVEL)) AS NAMES
FROM TEST_NAMES
CONNECT BY REGEXP_SUBSTR(FIRSTNAME || ' ' || SECONDNAME || ' ' || THIRDNAME || ' ' || FOURTHNAME || ' ' || FIFTHNAME
,'[^ ]+', 1, LEVEL)
IS NOT NULL
AND PRIOR ID = ID
AND PRIOR SYS_GUID() IS NOT NULL)
SELECT ID
FROM PREPARE_NAMES
WHERE 'rajesh kumar sachin' LIKE '%' || PREPARE_NAMES.NAMES || '%'
GROUP BY ID
HAVING COUNT(*) > 1
ORDER BY ID; |
|
|
|
| Re: need two token matching query [message #667105 is a reply to message #667094] |
Thu, 07 December 2017 11:18 |
Solomon Yakobson
Messages: 3252
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Bad design. Normalize table layout and search string too. Anyway:
with t as (
select id,
(
select count(distinct column_value)
from table(sys.OdciVarchar2List(firstname,secondname,thirdname,fourthname,fifthname))
where instr(' ' || '&search_tring' || ' ',' ' || column_value || ' ') > 0
) cnt
from test_names
)
select id
from t
where cnt > 1
/
Enter value for search_tring: rajesh kumar sachin
old 6: where instr(' ' || '&search_tring' || ' ',' ' || column_value || ' ') > 0
new 6: where instr(' ' || 'rajesh kumar sachin' || ' ',' ' || column_value || ' ') > 0
ID
----------
1
3
4
SQL>
SY.
|
|
|
|
| Re: need two token matching query [message #667106 is a reply to message #667094] |
Thu, 07 December 2017 15:33 |
 |
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names(id number,firstname varchar2(100),secondname varchar2(100),thirdname varchar2(100),fourthname varchar2(100),fifthname varchar2(100));
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,'rajesh','kumar',null,null,null)
3 into test_names values (2,'rajesh',null,'rajesh',null,null)
4 into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
5 into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
6 select * from dual
7 /
4 rows created.
-- Oracle Text multi column datastore and context index:
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'firstname, secondname, thirdname, fourthname, fifthname');
4 end;
5 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> alter table test_names add (allnames varchar2(1))
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (allnames)
2 indextype is ctxsys.context
3 parameters ('datastore test_mcds')
4 /
Index created.
-- inputname and query:
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar sachin'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains
4 (allnames,
5 'definemerge(((definescore('
6 || replace (:inputname, ' ', ',discrete/100)),(definescore(')
7 || ',discrete/100))),or,add)', 1) >= 2
8 /
ID
----------
1
3
4
3 rows selected.
|
|
|
|
|
|
| Re: need two token matching query [message #667115 is a reply to message #667110] |
Fri, 08 December 2017 01:55 |
|
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
mvrkr44 wrote on Thu, 07 December 2017 22:21Thanks Barbara.
With out Context Index I need to write the sql query.Could you please help in this scenario..
Regards,
Rajesh.
Then I would suggest something like what Solomon suggested:
-- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names(id number,firstname varchar2(100),secondname varchar2(100),thirdname varchar2(100),fourthname varchar2(100),fifthname varchar2(100));
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,'rajesh','kumar',null,null,null)
3 into test_names values (2,'rajesh',null,'rajesh',null,null)
4 into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
5 into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
6 select * from dual
7 /
4 rows created.
-- inputname and query:
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar sachin'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names,
3 table (sys.odcivarchar2list (firstname, secondname, thirdname, fourthname, fifthname))
4 where instr (' ' || :inputname || ' ', ' ' || column_value || ' ') > 0
5 group by id
6 having count (distinct column_value) >= 2
7 /
ID
----------
1
4
3
3 rows selected.
|
|
|
|
| Re: need two token matching query [message #667137 is a reply to message #667115] |
Sat, 09 December 2017 23:49 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Thanks Barbara.
Small change in the table structure.
create table test_names(id number,id2 number,id3 number,firstname varchar2(100),secondname varchar2(100),thirdname varchar2(100),fourthname varchar2(100),fifthname varchar2(100));
ALTER TABLE test_names
ADD CONSTRAINT pk_test_names PRIMARY KEY (id, id2,id3);
insert into test_names values (1,1,1'rajesh','kumar',null,null,null);
insert into test_names values (1,1,2'rajesh','reddy',null,null,null);
insert into test_names values (1,1,3'rajesh','reddy',null,null,null);
insert into test_names values (1,2,1'rajesh',null,'rajesh',null,null);
insert into test_names values (1,2,2'rajesh kumar',null,'rajesh',null,null);
insert into test_names values (1,3,1'rajesh',null,'rajesh kumar',null,'reddy');
insert into test_names values (1,4,1'rajesh',null,'rajesh kumar',null,'reddy');
insert into test_names values (1,5,1'rajesh',null,'rajesh kumar',null,'reddy');
insert into test_names values (1,1,4'rajesh',null,'rajesh',null,null);
insert into test_names values (1,1,5'rajesh',null,'sachin',null,null);
Input :'rajesh kumar sachin'
Out put expecting:id id2 id3
1 1 1
1 2 2
1 3 1
1 4 1
1 5 1
1 1 5
|
|
|
|
|
|
| Re: need two token matching query [message #667141 is a reply to message #667139] |
Sun, 10 December 2017 06:00 |
|
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Then I would suggest something more like what mvrkr44 suggested.
-- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 firstname varchar2(100),
6 secondname varchar2(100),
7 thirdname varchar2(100),
8 fourthname varchar2(100),
9 fifthname varchar2(100))
10 /
Table created.
SCOTT@orcl_12.1.0.2.0> ALTER TABLE test_names ADD CONSTRAINT pk_test_names PRIMARY KEY (id, id2,id3)
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh','kumar',null,null,null)
3 into test_names values (1,1,2,'rajesh','reddy',null,null,null)
4 into test_names values (1,1,3,'rajesh','reddy',null,null,null)
5 into test_names values (1,2,1,'rajesh',null,'rajesh',null,null)
6 into test_names values (1,2,2,'rajesh kumar',null,'rajesh',null,null)
7 into test_names values (1,3,1,'rajesh',null,'rajesh kumar',null,'reddy')
8 into test_names values (1,4,1,'rajesh',null,'rajesh kumar',null,'reddy')
9 into test_names values (1,5,1,'rajesh',null,'rajesh kumar',null,'reddy')
10 into test_names values (1,1,4,'rajesh',null,'rajesh',null,null)
11 into test_names values (1,1,5,'rajesh',null,'sachin',null,null)
12 select * from dual
13 /
10 rows created.
-- inputname and query:
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar sachin'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from (select distinct id, id2, id3,
3 trim
4 (regexp_substr
5 (firstname || ' ' || secondname || ' ' || thirdname || ' ' || fourthname || ' ' || fifthname,
6 '[^ ]+', 1, level)) as names
7 from test_names
8 connect by regexp_substr (firstname || ' ' || secondname || ' ' || thirdname || ' ' || fourthname || ' ' || fifthname,
9 '[^ ]+', 1, level) is not null
10 and prior id = id
11 and prior sys_guid() is not null)
12 where instr (' ' || :inputname || ' ', ' ' || names || ' ') > 0
13 group by id, id2, id3
14 having count(*) >= 2
15 order by id, id2, id3
16 /
ID ID2 ID3
---------- ---------- ----------
1 1 1
1 1 5
1 2 2
1 3 1
1 4 1
1 5 1
6 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'kumar sachin rajesh'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
ID ID2 ID3
---------- ---------- ----------
1 1 1
1 1 5
1 2 2
1 3 1
1 4 1
1 5 1
6 rows selected.
|
|
|
|
|
|
| Re: need two token matching query [message #667143 is a reply to message #667139] |
Sun, 10 December 2017 10:34 |
Solomon Yakobson
Messages: 3252
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
This is no different from original task:
with t as (
select id,
id2,
id3,
(
select count(distinct column_value)
from table(sys.OdciVarchar2List(firstname,secondname,thirdname,fourthname,fifthname))
where instr(' ' || '&search_tring' || ' ',' ' || column_value || ' ') > 0
) cnt
from test_names
)
select id,
id2,
id3
from t
where cnt > 1
/
Enter value for search_tring: rajesh kumar sachin
old 8: where instr(' ' || '&search_tring' || ' ',' ' || column_value || ' ') > 0
new 8: where instr(' ' || 'rajesh kumar sachin' || ' ',' ' || column_value || ' ') > 0
ID ID2 ID3
---------- ---------- ----------
1 1 1
1 2 2
1 3 1
1 4 1
1 5 1
1 1 5
6 rows selected.
SQL>
SY.
|
|
|
|
| Re: need two token matching query [message #667144 is a reply to message #667143] |
Sun, 10 December 2017 13:49 |
|
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Solomon Yakobson wrote on Sun, 10 December 2017 08:34This is no different from original task:...
Yes, it is different. The original poster said that he wants the same results for input of 'rajesh kumar sachin' and input of 'kumar sachin rejesh'. You only ran your query for the first input. The following run of your query with the second input produces different results.
SCOTT@orcl_12.1.0.2.0> -- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 firstname varchar2(100),
6 secondname varchar2(100),
7 thirdname varchar2(100),
8 fourthname varchar2(100),
9 fifthname varchar2(100))
10 /
Table created.
SCOTT@orcl_12.1.0.2.0> ALTER TABLE test_names ADD CONSTRAINT pk_test_names PRIMARY KEY (id, id2,id3)
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh','kumar',null,null,null)
3 into test_names values (1,1,2,'rajesh','reddy',null,null,null)
4 into test_names values (1,1,3,'rajesh','reddy',null,null,null)
5 into test_names values (1,2,1,'rajesh',null,'rajesh',null,null)
6 into test_names values (1,2,2,'rajesh kumar',null,'rajesh',null,null)
7 into test_names values (1,3,1,'rajesh',null,'rajesh kumar',null,'reddy')
8 into test_names values (1,4,1,'rajesh',null,'rajesh kumar',null,'reddy')
9 into test_names values (1,5,1,'rajesh',null,'rajesh kumar',null,'reddy')
10 into test_names values (1,1,4,'rajesh',null,'rajesh',null,null)
11 into test_names values (1,1,5,'rajesh',null,'sachin',null,null)
12 select * from dual
13 /
10 rows created.
SCOTT@orcl_12.1.0.2.0> with t as (
2 select id,
3 id2,
4 id3,
5 (
6 select count(distinct column_value)
7 from table(sys.OdciVarchar2List(firstname,secondname,thirdname,fourthname,fifthname))
8 where instr(' ' || '&search_tring' || ' ',' ' || column_value || ' ') > 0
9 ) cnt
10 from test_names
11 )
12 select id,
13 id2,
14 id3
15 from t
16 where cnt > 1
17 /
Enter value for search_tring: kumar sachin rajesh
old 8: where instr(' ' || '&search_tring' || ' ',' ' || column_value || ' ') > 0
new 8: where instr(' ' || 'kumar sachin rajesh' || ' ',' ' || column_value || ' ') > 0
ID ID2 ID3
---------- ---------- ----------
1 1 1
1 1 5
2 rows selected.
|
|
|
|
|
|
| Re: need two token matching query [message #667159 is a reply to message #667151] |
Mon, 11 December 2017 21:11 |
|
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Please try the following instead. Also, if you do not need the order by clause, then you can eliminate that.
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names,
3 (select regexp_substr (:inputname, '[^ ]+', 1, rownum) names
4 from dual
5 connect by level <= regexp_count (:inputname, ' +') + 1)
6 where instr
7 (' '||firstname||' '||secondname||' '||thirdname||' '||fourthname||' '||fifthname||' ',
8 ' ' || names || ' ') > 0
9 group by id, id2, id3
10 having count (distinct names) >= 2
11 order by id, id2, id3
12 /
ID ID2 ID3
---------- ---------- ----------
1 1 1
1 1 5
1 2 2
1 3 1
1 4 1
1 5 1
6 rows selected.
|
|
|
|
| Re: need two token matching query [message #667293 is a reply to message #667159] |
Wed, 20 December 2017 00:27 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Thanks Barbara Boehmer .
Now little bit requirement changed.
SCOTT@orcl_12.1.0.2.0> -- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 firstname varchar2(100),
6 secondname varchar2(100),
7 thirdname varchar2(100),
8 fourthname varchar2(100),
9 fifthname varchar2(100))
10 /
Table created.
SCOTT@orcl_12.1.0.2.0> ALTER TABLE test_names ADD CONSTRAINT pk_test_names PRIMARY KEY (id, id2,id3)
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh','kumar',null,null,null)
3 into test_names values (1,1,2,'rajesh','reddy',null,null,null)
4 into test_names values (1,1,3,'rajesh','reddy',null,null,null)
5 into test_names values (1,2,1,'rajesh',null,'rajesh',null,null)
6 into test_names values (1,2,2,'rajesh kumar',null,'rajesh',null,null)
7 into test_names values (1,3,1,'rajesh',null,'rajesh kumar',null,'reddy')
8 into test_names values (1,4,1,'rajesh',null,'rajesh kumar',null,'reddy')
9 into test_names values (1,5,1,'rajesh',null,'rajesh kumar',null,'reddy')
10 into test_names values (1,1,4,'rajesh',null,'rajesh',null,null)
11 into test_names values (1,1,5,'rajesh',null,'sachin',null,null)
12 select * from dual
13 /
My input query : rajesh rajesh ganguly
output i need : id id2 id3
1 2 1
1 2 2
1 3 1
1 4 1
1 5 1
1 1 4
Second input query : rajesh sachin
output i need : id id2 id3
1 1 5
I need single query to satisfy the two above conditions
Could you please help me in this scenario??
Regards,
Rajesh
|
|
|
|
|
|
| Re: need two token matching query [message #667319 is a reply to message #667293] |
Wed, 20 December 2017 12:58 |
|
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> -- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 firstname varchar2(100),
6 secondname varchar2(100),
7 thirdname varchar2(100),
8 fourthname varchar2(100),
9 fifthname varchar2(100))
10 /
Table created.
SCOTT@orcl_12.1.0.2.0> ALTER TABLE test_names ADD CONSTRAINT pk_test_names PRIMARY KEY (id, id2,id3)
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh','kumar',null,null,null)
3 into test_names values (1,1,2,'rajesh','reddy',null,null,null)
4 into test_names values (1,1,3,'rajesh','reddy',null,null,null)
5 into test_names values (1,2,1,'rajesh',null,'rajesh',null,null)
6 into test_names values (1,2,2,'rajesh kumar',null,'rajesh',null,null)
7 into test_names values (1,3,1,'rajesh',null,'rajesh kumar',null,'reddy')
8 into test_names values (1,4,1,'rajesh',null,'rajesh kumar',null,'reddy')
9 into test_names values (1,5,1,'rajesh',null,'rajesh kumar',null,'reddy')
10 into test_names values (1,1,4,'rajesh',null,'rajesh',null,null)
11 into test_names values (1,1,5,'rajesh',null,'sachin',null,null)
12 select * from dual
13 /
10 rows created.
SCOTT@orcl_12.1.0.2.0> commit
2 /
Commit complete.
SCOTT@orcl_12.1.0.2.0> -- inputname and query:
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh ganguly'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names,
3 (select row_number () over (partition by names order by rownum) rn, names
4 from (select regexp_substr (:inputname, '[^ ]+', 1, rownum) names
5 from dual
6 connect by level <= regexp_count (:inputname, ' +') + 1))
7 group by id, id2, id3
8 having sum
9 (case when rn = 1 and
10 instr
11 (' '||firstname||' '||secondname||' '||thirdname||' '||fourthname||' '||fifthname||' ',
12 ' ' || names || ' ', 1, 1) > 0
13 then 1 else 0 end
14 +
15 case when rn = 2 and
16 instr
17 (' '||firstname||' '||secondname||' '||thirdname||' '||fourthname||' '||fifthname||' ',
18 ' ' || names || ' ', 1, 2) > 0
19 then 1 else 0 end) >= 2
20 order by id, id2, id3
21 /
ID ID2 ID3
---------- ---------- ----------
1 1 4
1 2 1
1 2 2
1 3 1
1 4 1
1 5 1
6 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh sachin'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> /
ID ID2 ID3
---------- ---------- ----------
1 1 5
1 row selected.
|
|
|
|
| Re: need two token matching query [message #667330 is a reply to message #667319] |
Thu, 21 December 2017 02:37 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Thanks Barbara Boehmer .
It is working fine..Now i need same logic with context index on full name column.
create table test_names
(id number,
id2 number,
id3 number,
fullname varchar2(500));
Table created.
create index test_names_idx on test_names (fullname)
indextype is ctxsys.context;
Index created.
insert all
into test_names values (1,1,1,'rajesh kumar')
into test_names values (1,1,2,'rajesh reddy')
into test_names values (1,1,3,'rajesh reddy')
into test_names values (1,2,1,'rajesh rajesh')
into test_names values (1,2,2,'rajesh kumar rajesh')
into test_names values (1,3,1,'rajesh rajesh kumar reddy')
into test_names values (1,4,1,'rajesh rajesh kumar reddy')
into test_names values (1,5,1,'rajesh rajesh kumar reddy')
into test_names values (1,1,4,'rajesh rajesh')
into test_names values (1,1,5,'rajesh sachin')
select * from dual
/
begin
ctx_ddl.sync_index('test_names_idx');
end;
/
My input query : rajesh rajesh ganguly
output i need : id id2 id3
1 2 1
1 2 2
1 3 1
1 4 1
1 5 1
1 1 4
Second input query : rajesh sachin
output i need : id id2 id3
1 1 5
I need single query to satisfy the two above conditions with CONTAINS function
Could you please help me in this scenario??
[mod-edit: code tags added by bb]
[Updated on: Thu, 21 December 2017 11:58] by Moderator Report message to a moderator |
|
|
|
| Re: need two token matching query [message #667346 is a reply to message #667330] |
Thu, 21 December 2017 14:25 |
|
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I had to add some functions and a multi_column_datastore.
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 fullname varchar2(500))
6 /
Table created.
SCOTT@orcl_12.1.0.2.0> create or replace function format_fullname
2 (p_fullname in varchar2)
3 return varchar2
4 as
5 v_fullname varchar2(100);
6 begin
7 for i in
8 (select regexp_substr (p_fullname, '[^ ]+', 1, level) name
9 from dual
10 connect by level <= regexp_count (p_fullname, ' +') + 1
11 order by name)
12 loop
13 v_fullname := v_fullname || i.name || ' ';
14 end loop;
15 return trim (v_fullname);
16 end format_fullname;
17 /
Function created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'format_fullname (fullname) names');
4 end;
5 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (fullname)
2 indextype is ctxsys.context
3 parameters ('datastore test_mcds')
4 /
Index created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh kumar')
3 into test_names values (1,1,2,'rajesh reddy')
4 into test_names values (1,1,3,'rajesh reddy')
5 into test_names values (1,2,1,'rajesh rajesh')
6 into test_names values (1,2,2,'rajesh kumar rajesh')
7 into test_names values (1,3,1,'rajesh rajesh kumar reddy')
8 into test_names values (1,4,1,'rajesh rajesh kumar reddy')
9 into test_names values (1,5,1,'rajesh rajesh kumar reddy')
10 into test_names values (1,1,4,'rajesh rajesh')
11 into test_names values (1,1,5,'rajesh sachin')
12 select * from dual
13 /
10 rows created.
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.sync_index('test_names_idx');
3 end;
4 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 begin
7 v_searchstring := 'definemerge((';
8 for i in
9 (select name, count(*) cnt
10 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
11 from dual
12 connect by level <= regexp_count (p_inputname, ' +') + 1)
13 group by name)
14 loop
15 v_searchstring := v_searchstring || '(definescore(' || i.name || ',discrete/100)),';
16 if i.cnt > 1 then
17 v_searchstring := v_searchstring || '(definescore(' || i.name || ' ' || i.name || ',discrete/100)),';
18 end if;
19 end loop;
20 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
21 return v_searchstring;
22 end format_input;
23 /
Function created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh ganguly'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 order by id, id2, id3
5 /
ID ID2 ID3
---------- ---------- ----------
1 1 4
1 2 1
1 2 2
1 3 1
1 4 1
1 5 1
6 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh sachin'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 order by id, id2, id3
5 /
ID ID2 ID3
---------- ---------- ----------
1 1 5
1 row selected.
|
|
|
|
|
|
|
|
|
|
| Re: need two token matching query [message #667667 is a reply to message #667418] |
Wed, 10 January 2018 02:34 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Hi Barbara,
SCOTT@orcl_12.1.0.2.0> create table test_names(id number,firstname varchar2(100),secondname varchar2(100),thirdname varchar2(100),fourthname varchar2(100),fifthname varchar2(100));
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,'rajesh','kumar',null,null,null)
3 into test_names values (2,'rajesh',null,'rajesh',null,null)
4 into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
5 into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
6 select * from dual
7 /
4 rows created.
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'firstname, secondname, thirdname, fourthname, fifthname');
4 end;
5 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> alter table test_names add (allnames varchar2(1))
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 begin
7 v_searchstring := 'definemerge((';
8 for i in
9 (select name, count(*) cnt
10 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
11 from dual
12 connect by level <= regexp_count (p_inputname, ' +') + 1)
13 group by name)
14 loop
15 v_searchstring := v_searchstring || '(definescore(' || i.name || ',discrete/100)),';
16 if i.cnt > 1 then
17 v_searchstring := v_searchstring || '(definescore(' || i.name || ' ' || i.name || ',discrete/100)),';
18 end if;
19 end loop;
20 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
21 return v_searchstring;
22 end format_input;
23 /
Function created.
Scenario:
------------
How can i create the context index and use in the below query?Please help in this scenario.
select id, id2, id3
from test_names
where contains (allnames, format_input (:inputname)) >= 2
Regards,
Rajesh
|
|
|
|
| Re: need two token matching query [message #667706 is a reply to message #667667] |
Thu, 11 January 2018 04:45 |
|
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- what you provided:
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 firstname varchar2(100),
4 secondname varchar2(100),
5 thirdname varchar2(100),
6 fourthname varchar2(100),
7 fifthname varchar2(100))
8 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,'rajesh','kumar',null,null,null)
3 into test_names values (2,'rajesh',null,'rajesh',null,null)
4 into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
5 into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
6 select * from dual
7 /
4 rows created.
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'firstname, secondname, thirdname, fourthname, fifthname');
4 end;
5 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> alter table test_names add (allnames varchar2(1))
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 begin
7 v_searchstring := 'definemerge((';
8 for i in
9 (select name, count(*) cnt
10 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
11 from dual
12 connect by level <= regexp_count (p_inputname, ' +') + 1)
13 group by name)
14 loop
15 v_searchstring := v_searchstring || '(definescore(' || i.name || ',discrete/100)),';
16 if i.cnt > 1 then
17 v_searchstring := v_searchstring || '(definescore(' || i.name || ' ' || i.name || ',discrete/100)),';
18 end if;
19 end loop;
20 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
21 return v_searchstring;
22 end format_input;
23 /
Function created.
-- what you asked for:
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
3 end;
4 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (allnames)
2 indextype is ctxsys.context
3 parameters ('datastore test_mcds')
4 /
Index created.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar sachin'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /
ID
----------
1
3
4
3 rows selected.
|
|
|
|
| Re: need two token matching query [message #667852 is a reply to message #667706] |
Sun, 21 January 2018 06:29 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Thanks Barbara Boehmer ,
I need one more change to search the special characters,i succedd most of the characters but failing only with () characters to search. Please help in this scenario
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 firstname varchar2(100),
4 secondname varchar2(100),
5 thirdname varchar2(100),
6 fourthname varchar2(100),
7 fifthname varchar2(100))
8 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,'rajesh','kumar',null,null,null)
3 into test_names values (2,'rajesh',null,'rajesh',null,null)
4 into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
5 into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
6 into test_names values (5,'rajesh'||chr(40),null,'rajeshkumar',null,'reddy')
7 select * from dual
7 /
I need to serach like the below query
select id
from test_names
where contains (allnames, format_input ('rajesh( reddy')) >= 2
/
Output should come as below --(i need to search special characters also lik (& * % ( ) { } % # $ ~ as it is in names)
ID
--
5
[Updated on: Sun, 21 January 2018 06:30] Report message to a moderator |
|
|
|
| Re: need two token matching query [message #667855 is a reply to message #667852] |
Sun, 21 January 2018 16:17 |
|
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can create a lexer and set the special characters as printjoins, then use that lexer in the index parameters. That will cause the special characters to be included as part of the indexed tokens, so that they are searchable. You can modify the format_input function to add a \ in front of each special character, so that they are treated as text characters in the search string, instead of special characters that would otherwise cause a text query parser error. Please see the demonstration below that does this.
This is just the option that you requested. Another option would be to not use a lexer so that the special characters would be ignored when indexing and modify the format_input function to remove the special characters. This would, however cause it to return results with and without the special characters. So, it all depends on whether you want to ignore the special characters or search for them.
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 firstname varchar2(100),
4 secondname varchar2(100),
5 thirdname varchar2(100),
6 fourthname varchar2(100),
7 fifthname varchar2(100))
8 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,'rajesh','kumar',null,null,null)
3 into test_names values (2,'rajesh',null,'rajesh',null,null)
4 into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
5 into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
6 into test_names values (5,'rajesh'||chr(40),null,'rajeshkumar',null,'reddy')
7 select * from dual
8 /
5 rows created.
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'firstname, secondname, thirdname, fourthname, fifthname');
4 ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
5 end;
6 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> alter table test_names add (allnames varchar2(1))
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> -- add lexer and set special characters as printjoins:
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_lex', 'basic_lexer');
3 ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~');
4 end;
5 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> -- use lexer in index parameters:
SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (allnames)
2 indextype is ctxsys.context
3 parameters
4 ('datastore test_mcds
5 lexer test_lex')
6 /
Index created.
SCOTT@orcl_12.1.0.2.0> -- modify function to add \ in front of each special character:
SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 v_name varchar2(100);
7 begin
8 v_searchstring := 'definemerge((';
9 for i in
10 (select name,
11 count(*) cnt
12 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
13 from dual
14 connect by level <= regexp_count (p_inputname, ' +') + 1)
15 group by name)
16 loop
17 v_name := i.name;
18 v_name := replace (v_name, '(', '\(');
19 v_name := replace (v_name, ')', '\)');
20 v_name := replace (v_name, '&', '\&');
21 v_name := replace (v_name, '*', '\*');
22 v_name := replace (v_name, '%', '\%');
23 v_name := replace (v_name, '{', '\{');
24 v_name := replace (v_name, '}', '\}');
25 v_name := replace (v_name, '#', '\#');
26 v_name := replace (v_name, '$', '\$');
27 v_name := replace (v_name, '~', '\~');
28 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
29 if i.cnt > 1 then
30 v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
31 end if;
32 end loop;
33 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
34 return v_searchstring;
35 end format_input;
36 /
Function created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh( reddy'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /
ID
----------
5
1 row selected.
|
|
|
|
| Re: need two token matching query [message #667856 is a reply to message #667855] |
Mon, 22 January 2018 02:40 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Barbara Boehmer ,
Its working fine..But when i add special character ? in the print joins lexer..
it is not working as expected.
insert all
into test_names values (6,'rajesh?','kumar',null,null,null)
select * from dual;
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar'
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /
ID
---------------
1
3
4
6
In the above query i should not get the ID-6.
And also when i execute
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh? kumar'
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /
ID
---------------
1
3
4
6
here i should not get ID-1,3,4
Please help in this scenario
Regards,
Rajesh
|
|
|
|
| Re: need two token matching query [message #667858 is a reply to message #667856] |
Mon, 22 January 2018 04:17 |
|
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
By default, the question mark is a punctuation, along with the period and exclamation point and that overrides setting it as a printjoin in some cases. You can modify this by specifying the punctuations as only period and exclamation point:
ctx_ddl.set_attribute ('test_lex', 'punctuations', '.!');
Please see the revised demonstration below, that includes the above as well as adding the question mark to the printjoins and modifying the format_input function to add a backslash in front of each question mark.
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 firstname varchar2(100),
4 secondname varchar2(100),
5 thirdname varchar2(100),
6 fourthname varchar2(100),
7 fifthname varchar2(100))
8 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,'rajesh','kumar',null,null,null)
3 into test_names values (2,'rajesh',null,'rajesh',null,null)
4 into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
5 into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
6 into test_names values (5,'rajesh'||chr(40),null,'rajeshkumar',null,'reddy')
7 into test_names values (6,'rajesh?','kumar',null,null,null)
8 select * from dual
9 /
6 rows created.
SCOTT@orcl_12.1.0.2.0> alter table test_names add (allnames varchar2(1))
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'firstname, secondname, thirdname, fourthname, fifthname');
4 ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
5 ctx_ddl.create_preference ('test_lex', 'basic_lexer');
6 ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~?');
7 ctx_ddl.set_attribute ('test_lex', 'punctuations', '.!');
8 end;
9 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (allnames)
2 indextype is ctxsys.context
3 parameters
4 ('datastore test_mcds
5 lexer test_lex')
6 /
Index created.
SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 v_name varchar2(100);
7 begin
8 v_searchstring := 'definemerge((';
9 for i in
10 (select name,
11 count(*) cnt
12 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
13 from dual
14 connect by level <= regexp_count (p_inputname, ' +') + 1)
15 group by name)
16 loop
17 v_name := i.name;
18 v_name := replace (v_name, '(', '\(');
19 v_name := replace (v_name, ')', '\)');
20 v_name := replace (v_name, '&', '\&');
21 v_name := replace (v_name, '*', '\*');
22 v_name := replace (v_name, '%', '\%');
23 v_name := replace (v_name, '{', '\{');
24 v_name := replace (v_name, '}', '\}');
25 v_name := replace (v_name, '#', '\#');
26 v_name := replace (v_name, '$', '\$');
27 v_name := replace (v_name, '~', '\~');
28 v_name := replace (v_name, '?', '\?');
29 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
30 if i.cnt > 1 then
31 v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
32 end if;
33 end loop;
34 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
35 return v_searchstring;
36 end format_input;
37 /
Function created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /
ID
----------
1
3
4
3 rows selected.
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh? kumar'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /
ID
----------
6
1 row selected.
|
|
|
|
| Re: need two token matching query [message #667860 is a reply to message #667858] |
Mon, 22 January 2018 05:21 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Thanks Barbara Boehmer ,
I have used the punctuations with the given solution and i inserted the below records,but i am not getting the expected result
insert all
into test_names values (7,'rajesh.','kumar',null,null,null)
into test_names values (8,'rajesh|',null,'rajesh',null,null)
into test_names values (9,'rajesh! kumar',null,'rajesh',null,null)
select * from dual;
I need to get the only ouput
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh. kumar'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /
expected output ID
------------------
7
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh! kumar'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /
expected output ID
------------------
9
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh| kumar'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /
expected output ID
------------------
8
I tried different ways but i didnt succeeded for these confitions .!|
Regards,
Rajesh.
|
|
|
|
| Re: need two token matching query [message #667861 is a reply to message #667860] |
Mon, 22 January 2018 06:08 |
|
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You need to post a copy and paste of exactly what you have done, not just describe it. For each special character, you will need to add it to the printjoins. If it is a punctuation, then you will also need to remove it from the punctuations. If you remove all punctuations, you cannot use a null value or empty string as a parameter, so you must use a space or something. For each special character, you will also need to add a line to the format_input function to add a \ in front of it. Please see the following demonstration, noting the comments prefaced by -- along the left margin. Also, note that I added the name kumar to the row with id 8; Otherwise, that row does not belong in your result set, given the search string provided.
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 firstname varchar2(100),
4 secondname varchar2(100),
5 thirdname varchar2(100),
6 fourthname varchar2(100),
7 fifthname varchar2(100))
8 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,'rajesh','kumar',null,null,null)
3 into test_names values (2,'rajesh',null,'rajesh',null,null)
4 into test_names values (3,'rajesh kumar',null,'rajesh',null,null)
5 into test_names values (4,'rajesh',null,'rajesh kumar',null,'reddy')
6 into test_names values (5,'rajesh'||chr(40),null,'rajeshkumar',null,'reddy')
7 into test_names values (6,'rajesh?','kumar',null,null,null)
8 into test_names values (7,'rajesh.','kumar',null,null,null)
9 into test_names values (8,'rajesh|',null,'kumar',null,null)
10 into test_names values (9,'rajesh! kumar',null,'rajesh',null,null)
11 select * from dual
12 /
9 rows created.
SCOTT@orcl_12.1.0.2.0> alter table test_names add (allnames varchar2(1))
2 /
Table altered.
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'firstname, secondname, thirdname, fourthname, fifthname');
4 ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
5 ctx_ddl.create_preference ('test_lex', 'basic_lexer');
6 -- add characters to printjoins:
7 ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~?.|!');
8 -- remove characters from punctuations:
9 ctx_ddl.set_attribute ('test_lex', 'punctuations', ' ');
10 end;
11 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (allnames)
2 indextype is ctxsys.context
3 parameters
4 ('datastore test_mcds
5 lexer test_lex')
6 /
Index created.
SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 v_name varchar2(100);
7 begin
8 v_searchstring := 'definemerge((';
9 for i in
10 (select name,
11 count(*) cnt
12 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
13 from dual
14 connect by level <= regexp_count (p_inputname, ' +') + 1)
15 group by name)
16 loop
17 v_name := i.name;
18 v_name := replace (v_name, '(', '\(');
19 v_name := replace (v_name, ')', '\)');
20 v_name := replace (v_name, '&', '\&');
21 v_name := replace (v_name, '*', '\*');
22 v_name := replace (v_name, '%', '\%');
23 v_name := replace (v_name, '{', '\{');
24 v_name := replace (v_name, '}', '\}');
25 v_name := replace (v_name, '#', '\#');
26 v_name := replace (v_name, '$', '\$');
27 v_name := replace (v_name, '~', '\~');
28 v_name := replace (v_name, '?', '\?');
29 -- add \ in front of each character:
30 v_name := replace (v_name, '.', '\.');
31 v_name := replace (v_name, '|', '\|');
32 v_name := replace (v_name, '!', '\!');
33 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
34 if i.cnt > 1 then
35 v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
36 end if;
37 end loop;
38 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
39 return v_searchstring;
40 end format_input;
41 /
Function created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh. kumar'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /
ID
----------
7
1 row selected.
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh! kumar'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /
ID
----------
9
1 row selected.
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh| kumar'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id
2 from test_names
3 where contains (allnames, format_input (:inputname)) >= 2
4 /
ID
----------
8
1 row selected.
|
|
|
|
| Re: need two token matching query [message #667933 is a reply to message #667861] |
Wed, 24 January 2018 11:11 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Thanks Barbara Boehmer ,
Small fix needed may be this is my final requirement.I am not getting the few records in the below scenario.Please help me
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 fullname varchar2(500))
6 /
SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 v_name varchar2(100);
7 begin
8 v_searchstring := 'definemerge((';
9 for i in
10 (select name,
11 count(*) cnt
12 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
13 from dual
14 connect by level <= regexp_count (p_inputname, ' +') + 1)
15 group by name)
16 loop
17 v_name := i.name;
18 v_name := replace (v_name, '(', '\(');
19 v_name := replace (v_name, ')', '\)');
20 v_name := replace (v_name, '&', '\&');
21 v_name := replace (v_name, '*', '\*');
22 v_name := replace (v_name, '%', '\%');
23 v_name := replace (v_name, '{', '\{');
24 v_name := replace (v_name, '}', '\}');
25 v_name := replace (v_name, '#', '\#');
26 v_name := replace (v_name, '$', '\$');
27 v_name := replace (v_name, '~', '\~');
28 v_name := replace (v_name, '?', '\?');
29 -- add \ in front of each character:
30 v_name := replace (v_name, '.', '\.');
31 v_name := replace (v_name, '|', '\|');
32 v_name := replace (v_name, '!', '\!');
33 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
34 if i.cnt > 1 then
35 v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
36 end if;
37 end loop;
38 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
39 return v_searchstring;
40 end format_input;
41 /
Function created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh kumar')
3 into test_names values (1,1,2,'rajesh reddy')
4 into test_names values (1,1,3,'rajesh reddy')
5 into test_names values (1,2,1,'rajesh rajesh')
6 into test_names values (1,2,2,'rajesh kumar rajesh')
7 into test_names values (1,3,1,'rajesh rajesh kumar reddy')
8 into test_names values (1,4,1,'rajesh dravid')
9 into test_names values (1,5,1,'rajesh rajesh kumar reddy')
10 into test_names values (1,1,4,'rajesh rajesh_ kumar sachin')
11 into test_names values (1,1,5,'rajesh sachin? ganguly')
12 into test_names values (1,1,7,'rajesh sachin')
13 into test_names values (1,1,8,'rajesh sachin')
14 into test_names values (1,1,9,'rajesh sachin')
15 select * from dual
16 /
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'fullname');
4 ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
5 ctx_ddl.create_preference ('test_lex', 'basic_lexer');
6 -- add characters to printjoins:
7 ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~?.|!');
8 -- remove characters from punctuations:
9 ctx_ddl.set_attribute ('test_lex', 'punctuations', ' ');
10 end;
11 /
SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (fullname)
2 indextype is ctxsys.context
3 parameters
4 ('datastore test_mcds
5 lexer test_lex')
6 /
Index created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh'
/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id,id2,id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 /
id id2 id3
--------------
1 2 1
1 3 1
1 5 1
i am not getting the expected output, i need to get the output as below including
id id2 id3
--------------
1 2 1
1 3 1
1 5 1
1 2 2
|
|
|
|
| Re: need two token matching query [message #667937 is a reply to message #667933] |
Wed, 24 January 2018 16:00 |
|
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 fullname varchar2(500))
6 /
Table created.
SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 v_name varchar2(100);
7 begin
8 v_searchstring := 'definemerge((';
9 for i in
10 (select name,
11 count(*) cnt
12 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
13 from dual
14 connect by level <= regexp_count (p_inputname, ' +') + 1)
15 group by name)
16 loop
17 v_name := i.name;
18 v_name := replace (v_name, '(', '\(');
19 v_name := replace (v_name, ')', '\)');
20 v_name := replace (v_name, '&', '\&');
21 v_name := replace (v_name, '*', '\*');
22 v_name := replace (v_name, '%', '\%');
23 v_name := replace (v_name, '{', '\{');
24 v_name := replace (v_name, '}', '\}');
25 v_name := replace (v_name, '#', '\#');
26 v_name := replace (v_name, '$', '\$');
27 v_name := replace (v_name, '~', '\~');
28 v_name := replace (v_name, '?', '\?');
29 v_name := replace (v_name, '.', '\.');
30 v_name := replace (v_name, '|', '\|');
31 v_name := replace (v_name, '!', '\!');
32 -- added \ in front of _:
33 v_name := replace (v_name, '_', '\_');
34 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
35 if i.cnt > 1 then
36 v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
37 end if;
38 end loop;
39 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
40 return v_searchstring;
41 end format_input;
42 /
Function created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh kumar')
3 into test_names values (1,1,2,'rajesh reddy')
4 into test_names values (1,1,3,'rajesh reddy')
5 into test_names values (1,2,1,'rajesh rajesh')
6 into test_names values (1,2,2,'rajesh kumar rajesh')
7 into test_names values (1,3,1,'rajesh rajesh kumar reddy')
8 into test_names values (1,4,1,'rajesh dravid')
9 into test_names values (1,5,1,'rajesh rajesh kumar reddy')
10 into test_names values (1,1,4,'rajesh rajesh_ kumar sachin')
11 into test_names values (1,1,5,'rajesh sachin? ganguly')
12 into test_names values (1,1,7,'rajesh sachin')
13 into test_names values (1,1,8,'rajesh sachin')
14 into test_names values (1,1,9,'rajesh sachin')
15 select * from dual
16 /
13 rows created.
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'fullname');
4 ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
5 ctx_ddl.create_preference ('test_lex', 'basic_lexer');
6 -- added underscore to printjoins:
7 ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~?.|!_');
8 ctx_ddl.set_attribute ('test_lex', 'punctuations', ' ');
9 end;
10 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (fullname)
2 indextype is ctxsys.context
3 parameters
4 ('datastore test_mcds
5 lexer test_lex')
6 /
Index created.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select format_input (:inputname) from dual
2 /
FORMAT_INPUT(:INPUTNAME)
--------------------------------------------------------------------------------
definemerge(((definescore(rajesh,discrete/100)),(definescore(rajesh rajesh,discr
ete/100))),or,add)
1 row selected.
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 -- added 3 lines below:
5 or (contains (fullname, format_input (:inputname)) >= 1 and
6 regexp_count (' ' || fullname|| ' ', ' ' || regexp_substr (:inputname, '[^ ]+') || ' ') >= 2 and
7 regexp_count (' ' || :inputname || ' ', regexp_substr (' ' || :inputname, '[^ ]+') || ' ') >= 2)
8 /
ID ID2 ID3
---------- ---------- ----------
1 2 1
1 2 2
1 3 1
1 5 1
4 rows selected.
|
|
|
|
| Re: need two token matching query [message #667963 is a reply to message #667937] |
Sun, 28 January 2018 00:23 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Thanks Barbara Boehmer ,
The suggested solution is failing in the below scenario.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh( rajesh'
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 -- added 3 lines below:
5 or (contains (fullname, format_input (:inputname)) >= 1 and
6 regexp_count (' ' || fullname|| ' ', ' ' || regexp_substr (:inputname, '[^ ]+') || ' ') >= 2 and
7 regexp_count (' ' || :inputname || ' ', regexp_substr (' ' || :inputname, '[^ ]+') || ' ') >= 2)
8 /
Ora-12725:unmatched parentheses in regular expression.
Regards,
Rajesh.
|
|
|
|
| Re: need two token matching query [message #667964 is a reply to message #667963] |
Sun, 28 January 2018 01:54 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Thanks Barbara Boehmer ,
The suggested solution is failing in the below scenarios.
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 fullname varchar2(500))
6 /
SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 v_name varchar2(100);
7 begin
8 v_searchstring := 'definemerge((';
9 for i in
10 (select name,
11 count(*) cnt
12 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
13 from dual
14 connect by level <= regexp_count (p_inputname, ' +') + 1)
15 group by name)
16 loop
17 v_name := i.name;
18 v_name := replace (v_name, '(', '\(');
19 v_name := replace (v_name, ')', '\)');
20 v_name := replace (v_name, '&', '\&');
21 v_name := replace (v_name, '*', '\*');
22 v_name := replace (v_name, '%', '\%');
23 v_name := replace (v_name, '{', '\{');
24 v_name := replace (v_name, '}', '\}');
25 v_name := replace (v_name, '#', '\#');
26 v_name := replace (v_name, '$', '\$');
27 v_name := replace (v_name, '~', '\~');
28 v_name := replace (v_name, '?', '\?');
29 -- add \ in front of each character:
30 v_name := replace (v_name, '.', '\.');
31 v_name := replace (v_name, '|', '\|');
32 v_name := replace (v_name, '!', '\!');
32 v_name := replace (v_name, '>', '\>');
32 v_name := replace (v_name, '<', '\<');
32 v_name := replace (v_name, '_', '\_');
33 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
34 if i.cnt > 1 then
35 v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
36 end if;
37 end loop;
38 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
39 return v_searchstring;
40 end format_input;
41 /
Function created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh kumar')
3 into test_names values (1,1,2,'rajesh reddy')
4 into test_names values (1,1,3,'rajesh reddy')
5 into test_names values (1,2,1,'rajesh rajesh')
6 into test_names values (1,2,2,'rajesh kumar rajesh')
7 into test_names values (1,3,1,'rajesh rajesh kumar reddy')
8 into test_names values (1,4,1,'rajesh dravid')
9 into test_names values (1,5,1,'rajesh rajesh kumar reddy')
10 into test_names values (1,1,4,'rajesh rajesh_ kumar sachin')
11 into test_names values (1,1,5,'rajesh sachin? ganguly')
12 into test_names values (1,1,7,'rajesh sachin')
13 into test_names values (1,1,8,'rajesh sachin')
14 into test_names values (1,1,9,'rajesh sachin')
15 into test_names values (1,1,10,'sachin rajesh kumar rajesh')
16 select * from dual
17 /
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'fullname');
4 ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
5 ctx_ddl.create_preference ('test_lex', 'basic_lexer');
6 -- add characters to printjoins:
7 ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~?.|!><_');
8 -- remove characters from punctuations:
9 ctx_ddl.set_attribute ('test_lex', 'punctuations', ' ');
10 end;
11 /
SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (fullname)
2 indextype is ctxsys.context
3 parameters
4 ('datastore test_mcds
5 lexer test_lex')
6 /
Index created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh kumar')
3 into test_names values (1,1,2,'rajesh reddy')
4 into test_names values (1,1,3,'rajesh reddy')
5 into test_names values (1,2,1,'rajesh rajesh')
6 into test_names values (1,2,2,'rajesh kumar rajesh')
7 into test_names values (1,3,1,'rajesh rajesh kumar reddy')
8 into test_names values (1,4,1,'rajesh dravid')
9 into test_names values (1,5,1,'rajesh rajesh kumar reddy')
10 into test_names values (1,1,4,'rajesh rajesh_ kumar sachin')
11 into test_names values (1,1,5,'rajesh sachin? ganguly')
12 into test_names values (1,1,7,'rajesh sachin')
13 into test_names values (1,1,8,'rajesh sachin')
14 into test_names values (1,1,9,'rajesh sachin')
15 into test_names values (1,1,10,'sachin rajesh kumar rajesh')
16 into test_names values (1,1,11,'sachin kumar rajesh reddy dravid rajesh')
16 select * from dual
17 /
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh( rajesh'
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 -- added 3 lines below:
5 or (contains (fullname, format_input (:inputname)) >= 1 and
6 regexp_count (' ' || fullname|| ' ', ' ' || regexp_substr (:inputname, '[^ ]+') || ' ') >= 2 and
7 regexp_count (' ' || :inputname || ' ', regexp_substr (' ' || :inputname, '[^ ]+') || ' ') >= 2)
8 /
Ora-12725:unmatched parentheses in regular expression.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh'
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 -- added 3 lines below:
5 or (contains (fullname, format_input (:inputname)) >= 1 and
6 regexp_count (' ' || fullname|| ' ', ' ' || regexp_substr (:inputname, '[^ ]+') || ' ') >= 2 and
7 regexp_count (' ' || :inputname || ' ', regexp_substr (' ' || :inputname, '[^ ]+') || ' ') >= 2)
8 /
id id2 id3
--------------
1 2 1
1 3 1
1 5 1
1 2 2
i am not getting the expected output, i need to get the output including
id id2 id3
--------------
1 2 1
1 3 1
1 5 1
1 2 2
1 1 10
1 1 11
Regards,
Rajesh
|
|
|
|
| Re: need two token matching query [message #667985 is a reply to message #667964] |
Mon, 29 January 2018 17:04 |
|
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Part of the purpose of asking you to post a copy and paste of a run of the code from SQL*Plus on your system is to be certain of what you have actually run. In your previous post, what you posted did not produce the results you claim to have gotten, so it was obviously faked. In this post you have posted two sets on insert statements, one of them after index creation without any synchronization. It is obvious that you are just copying and pasting mismatched pieces in random order, much of it just taken from what I have posted with some modifications. This defeats the whole purpose and makes it very time-consuming to sort out the problem. If you continue to do this, I may choose not to waste my time responding in the future. You need to stop doing this and post an actual copy and paste of a complete test run from your system, as I have done below.
In the below, I have added a function to escape the special characters in the variable for usage with regular expressions. I have also made modifications to the query.
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 fullname varchar2(500))
6 /
Table created.
SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 v_name varchar2(100);
7 begin
8 v_searchstring := 'definemerge((';
9 for i in
10 (select name, count(*) cnt
11 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
12 from dual
13 connect by level <= regexp_count (p_inputname, ' +') + 1)
14 group by name)
15 loop
16 v_name := i.name;
17 v_name := replace (v_name, '(', '\(');
18 v_name := replace (v_name, ')', '\)');
19 v_name := replace (v_name, '&', '\&');
20 v_name := replace (v_name, '*', '\*');
21 v_name := replace (v_name, '%', '\%');
22 v_name := replace (v_name, '{', '\{');
23 v_name := replace (v_name, '}', '\}');
24 v_name := replace (v_name, '#', '\#');
25 v_name := replace (v_name, '$', '\$');
26 v_name := replace (v_name, '~', '\~');
27 v_name := replace (v_name, '?', '\?');
28 v_name := replace (v_name, '.', '\.');
29 v_name := replace (v_name, '|', '\|');
30 v_name := replace (v_name, '!', '\!');
31 v_name := replace (v_name, '>', '\>');
32 v_name := replace (v_name, '<', '\<');
33 v_name := replace (v_name, '_', '\_');
34 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
35 if i.cnt > 1 then
36 v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
37 end if;
38 end loop;
39 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
40 return v_searchstring;
41 end format_input;
42 /
Function created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh kumar')
3 into test_names values (1,1,2,'rajesh reddy')
4 into test_names values (1,1,3,'rajesh reddy')
5 into test_names values (1,2,1,'rajesh rajesh')
6 into test_names values (1,2,2,'rajesh kumar rajesh')
7 into test_names values (1,3,1,'rajesh rajesh kumar reddy')
8 into test_names values (1,4,1,'rajesh dravid')
9 into test_names values (1,5,1,'rajesh rajesh kumar reddy')
10 into test_names values (1,1,4,'rajesh rajesh_ kumar sachin')
11 into test_names values (1,1,5,'rajesh sachin? ganguly')
12 into test_names values (1,1,7,'rajesh sachin')
13 into test_names values (1,1,8,'rajesh sachin')
14 into test_names values (1,1,9,'rajesh sachin')
15 into test_names values (1,1,10,'sachin rajesh kumar rajesh')
16 into test_names values (1,1,11,'sachin kumar rajesh reddy dravid rajesh')
17 select * from dual
18 /
15 rows created.
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'fullname');
4 ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
5 ctx_ddl.create_preference ('test_lex', 'basic_lexer');
6 ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~?.|!><_');
7 ctx_ddl.set_attribute ('test_lex', 'punctuations', ' ');
8 end;
9 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (fullname)
2 indextype is ctxsys.context
3 parameters
4 ('datastore test_mcds
5 lexer test_lex')
6 /
Index created.
SCOTT@orcl_12.1.0.2.0> -- added function to escape the special characters for usage with regular expressions:
SCOTT@orcl_12.1.0.2.0> create or replace function esc_spec_chars
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_name varchar2(100);
6 begin
7 v_name := p_inputname;
8 v_name := replace (v_name, '(', '\(');
9 v_name := replace (v_name, ')', '\)');
10 v_name := replace (v_name, '&', '\&');
11 v_name := replace (v_name, '*', '\*');
12 v_name := replace (v_name, '%', '\%');
13 v_name := replace (v_name, '{', '\{');
14 v_name := replace (v_name, '}', '\}');
15 v_name := replace (v_name, '#', '\#');
16 v_name := replace (v_name, '$', '\$');
17 v_name := replace (v_name, '~', '\~');
18 v_name := replace (v_name, '?', '\?');
19 v_name := replace (v_name, '.', '\.');
20 v_name := replace (v_name, '|', '\|');
21 v_name := replace (v_name, '!', '\!');
22 v_name := replace (v_name, '_', '\_');
23 return v_name;
24 end esc_spec_chars;
25 /
Function created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh( rajesh'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> -- modified query:
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 or (contains (fullname, format_input (:inputname)) >= 1 and
5 regexp_count
6 (' ' || esc_spec_chars(fullname)|| ' ',
7 ' ' || regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2 and
8 regexp_count
9 (' ' || esc_spec_chars(:inputname) || ' ',
10 regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2)
11 /
no rows selected
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 or (contains (fullname, format_input (:inputname)) >= 1 and
5 regexp_count
6 (' ' || esc_spec_chars(fullname)|| ' ',
7 ' ' || regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2 and
8 regexp_count
9 (' ' || esc_spec_chars(:inputname) || ' ',
10 regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2)
11 /
ID ID2 ID3
---------- ---------- ----------
1 2 1
1 2 2
1 3 1
1 5 1
1 1 10
1 1 11
6 rows selected.
|
|
|
|
|
|
| Re: need two token matching query [message #668015 is a reply to message #667987] |
Wed, 31 January 2018 01:09 |
|
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
Thanks Barbara Boehmer ,
Thanks for your support,this may be the last post on this topic .
.Please suggest any changes need for the below.
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 fullname varchar2(500))
6 /
Table created.
SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_searchstring varchar2(4000);
6 v_name varchar2(100);
7 begin
8 v_searchstring := 'definemerge((';
9 for i in
10 (select name, count(*) cnt
11 from (select regexp_substr (p_inputname, '[^ ]+', 1, level) name
12 from dual
13 connect by level <= regexp_count (p_inputname, ' +') + 1)
14 group by name)
15 loop
16 v_name := i.name;
17 v_name := replace (v_name, '(', '\(');
18 v_name := replace (v_name, ')', '\)');
19 v_name := replace (v_name, '&', '\&');
20 v_name := replace (v_name, '*', '\*');
21 v_name := replace (v_name, '%', '\%');
22 v_name := replace (v_name, '{', '\{');
23 v_name := replace (v_name, '}', '\}');
24 v_name := replace (v_name, '#', '\#');
25 v_name := replace (v_name, '$', '\$');
26 v_name := replace (v_name, '~', '\~');
27 v_name := replace (v_name, '?', '\?');
28 v_name := replace (v_name, '.', '\.');
29 v_name := replace (v_name, '|', '\|');
30 v_name := replace (v_name, '!', '\!');
31 v_name := replace (v_name, '>', '\>');
32 v_name := replace (v_name, '<', '\<');
33 v_name := replace (v_name, '_', '\_');
34 v_searchstring := v_searchstring || '(definescore(' || v_name || ',discrete/100)),';
35 if i.cnt > 1 then
36 v_searchstring := v_searchstring || '(definescore(' || v_name || ' ' || v_name || ',discrete/100)),';
37 end if;
38 end loop;
39 v_searchstring := rtrim (v_searchstring, ',') || '),or,add)';
40 return v_searchstring;
41 end format_input;
42 /
Function created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh kumar')
3 into test_names values (1,1,2,'rajesh reddy')
4 into test_names values (1,1,3,'rajesh reddy')
5 into test_names values (1,2,1,'rajesh rajesh')
6 into test_names values (1,2,2,'rajesh kumar rajesh')
7 into test_names values (1,3,1,'rajesh rajesh kumar reddy')
8 into test_names values (1,4,1,'rajesh dravid')
9 into test_names values (1,5,1,'rajesh rajesh kumar reddy')
10 into test_names values (1,1,4,'rajesh rajesh_ kumar sachin')
11 into test_names values (1,1,5,'rajesh sachin? ganguly')
12 into test_names values (1,1,7,'rajesh sachin')
13 into test_names values (1,1,8,'rajesh sachin')
14 into test_names values (1,1,9,'rajesh sachin')
15 into test_names values (1,1,10,'sachin rajesh kumar rajesh')
16 into test_names values (1,1,11,'sachin kumar rajesh reddy dravid rajesh')
17 into test_names values (1,1,12,'khan rajesh( kumar rajesh(')
18 into test_names values (1,1,13,'zaheer rajesh( rajesh(')
19 select * from dual
20 /
17 rows created.
SCOTT@orcl_12.1.0.2.0> begin
2 ctx_ddl.create_preference ('test_mcds', 'multi_column_datastore');
3 ctx_ddl.set_attribute ('test_mcds', 'columns', 'fullname');
4 ctx_ddl.set_attribute ('test_mcds', 'delimiter', 'newline');
5 ctx_ddl.create_preference ('test_lex', 'basic_lexer');
6 ctx_ddl.set_attribute ('test_lex', 'printjoins', '&*%(){}%#$~?.|!><_');
7 ctx_ddl.set_attribute ('test_lex', 'punctuations', ' ');
8 end;
9 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> create index test_names_idx on test_names (fullname)
2 indextype is ctxsys.context
3 parameters
4 ('datastore test_mcds
5 lexer test_lex')
6 /
Index created.
SCOTT@orcl_12.1.0.2.0> -- added function to escape the special characters for usage with regular expressions:
SCOTT@orcl_12.1.0.2.0> create or replace function esc_spec_chars
2 (p_inputname in varchar2)
3 return varchar2
4 as
5 v_name varchar2(100);
6 begin
7 v_name := p_inputname;
8 v_name := replace (v_name, '(', '\(');
9 v_name := replace (v_name, ')', '\)');
10 v_name := replace (v_name, '&', '\&');
11 v_name := replace (v_name, '*', '\*');
12 v_name := replace (v_name, '%', '\%');
13 v_name := replace (v_name, '{', '\{');
14 v_name := replace (v_name, '}', '\}');
15 v_name := replace (v_name, '#', '\#');
16 v_name := replace (v_name, '$', '\$');
17 v_name := replace (v_name, '~', '\~');
18 v_name := replace (v_name, '?', '\?');
19 v_name := replace (v_name, '.', '\.');
20 v_name := replace (v_name, '|', '\|');
21 v_name := replace (v_name, '!', '\!');
22 v_name := replace (v_name, '_', '\_');
23 return v_name;
24 end esc_spec_chars;
25 /
Function created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh( rajesh'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> -- modified query:
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 or (contains (fullname, format_input (:inputname)) >= 1 and
5 regexp_count
6 (' ' || esc_spec_chars(fullname)|| ' ',
7 ' ' || regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2 and
8 regexp_count
9 (' ' || esc_spec_chars(:inputname) || ' ',
10 regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2)
11 /
no rows selected
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'dhoni rajesh rajesh'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 or (contains (fullname, format_input (:inputname)) >= 1 and
5 regexp_count
6 (' ' || esc_spec_chars(fullname)|| ' ',
7 ' ' || regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2 and
8 regexp_count
9 (' ' || esc_spec_chars(:inputname) || ' ',
10 regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2)
11 /
ID ID2 ID3
---------- ---------- ----------
1 2 1
1 3 1
1 5 1
3 rows selected.
But i need to get the below records also .(if any two tokens matching with the given name we need to get the output)
expected output:
ID ID2 ID3
---------- ---------- ----------
1 2 1
1 3 1
1 5 1
1 2 2
1 1 10
1 1 11
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'dhoni rajesh( rajesh('
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names
3 where contains (fullname, format_input (:inputname)) >= 2
4 or (contains (fullname, format_input (:inputname)) >= 1 and
5 regexp_count
6 (' ' || esc_spec_chars(fullname)|| ' ',
7 ' ' || regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2 and
8 regexp_count
9 (' ' || esc_spec_chars(:inputname) || ' ',
10 regexp_substr (esc_spec_chars(:inputname), '[^ ]+') || ' ') >= 2)
11 /
no rows selected
But i need to get the below records also .(if any two tokens matching with the given name we need to get the output)
expected output:
ID ID2 ID3
---------- ---------- ----------
1 1 12
1 1 13
Regards,
Rajesh.
|
|
|
|
| Re: need two token matching query [message #668028 is a reply to message #668015] |
Wed, 31 January 2018 18:17 |
|
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The requirements seem to be getting more complex in terms of either matching 2 different tokens or matching 2 tokens if there are 2 duplicates in the input string and 2 duplicates of the same token in the fullname and including special characters. It keeps getting more convoluted in terms of escaping the special characters for the context index and nested regular expressions. You don't seem to need any of the features of Oracle Text. So, at this point, I think it is appropriate to re-examine and re-think the whole process. I suggest the following without a context index or nested regular expressions or functions. Instead it uses one standard index concatenating spaces to fullname and LIKE which uses the index to produce a small result set, then uses INSTR and such on that small result set to narrow that result set further. I have added some additional data to create a larger data set and used timing and autotrace to show that the results are returned quickly using the index.
-- your table and your latest data set that you provided:
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 fullname varchar2(500))
6 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test_names values (1,1,1,'rajesh kumar')
3 into test_names values (1,1,2,'rajesh reddy')
4 into test_names values (1,1,3,'rajesh reddy')
5 into test_names values (1,2,1,'rajesh rajesh')
6 into test_names values (1,2,2,'rajesh kumar rajesh')
7 into test_names values (1,3,1,'rajesh rajesh kumar reddy')
8 into test_names values (1,4,1,'rajesh dravid')
9 into test_names values (1,5,1,'rajesh rajesh kumar reddy')
10 into test_names values (1,1,4,'rajesh rajesh_ kumar sachin')
11 into test_names values (1,1,5,'rajesh sachin? ganguly')
12 into test_names values (1,1,7,'rajesh sachin')
13 into test_names values (1,1,8,'rajesh sachin')
14 into test_names values (1,1,9,'rajesh sachin')
15 into test_names values (1,1,10,'sachin rajesh kumar rajesh')
16 into test_names values (1,1,11,'sachin kumar rajesh reddy dravid rajesh')
17 into test_names values (1,1,12,'khan rajesh( kumar rajesh(')
18 into test_names values (1,1,13,'zaheer rajesh( rajesh(')
19 select * from dual
20 /
17 rows created.
SCOTT@orcl_12.1.0.2.0> commit
2 /
Commit complete.
-- extra data for testing:
SCOTT@orcl_12.1.0.2.0> insert into test_names (id, id2, id3, fullname)
2 select object_id, data_object_id, namespace, 'rajesh ' || object_name
3 from all_objects
4 /
92177 rows created.
SCOTT@orcl_12.1.0.2.0> commit
2 /
Commit complete.
-- standard index concatenating spaces to fullname (not a text index):
SCOTT@orcl_12.1.0.2.0> -- standard index:
SCOTT@orcl_12.1.0.2.0> create index test_idx on test_names (' ' || fullname || ' ', id, id2, id3)
2 /
Index created.
-- gather statistics on the index to help the optimizer choose the best execution plan
-- (you may be able to skip this step if your database is set to gather statistics automatically or use sampling):
SCOTT@orcl_12.1.0.2.0> exec dbms_stats.gather_table_stats (user, 'TEST_NAMES')
PL/SQL procedure successfully completed.
-- queries with different variables, showing timing and showing the execution plan on the first run:
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar sachin'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> set timing on
SCOTT@orcl_12.1.0.2.0> set autotrace on explain
SCOTT@orcl_12.1.0.2.0> select distinct fullname, id, id2, id3
2 from (select fullname, id, id2, id3, name, ncnt,
3 count (*) over (partition by fullname, id, id2, id3) fcnt
4 from test_names,
5 (select name, count (*) ncnt
6 from (select regexp_substr (:inputname, '[^ ]+', 1, level) name
7 from dual
8 connect by level <= regexp_count (:inputname, ' +') + 1)
9 group by name)
10 where ' ' || fullname || ' ' like '% ' || name || ' %')
11 where fcnt >= 2
12 or (ncnt >= 2 and instr (' ' || fullname || ' ', ' ' || name || ' ', 2) > 0)
13 /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar 1 1 1
rajesh kumar rajesh 1 2 2
rajesh rajesh kumar reddy 1 3 1
rajesh rajesh kumar reddy 1 5 1
rajesh rajesh_ kumar sachin 1 1 4
rajesh sachin 1 1 7
rajesh sachin 1 1 8
rajesh sachin 1 1 9
sachin kumar rajesh reddy dravid rajesh 1 1 11
sachin rajesh kumar rajesh 1 1 10
10 rows selected.
Elapsed: 00:00:00.48
Execution Plan
----------------------------------------------------------
Plan hash value: 1624550815
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4610 | 603K| | 472 (1)| 00:00:01 |
| 1 | SORT UNIQUE NOSORT | | 4610 | 603K| | 472 (1)| 00:00:01 |
|* 2 | VIEW | | 4610 | 603K| | 331 (1)| 00:00:01 |
| 3 | WINDOW SORT | | 4610 | 693K| 784K| 331 (1)| 00:00:01 |
| 4 | NESTED LOOPS | | 4610 | 693K| | 172 (1)| 00:00:01 |
| 5 | VIEW | | 1 | 79 | | 3 (34)| 00:00:01 |
| 6 | HASH GROUP BY | | 1 | 66 | | 3 (34)| 00:00:01 |
| 7 | VIEW | | 1 | 66 | | 2 (0)| 00:00:01 |
|* 8 | CONNECT BY WITHOUT FILTERING| | | | | | |
| 9 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | TEST_NAMES | 4610 | 337K| | 169 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | TEST_IDX | 830 | | | 8 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FCNT">=2 OR "NCNT">=2 AND INSTR(' '||"FULLNAME"||' ',' '||"NAME"||' ',2)>0)
8 - filter(LEVEL<= REGEXP_COUNT (:INPUTNAME,' +')+1)
11 - access(' '||"FULLNAME"||' ' LIKE '% '||"NAME"||' %')
filter(' '||"FULLNAME"||' ' LIKE '% '||"NAME"||' %')
SCOTT@orcl_12.1.0.2.0> set autotrace off
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'kumar sachin rajesh'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar 1 1 1
rajesh kumar rajesh 1 2 2
rajesh rajesh kumar reddy 1 3 1
rajesh rajesh kumar reddy 1 5 1
rajesh rajesh_ kumar sachin 1 1 4
rajesh sachin 1 1 7
rajesh sachin 1 1 8
rajesh sachin 1 1 9
sachin kumar rajesh reddy dravid rajesh 1 1 11
sachin rajesh kumar rajesh 1 1 10
10 rows selected.
Elapsed: 00:00:00.45
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh ganguly'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar rajesh 1 2 2
rajesh rajesh 1 2 1
rajesh rajesh kumar reddy 1 3 1
rajesh rajesh kumar reddy 1 5 1
rajesh sachin? ganguly 1 1 5
sachin kumar rajesh reddy dravid rajesh 1 1 11
sachin rajesh kumar rajesh 1 1 10
7 rows selected.
Elapsed: 00:00:00.57
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh sachin'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
rajesh rajesh_ kumar sachin 1 1 4
rajesh sachin 1 1 7
rajesh sachin 1 1 8
rajesh sachin 1 1 9
sachin kumar rajesh reddy dravid rajesh 1 1 11
sachin rajesh kumar rajesh 1 1 10
6 rows selected.
Elapsed: 00:00:00.50
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar 1 1 1
rajesh kumar rajesh 1 2 2
rajesh rajesh kumar reddy 1 3 1
rajesh rajesh kumar reddy 1 5 1
rajesh rajesh_ kumar sachin 1 1 4
sachin kumar rajesh reddy dravid rajesh 1 1 11
sachin rajesh kumar rajesh 1 1 10
7 rows selected.
Elapsed: 00:00:00.42
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar rajesh 1 2 2
rajesh rajesh 1 2 1
rajesh rajesh kumar reddy 1 3 1
rajesh rajesh kumar reddy 1 5 1
sachin kumar rajesh reddy dravid rajesh 1 1 11
sachin rajesh kumar rajesh 1 1 10
6 rows selected.
Elapsed: 00:00:00.40
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'dhoni rajesh rajesh'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar rajesh 1 2 2
rajesh rajesh 1 2 1
rajesh rajesh kumar reddy 1 3 1
rajesh rajesh kumar reddy 1 5 1
sachin kumar rajesh reddy dravid rajesh 1 1 11
sachin rajesh kumar rajesh 1 1 10
6 rows selected.
Elapsed: 00:00:00.46
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'dhoni rajesh( rajesh('
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
khan rajesh( kumar rajesh( 1 1 12
zaheer rajesh( rajesh( 1 1 13
2 rows selected.
Elapsed: 00:00:00.12
[Updated on: Wed, 31 January 2018 18:34] Report message to a moderator |
|
|
|
| Re: need two token matching query [message #668029 is a reply to message #668028] |
Wed, 31 January 2018 18:50 |
|
Barbara Boehmer
Messages: 9062
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following query with the additional INSTR condition seems to have similar times, but a better execution plan, narrowing the results to a smaller data set sooner, so it may scale better on larger data sets. I ran it on the same data and index as above.
-- queries with different variables, showing timing and execution plan on first run:
SCOTT@orcl_12.1.0.2.0> variable inputname varchar2(100)
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar sachin'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> set timing on
SCOTT@orcl_12.1.0.2.0> set autotrace on explain
SCOTT@orcl_12.1.0.2.0> select distinct fullname, id, id2, id3
2 from (select fullname, id, id2, id3, name, ncnt,
3 count (*) over (partition by fullname, id, id2, id3) fcnt
4 from test_names,
5 (select name, count (*) ncnt
6 from (select regexp_substr (:inputname, '[^ ]+', 1, level) name
7 from dual
8 connect by level <= regexp_count (:inputname, ' +') + 1)
9 group by name)
10 where ' ' || fullname || ' ' like '% ' || name || ' %'
11 and instr (' ' || fullname || ' ', ' ' || name || ' ', 1) > 0)
12 where fcnt >= 2
13 or (ncnt >= 2 and instr (' ' || fullname || ' ', ' ' || name || ' ', 2) > 0)
14 /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar 1 1 1
rajesh kumar rajesh 1 2 2
rajesh rajesh kumar reddy 1 3 1
rajesh rajesh kumar reddy 1 5 1
rajesh rajesh_ kumar sachin 1 1 4
rajesh sachin 1 1 7
rajesh sachin 1 1 8
rajesh sachin 1 1 9
sachin kumar rajesh reddy dravid rajesh 1 1 11
sachin rajesh kumar rajesh 1 1 10
10 rows selected.
Elapsed: 00:00:00.68
Execution Plan
----------------------------------------------------------
Plan hash value: 1624550815
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 230 | 30820 | 34 (9)| 00:00:01 |
| 1 | SORT UNIQUE NOSORT | | 230 | 30820 | 34 (9)| 00:00:01 |
|* 2 | VIEW | | 230 | 30820 | 33 (7)| 00:00:01 |
| 3 | WINDOW SORT | | 230 | 35420 | 33 (7)| 00:00:01 |
| 4 | NESTED LOOPS | | 230 | 35420 | 32 (4)| 00:00:01 |
| 5 | VIEW | | 1 | 79 | 3 (34)| 00:00:01 |
| 6 | HASH GROUP BY | | 1 | 66 | 3 (34)| 00:00:01 |
| 7 | VIEW | | 1 | 66 | 2 (0)| 00:00:01 |
|* 8 | CONNECT BY WITHOUT FILTERING| | | | | |
| 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | TEST_NAMES | 230 | 17250 | 29 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | TEST_IDX | 41 | | 8 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FCNT">=2 OR "NCNT">=2 AND INSTR(' '||"FULLNAME"||' ',' '||"NAME"||' ',2)>0)
8 - filter(LEVEL<= REGEXP_COUNT (:INPUTNAME,' +')+1)
11 - access(' '||"FULLNAME"||' ' LIKE '% '||"NAME"||' %')
filter(' '||"FULLNAME"||' ' LIKE '% '||"NAME"||' %' AND INSTR(' '||"FULLNAME"||'
',' '||"NAME"||' ',1)>0)
SCOTT@orcl_12.1.0.2.0> set autotrace off
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'kumar sachin rajesh'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar 1 1 1
rajesh kumar rajesh 1 2 2
rajesh rajesh kumar reddy 1 3 1
rajesh rajesh kumar reddy 1 5 1
rajesh rajesh_ kumar sachin 1 1 4
rajesh sachin 1 1 7
rajesh sachin 1 1 8
rajesh sachin 1 1 9
sachin kumar rajesh reddy dravid rajesh 1 1 11
sachin rajesh kumar rajesh 1 1 10
10 rows selected.
Elapsed: 00:00:00.51
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh ganguly'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar rajesh 1 2 2
rajesh rajesh 1 2 1
rajesh rajesh kumar reddy 1 3 1
rajesh rajesh kumar reddy 1 5 1
rajesh sachin? ganguly 1 1 5
sachin kumar rajesh reddy dravid rajesh 1 1 11
sachin rajesh kumar rajesh 1 1 10
7 rows selected.
Elapsed: 00:00:00.51
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh sachin'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
rajesh rajesh_ kumar sachin 1 1 4
rajesh sachin 1 1 7
rajesh sachin 1 1 8
rajesh sachin 1 1 9
sachin kumar rajesh reddy dravid rajesh 1 1 11
sachin rajesh kumar rajesh 1 1 10
6 rows selected.
Elapsed: 00:00:00.54
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh kumar'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar 1 1 1
rajesh kumar rajesh 1 2 2
rajesh rajesh kumar reddy 1 3 1
rajesh rajesh kumar reddy 1 5 1
rajesh rajesh_ kumar sachin 1 1 4
sachin kumar rajesh reddy dravid rajesh 1 1 11
sachin rajesh kumar rajesh 1 1 10
7 rows selected.
Elapsed: 00:00:00.53
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'rajesh rajesh'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar rajesh 1 2 2
rajesh rajesh 1 2 1
rajesh rajesh kumar reddy 1 3 1
rajesh rajesh kumar reddy 1 5 1
sachin kumar rajesh reddy dravid rajesh 1 1 11
sachin rajesh kumar rajesh 1 1 10
6 rows selected.
Elapsed: 00:00:00.43
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'dhoni rajesh rajesh'
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SCOTT@orcl_12.1.0.2.0> /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
rajesh kumar rajesh 1 2 2
rajesh rajesh 1 2 1
rajesh rajesh kumar reddy 1 3 1
rajesh rajesh kumar reddy 1 5 1
sachin kumar rajesh reddy dravid rajesh 1 1 11
sachin rajesh kumar rajesh 1 1 10
6 rows selected.
Elapsed: 00:00:00.51
SCOTT@orcl_12.1.0.2.0> exec :inputname := 'dhoni rajesh( rajesh('
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SCOTT@orcl_12.1.0.2.0> /
FULLNAME ID ID2 ID3
---------------------------------------- ---------- ---------- ----------
khan rajesh( kumar rajesh( 1 1 12
zaheer rajesh( rajesh( 1 1 13
2 rows selected.
Elapsed: 00:00:00.12
|
|
|
|
Goto Forum:
Current Time: Sat Sep 30 13:48:41 CDT 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
