Home » Server Options » Text & interMedia » Oracle Text using Contains (Oracle 10g)
Oracle Text using Contains [message #334636] Thu, 17 July 2008 08:18 Go to next message
Reychill
Messages: 3
Registered: July 2008
Location: India
Junior Member
Can oracle text be used on computed columns

[Edit MC: Removed Poll]

[Updated on: Thu, 17 July 2008 08:28] by Moderator

Report message to a moderator

Re: Oracle Text using Contains [message #334640 is a reply to message #334636] Thu, 17 July 2008 08:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I am not sure what you mean by a "computed column"? If you mean something that is calculated from other columns, then yes, if you either put that calculated value in a materialized view or user_datastore with a procedure that can then be tokenized, indexed, and searched. I have demonstrated both methods below.

-- materialized view:
SCOTT@orcl_11g> CREATE TABLE base_tab
  2    (col1  NUMBER,
  3  	col2  NUMBER,
  4  	col3  VARCHAR2 (30))
  5  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO base_tab VALUES (2, 3, 'widgets')
  3  INTO base_tab VALUES (3, 5, 'widgets')
  4  INTO base_tab VALUES (2, 3, 'gadgets')
  5  INTO base_tab VALUES (3, 5, 'gadgets')
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl_11g> CREATE MATERIALIZED VIEW mview AS
  2  SELECT (col1 * col2) || ' ' || col3 AS computed_col
  3  FROM   base_tab
  4  /

Materialized view created.

SCOTT@orcl_11g> CREATE INDEX test_idx ON mview (computed_col)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$test_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
15
6
GADGETS
WIDGETS

SCOTT@orcl_11g> SELECT * FROM mview WHERE CONTAINS (computed_col, '6 widgets') > 0
  2  /

COMPUTED_COL
-----------------------------------------------------------------------
6 widgets

SCOTT@orcl_11g> SELECT * FROM mview WHERE CONTAINS (computed_col, '15 gadgets') > 0
  2  /

COMPUTED_COL
-----------------------------------------------------------------------
15 gadgets

SCOTT@orcl_11g> 


-- user_datastore:
SCOTT@orcl_11g> CREATE TABLE base_tab
  2    (col1  NUMBER,
  3  	col2  NUMBER,
  4  	col3  VARCHAR2 (30),
  5  	computed_col VARCHAR2 (1))
  6  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO base_tab VALUES (2, 3, 'widgets', NULL)
  3  INTO base_tab VALUES (3, 5, 'widgets', NULL)
  4  INTO base_tab VALUES (2, 3, 'gadgets', NULL)
  5  INTO base_tab VALUES (3, 5, 'gadgets', NULL)
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE compute_col
  2    (p_rowid IN ROWID,
  3  	p_clob	IN OUT NOCOPY CLOB)
  4  AS
  5  BEGIN
  6    SELECT (col1 * col2) || ' ' || col3 AS computed_col
  7    INTO   p_clob
  8    FROM   base_tab
  9    WHERE  ROWID = p_rowid;
 10  END compute_col;
 11  /

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', 'compute_col');
  4  END;
  5  /

PL/SQL procedure successfully completed.

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

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$test_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
15
6
GADGETS
WIDGETS

SCOTT@orcl_11g> SELECT * FROM base_tab WHERE CONTAINS (computed_col, '6 widgets') > 0
  2  /

      COL1       COL2 COL3                           C
---------- ---------- ------------------------------ -
         2          3 widgets

SCOTT@orcl_11g> SELECT * FROM base_tab WHERE CONTAINS (computed_col, '15 gadgets') > 0
  2  /

      COL1       COL2 COL3                           C
---------- ---------- ------------------------------ -
         3          5 gadgets

SCOTT@orcl_11g> 




Re: Oracle Text using Contains [message #334819 is a reply to message #334640] Fri, 18 July 2008 04:01 Go to previous messageGo to next message
Reychill
Messages: 3
Registered: July 2008
Location: India
Junior Member
Thanks Barbara for your help.
I will explain you my scenario:

The scema for table is

CREATE TABLE ECN_DESCRIPTION
(	"ECN_ID" NUMBER(38,0) NOT NULL ENABLE, 
	"ECN_DESCRIPTION_TYPE" NUMBER(2,0) NOT NULL ENABLE, 
	"BLANK_FLAG" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE, 
	"DESCRIPTION" VARCHAR2(4000 BYTE), 
	 CONSTRAINT "PK_ECN_DESCRIPTION" PRIMARY KEY ("ECN_ID", "ECN_DESCRIPTION_TYPE") 	 
) ;

CREATE INDEX ECNSRCH ON ECN_DESCRIPTION ("DESCRIPTION") 
INDEXTYPE IS "CTXSYS"."CONTEXT" ;


Query:

 Select distinct ecn_id FROM

              (Select ecn_id,

              (select description from ecn_description where ecn_description_type = 1 and ecn_id = ecndesc.ecn_id) "Change Description",

              (select description from ecn_description where ecn_description_type = 2 and ecn_id = ecndesc.ecn_id) "Change Reason",

              (select description from ecn_description where ecn_description_type = 3 and ecn_id = ecndesc.ecn_id) "Test Approach",

              (select description from ecn_description where ecn_description_type = 4 and ecn_id = ecndesc.ecn_id) "Implementation Notes",

              (select title from environment_change_note where environment_change_note.ecn_id = ecndesc.ecn_id) "Title"

              from ecn_description ecndesc)
         where CONTAINS("Change Description", '%b%')  > 0


Now I am having an index on Description column but in query I m using Contains on "Change Description" which is computed column from description andecn_description_type.

I get the error as
ORA-20000: Oracle Text error
DRG-10599: Column is not indexed

Now give me the solution to this.



[mod-edit: bb added code tags]

[Updated on: Fri, 18 July 2008 12:10] by Moderator

Report message to a moderator

Re: Oracle Text using Contains [message #334930 is a reply to message #334819] Fri, 18 July 2008 13:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The first parameter of the contains operator must be a column that has a text index created on it, not a column alias for a column from a correlated sub-query. So, you need to somehow move the contains clause into a query that has the actual indexed column, for example:

Select distinct ecn_id 
FROM   (Select ecn_id,
               (select description 
                from   ecn_description 
                where  CONTAINS (description, '%b%') > 0
                and    ecn_description_type = 1 
                and    ecn_id = ecndesc.ecn_id) "Change Description",
               (select description 
                from   ecn_description 
                where  ecn_description_type = 2 
                and    ecn_id = ecndesc.ecn_id) "Change Reason",
               (select description 
                from   ecn_description 
                where  ecn_description_type = 3 
                and    ecn_id = ecndesc.ecn_id) "Test Approach",
               (select description 
                from   ecn_description 
                where  ecn_description_type = 4 
                and    ecn_id = ecndesc.ecn_id) "Implementation Notes"
               (select title 
                from   environment_change_note 
                where  environment_change_note.ecn_id = ecndesc.ecn_id) "Title"
        from   ecn_description ecndesc)
/


Although this should eliminate the error, it may not be the most efficient method for getting the result you want. I would need to see a create table statement for your environment_change_note table and some insert statements for sample data and an example of the results that you want based on that data. If all you want is the distinct ecn_id, then you might be better off with a where exists clause. If you want the other values that you have calculated then this is more of a pivot for which you would typically use an aggregate function and decode. If most of your searches use prefix and suffix wildcards, then you should also include a substring index in your context index parameters. Please read the forum guidelines for what information we expect when posting questions.



Re: Oracle Text using Contains [message #335237 is a reply to message #334930] Mon, 21 July 2008 09:35 Go to previous messageGo to next message
Reychill
Messages: 3
Registered: July 2008
Location: India
Junior Member
Thanks Barbara for the help.
I am using materialized view.
For refreshing the materialized view have used the ON Demand.
Executing DBMS_MVIEW.REFRESH procedure.
This procedure updates all of the related indexes on a materialized view, is there any way
by which I can restrict refreshing of indexes?
Re: Oracle Text using Contains [message #335819 is a reply to message #335237] Wed, 23 July 2008 18:14 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Reychill wrote on Mon, 21 July 2008 07:35
Thanks Barbara for the help.
I am using materialized view.
For refreshing the materialized view have used the ON Demand.
Executing DBMS_MVIEW.REFRESH procedure.
This procedure updates all of the related indexes on a materialized view, is there any way
by which I can restrict refreshing of indexes?



I don't understand what you are asking. If you are selecting from a materialized view, then you must create your context index on the column in the materialized view, not the column in the table that the materialized view is selecting from. I don't understand why you want to "restrict" (prevent?) refreshing of indexes. There are various commands for refreshing of materialized views and various commands for synchronizing text indexes. Of course if you want your index current, then your materialized view must be current as well. Here is an example that uses refresh fast on commit for a materialized view and sync(on commit) for a text index on the view:

http://www.orafaq.com/forum/m/314509/43710/?srch=materialized+view+log#msg_314509

If this does not help, then please post a complete copy and paste of what you are doing, what you are getting, and what you want instead.
Previous Topic: Merge columns from dif tables in one index and markup the result
Next Topic: ctxsys creation in 10g
Goto Forum:
  


Current Time: Thu Mar 28 18:50:58 CDT 2024