Home » Server Options » Text & interMedia » Oracle text concatenated datastore
Re: Oracle text concatenated datastore [message #437695 is a reply to message #437691] Tue, 05 January 2010 10:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
The MARKER maintenance column is required for a meterialized view that uses UNION ALL to be able to fast refresh. Please see the links below to the documentation sections.

Restrictions on Fast Refresh on Materialized Views with UNION ALL
http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/basicmv.htm#CACBHGIJ

UNION ALL MARKER
http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/qradv.htm#BABEAHIA
Re: Oracle text concatenated datastore [message #438945 is a reply to message #437695] Wed, 13 January 2010 03:06 Go to previous messageGo to next message
rozora
Messages: 9
Registered: January 2010
Junior Member
Hi Barbara ,
thanks for all the help, developers were pleased with both solutions ,it look like we'll use the user_datastore solution after all.
i now have to index another set of data, this time one table containing few varchar columns and one column containing a path for text document on the file system.
is there a way to combine multi_column_datastore with file_datastore so that i can create one index for both documents and their metadata?
Re: Oracle text concatenated datastore [message #439177 is a reply to message #438945] Thu, 14 January 2010 15:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
As far as I know, you cannot combine multiptle datastores in one index. However, you can combine the data from multiple text columns with data from the file system, based on columns containing the directory object and filename. You would use a user_datastore with a procedure as already planned and just add a section to the procedure to append the file data. I have provided a minimal example below. You will need to test for efficiency and consider adding routines for things like checking whether the file exists and consider synchronization and optimization.

-- contents of c:\oracle11g\test1.dat:
this is test 1


-- content of c:\oracle11g\test2.dat:
 
this is test 2


SCOTT@orcl_11g> CREATE TABLE test_tab
  2    (id	   NUMBER,
  3  	text_col1  VARCHAR2 (10),
  4  	text_col2  VARCHAR2 (10),
  5  	dir_col    VARCHAR2 (30),
  6  	path_col   VARCHAR2 (30),
  7  	all_cols   VARCHAR2 ( 1))
  8  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY test_dir AS 'c:\oracle11g'
  2  /

Directory created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO test_tab VALUES (1, 'test one', null, null, null, null)
  3  INTO test_tab VALUES (2, null, 'test two', null, null, null)
  4  INTO test_tab VALUES (3, 'test three', 'test four', null, null, null)
  5  INTO test_tab VALUES (4, null, null, 'TEST_DIR', 'test1.dat', null)
  6  INTO test_tab VALUES (5, null, null, 'TEST_DIR', 'test2.dat', null)
  7  INTO test_tab VALUES (6, null, null, null, null, null)
  8  SELECT * FROM DUAL
  9  /

6 rows created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE test_proc
  2    (p_rowid IN ROWID,
  3  	p_clob	IN OUT NOCOPY CLOB)
  4  AS
  5    v_clob	   CLOB;
  6    v_bfile	   BFILE;
  7  BEGIN
  8    FOR r IN (SELECT * FROM test_tab WHERE ROWID = p_rowid) LOOP
  9  	 DBMS_LOB.WRITEAPPEND (p_clob, NVL (LENGTH (r.text_col1), 0) + 1, r.text_col1 || CHR (10));
 10  	 DBMS_LOB.WRITEAPPEND (p_clob, NVL (LENGTH (r.text_col2), 0) + 1, r.text_col2 || CHR (10));
 11  	 IF r.dir_col IS NOT NULL AND r.path_col IS NOT NULL THEN
 12  	   v_bfile := BFILENAME (r.dir_col, r.path_col);
 13  	   DBMS_LOB.OPEN (v_bfile);
 14  	   DBMS_LOB.CREATETEMPORARY (v_clob, TRUE);
 15  	   DBMS_LOB.LOADFROMFILE (v_clob, v_bfile, DBMS_LOB.GETLENGTH (v_bfile));
 16  	   DBMS_LOB.CLOSE (v_bfile);
 17  	   DBMS_LOB.APPEND (p_clob, v_clob);
 18  	   DBMS_LOB.FREETEMPORARY (v_clob);
 19  	 END IF;
 20    END LOOP;
 21  END test_proc;
 22  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_datastore', 'USER_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('test_datastore', 'PROCEDURE', 'test_proc');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX test_idx ON test_tab (all_cols)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('DATASTORE test_datastore')
  4  /

Index created.

SCOTT@orcl_11g> EXEC CTX_DOC.SET_KEY_TYPE ('ROWID')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT id, CTX_DOC.SNIPPET ('test_idx', ROWID, 'test')
  2  FROM   test_tab
  3  WHERE  CONTAINS (all_colS, 'test') > 0
  4  /

        ID
----------
CTX_DOC.SNIPPET('TEST_IDX',ROWID,'TEST')
--------------------------------------------------------------------------------
         1
<b>test</b> one


         2
<b>test</b> two

         3
<b>test</b> three
<b>test</b> four

         4
this is <b>test</b> 1

         5
this is <b>test</b> 2


SCOTT@orcl_11g> 
SCOTT@orcl_11g>

Re: Oracle text concatenated datastore [message #439942 is a reply to message #439177] Wed, 20 January 2010 05:16 Go to previous messageGo to next message
rozora
Messages: 9
Registered: January 2010
Junior Member
thanks,
not sure why but :
EXEC CTX_DOC.SET_KEY_TYPE ('ROWID')
didn't work - it complained about illegal sql.

by the way since my table is quite large ~2million documents,
is there a way to create the index and allow users to query/dml the table while index created - i'm looking for something like populate pending, or create online, i tried this options but they still prevented me from query the table.
Re: Oracle text concatenated datastore [message #440058 is a reply to message #439942] Wed, 20 January 2010 15:17 Go to previous message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Quote:

not sure why but :
EXEC CTX_DOC.SET_KEY_TYPE ('ROWID')
didn't work - it complained about illegal sql.


What are you executing from? I was executing from SQL*Plus. EXEC is a SQL*Plus command. If executing from something that expects only SQL or PL/SQL, then remove the EXEC and put it in a PL/SQL block like so:

BEGIN 
  CTX_DOC.SET_KEY_TYPE ('ROWID'); 
END;
/



Quote:

by the way since my table is quite large ~2million documents,
is there a way to create the index and allow users to query/dml the table while index created - i'm looking for something like populate pending, or create online, i tried this options but they still prevented me from query the table.


Here is waht the 11g documentation says about the ONLINE option:

ONLINE

    Creates the index while enabling DML inserts/updates/deletes on the base table.

    During indexing, Oracle Text enqueues DML requests in a pending queue. At the end of the index creation, Oracle Text locks the base table. During this time DML is blocked. You must synchronize the index in order for DML changes to be available.

Limitations

The following limitations apply to using ONLINE:

    *

      At the very beginning or very end of the ONLINE process, DML might fail.
    *

      ONLINE is supported for CONTEXT indexes only.

Previous Topic: Highlighting search results with user_datastore context index
Next Topic: Wildcard Seraches Using CONTAINS
Goto Forum:
  


Current Time: Mon Jan 20 13:01:52 CST 2020