Home » Server Options » Text & interMedia » Fulltext search (Oracle 11g or Hive 0.14.0)
Fulltext search [message #654458] Wed, 03 August 2016 04:07 Go to next message
NikoTrend1
Messages: 4
Registered: April 2016
Junior Member
Hello, my friends)
I try to make some search in text.
And I have 2 tables. For example,
First table (CHECK_ALLTEXT T1) it's large table about 60 000 000 rows:
Title; Text
News_1; Some text about wheather
News_2; Some new text about travel and cheap avia tickets
News_3; Text about little rabbits, chickens and porks
....
News_N Information about cars, trucks, buses

Second table (CHECK_CATEGORY_DIM T2) it's small table about 1 000 rows:
Subtext; Category
little rabbit; animals
car; wehicles
....
cheap avia; travel

My goal is:
News_2, travel
News_3, animals
...
News_N, wehicles

And that's what i do:
SELECT T1.Title,
T2.Category
from CHECK_ALLTEXT T1, CHECK_CATEGORY_DIM T2
where 
T1.Text LIKE concat('%', T2.Subtext, '%');

However it works only with little tables.
Do you have any decisions of such search?
Re: Fulltext search [message #654460 is a reply to message #654458] Wed, 03 August 2016 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

John Watson wrote on Mon, 04 April 2016 14:34
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
...
Michel Cadot wrote on Mon, 04 April 2016 14:50

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

Have a look at "Oracle Text" extension, examples in our "Text & interMedia" forum.

[Updated on: Wed, 03 August 2016 04:18]

Report message to a moderator

Re: Fulltext search [message #654488 is a reply to message #654458] Wed, 03 August 2016 15:39 Go to previous message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
You can use Oracle Text for this. As with your current method, a simple Oracle Text query is likely to produce an error with large data sets when the first two parameters both come from tables. However, there is a workaround, as I have demonstrated below.

-- test data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM check_category_dim ORDER BY category
  2  /

SUBTEXT       CATEGORY
------------- ------------------------------
little rabbit animals
cheap avia    travel
car           vehicles

3 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM check_alltext ORDER BY title
  2  /

TITLE  TEXT
------ -------------------------------------------------
News_1 Some text about weather
News_2 Some new text about travel and cheap avia tickets
News_3 Text about little rabbits, chickens and pigs
News_N Information about cars, trucks, buses

4 rows selected.

-- Oracle Text context index (needed for either method below):
SCOTT@orcl_12.1.0.2.0> CREATE INDEX check_alltext_text_idx ON check_alltext (text) INDEXTYPE IS CTXSYS.CONTEXT
  2  /

Index created.

-- simple query (may produce error on large data sets):
SCOTT@orcl_12.1.0.2.0> SELECT t1.title, t2.category
  2  FROM   check_alltext t1, check_category_dim t2
  3  WHERE  CONTAINS (t1.text, t2.subtext || '%') > 0
  4  ORDER  BY title
  5  /

TITLE  CATEGORY
------ ------------------------------
News_2 travel
News_3 animals
News_N vehicles

3 rows selected.

-- alternate method if above produces error due to size of data in both tables:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE test_pkg
  2  AS
  3    TYPE result_rec IS RECORD
  4  	 (title      check_alltext.title%TYPE,
  5  	  category   check_category_dim.category%TYPE);
  6    TYPE result_tab IS TABLE OF result_rec;
  7    FUNCTION test_func
  8  	 RETURN result_tab PIPELINED;
  9  END test_pkg;
 10  /

Package created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE BODY test_pkg
  2  AS
  3    FUNCTION test_func
  4  	 RETURN result_tab PIPELINED
  5    IS
  6  	 out_rec result_rec;
  7    BEGIN
  8  	 FOR r1 IN
  9  	   (SELECT subtext, category FROM check_category_dim ORDER BY category)
 10  	 LOOP
 11  	   FOR r2 IN
 12  	     (SELECT title
 13  	      FROM   check_alltext t1
 14  	      WHERE  CONTAINS (text, r1.subtext || '%') > 0
 15  	      ORDER  BY title)
 16  	   LOOP
 17  	     out_rec.title := r2.title;
 18  	     out_rec.category := r1.category;
 19  	     PIPE ROW(out_rec);
 20  	   END LOOP;
 21  	 END LOOP;
 22  	 RETURN;
 23    END test_func;
 24  END test_pkg;
 25  /

Package body created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM TABLE (test_pkg.test_func())
  2  /

TITLE  CATEGORY
------ ------------------------------
News_3 animals
News_2 travel
News_N vehicles

3 rows selected.
Previous Topic: PDF to HTML convert using ctxsys.auto_filter different result db 11.2 and 12.1
Next Topic: Create Index for CLOB-datatype
Goto Forum:
  


Current Time: Thu Nov 23 16:29:41 CST 2017

Total time taken to generate the page: 0.03473 seconds