Home » Server Options » Text & interMedia » Search over multiple Columns and Tables possible?
|
|
| Re: Search over multiple Columns and Tables possible? [message #655862 is a reply to message #655836] |
Wed, 14 September 2016 17:09   |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Yes, this is possible with Oracle Text. I have provided a simple demonstration below. I have included tags to enable searching within tags as well as the whole data. I have also shown what the procedure and index produce for better understanding. This is just a very basic example. The output of the procedure could be more complex and there are various options for indexing and searching and scoring.
-- tables and data:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE people
2 (id NUMBER,
3 givenname VARCHAR2(15),
4 surname VARCHAR2(15))
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT ALL
2 INTO people (id, givenname, surname) VALUES (1, 'Hans', 'Meier')
3 INTO people (id, givenname, surname) VALUES (2, 'Frauke', 'Huber')
4 INTO people (id, givenname, surname) VALUES (3, 'Werner', 'Krause-Meier')
5 SELECT * FROM DUAL
6 /
3 rows created.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM people ORDER BY id
2 /
ID GIVENNAME SURNAME
---------- --------------- ---------------
1 Hans Meier
2 Frauke Huber
3 Werner Krause-Meier
3 rows selected.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE address
2 (id NUMBER,
3 street VARCHAR2(15),
4 street_number NUMBER)
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT ALL
2 INTO address (id, street, street_number) VALUES (1, 'Hauptstr', 4)
3 INTO address (id, street, street_number) VALUES (2, 'Lukas-Meier-Str', 23)
4 INTO address (id, street, street_number) VALUES (3, 'Bahnhofstr', 11)
5 SELECT * FROM DUAL
6 /
3 rows created.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM address ORDER BY id
2 /
ID STREET STREET_NUMBER
---------- --------------- -------------
1 Hauptstr 4
2 Lukas-Meier-Str 23
3 Bahnhofstr 11
3 rows selected.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE people_address
2 (people_id NUMBER,
3 street_id NUMBER)
4 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT ALL
2 INTO people_address (people_id, street_id) VALUES (1, 2)
3 INTO people_address (people_id, street_id) VALUES (2, 1)
4 INTO people_address (people_id, street_id) VALUES (3, 3)
5 SELECT * FROM DUAL
6 /
3 rows created.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM people_address ORDER BY people_id, street_id
2 /
PEOPLE_ID STREET_ID
---------- ----------
1 2
2 1
3 3
3 rows selected.
-- procedure to join and concatenate the data with optional tags to enable searching within tags:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE test_proc
2 (p_rid IN ROWID,
3 p_clob IN OUT NOCOPY CLOB)
4 AS
5 BEGIN
6 FOR r IN
7 (SELECT '<name>' || p.givenname || ' ' || p.surname || '</name><address>' ||
8 a.street || ' ' || a.street_number || '</address>' AS name_and_address
9 FROM people_address pa, people p, address a
10 WHERE pa.ROWID = p_rid
11 AND pa.people_id = p.id
12 AND pa.street_id = a.id)
13 LOOP
14 DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r.name_and_address), r.name_and_address);
15 END LOOP;
16 END test_proc;
17 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
-- example of what above procedure produces:
SCOTT@orcl_12.1.0.2.0> DECLARE
2 v_clob CLOB;
3 BEGIN
4 FOR r IN (SELECT ROWID rid FROM people_address) LOOP
5 DBMS_LOB.CREATETEMPORARY (v_clob, FALSE);
6 test_proc (r.rid, v_clob);
7 DBMS_OUTPUT.PUT_LINE (v_clob);
8 DBMS_LOB.FREETEMPORARY (v_clob);
9 END LOOP;
10 END;
11 /
<name>Hans Meier</name><address>Lukas-Meier-Str 23</address>
<name>Frauke Huber</name><address>Hauptstr 4</address>
<name>Werner Krause-Meier</name><address>Bahnhofstr 11</address>
PL/SQL procedure successfully completed.
-- user datastore that uses above procedure
-- and automatic section group for searching within tags
SCOTT@orcl_12.1.0.2.0> BEGIN
2 CTX_DDL.CREATE_PREFERENCE ('test_ds', 'USER_DATASTORE');
3 CTX_DDL.SET_ATTRIBUTE ('test_ds', 'PROCEDURE', 'test_proc');
4 CTX_DDL.CREATE_SECTION_GROUP ('test_sg', 'AUTO_SECTION_GROUP');
5 END;
6 /
PL/SQL procedure successfully completed.
-- dummy column to create index on (index will be updated whenever the dummy column is updated):
SCOTT@orcl_12.1.0.2.0> ALTER TABLE people_address ADD (dummy VARCHAR2(1))
2 /
Table altered.
-- Oracle Text CONTEXT index on dummy column using datastore and section group:
SCOTT@orcl_12.1.0.2.0> CREATE INDEX test_idx ON people_address (dummy)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS
4 ('DATASTORE test_ds
5 SECTION GROUP test_sg
6 SYNC (ON COMMIT)')
7 /
Index created.
-- tokens that are indexed as a result of index creation:
SCOTT@orcl_12.1.0.2.0> SELECT token_text FROM dr$test_idx$i ORDER BY token_text
2 /
TOKEN_TEXT
----------------------------------------------------------------
11
23
4
ADDRESS
BAHNHOFSTR
FRAUKE
HANS
HAUPTSTR
HUBER
KRAUSE
LUKAS
MEIER
NAME
STR
WERNER
15 rows selected.
-- search for Meier in name or address:
SCOTT@orcl_12.1.0.2.0> COLUMN "Result" FORMAT A50
SCOTT@orcl_12.1.0.2.0> SELECT SCORE(1) AS "Ranking",
2 p.givenname || ' ' || p.surname || ' ' || a.street || ' ' || a.street_number AS "Result"
3 FROM people_address pa, people p, address a
4 WHERE CONTAINS (pa.dummy, 'Meier', 1) > 0
5 AND pa.people_id = p.id
6 AND pa.street_id = a.id
7 ORDER BY SCORE(1) DESC
8 /
Ranking Result
---------- --------------------------------------------------
7 Hans Meier Lukas-Meier-Str 23
4 Werner Krause-Meier Bahnhofstr 11
2 rows selected.
-- search for Meier in name:
SCOTT@orcl_12.1.0.2.0> SELECT SCORE(1) AS "Ranking",
2 p.givenname || ' ' || p.surname || ' ' || a.street || ' ' || a.street_number AS "Result"
3 FROM people_address pa, people p, address a
4 WHERE CONTAINS (pa.dummy, 'Meier WITHIN name', 1) > 0
5 AND pa.people_id = p.id
6 AND pa.street_id = a.id
7 ORDER BY SCORE(1) DESC
8 /
Ranking Result
---------- --------------------------------------------------
4 Werner Krause-Meier Bahnhofstr 11
4 Hans Meier Lukas-Meier-Str 23
2 rows selected.
-- search for Meier in address:
SCOTT@orcl_12.1.0.2.0> SELECT SCORE(1) AS "Ranking",
2 p.givenname || ' ' || p.surname || ' ' || a.street || ' ' || a.street_number AS "Result"
3 FROM people_address pa, people p, address a
4 WHERE CONTAINS (pa.dummy, 'Meier WITHIN address', 1) > 0
5 AND pa.people_id = p.id
6 AND pa.street_id = a.id
7 ORDER BY SCORE(1) DESC
8 /
Ranking Result
---------- --------------------------------------------------
4 Hans Meier Lukas-Meier-Str 23
1 row selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:55:40 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
