| Fulltext search [message #654458] |
Wed, 03 August 2016 04:07  |
 |
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  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
From your previous topic:
John Watson wrote on Mon, 04 April 2016 14:34Welcome 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 |
 |
Barbara Boehmer
Messages: 9100
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
