OraFAQ Forum: Text & interMedia » Oracle Text Performance

Home » Server Options » Text & interMedia » Oracle Text Performance (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Text Performance [message #670763]

Tue, 24 July 2018 20:19

vka2b
Messages: 21
Registered: June 2018

Junior Member

Hi,

This is more of a general question, and I'm using an example that I've seen on this forum before as the basis (and also used when I asked my first and only other question on this forum):

SCOTT@orcl_11gR2> SELECT SCORE(1), user_desc, emp_name
  2  FROM   emp_master, menu_user_d
  3  WHERE  CONTAINS (emp_name, '?' || REPLACE (user_desc, ' ', ',?'), 1) > 0
  4  ORDER  BY SCORE(1) DESC
  5  /

  SCORE(1) USER_DESC     EMP_NAME
---------- ------------- -------------
         4 Wajahat       Wajahat
         4 Imd           Imad El Kane
         2 Mohammed Arif Md.Arif

3 rows selected.

I am doing something similar to the above for my own project, and it works just fine functionally. However, the tables I'm using (equivalent to emp_master and menu_user_d) are huge -- one of them has around 1 million records, and the other around 1 thousand. Given that the above query essentially forms a cartesian product between the two tables, with my data the performance is quite slow. I am essentially trying to convert fuzzy name matching functionality we currently have in a C++ engine to using Oracle Text instead, and in the C++ code we have various short-circuiting logic (e.g. discarding match-pairs whose initials don't match and such from the processing). I'm sure I could write some sort of UDF in PL/SQL to achieve something similar, but before re-inventing the wheel, I wanted to see if there is any built-in filtering functionality I might be missing, or any other best practices anybody can advise on -- this area isn't my expertise (my background is the C/C++ world), so I apologize in advance if this is a ridiculous question, but I'd really appreciate any pointers. Thanks!

Report message to a moderator

[Message index]

		Oracle Text Performance By: vka2b on Tue, 24 July 2018 20:19
		Re: Oracle Text Performance By: Barbara Boehmer on Tue, 24 July 2018 20:55
		Re: Oracle Text Performance By: vka2b on Wed, 25 July 2018 15:47
		Re: Oracle Text Performance By: Barbara Boehmer on Wed, 25 July 2018 16:39
		Re: Oracle Text Performance By: vka2b on Wed, 25 July 2018 21:50
		Re: Oracle Text Performance By: Barbara Boehmer on Thu, 26 July 2018 01:44
		Re: Oracle Text Performance By: vka2b on Thu, 26 July 2018 07:50
		Re: Oracle Text Performance By: vka2b on Thu, 26 July 2018 13:56
		Re: Oracle Text Performance By: Barbara Boehmer on Thu, 26 July 2018 19:38
		Re: Oracle Text Performance By: vka2b on Mon, 30 July 2018 22:44
		Re: Oracle Text Performance By: Barbara Boehmer on Tue, 31 July 2018 03:53
		Re: Oracle Text Performance By: vka2b on Tue, 31 July 2018 14:08
		Re: Oracle Text Performance By: Barbara Boehmer on Tue, 31 July 2018 18:42
		Re: Oracle Text Performance By: vka2b on Tue, 31 July 2018 19:30
		Re: Oracle Text Performance By: Barbara Boehmer on Tue, 31 July 2018 21:43
		Re: Oracle Text Performance By: vka2b on Tue, 31 July 2018 23:09
		Re: Oracle Text Performance By: vka2b on Tue, 11 September 2018 09:29
		Re: Oracle Text Performance By: Barbara Boehmer on Tue, 11 September 2018 13:09
		Re: Oracle Text Performance By: vka2b on Wed, 12 September 2018 18:28

Previous Topic:	How to escape reserved words returned in column values as parameters to CONTAINS function
Next Topic:	Stoplists and Nickname lists

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 07 18:32:50 CDT 2024