OraFAQ Forum: Text & interMedia » How to create a single text index to search multiple tables with multiple columns as CLOB data type

Home » Server Options » Text & interMedia » How to create a single text index to search multiple tables with multiple columns as CLOB data type

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to create a single text index to search multiple tables with multiple columns as CLOB data type [message #158638]

Mon, 13 February 2006 13:03

garywang
Messages: 2
Registered: February 2006
Location: Richmond, VA

Junior Member

Hello, everyone,

I have a problem: I need to create a single text index to search the columns (data type is CLOB) cross multiple tables (about 50 tables) in the whole database in 10G.
I can create a sinlge text index cross multiple columns in a single table by using a dummy column. But I didn't find any information on how to create a single text index to cross the multiple table, nevertheless, one more requirement is the search result should be the tablename.column instead of the column content.

Anyone worked on the topic before? any clues? or any suggestions I can follow? I read the "ORACLE TEXT REFERENCE 10G" and "ORACLE TEXT APPLICATION DEVELOPER 10G", I didn't find any useful information.

I highly appreciate any response.

Regards,

Gary

Report message to a moderator

Re: How to create a single text index to search multiple tables with multiple columns as CLOB data t [message #158650 is a reply to message #158638]

Mon, 13 February 2006 15:56

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

scott@ORA92> CREATE TABLE tab1 (col1 CLOB, col2 CLOB)
  2  /

Table created.

scott@ORA92> INSERT ALL
  2  INTO tab1 VALUES ('abc', 'def')
  3  INTO tab1 VALUES ('mno', 'pqr')
  4  SELECT * FROM DUAL
  5  /

2 rows created.

scott@ORA92> CREATE TABLE tab2 (col3 CLOB, col4 CLOB)
  2  /

Table created.

scott@ORA92> INSERT ALL
  2  INTO tab2 VALUES ('ghi', 'jkl')
  3  INTO tab2 VALUES ('stu', 'vwx')
  4  SELECT * FROM DUAL
  5  /

2 rows created.

scott@ORA92> CREATE MATERIALIZED VIEW tabs_cols_clobs AS
  2  SELECT 'tab1' AS tab, 'col1' AS col, col1 AS clobs FROM tab1
  3  UNION ALL
  4  SELECT 'tab1' AS tab, 'col2' AS col, col2 AS clobs FROM tab1
  5  UNION ALL
  6  SELECT 'tab2' AS tab, 'col3' AS col, col3 AS clobs FROM tab2
  7  UNION ALL
  8  SELECT 'tab2' AS tab, 'col4' AS col, col4 AS clobs FROM tab2
  9  -- and so on for each column of each table
 10  /

Materialized view created.

scott@ORA92> COLUMN clobs FORMAT A30
scott@ORA92> SELECT * FROM tabs_cols_clobs
  2  /

TAB  COL  CLOBS
---- ---- ------------------------------
tab1 col1 abc
tab1 col1 mno
tab1 col2 def
tab1 col2 pqr
tab2 col3 ghi
tab2 col3 stu
tab2 col4 jkl
tab2 col4 vwx

8 rows selected.

scott@ORA92> CREATE INDEX tabs_cols_clobs_idx
  2  ON tabs_cols_clobs (clobs)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.

scott@ORA92> SELECT tab, col
  2  FROM   tabs_cols_clobs
  3  WHERE  CONTAINS (clobs, 'vwx') > 0
  4  /

TAB  COL
---- ----
tab2 col4

scott@ORA92>

Report message to a moderator

Re: How to create a single text index to search multiple tables with multiple columns as CLOB data t [message #158668 is a reply to message #158650]

Mon, 13 February 2006 18:30

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

The following was received by private message:

Thank you so much, Barbara.

Your example code works perfect.

I still have two questions:

1. In your install statement, for example, you use:
==================================
INSERT ALL
INTO tab1 VALUES ('abc', 'def')
INTO tab1 VALUES ('mno', 'pqr')
SELECT * FROM DUAL;

==================================

In mysense, 'abc', 'def', 'mno' and 'pqr' are good data for clob data type, why you use "SELECT * FROM DUAL"?
if I don't use "SELECT * FROM DUAL", the data still can be insert well, is there any impact on the text index creation?

2. I have 50 tables in the database, and 40 of them have clob data type, Does it mean I need to create the materialized view that includes the clob columns in the 40 tables?

I appreciate your help so much.

Best Regards,

Gary

Report message to a moderator

Re: How to create a single text index to search multiple tables with multiple columns as CLOB data t [message #158669 is a reply to message #158668]

Mon, 13 February 2006 18:37

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Please continue all follow-up questions on this thread, rather than by private message.

You do not need to use "INSERT ALL ... SELECT * FROM DUAL". You can use separate insert statements if you like. It does not matter how your data is inserted. The syntax I used just inserts multiples rows at one time. You can search for "INSERT ALL" in the online documentaton for a more detailed description.

You will need to include all 40 clob columns in your materialized view. If you want to include other non-clob columns, then you wiill need to use TO_CLOB so that the datatypes match.

Please see the additional demonstration below.

scott@ORA92> CREATE TABLE tab1 (col1 CLOB, col2 varchar2(30))
  2  /

Table created.

scott@ORA92> INSERT INTO tab1 VALUES ('abc', 'def')
  2  /

1 row created.

scott@ORA92> INSERT INTO tab1 VALUES ('mno', 'pqr')
  2  /

1 row created.

scott@ORA92> CREATE TABLE tab2 (col3 CLOB, col4 CLOB)
  2  /

Table created.

scott@ORA92> INSERT INTO tab2 VALUES ('ghi', 'jkl')
  2  /

1 row created.

scott@ORA92> INSERT INTO tab2 VALUES ('stu', 'vwx')
  2  /

1 row created.

scott@ORA92> CREATE MATERIALIZED VIEW tabs_cols_clobs AS
  2  SELECT 'tab1' AS tab, 'col1' AS col, col1 AS clobs FROM tab1
  3  UNION ALL
  4  SELECT 'tab1' AS tab, 'col2' AS col, TO_CLOB (col2) AS clobs FROM tab1
  5  UNION ALL
  6  SELECT 'tab2' AS tab, 'col3' AS col, col3 AS clobs FROM tab2
  7  UNION ALL
  8  SELECT 'tab2' AS tab, 'col4' AS col, col4 AS clobs FROM tab2
  9  -- and so on for each column of each table
 10  /

Materialized view created.

scott@ORA92> DESC tabs_cols_clobs
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 TAB                                                            CHAR(4)
 COL                                                            CHAR(4)
 CLOBS                                                          CLOB

scott@ORA92> COLUMN clobs FORMAT A30
scott@ORA92> SELECT * FROM tabs_cols_clobs
  2  /

TAB  COL  CLOBS
---- ---- ------------------------------
tab1 col1 abc
tab1 col1 mno
tab1 col2 def
tab1 col2 pqr
tab2 col3 ghi
tab2 col3 stu
tab2 col4 jkl
tab2 col4 vwx

8 rows selected.

scott@ORA92> CREATE INDEX tabs_cols_clobs_idx
  2  ON tabs_cols_clobs (clobs)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.

scott@ORA92> SELECT tab, col
  2  FROM   tabs_cols_clobs
  3  WHERE  CONTAINS (clobs, 'vwx') > 0
  4  /

TAB  COL
---- ----
tab2 col4

scott@ORA92>

Report message to a moderator

Re: How to create a single text index to search multiple tables with multiple columns as CLOB data t [message #158790 is a reply to message #158669]

Tue, 14 February 2006 07:19

garywang
Messages: 2
Registered: February 2006
Location: Richmond, VA

Junior Member

Thank you very much, Barbara.

You solved my probelm totally.

Gary

Report message to a moderator

Previous Topic:	Size of Oracle's default thesaurus?
Next Topic:	DRG-10509: invalid text column:

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 20:16:22 CST 2024