Home » Server Options » Text & interMedia » Can you find which section_group matched in a multi_column_datastore? (Oracle10g, Oracle-XE)
Can you find which section_group matched in a multi_column_datastore? [message #279035] Tue, 06 November 2007 12:31 Go to next message
gurunandan
Messages: 5
Registered: November 2007
Junior Member
I have a created a MULTI_COLUMN_DATASTORE with each column in a separate SECTION. If I search across all columns in the datastore (without using a WITHIN), isit possible to tell which column/section matched?

Thank you for your attention
Re: Can you find which section_group matched in a multi_column_datastore? [message #305700 is a reply to message #279035] Tue, 11 March 2008 10:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I know this is a little late, but I thought I would post it in case you are still looking for an answer or anybody else searches for the same thing.

-- test data:
SCOTT@orcl_11g> CREATE Table your_table
  2    (col1  VARCHAR2(10),
  3  	col2  VARCHAR2(10),
  4  	col3  VARCHAR2(10))
  5  /

Table created.

SCOTT@orcl_11g> BEGIN
  2    INSERT INTO your_table VALUES ('test1 test', NULL, NULL);
  3    INSERT INTO your_table VALUES (NULL, 'test2 test', NULL);
  4    INSERT INTO your_table VALUES (NULL, NULL, 'test3 test');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('your_datastore', 'MULTI_COLUMN_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('your_datastore', 'COLUMNS', 'col1, col2, col3');
  4    CTX_DDL.CREATE_SECTION_GROUP ('your_sec_group', 'BASIC_SECTION_GROUP');
  5    CTX_DDL.ADD_FIELD_SECTION ('your_sec_group', 'column1', 'col1', TRUE);
  6    CTX_DDL.ADD_FIELD_SECTION ('your_sec_group', 'column2', 'col2', TRUE);
  7    CTX_DDL.ADD_FIELD_SECTION ('your_sec_group', 'column3', 'col3', TRUE);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX your_index ON your_table (col1)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('DATASTORE	your_datastore
  5  	 SECTION GROUP	your_sec_group
  6  	 STOPLIST	CTXSYS.EMPTY_STOPLIST')
  7  /

Index created.


-- function for usage in extracting information:
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION list_element
  2  	(p_string    VARCHAR2,
  3  	 p_element   INTEGER,
  4  	 p_separator VARCHAR2 DEFAULT ':')
  5  	RETURN	     VARCHAR2
  6  AS
  7    v_string      VARCHAR2 (32767);
  8  BEGIN
  9    v_string := p_string || p_separator;
 10    FOR i IN 1 .. p_element - 1 LOOP
 11  	 v_string := SUBSTR (v_string,
 12  			     INSTR (v_string, p_separator)
 13  			       + LENGTH (p_separator));
 14    END LOOP;
 15    RETURN SUBSTR (v_string, 1, INSTR (v_string, p_separator) - 1);
 16  END list_element;
 17  /

Function created.


-- where the information comes from:
SCOTT@orcl_11g> SELECT t.*, ROWID FROM your_table t
  2  /

COL1       COL2       COL3       ROWID
---------- ---------- ---------- ------------------
test1 test                       AAAYHcAAEAAAyoYAAA
           test2 test            AAAYHcAAEAAAyoYAAB
                      test3 test AAAYHcAAEAAAyoYAAC

SCOTT@orcl_11g> COLUMN token_text FORMAT A15
SCOTT@orcl_11g> COLUMN token_type FORMAT 9999999999
SCOTT@orcl_11g> SELECT token_text, token_type, token_first, token_last
  2  FROM   dr$your_index$i
  3  /

TOKEN_TEXT       TOKEN_TYPE TOKEN_FIRST TOKEN_LAST
--------------- ----------- ----------- ----------
TEST                      0           1          3
TEST                     16           1          1
TEST                     17           2          2
TEST                     18           3          3
TEST1                     0           1          1
TEST1                    16           1          1
TEST2                     0           2          2
TEST2                    17           2          2
TEST3                     0           3          3
TEST3                    18           3          3

10 rows selected.

SCOTT@orcl_11g> SELECT * FROM dr$your_index$k
  2  /

     DOCID TEXTKEY
---------- ------------------
         1 AAAYHcAAEAAAyoYAAA
         2 AAAYHcAAEAAAyoYAAB
         3 AAAYHcAAEAAAyoYAAC

SCOTT@orcl_11g> COLUMN ixv_value FORMAT A20
SCOTT@orcl_11g> COLUMN from_column FORMAT A15
SCOTT@orcl_11g> SELECT ixv_value,
  2  	    list_element (ixv_value, 2) AS from_column,
  3  	    TO_NUMBER (list_element (ixv_value, 3 )) AS token_type
  4  FROM   ctx_user_index_values
  5  WHERE  ixv_index_name = 'YOUR_INDEX'
  6  AND    ixv_class = 'SECTION_GROUP'
  7  /

IXV_VALUE            FROM_COLUMN      TOKEN_TYPE
-------------------- --------------- -----------
COLUMN3:COL3:18:Y    COL3                     18
COLUMN1:COL1:16:Y    COL1                     16
COLUMN2:COL2:17:Y    COL2                     17


-- queries:
SCOTT@orcl_11g> SELECT t.*,
  2  	    i.token_text,
  3  	    list_element (v.ixv_value, 2) AS from_column
  4  FROM   your_table t, dr$your_index$i i, dr$your_index$k k, ctx_user_index_values v
  5  WHERE  CONTAINS (t.col1, 'test1 OR test2 OR test3') > 0
  6  AND    i.token_type = TO_NUMBER (list_element (v.ixv_value, 3))
  7  AND    k.docid BETWEEN i.token_first AND i.token_last
  8  AND    k.textkey = t.ROWID
  9  /

COL1       COL2       COL3       TOKEN_TEXT      FROM_COLUMN
---------- ---------- ---------- --------------- ---------------
                      test3 test TEST3           COL3
                      test3 test TEST            COL3
           test2 test            TEST2           COL2
           test2 test            TEST            COL2
test1 test                       TEST1           COL1
test1 test                       TEST            COL1

6 rows selected.

SCOTT@orcl_11g> SELECT t.*,
  2  	    i.token_text,
  3  	    list_element (v.ixv_value, 2) AS from_column
  4  FROM   your_table t, dr$your_index$i i, dr$your_index$k k, ctx_user_index_values v
  5  WHERE  CONTAINS (t.col1, 'test2') > 0
  6  AND    i.token_text = 'TEST2'
  7  AND    i.token_type = TO_NUMBER (list_element (v.ixv_value, 3))
  8  AND    k.docid BETWEEN i.token_first AND i.token_last
  9  AND    k.textkey = t.ROWID
 10  /

COL1       COL2       COL3       TOKEN_TEXT      FROM_COLUMN
---------- ---------- ---------- --------------- ---------------
           test2 test            TEST2           COL2

SCOTT@orcl_11g> 


Re: Can you find which section_group matched in a multi_column_datastore? [message #305701 is a reply to message #279035] Tue, 11 March 2008 10:34 Go to previous messageGo to next message
gurunandan
Messages: 5
Registered: November 2007
Junior Member
Thank you Barbara. Your help is valuable and as always comprehensive.
Re: Can you find which section_group matched in a multi_column_datastore? [message #609475 is a reply to message #305701] Fri, 07 March 2014 03:33 Go to previous messageGo to next message
ariton
Messages: 5
Registered: February 2007
Location: Bucharest / Romania
Junior Member

Hello,

How can I cover the same approach for an index with stored procedure datastore and XML clob for each value.

The clob are dinamically created using master- details tables
Ex: <animal>test test1</animal><name>test test2</name>
<animal>test test3</animal><name>test4</name>

When I looking for test - I want the this result:

Value category
test animal
test name

When I looking for test1

Value category
test1 animal
Re: Can you find which section_group matched in a multi_column_datastore? [message #609504 is a reply to message #609475] Fri, 07 March 2014 14:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The method previously provided in this thread is horribly inefficient. The only reason it was provided was because the original poster requested a method that did not use WITHIN, for some unknown reason. Also, that method is unsupported and no longer works in Oracle 12c, due to changes in how the data is stored in the ctx_user_index_values table. The following is a more typical method:

SCOTT@orcl> -- Supposing that you have tables and data like this:
SCOTT@orcl> CREATE TABLE master
  2    (id	NUMBER PRIMARY KEY,
  3  	animal	VARCHAR2(30),
  4  	dummy	VARCHAR2(1))
  5  /

Table created.

SCOTT@orcl> INSERT ALL
  2  INTO master VALUES (1, 'test test1', NULL)
  3  INTO master VALUES (2, 'test test3', NULL)
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl> CREATE TABLE detail
  2    (id	NUMBER REFERENCES master (id),
  3  	name	VARCHAR2(30))
  4  /

Table created.

SCOTT@orcl> INSERT ALL
  2  INTO detail VALUES (1, 'test test2')
  3  INTO detail VALUES (2, 'test4')
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl> -- and you have a procedure like this:
SCOTT@orcl> CREATE OR REPLACE PROCEDURE test_proc
  2    (p_rowid IN	      ROWID,
  3  	p_clob	IN OUT NOCOPY CLOB)
  4  AS
  5  BEGIN
  6    FOR r1 IN
  7  	 (SELECT * FROM master WHERE ROWID = p_rowid)
  8    LOOP
  9  	 DBMS_LOB.WRITEAPPEND (p_clob, 8, '<animal>');
 10  	 DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.animal), r1.animal);
 11  	 DBMS_LOB.WRITEAPPEND (p_clob, 9, '</animal>');
 12  	 FOR r2 IN
 13  	   (SELECT * FROM detail WHERE id = r1.id)
 14  	 LOOP
 15  	   DBMS_LOB.WRITEAPPEND (p_clob, 6, '<name>');
 16  	   DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r2.name), r2.name);
 17  	   DBMS_LOB.WRITEAPPEND (p_clob, 7, '</name>');
 18  	 END LOOP;
 19    END LOOP;
 20  END test_proc;
 21  /

Procedure created.

SCOTT@orcl> SHOW ERRORS
No errors.
SCOTT@orcl> -- that produces xml clobs like this:
SCOTT@orcl> DECLARE
  2    v_clob  CLOB;
  3  BEGIN
  4    FOR r1 IN
  5  	 (SELECT ROWID FROM master)
  6    LOOP
  7  	 DBMS_LOB.CREATETEMPORARY (v_clob, FALSE);
  8  	 test_proc (r1.ROWID, v_clob);
  9  	 DBMS_OUTPUT.PUT_LINE (v_clob);
 10  	 DBMS_LOB.FREETEMPORARY (v_clob);
 11    END LOOP;
 12  END;
 13  /
<animal>test test1</animal><name>test test2</name>
<animal>test test3</animal><name>test4</name>

PL/SQL procedure successfully completed.

SCOTT@orcl> -- and you have a user_datastore that uses that procedure like this:
SCOTT@orcl> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_ds', 'USER_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('test_ds', 'PROCEDURE', 'test_proc');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl> -- and you have a text index that uses that datastore
SCOTT@orcl> -- and some kind of section group like this:
SCOTT@orcl> CREATE INDEX your_index ON master (dummy)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('DATASTORE	test_ds
  5  	 SECTION GROUP	CTXSYS.AUTO_SECTION_GROUP')
  6  /

Index created.

SCOTT@orcl> -- then you can get the values and categories accessing the index like this:
SCOTT@orcl> VARIABLE search_string VARCHAR2(30)
SCOTT@orcl> EXEC :search_string := 'test'

PL/SQL procedure successfully completed.

SCOTT@orcl> COLUMN "Value"    FORMAT A30
SCOTT@orcl> COLUMN "category" FORMAT A30
SCOTT@orcl> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl> SELECT :search_string "Value",
  2  	    'animal'	   "category"
  3  FROM   master
  4  WHERE  CONTAINS (dummy, :search_string || ' WITHIN animal') > 0
  5  UNION
  6  SELECT :search_string "Value",
  7  	    'name'	   "category"
  8  FROM   master
  9  WHERE  CONTAINS (dummy, :search_string || ' WITHIN name') > 0
 10  /

Value                          category
------------------------------ ------------------------------
test                           animal
test                           name

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1954242363

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     2 |    28 |    10  (60)| 00:00:01 |
|   1 |  SORT UNIQUE     |            |     2 |    28 |    10  (60)| 00:00:01 |
|   2 |   UNION-ALL      |            |       |       |            |          |
|*  3 |    DOMAIN INDEX  | YOUR_INDEX |     1 |    14 |     4   (0)| 00:00:01 |
|*  4 |    DOMAIN INDEX  | YOUR_INDEX |     1 |    14 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CTXSYS"."CONTAINS"("DUMMY",:SEARCH_STRING||' WITHIN
              animal')>0)
   4 - access("CTXSYS"."CONTAINS"("DUMMY",:SEARCH_STRING||' WITHIN
              name')>0)

Note
-----
   - dynamic sampling used for this statement (level=2)

SCOTT@orcl> EXEC :search_string := 'test2'

PL/SQL procedure successfully completed.

SCOTT@orcl> /

Value                          category
------------------------------ ------------------------------
test2                          name

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1954242363

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     2 |    28 |    10  (60)| 00:00:01 |
|   1 |  SORT UNIQUE     |            |     2 |    28 |    10  (60)| 00:00:01 |
|   2 |   UNION-ALL      |            |       |       |            |          |
|*  3 |    DOMAIN INDEX  | YOUR_INDEX |     1 |    14 |     4   (0)| 00:00:01 |
|*  4 |    DOMAIN INDEX  | YOUR_INDEX |     1 |    14 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CTXSYS"."CONTAINS"("DUMMY",:SEARCH_STRING||' WITHIN
              animal')>0)
   4 - access("CTXSYS"."CONTAINS"("DUMMY",:SEARCH_STRING||' WITHIN
              name')>0)

Note
-----
   - dynamic sampling used for this statement (level=2)

SCOTT@orcl>

Re: Can you find which section_group matched in a multi_column_datastore? [message #609506 is a reply to message #609504] Fri, 07 March 2014 16:56 Go to previous message
ariton
Messages: 5
Registered: February 2007
Location: Bucharest / Romania
Junior Member


Thank you very much, Barbara, you are great as always
Previous Topic: contains
Next Topic: Domain index DEFINESCORE
Goto Forum:
  


Current Time: Fri Mar 29 08:26:22 CDT 2024