Home » Server Options » Text & interMedia » Search over multiple Columns and Tables possible?
Search over multiple Columns and Tables possible? [message #655836] Wed, 14 September 2016 03:38 Go to next message
GBuchner
Messages: 2
Registered: September 2016
Junior Member
Hi all,

i am new here and from Germany, so please be patient with my english Smile

We try to implement a fulltext search with a ranking but we are not able to set Oracle Text to our needs. So i will discribe what we want to do and it would be great if u could tell us, if this is possible and how.

There are some tables like:
Table People
ID      Givenname            Surename      
------- -------------------- ----------------
1       Hans                 Meier
2       Frauke               Huber
3       Werner               Krause-Meier

Table Adress
ID      Street               Street_Number
------- -------------------- ----------------
1       Hauptstr             4
2       Lukas-Meier-Str      23
3       Bahnhofstr           11

Table People_Adress
People_ID     Street_ID
------------- --------------
1             2
2             1
3             3

Now we want to search "Meier".

The result should be like:
Ranking Result
------- -------------------------
10      Hans Meier Lukas-Meier-Str 23        // ranking 10 because "Meier" is found in People and Adress
5       Werner Krause-Meier Bahnhofstr 11    // ranking 6 because "Meier" is only found in People

In real, there are more tables and columns per table and of course dependencies. Before we get deeper, we need to know if this is possible with Oracle Text.

Re: Search over multiple Columns and Tables possible? [message #655862 is a reply to message #655836] Wed, 14 September 2016 17:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.
Re: Search over multiple Columns and Tables possible? [message #655874 is a reply to message #655862] Thu, 15 September 2016 01:44 Go to previous message
GBuchner
Messages: 2
Registered: September 2016
Junior Member
Hi Barbara,

thanks a lot for that detailed description. This should help us and i will try to reproduce the shown way with our real tables.

Thanks again for your help!!!

Bye
Gerhard

[Updated on: Thu, 15 September 2016 02:09]

Report message to a moderator

Previous Topic: Create Index for CLOB-datatype
Next Topic: Full Text Search & ifilter for .docx
Goto Forum:
  


Current Time: Tue Mar 19 06:19:51 CDT 2024