Home » Server Options » Text & interMedia » How to create a single text index to search multiple tables with multiple columns as CLOB data type
 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  |
|
|
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
|
|
|
|
| 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: 7668
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>
|
|
|
|
| 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: 7668
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
|
|
|
|
| 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: 7668
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>
|
|
|
|
|
|
Goto Forum:
Current Time: Wed May 22 10:52:19 CDT 2013
Total time taken to generate the page: 0.12048 seconds
|
] 
Members
Search
Help
Register
Login
Home
