Skip to Main Content
  • Questions
  • Using "Oracle Text" CONTAINS syntax on multiple columns

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Tom.

Asked: April 28, 2003 - 10:55 am UTC

Answered by: Tom Kyte - Last updated: January 26, 2012 - 10:05 am UTC

Category: Database - Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

We have a DB where users are able to search for text, using Oracle Text (I guess it's Intermedia in 8.1.7). Anyway, there are 3 columns that are indexed. Each of them is in a separate table. The user has a single field to type in their search criteria. It's great that the entire set of CONTAINS syntax is available to the users... with the '&" and '|' and '~' and all that (I just point them to the Intermedia docs). But, I realize now that this is not really giving them the functionality they think it is. When I pass through this WHERE clause (assume all the join conditions are there):

where contains(t1.varchar2_col1,'this & that',10) > 1
or contains(t2.blob_col1,'this & that',20) > 1
or contains(t3.blob_col2,'this & that',30) > 1

I realize I'm requiring "this" and "that" to occur together in at least one of the columns. What the users want is this condition ACROSS THE CONCATENATION OF ALL 3 COLUMNS. I.E. if "this" is in one of the columns and "that" is in one of the others, that result should be returned (as well as the case where both strings are indeed in a single column, of course).

Is there a way to get Intermedia to index in this fashion? I don't want to parse the contains syntax, because that's hard, and I'd have to keep up with it as new releases come out.

and we said...

you want a single index across all three columns/tables. We can do that. Consider:

ops$tkyte@ORA817DEV> create table t1 ( id int primary key, varchar2_col varchar2(80) );

Table created.

ops$tkyte@ORA817DEV> create table t2 ( id int primary key, clob_col1 clob );

Table created.

ops$tkyte@ORA817DEV> create table t3 ( id int primary key, clob_col2 clob );

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into t1 values ( 1, 'This' );

1 row created.

ops$tkyte@ORA817DEV> insert into t2 values ( 1, 'That' );

1 row created.

ops$tkyte@ORA817DEV> insert into t3 values ( 1, 'and the other thing' );

1 row created.

ops$tkyte@ORA817DEV> commit;

Commit complete.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> grant select on t1 to ctxsys;

Grant succeeded.

ops$tkyte@ORA817DEV> grant select on t2 to ctxsys;

Grant succeeded.

ops$tkyte@ORA817DEV> grant select on t3 to ctxsys;

Grant succeeded.

ops$tkyte@ORA817DEV> pause

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> @su ctxsys
ops$tkyte@ORA817DEV> set termout off
ctxsys@ORA817DEV> get afiedt.buf nolist
ctxsys@ORA817DEV> set termout on
ctxsys@ORA817DEV>
ctxsys@ORA817DEV> create or replace procedure glue_them_together( p_id in rowid, p_lob in out clob )
2 as
3 begin
4 for x in ( select varchar2_col, clob_col1, clob_col2
5 from ops$tkyte.t1, ops$tkyte.t2, ops$tkyte.t3
6 where t1.rowid = p_id
7 and t2.id(+) = t1.id
8 and t3.id(+) = t1.id )
9 loop
10 dbms_lob.writeAppend( p_lob, length(x.varchar2_col)+1, x.varchar2_col||' ');
11 dbms_lob.append( p_lob, x.clob_col1 );
12 dbms_lob.writeAppend( p_lob, 1, ' ' );
13 dbms_lob.append( p_lob, x.clob_col2 );
14 end loop;
15 end;
16 /

Procedure created.

ctxsys@ORA817DEV> grant execute on glue_them_together to ops$tkyte;

Grant succeeded.

ctxsys@ORA817DEV> pause

ctxsys@ORA817DEV>
ctxsys@ORA817DEV> @connect /
ctxsys@ORA817DEV> set termout off
ops$tkyte@ORA817DEV> REM GET afiedt.buf NOLIST
ops$tkyte@ORA817DEV> set termout on
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> exec ctx_ddl.drop_preference('foo_user_datastore');

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV> exec ctx_ddl.drop_preference('my_lexer');

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV> begin
2 ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
3 ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'glue_them_together' );
4 end;
5 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> begin
2 ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
3 ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
4 ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
5 end;
6 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create index t1_idx on t1(varchar2_col)
2 indextype is ctxsys.context
3 parameters( 'datastore foo_user_datastore lexer my_lexer');

Index created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> set define off
ops$tkyte@ORA817DEV> column varchar2_col format a20
ops$tkyte@ORA817DEV> column clob_col1 format a20
ops$tkyte@ORA817DEV> column clob_col2 format a20
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select varchar2_col, clob_col1, clob_col2
2 from t1, t2, t3
3 where t1.id = t2.id
4 and t1.id = t3.id
5 and contains( varchar2_col, 'this & that & thing' ) > 0;

VARCHAR2_COL CLOB_COL1 CLOB_COL2
-------------------- -------------------- --------------------
This That and the other thing

ops$tkyte@ORA817DEV> set define on


Now, you only have one index that spans all three.

now for the tricky part!

this index will only recognize changes when T1's row is updated. Hence a dbms_lob.write to T2 or T3 will not cause the index to refresh itself. You'll need to look at the code that modifies T2 and T3's lob columns and see if you cannot add a "tickle" update to T1 -- so when you dbms_lob.write them -- you update T1 as well and when you commit, interMedia can see the changes.

Normally, we can do this via a triggger, but since triggers do not fire up modification of a lob's value.... that won't work here.




and you rated our response

  (76 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

different result :-(

April 28, 2003 - 5:51 pm UTC

Reviewer: Mirko

This was very helpfull, thanks. I have only one problem. The created index doesn't work. With the commands from your example, the last select returns no rows.
This is all what i see:

VARCHAR2_COL CLOB_COL1 CLOB_COL2
-------------------- -------------------- --------------------

What i'm doing wrong?

Tom Kyte

Followup  

April 28, 2003 - 9:08 pm UTC

if it returned column headings -- it would return data? sqlplus doesn't print them out with no data found.

Please elaborate (cut and paste maybe the whole thing)....

I was using sqlplus 8174 against 8174 -- what about you.

Forgot the version

April 28, 2003 - 5:57 pm UTC

Reviewer: Mirko

My oracle version is 9.2.0.1.0.
Is this part of the problem?

Tom Kyte

Followup  

April 28, 2003 - 9:11 pm UTC

ops$tkyte@ORA920> select           varchar2_col, clob_col1, clob_col2
  2   from t1, t2, t3
  3   where t1.id = t2.id
  4     and t1.id = t3.id
  5     and contains( varchar2_col, 'this & that & thing' ) > 0;

VARCHAR2_COL         CLOB_COL1            CLOB_COL2
-------------------- -------------------- --------------------
This                 That                 and the other thing

ops$tkyte@ORA920>


ops$tkyte@ORA920> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production

 

really no rows

April 29, 2003 - 3:27 am UTC

Reviewer: Mirko

This is my output from PL/SQL Developer:

Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 
Connected as obr

SQL> 
SQL> drop table t1;

Table dropped

SQL> drop table t2;

Table dropped

SQL> drop table t3;

Table dropped

SQL> create table t1 ( id int primary key, varchar2_col varchar2(80) );

Table created

SQL> create table t2 ( id int primary key, clob_col1    clob );

Table created

SQL> create table t3 ( id int primary key, clob_col2    clob );

Table created

SQL> insert into t1 values ( 1, 'This' );

1 row inserted

SQL> insert into t2 values ( 1, 'That' );

1 row inserted

SQL> insert into t3 values ( 1, 'and the other thing' );

1 row inserted

SQL> commit;

Commit complete

SQL> grant select on t1 to ctxsys;

Grant succeeded

SQL> grant select on t2 to ctxsys;

Grant succeeded

SQL> grant select on t3 to ctxsys;

Grant succeeded

SQL> pause
SQL> connect ctxsys/ctxsys
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 
Connected as ctxsys

SQL> create or replace procedure glue_them_together( p_id in rowid, p_lob in out clob )
  2  as
  3  begin
  4            for x in ( select varchar2_col, clob_col1, clob_col2
  5                                   from obr.t1, obr.t2, obr.t3
  6  
  7                                    where t1.rowid = p_id
  8                                      and t2.id(+) = t1.id
  9                                      and t3.id(+) = t1.id )
 10            loop
 11                    dbms_lob.writeAppend( p_lob, length(x.varchar2_col)+1, x.varchar2_col||' ');
 12                    dbms_lob.append( p_lob, x.clob_col1 );
 13                    dbms_lob.writeAppend( p_lob, 1, ' ' );
 14                    dbms_lob.append( p_lob, x.clob_col2 );
 15            end loop;
 16   end;
 17  /

Procedure created

SQL> grant execute on glue_them_together to obr;

Grant succeeded

SQL> pause
SQL> connect obr/obr
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 
Connected as obr

SQL> set termout off
SQL> REM GET afiedt.buf NOLIST
SQL> set termout on
SQL> exec ctx_ddl.drop_preference('foo_user_datastore');

PL/SQL procedure successfully completed

SQL> exec ctx_ddl.drop_preference('my_lexer');

PL/SQL procedure successfully completed

SQL> begin
  2       ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
  3       ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'glue_them_together' );
  4    end;
  5  /

PL/SQL procedure successfully completed

SQL> begin
  2       ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
  3       ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
  4       ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
  5  end;
  6  /

PL/SQL procedure successfully completed

SQL> create index t1_idx on t1(varchar2_col)
  2    indextype is ctxsys.context
  3    parameters( 'datastore foo_user_datastore lexer my_lexer');

Index created

SQL> set define off
SQL> column varchar2_col format a20
SQL> column clob_col1 format a20
SQL> column clob_col2 format a20
SQL> select     varchar2_col, clob_col1, clob_col2
  2     from t1, t2, t3
  3     where t1.id = t2.id
  4       and t1.id = t3.id
  5       and contains( varchar2_col, 'this & that & thing' ) > 0;

VARCHAR2_COL         CLOB_COL1            CLOB_COL2
-------------------- -------------------- --------------------

SQL> 


And this is the script (for copy & paste):

drop table t1;
drop table t2;
drop table t3;
create table t1 ( id int primary key, varchar2_col varchar2(80) );
create table t2 ( id int primary key, clob_col1    clob );
create table t3 ( id int primary key, clob_col2    clob );
insert into t1 values ( 1, 'This' );
insert into t2 values ( 1, 'That' );
insert into t3 values ( 1, 'and the other thing' );
commit;


grant select on t1 to ctxsys;
grant select on t2 to ctxsys;
grant select on t3 to ctxsys;
pause
connect ctxsys/ctxsys
create or replace procedure glue_them_together( p_id in rowid, p_lob in out clob )
as
begin
          for x in ( select varchar2_col, clob_col1, clob_col2
                                 from obr.t1, obr.t2, obr.t3 

                                  where t1.rowid = p_id
                                    and t2.id(+) = t1.id
                                    and t3.id(+) = t1.id )
          loop
                  dbms_lob.writeAppend( p_lob, length(x.varchar2_col)+1, x.varchar2_col||' ');
                  dbms_lob.append( p_lob, x.clob_col1 );
                  dbms_lob.writeAppend( p_lob, 1, ' ' );
                  dbms_lob.append( p_lob, x.clob_col2 );
          end loop;
 end;
/

grant execute on glue_them_together to obr;
pause
connect obr/obr
set termout off
REM GET afiedt.buf NOLIST
set termout on
exec ctx_ddl.drop_preference('foo_user_datastore');
exec ctx_ddl.drop_preference('my_lexer');
begin
     ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
     ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'glue_them_together' );
  end;
/
begin
     ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
     ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
     ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
end;
/

create index t1_idx on t1(varchar2_col)
  indextype is ctxsys.context
  parameters( 'datastore foo_user_datastore lexer my_lexer');
set define off
column varchar2_col format a20
column clob_col1 format a20
column clob_col2 format a20
select     varchar2_col, clob_col1, clob_col2
   from t1, t2, t3
   where t1.id = t2.id
     and t1.id = t3.id
     and contains( varchar2_col, 'this & that & thing' ) > 0;



 

Tom Kyte

Followup  

April 29, 2003 - 8:22 am UTC

what version of sqlplus -- if you remove the "contains" (just select all data) what then.

It looks like it is "working" -- the headings wouldn't print otherwise. Don't know why it isn't showing the data. But if you get "nothing" without contains -- we know it is "working"

perhaps you can put it in a plsql block and dbms_output it - just to see the data.

TKPROF it as well.
or use autotrace -- see the "rows processed". then we can fix sqlplus....

Solved!

April 29, 2003 - 6:08 am UTC

Reviewer: Mirko

Case sensitive search condition!
Following query works:

select varchar2_col, clob_col1, clob_col2
from t1, t2, t3
where t1.id = t2.id
and t1.id = t3.id
and contains( varchar2_col, 'This & That & thing' ) > 0;



Good solution.... but I run out of cursors

July 08, 2003 - 3:04 pm UTC

Reviewer: Tom Best from PA

Thanks for this answer, Tom.  I did this, but intermedia is leaving a cursor open for each call to the "glue" proc.  I don't see any bug reports about this in metalink, either.

I get this:

SQL> create index resitem_text on siebel.s_resitem(name)
  2  indextype is ctxsys.context
  3  parameters( 'datastore foo_user_datastore lexer my_lexer')
  4  /
create index resitem_text on siebel.s_resitem(name)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: interMedia Text error:
ORA-01000: maximum open cursors exceeded
DRG-50857: oracle error in drueixe
oracle error in drueixe
ORA-06512: at "CTXSYS.DRUE", line 126
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 78
ORA-06512: at line 1

And, sure enough, v$open_cursor contains hundreds of rows with this SQL_TEXT in it:

  begin ctxsys."GLUE_THEM_TOGETHER"(:rid, :lob); end;

BTW - I creating the index with a user that does not own the table being indexed.

At first I coded it exactly as you had it, and got this result, and then I coded it with an explicit cursor, but still get the error.  Here is my code as of now:

PROCEDURE GLUE_THEM_TOGETHER( p_id in rowid, p_lob in out clob )
as

cursor x is
    select name, desc_text, faq_ques_text, resolution_text, x_problem_text
                    from siebel.s_resitem sr
                    where sr.rowid = p_id;
                    
l_name siebel.s_resitem.name%type;
l_desc_text siebel.s_resitem.desc_text%type;
l_faq_ques_text siebel.s_resitem.faq_ques_text%type;
l_resolution_text siebel.s_resitem.resolution_text%type;
l_x_problem_text siebel.s_resitem.x_problem_text%type;

begin
/*
    for x in ( select name, desc_text, faq_ques_text, resolution_text, x_problem_text
                    from siebel.s_resitem sr
                    where sr.rowid = p_id)
*/
    open x;
    loop
        fetch x into l_name, l_desc_text, l_faq_ques_text, l_resolution_text, l_x_problem_text;
        
            dbms_lob.writeAppend( p_lob, length(l_name)+1, l_name||' ');
            dbms_lob.writeAppend( p_lob, length(l_desc_text)+1, l_desc_text||' ');
            dbms_lob.writeAppend( p_lob, length(l_faq_ques_text)+1, l_faq_ques_text||' ');
            dbms_lob.writeAppend( p_lob, length(l_resolution_text)+1, l_resolution_text||' ');
            dbms_lob.writeAppend( p_lob, length(l_x_problem_text)+1, l_x_problem_text);
            
    end loop;
    close x;
end;

 

Tom Kyte

Followup  

July 08, 2003 - 3:52 pm UTC

what version?

i've used this technique on hundreds of thousands/millions of rows.

Version is 8.1.7.2.0

July 08, 2003 - 4:05 pm UTC

Reviewer: Tom Best from PA

Version is 8.1.7.2.0



Tom Kyte

Followup  

July 08, 2003 - 4:20 pm UTC

how many rows in the table being indexed.

# of rows

July 08, 2003 - 5:07 pm UTC

Reviewer: Tom Best from PA

select count(*) from siebel.s_resitem
Query finished, retrieving results...
COUNT(*)
---------------------------------------
26201


desc siebel.s_resitem
Describing siebel.s_resitem....
NAME Null? Type
------------------------------- --------- -----
ROW_ID NOT NULL VARCHAR2(15)
CREATED NOT NULL DATE
CREATED_BY NOT NULL VARCHAR2(15)
LAST_UPD NOT NULL DATE
LAST_UPD_BY NOT NULL VARCHAR2(15)
MODIFICATION_NUM NOT NULL NUMBER(10,0)
CONFLICT_ID NOT NULL VARCHAR2(15)
NAME NOT NULL VARCHAR2(100)
SOLUTION_ITEM_FLG NOT NULL CHAR(1)
FILE_AUTO_UPD_FLG CHAR(1)
FILE_DEFER_FLG CHAR(1)
FILE_DOCK_REQ_FLG CHAR(1)
FILE_DOCK_STAT_FLG CHAR(1)
FILE_NAME VARCHAR2(200)
DESC_TEXT VARCHAR2(255)
FAQ_QUES_TEXT VARCHAR2(250)
FILE_DATE DATE
FILE_EXT VARCHAR2(10)
FILE_REV_NUM VARCHAR2(15)
FILE_SIZE NUMBER(22,7)
FILE_SRC_PATH VARCHAR2(255)
FILE_SRC_TYPE VARCHAR2(30)
INTR_PUBLISH_FLG CHAR(1)
ORIG_FILE_NAME VARCHAR2(220)
PR_AUTHOR_ID VARCHAR2(15)
PUBLISH_FLG CHAR(1)
RESITEM_TYPE VARCHAR2(30)
RESOLUTION_TEXT VARCHAR2(2000)
SAVED_AS_FILE_NAME VARCHAR2(220)
STATUS_CD VARCHAR2(30)
X_AREA VARCHAR2(50)
X_CHANGE_FLG CHAR(1)
X_LANG_ID VARCHAR2(15)
X_PAR_SOLUTION_ID VARCHAR2(15)
X_PLATFORM_CD VARCHAR2(30)
X_PROBLEM_TEXT VARCHAR2(2000)
X_PROD_DEFECT_ID VARCHAR2(15)
X_PROD_LINE_ID VARCHAR2(15)
X_VERSION VARCHAR2(50)
X_CONF_FLAG CHAR(1)
*** SCRIPT END : Session:CTXSYS


Tom Kyte

Followup  

July 08, 2003 - 6:21 pm UTC

well, i've done tables much much larger then that, I've not hit this personally. I neither can find any bugs logged against this at all.

what is your open cursors set to currently?

is your data in need of a lob, we could try a varchar2 if it is under 32k and see if that makes any difference (just use a varchar2 in out instead of a lob)

July 09, 2003 - 8:36 am UTC

Reviewer: A reader

Hi Tom,

This Question is to TOM BEST (Siebel guy).

Why you are using intermedia in siebel application?
There is thing called Fulcrum search available in Siebel.
The reason for this question is we are using siebel call center and I am also looking any possibility to use Intermedia in siebel

Thanks

My Stupid

July 09, 2003 - 2:41 pm UTC

Reviewer: Tom Best from PA

I had a bug. The last call to dbms_lob.writeAppend had the length + 1, but does not need the appended space (' '). And, indeed the doc for this function says:

"There is an error if the input amount is more than the data in the buffer."

I would not have expected this particular error, but that's good to know now.

Sorry to waste your time, Tom.

To the guy with the Siebel question... I believe predecesors of mine investigated Fulcrum and declined to use it. When using Oracle text, we just have to make sure we rebuild these indexes after an SRF release, since Siebel doesn't know about it.

July 14, 2003 - 1:29 pm UTC

Reviewer: A reader from USA

Is it true that a table, using context indexes with USER_DATASTORE, that whenever new entries are added to a table the index needs to be re-created.

Can this be a performance hit?



Tom Kyte

Followup  

July 15, 2003 - 12:58 am UTC

no, the index does not need to be recreated, it needs to be maintained (added to) just like any index would

October 13, 2003 - 1:29 pm UTC

Reviewer: David Pujol from Barcelona (Catalonia)


Complete Answers

September 02, 2004 - 1:53 am UTC

Reviewer: MSU from India

Tom,

When I run a query I get the following error

ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-10837: section AccountingSegment@id does not exist

One of the the table which I query contains an XML as a column and "AccountingSegment" is one of the tag in the XML. I am unable to interpret the error message. Can you help me what error is it actually.

Tom Kyte

Followup  

September 02, 2004 - 8:08 am UTC

give me a full example to work with please.


[tkyte@xtkyte-pc tkyte]$ oerr drg 10837
10837,0, "section %(1)s does not exist"
// *Cause: The given section name does not exist in USER_SECTIONS
// *Action: Rewrite query with valid section name
// *Mnemonic: QE_INV_SECTION
//


it is basically saying the section you tried to use is not an indexed section. so, give us an example that creates the table, inserts a row, creates the index with this section and queries it.

Complete Answers

September 03, 2004 - 9:51 am UTC

Reviewer: MSU from India

Hi Tom,

This has reference to what you had asked for in your prior followup.

The application is a Typical Java ORACLE webbased application. When an order is placed through the web all the details regarding the order are made into an XML script and it is stored in the ORDER_XML column of the ORDER_FORMATS table. The script for the same is given below.


CREATE TABLE ORDER_FORMATS
(
  ORDER_FORMAT_ID  NUMBER(18)                   NOT NULL,
  ORDER_ID         NUMBER(18)                   NOT NULL,
  ORDER_TYPE_ID    NUMBER(18)                   NOT NULL,
  ORDER_XML        SYS.XMLTYPE                  NOT NULL,
  STATUS_ID        NUMBER(18)                   NOT NULL,
  CREATED_BY_ID    NUMBER(18)                   NOT NULL,
  CREATED_DATE     DATE                         NOT NULL,
  UPDATED_BY_ID    NUMBER(18),
  UPDATED_DATE     DATE
)
LOGGING 
NOCACHE
NOPARALLEL
/


CREATE INDEX IDX_ORDERFORMATS_ORDERXML ON ORDER_FORMATS
(SYS_MAKEXML("SYS_NC00005$"))
LOGGING
NOPARALLEL
/


CREATE INDEX IDX_ORDERFORMATS_ORDTYPEORDID ON ORDER_FORMATS
(ORDER_TYPE_ID, ORDER_ID)
LOGGING
NOPARALLEL
/


CREATE INDEX IDX_ORDFORMATS_STATTYPEORDID ON ORDER_FORMATS
(STATUS_ID, ORDER_TYPE_ID, ORDER_ID)
LOGGING
NOPARALLEL
/


A typical entry in the ORDER_FORMATS table will be as given below.

SQL> select * from order_formats where order_id = 2055138;

ORDER_FORMAT_ID   --> 61510 
ORDER_ID       --> 2055138
ORDER_TYPE_ID     --> 2
STATUS_ID       --> 1
CREATED_BY_ID     --> 40164019
CREATED_DATE       --> 03-SEP-04
UPDATED_BY_ID       --> 40164019
UPDATED_D         --> 03-SEP-04
ORDER_XML

<cXML timestamp="2004-09-03T10:24::03">                                                                                        
  <Request>
    <OrderRequest>
      <OrderRequestHeader orderDate="20040903T10:23:03">
        <Total>
          <Money currency="USD">0.55</Money>
        </Total>
        <ShipTo>
          <Address addressID="40249628">
            <Name xml:lang="en">Demoville Elementary School</Name>
            <PostalAddress name="Demoville Elementary School">
              <Street>500 Demo Lane</Street>
              <City>Appleton</City>
              <State>WI</State>
              <PostalCode>99999</PostalCode>
              <Country isoCountryCode="USA">null</Country>
              <Components>
                <AddressID>40249628</AddressID>
                <LocalExempt>false</LocalExempt>
                <FederalExempt>false</FederalExempt>
              </Components>
            </PostalAddress>
            <Email name="Demoville Elementary School">marketplace-dev@junebox.com</Email>
          </Address>
        </ShipTo>
        <BillTo>
          <Address addressID="40249624">
            <Name xml:lang="en">Demoville School District</Name>
            <PostalAddress name="Demoville School District">
              <Street>500 Demo Lane</Street>
              <City>Appleton</City>
              <State>WI</State>
              <PostalCode>99999</PostalCode>
              <Country isoCountryCode="USA">null</Country>
              <Components>
                <AddressID>40249624</AddressID>
                <LocalExempt>false</LocalExempt>
                <FederalExempt>false</FederalExempt>
              </Components>
            </PostalAddress>
            <Email name="Demoville School District">marketplace-dev@junebox.com</Email>
          </Address>
        </BillTo>
      </OrderRequestHeader>
      <ItemOut quantity="1" lineNumber="1">
        <ItemID>
          <SupplierPartID>30000072</SupplierPartID>
          <SupplierPartAuxiliaryID>Catalog|SS04|054054|40181493|4|000072|40554160|2055138</SupplierPartAuxiliaryID>
        </ItemID>
        <ItemDetail>
          <UnitPrice>
            <Money currency="USD">0.55</Money>
          </UnitPrice>
          <Description xml:lang="en">59972 JUMBO SMOOTH PAPER CLIP BX/100 CLASSROOM SELECT (301E)</Description>
          <UnitOfMeasure>BX</UnitOfMeasure>
          <Classification domain="UNSPSC">4412210400</Classification>
          <ManufacturerPartID>59972</ManufacturerPartID>
          <ManufacturerName>CHARLES LEONARD INC</ManufacturerName>
          <Components>
            <Hazardous>false</Hazardous>
            <ManufacturerID>40019843</ManufacturerID>
            <Direct>true</Direct>
            <SSIVendorID>036858</SSIVendorID>
            <Ups>true</Ups>
          </Components>
        </ItemDetail>
        <Shipping>
          <Description xml:lang="en">Freight Charges</Description>
          <Money currency="USD">5.95</Money>
          <Components>
            <CMFShipping>
              <CMFFreight>
                <FreightRateID>1062</FreightRateID>
                <FreightTypeID>2</FreightTypeID>
                <FreightChargeType/>
                <Description xml:lang="en">Freight Charges</Description>
                <Money currency="USD">5.95</Money>
                <Date/>
                <Status/>
              </CMFFreight>
            </CMFShipping>
          </Components>
        </Shipping>
        <Distribution>
          <Accounting>
            <AccountingSegment id="10 e 111 5900 4250">
              <Name>10 e 111 5900 4250</Name>
              <Description>null</Description>
            </AccountingSegment>
          </Accounting>
        </Distribution>
        <Components>
          <ListPrice>0.55</ListPrice>
          <ExtendedPriceFloat>0.55</ExtendedPriceFloat>
          <PricingMethod>List</PricingMethod>
          <CatalogPricingOnly>N</CatalogPricingOnly>
          <CanadaTaxCode>1051</CanadaTaxCode>
        </Components>
      </ItemOut>
    </OrderRequest>
    <Components>
      <PunchOutUser>false</PunchOutUser>
      <PersonalCreditCard>false</PersonalCreditCard>
      <UidLong>40164019</UidLong>
      <YantraOrderNumber>S2055138</YantraOrderNumber>
      <OrderStatusID>800</OrderStatusID>
      <OrderID>2055138</OrderID>
      <storeID>4</storeID>
      <ExtendedGrandTotalFloat>6.5</ExtendedGrandTotalFloat>
      <IdentID>40124582</IdentID>
      <organizationID>40022967</organizationID>
      <FederalTaxExempt>false</FederalTaxExempt>
      <ExtendedPriceFloat>0.55</ExtendedPriceFloat>
    </Components>
  </Request>
</cXML>

SELECT
o.order_id,
o.order_number,
o.identity_id,
o.organization_id,
o.order_status_id,
o.created_date,
o.store_id,
o.yantra_order_header_key,
u.user_name,
u.first_name,
u.last_name
FROM
order_formats formats,
orders o,
identities i,
users u
WHERE
formats.order_id = o.order_id
AND
formats.order_xml.extract('/cXML/Request/OrderRequest/OrderRequestHead
er/ShipTo/Address/PostalAddress/DeliverTo[1]/text()').getStringVal()
LIKE '%attention%'
AND
UPPER(formats.order_xml.extract('/cXML/Request/OrderRequest/OrderReque
stHeader@orderID').getStringVal()) = 'PO'
AND (contains(formats.order_xml, '%'||'budget'||'% within
AccountingSegment@id') > 0)
AND
formats.order_xml.extract('/cXML/Request/OrderRequest/OrderRequestHead
er/ShipTo/Address@addressID').getStringVal() = '40249624'
AND o.identity_id = i.identity_id
AND u.user_id = i.user_id
ORDER BY o.order_number DESC

I get the following error message

*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-10837: section AccountingSegment@id does not exist.

Do let me know what best can be done 

Tom Kyte

Followup  

September 03, 2004 - 1:05 pm UTC

"The application is a Typical Java ORACLE webbased application"

sent chills down my spine already.


So, you have some of the most STRUCTURED DATA ON THE PLANET -- an Order Entry system -- and you globbed it into XML. man. man o man o man o man.

why would you take perfectly structured, awesomely relational, perfect data and XML it?


how about create's that can be run -- and a simple, small, just as big as it needs to be example -- with an insert into statement and all.

You can post it in the fashion I post my examples -- starting with the create table, the single --- small--- insert, and the SINGLE create index (that works) that is needed, followed by the shortest query possible.




Multiple Columns

September 23, 2004 - 2:34 pm UTC

Reviewer: John French from Maryland, U.S.A

I have a table with multiple columns that I want to create a context search on. What is the best context indexing to use.

I would like search across version, tmNotes, tmActionsBefore and tmActionsAfter columns in the table below.

CREATE TABLE ReleaseNote (
id int PRIMARY KEY,
version varchar(15),
tmNotes varchar2(4000),
tmActionsBefore varchar2(4000),
tmActionsAfter varchar2(4000),
appSize varchar(15),
downloadSize varchar(15),
upgradeSize varchar(15),
reboot number(1),
rebootReason varchar(200),
dependencies varchar(200),
applicationId int,
releaseId int
);


Tom Kyte

Followup  

September 24, 2004 - 9:25 am UTC

you would probably use a multicolumn data store for something so simple.

Sort on Clob Columns

November 03, 2004 - 3:58 pm UTC

Reviewer: Lamya from Houston , TX

Tom ,

I have a table

create table t
( id number,
summary clob ) ;

insert into t values ( 1, 'blan blab Gene...');
insert into t values ( 2, 'Gene...blah');
insert into t values ( 3, 'blah blah blah');

What I require is to do a search / sort on the clob column
without using dbms_lob functionality or Oracle Text . I told my manager its not possible but I want to make sure that is the case

Thanks a lot
Lamya

Tom Kyte

Followup  

November 05, 2004 - 11:01 am UTC

time to correct yourself.


Oracle Text -- absolutely (it is what you use to search on this site - on clobs)

dbms_lob.instr -- you can, but it'll be rather "slow" unless you have something like:

where id = 5 and dbms_lob.instr( clob, 'blah' ) > 0

(that is, where id=5 is used to find a very few rows via index and then dbms_lob is executed)

</code> http://www.oracle.com/pls/db92/db92.docindex?remark=homepage#index-TEX
A quick glance at the Text docs would show you that this is exactly what Text is all about though.

http://docs.oracle.com/docs/cd/B10501_01/text.920/a96517/cdefault.htm#1007619 <code>

first section "what is text"...

Not getting the expected results..

September 01, 2005 - 12:33 pm UTC

Reviewer: Mohini from Germany

Tom,
I ran the test case almost like yours..except instead of clobs, I used varchar2.

Questions: (Please refer to the results in the script run)

1. If the columns are all varchar2 then can index be created on any column?
2. Does the column size matter?
3. Can we just create a dummy varchar2 column in one of the tables and create
index on it?
4. How does the index get updated?

Here is what I am trying to accomplish:
A seven digit number is passed in as a Key word.
The application needs to return two recordsets:
Look for an exact match in one of the columns and return those recordsets.
Then do a full like (%%) "or" search on few columns (of different tables)
and return that recordset excluding any records from the first recordset.

1. Does a full like search work with this procedure (please refer to the test run)
2. The mixed case attribute didn't work for me (please refer to the test run)

/*********Script*************/

--log in as any user

--create tables

drop table t1;
drop table t2;
drop table t3;
drop table t4;

create table t1 ( id int primary key, varchar2_col_t1
varchar2(80) );
create table t2 ( id int primary key, varchar2_col_t2
varchar2(80));
create table t3 ( id int primary key, varchar2_col_t3
varchar2(80));

--create a dummy table for glued index
--create table t4 ( dummycol varchar2(1) );

--insert data
insert into t1 values ( 1, 'This' );
insert into t2 values ( 1, 'That' );
insert into t3 values ( 1, 'and the other thing' );

insert into t1 values ( 2, 'ThisTHATotherwhAT' );
commit;

--grant rights to ctxsys
grant select on t1 to ctxsys;
grant select on t2 to ctxsys;
grant select on t3 to ctxsys;




--log in as ctxsys user
--procedure has to be owned by ctxsys
create or replace procedure glue_them_together( p_id in rowid,
p_lob in out clob )
as
begin
for x in ( select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
from textuser.t1, textuser.t2,
textuser.t3
where t1.rowid = p_id
and t2.id(+) = t1.id
and t3.id(+) = t1.id )
loop
dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t1)+1, x.varchar2_col_t1||' ');
dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t2)+1, x.varchar2_col_t2||' ');
dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t3)+1, x.varchar2_col_t3||' ');
end loop;

end;
/
grant execute on glue_them_together to textuser;


--create preferences

exec ctx_ddl.drop_preference('foo_user_datastore');
exec ctx_ddl.drop_preference('my_lexer');

begin
ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'glue_them_together' );
end;
/

begin
ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
end;
/

--any user
create index t1_idx on t1(varchar2_col_t1)
indextype is ctxsys.context
parameters( 'datastore ctxsys.foo_user_datastore lexer ctxsys.my_lexer');

set define off;
column varchar2_col_t1 format a20
column varchar2_col_t2 format a20
column varchar2_col_t3 format a20

--all records
select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
from t1, t2, t3
where t1.id = t2.id (+)
and t1.id = t3.id (+);

--generic case
select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
from t1, t2, t3
where t1.id = t2.id (+)
and t1.id = t3.id (+)
and contains( varchar2_col_t1, 'This' ) > 0;

--mixed case
select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
from t1, t2, t3
where t1.id = t2.id (+)
and t1.id = t3.id (+)
and contains( varchar2_col_t1, 'this' ) > 0;

--%% (full search)
select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
from t1, t2, t3
where t1.id = t2.id (+)
and t1.id = t3.id (+)
and contains( varchar2_col_t1, 'what' ) > 0;






/*********Script Run*************/


Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

P:\>sqlplus textuser@ora9i

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Sep 1 09:34:18 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

9.2.0.6.0(dev) textuser@ora9i> --log in as any user
9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --create tables
9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> drop table t1;

Table dropped.

9.2.0.6.0(dev) textuser@ora9i> drop table t2;

Table dropped.

9.2.0.6.0(dev) textuser@ora9i> drop table t3;

Table dropped.

9.2.0.6.0(dev) textuser@ora9i> drop table t4;
drop table t4
*
ERROR at line 1:
ORA-00942: table or view does not exist


9.2.0.6.0(dev) textuser@ora9i> create table t1 ( id int primary key, varchar2_col_t1
2 varchar2(80) );

Table created.

9.2.0.6.0(dev) textuser@ora9i> create table t2 ( id int primary key, varchar2_col_t2
2 varchar2(80));

Table created.

9.2.0.6.0(dev) textuser@ora9i> create table t3 ( id int primary key, varchar2_col_t3
2 varchar2(80));

Table created.

9.2.0.6.0(dev) textuser@ora9i> --create a dummy table for glued index
9.2.0.6.0(dev) textuser@ora9i> --create table t4 ( dummycol varchar2(1) );
9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --insert data
9.2.0.6.0(dev) textuser@ora9i> insert into t1 values ( 1, 'This' );

1 row created.

9.2.0.6.0(dev) textuser@ora9i> insert into t2 values ( 1, 'That' );

1 row created.

9.2.0.6.0(dev) textuser@ora9i> insert into t3 values ( 1, 'and the other thing' );

1 row created.

9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> insert into t1 values ( 2, 'ThisTHATotherwhAT' );

1 row created.

9.2.0.6.0(dev) textuser@ora9i> commit;

Commit complete.

9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --grant rights to ctxsys
9.2.0.6.0(dev) textuser@ora9i> grant select on t1 to ctxsys;

Grant succeeded.

9.2.0.6.0(dev) textuser@ora9i> grant select on t2 to ctxsys;

Grant succeeded.

9.2.0.6.0(dev) textuser@ora9i> grant select on t3 to ctxsys;

Grant succeeded.



Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

P:\>sqlplus ctxsys@ora9i

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Sep 1 09:34:54 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

9.2.0.6.0(dev) ctxsys@ora9i> --log in as ctxsys user
9.2.0.6.0(dev) ctxsys@ora9i> --procedure has to be owned by ctxsys
9.2.0.6.0(dev) ctxsys@ora9i> create or replace procedure glue_them_together( p_id in rowid,
2 p_lob in out clob )
3 as
4 begin
5 for x in ( select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
6 from textuser.t1, textuser.t2,
7 textuser.t3
8 where t1.rowid = p_id
9 and t2.id(+) = t1.id
10 and t3.id(+) = t1.id )
11 loop
12 dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t1)+1, x.varchar2_col_t1||' ');
13 dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t2)+1, x.varchar2_col_t2||' ');
14 dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t3)+1, x.varchar2_col_t3||' ');
15 end loop;
16
17 end;
18 /

Procedure created.

9.2.0.6.0(dev) ctxsys@ora9i> grant execute on glue_them_together to textuser;

Grant succeeded.

9.2.0.6.0(dev) ctxsys@ora9i>
9.2.0.6.0(dev) ctxsys@ora9i>
9.2.0.6.0(dev) ctxsys@ora9i>
9.2.0.6.0(dev) ctxsys@ora9i> --create preferences
9.2.0.6.0(dev) ctxsys@ora9i>
9.2.0.6.0(dev) ctxsys@ora9i> exec ctx_ddl.drop_preference('foo_user_datastore');

PL/SQL procedure successfully completed.

9.2.0.6.0(dev) ctxsys@ora9i> exec ctx_ddl.drop_preference('my_lexer');

PL/SQL procedure successfully completed.

9.2.0.6.0(dev) ctxsys@ora9i>
9.2.0.6.0(dev) ctxsys@ora9i> begin
2 ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
3 ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'glue_them_together' );
4 end;
5 /

PL/SQL procedure successfully completed.

9.2.0.6.0(dev) ctxsys@ora9i>
9.2.0.6.0(dev) ctxsys@ora9i> begin
2 ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
3 ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
4 ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
5 end;
6 /

PL/SQL procedure successfully completed.

9.2.0.6.0(dev) ctxsys@ora9i>



9.2.0.6.0(dev) textuser@ora9i> --any user
9.2.0.6.0(dev) textuser@ora9i> create index t1_idx on t1(varchar2_col_t1)
2 indextype is ctxsys.context
3 parameters( 'datastore ctxsys.foo_user_datastore lexer ctxsys.my_lexer');

Index created.

9.2.0.6.0(dev) textuser@ora9i> set define off;
9.2.0.6.0(dev) textuser@ora9i> column varchar2_col_t1 format a20
9.2.0.6.0(dev) textuser@ora9i> column varchar2_col_t2 format a20
9.2.0.6.0(dev) textuser@ora9i> column varchar2_col_t3 format a20
9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --all records
9.2.0.6.0(dev) textuser@ora9i> select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
2 from t1, t2, t3
3 where t1.id = t2.id (+)
4 and t1.id = t3.id (+);

VARCHAR2_COL_T1 VARCHAR2_COL_T2 VARCHAR2_COL_T3
-------------------- -------------------- --------------------
This That and the other thing
ThisTHATotherwhAT

9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --generic case
9.2.0.6.0(dev) textuser@ora9i> select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
2 from t1, t2, t3
3 where t1.id = t2.id (+)
4 and t1.id = t3.id (+)
5 and contains( varchar2_col_t1, 'This' ) > 0;

VARCHAR2_COL_T1 VARCHAR2_COL_T2 VARCHAR2_COL_T3
-------------------- -------------------- --------------------
This That and the other thing

9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --mixed case
9.2.0.6.0(dev) textuser@ora9i> select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
2 from t1, t2, t3
3 where t1.id = t2.id (+)
4 and t1.id = t3.id (+)
5 and contains( varchar2_col_t1, 'this' ) > 0;

no rows selected

9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --%% (full search)
9.2.0.6.0(dev) textuser@ora9i> select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
2 from t1, t2, t3
3 where t1.id = t2.id (+)
4 and t1.id = t3.id (+)
5 and contains( varchar2_col_t1, 'what' ) > 0;

no rows selected



Tom Kyte

Followup  

September 01, 2005 - 3:59 pm UTC

this is big, can you just point out a simple example where you thing "something is wrong".


1. If the columns are all varchar2 then can index be created on any column?

index can be created on any column that text supports.


2. Does the column size matter?

nope


3. Can we just create a dummy varchar2 column in one of the tables and create
index on it?

yep.

4. How does the index get updated?


you sync the index in 9i. I have a job to do so.

Mixed case and full like

September 01, 2005 - 10:07 pm UTC

Reviewer: mohini from germany

towards the end of my "tiny" script...
I ran two sqls..where I didn't get any results for mixed case or a full like..

thanks..

9.2.0.6.0(dev) textuser@ora9i> --mixed case
9.2.0.6.0(dev) textuser@ora9i> select varchar2_col_t1, varchar2_col_t2,
varchar2_col_t3
2 from t1, t2, t3
3 where t1.id = t2.id (+)
4 and t1.id = t3.id (+)
5 and contains( varchar2_col_t1, 'this' ) > 0;

no rows selected

9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --%% (full search)
9.2.0.6.0(dev) textuser@ora9i> select varchar2_col_t1, varchar2_col_t2,
varchar2_col_t3
2 from t1, t2, t3
3 where t1.id = t2.id (+)
4 and t1.id = t3.id (+)
5 and contains( varchar2_col_t1, 'what' ) > 0;

no rows selected


Tom Kyte

Followup  

September 02, 2005 - 1:23 am UTC

this and what are stopwords.

</code> http://docs.oracle.com/docs/cd/B10501_01/text.920/a96517/query.htm#1007652 <code>

Mixed case....

September 06, 2005 - 1:36 pm UTC

Reviewer: Mohini from Germany

9i (Release 2)

Couple of issues:
1. I am not getting the mixed case to fly for some reason...
2. Index rebuild (exec ctx_ddl.sync_index('T1_IDX');) is taking 5 to 7 minutes?

--Thanks.


Tom Kyte

Followup  

September 06, 2005 - 8:52 pm UTC

examples please.

(sure a rebuild of something big could take 5 to 7 minutes, why not? no details to work with here at all)

index rebuild

September 06, 2005 - 3:49 pm UTC

Reviewer: mohini from germany

O.k. the mixed case issue has been resolved..I had set mixed_case attribute to YES...I changed it to 'NO'..and it is all working.
But the re-synching of the index is still taking a while..and there are only 2-3 rows in the tables (from your example above)

Thanks

Tom Kyte

Followup  

September 06, 2005 - 9:07 pm UTC

my example doesn't take minutes on my system, are you doing it exactly the same?

index sync and memory

September 06, 2005 - 4:53 pm UTC

Reviewer: Mohini from Germany

Docs..Docs..Docs..got my answer..

Synchronizing the Index

Synchronizing the index involves processing all pending updates, inserts, and deletes to the base table. You can do this in PL/SQL with the CTX_DDL.SYNC_INDEX procedure.

The following example synchronizes the index with 2 megabytes of memory:

begin


ctx_ddl.sync_index('myindex', '2M');

end;

Excluding the exact match..

September 08, 2005 - 7:10 pm UTC

Reviewer: mohini from germany

9i (Release 2)

Tom,
Here is a scenerio:
user enters a keword "john"..
database will search along various employee related fields like employees lastname, firstname, title, location etc. and
two recordsets will be returned:
First one needs to be an exact match..like where firstname or lastname...is john
Second recordset needs to have all the partial matches (excluding the exact matches records from the first resultset)..
So, I thought I can do the whole set up in your example above for the first recordset...
and for the second recordset, I will add a wordlist preference..and pass the keyword as %john% to the contains clause (wordlist preference would always be there..it will get utilized in the second resultset)...

Now, I am trying to see what is the best way to exclude the records...will it be something like this:

select empno, lastname, firstname, dname
from emp e, dept d
where e.deptno = d.deptno
and (contains e.emplid, '%john%') > 0
and empno not in
(
select empno
from emp e, dept d
where e.deptno = d.deptno
and (contains e.emplid, 'john') > 0
);

Is there a better way to accomplish this?

Thanks





Tom Kyte

Followup  

September 08, 2005 - 7:36 pm UTC

why not just get them all at once?

ops$tkyte@ORA10G> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> update emp set ename = 'JFORDX' where ename = 'MILLER';
 
1 row updated.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create index emp_idx on emp(ename) indextype is ctxsys.context;
 
Index created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select empno, ename, contains(ename,'ford')
  2    from emp
  3   where contains(ename,'%ford%') > 0;
 
     EMPNO ENAME      CONTAINS(ENAME,'FORD')
---------- ---------- ----------------------
      7902 FORD                            6
      7934 JFORDX                          0


contains() <> 0 -> exact match.
            = 0 -> partial match. 

skipjoins

September 15, 2005 - 7:08 pm UTC

Reviewer: mohini from germany

9i release 2

Tom,
We have the following requirements:
1. keyword search oconner should return both o'conner and oconner
I am setting the skipjoins attribute of the basic_lexer to accomplish this...works fine..
Is it the correct way to accomplish this?
can skipjoins take comma delimited character list if we want to add more characters that we need to skip?
2. Some users have asked if we can add a capability..so that
even if something is misspelled..results are found...by correcting it? I have no idea how to begin addressing this?
should we add a spell check first and then pass the keyword..to oracle text?
3. Can Soundex capabilities be added to the search?

Thanks.


Tom Kyte

Followup  

September 15, 2005 - 8:36 pm UTC

1) yes on the o'conner

probably not on the "list" - it just removes the character from the string, not what you wanted.

You would take the comma delimited list and "or" them in the text query.

2) thesaurus if you have a "list of miss-spellings", otherwise, I'd recommend what google does (you get a spell checker piece of software and ask it for spellings and let them figure it out)

for example, google:

theasauras

3) sure, text supports soundex. and fuzzy..

multiple skip characters..

September 16, 2005 - 4:37 pm UTC

Reviewer: mohini from germany

"You would take the comma delimited list and "or" them in the text query."

So, you are saying if we want to ignore more than one characters then we can't add multiple characters to the skip join attribute...like
exec ctx_ddl.set_attribute('textuser_lexer', 'skipjoins', '''-');

we have to use "or" in the query

select varchar2_col_t1, varchar2_col_t2
from t1, t2
where t1.id = t2.id (+)
and
(contains( varchar2_col_t1, '%floydmyer%' ) > 0
or contains( varchar2_col_t1, '%floyd-myer%' ) > 0)


Tom Kyte

Followup  

September 16, 2005 - 6:09 pm UTC

oh, i misinterpreted you -- sorry.

floydmyer and floyd-myer are the same in that case.

skip joins..

September 19, 2005 - 4:19 pm UTC

Reviewer: mohini from germany

So, if you add a character to skip join attribute..then it should be ignored right..it worked for me for '..but not for
the -

I did re-sync the index:

exec ctx_ddl.set_attribute('textuser_lexer', 'skipjoins', '''-');

exec ctx_ddl.sync_index('t1_idx','5M');




textuser@dev> select * from t1;

ID VARCHAR2_COL_T1
---------- --------------------------------------------------------------------------------
1 wal mart
4 o'conner
5 oconner
6 floyd-myer
7 floydmyer

textuser@dev> select varchar2_col_t1, varchar2_col_t2, contains( varchar2_col_t1, 'o''conner' ) hits
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '%floydmyer%' ) > 0;

VARCHAR2_COL_T1 VARCHAR2_COL_T2
HITS
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------
- ----------
floydmyer china
0

textuser@dev>




textuser@dev> /

VARCHAR2_COL_T1 VARCHAR2_COL_T2
-------------------------------------------------------------------------------- -----------------------------------
-
oconner toronto
o'conner canada

textuser@dev> select varchar2_col_t1, varchar2_col_t2, contains( varchar2_col_t1, 'o''conner' ) hits
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '%oconner%' ) > 0;

VARCHAR2_COL_T1 VARCHAR2_COL_T2
HITS
-------------------------------------------------------------------------------- -----------------------------------
- ----------
oconner toronto
4
o'conner canada
4








Tom Kyte

Followup  

September 19, 2005 - 5:01 pm UTC

but sync only syncs, it does not rebuild.

if you'd like me to look, generate the entire script that

a) creates table
b) inserts data
c) created the preference
d) create index

(eg: a test case to work with)

skipjoin

September 19, 2005 - 5:50 pm UTC

Reviewer: mohini from germany

It worked..I guess I only synched the index after adding that new attribute..instead of rebuilding the index..again..
Thanks..


textuser@dev> select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '%oconner%' ) > 0;

VARCHAR2_COL_T1 VARCHAR2_COL_T2
----------------------------------- -----------------------------------
oconner toronto
o'conner canada

textuser@dev> select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '%floydmyer%' ) > 0;

VARCHAR2_COL_T1 VARCHAR2_COL_T2
----------------------------------- -----------------------------------
floydmyer china
floyd-myer france

textuser@dev>



floyd-myer no records

September 19, 2005 - 5:56 pm UTC

Reviewer: mohini from germany

Back again..
Floydmyer does brin back both "Floydmyer" and "Floyd-myer"
but Floyd-myer brings back no records..

Thanks..


textuser@dev> select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '%floydmyer%' ) > 0;

VARCHAR2_COL_T1 VARCHAR2_COL_T2
----------------------------------- -----------------------------------
floydmyer china
floyd-myer france


textuser@dev> select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '%floyd-myer%' ) > 0;

no rows selected


Here is my script to create the test case:

drop table t1;
drop table t2;

create table t1 ( id int primary key, varchar2_col_t1
varchar2(80) );
create table t2 ( id int primary key, varchar2_col_t2
varchar2(80));


insert into t1 values ( 1, 'wal mart' );
insert into t2 values ( 1, 'microsoft' );
insert into t1 values (4, 'o''conner');
insert into t2 values ( 4, 'canada' );

insert into t1 values (5, 'oconner');
insert into t2 values ( 5, 'toronto' );

insert into t1 values (6, 'floyd-myer');
insert into t2 values ( 6, 'france' );

insert into t1 values (7, 'floydmyer');
insert into t2 values ( 7, 'china' );


grant select on t1 to ctxsys;
grant select on t2 to ctxsys;


create or replace procedure ctxsys.glued_textuser ( p_id in rowid,
p_lob in out clob )
as
begin
for x in ( select varchar2_col_t1, varchar2_col_t2
from textuser.t1, textuser.t2
where t1.id = t2.id(+)
and t1.rowid = p_id
)
loop
dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t1)+1, x.varchar2_col_t1||' ');
dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t2)+1, x.varchar2_col_t2||' ');
end loop;

end;
/
grant execute on glued_textuser to textuser;

--create preferences

exec ctx_ddl.drop_preference('textuser_datastore');
exec ctx_ddl.drop_preference('textuser_lexer');
exec ctx_ddl.drop_preference('textuser_substring_pref');

begin
ctx_ddl.create_preference( 'textuser_datastore', 'user_datastore' );
ctx_ddl.set_attribute( 'textuser_datastore', 'procedure', 'glued_textuser' );
end;
/

begin
ctx_ddl.create_preference( 'textuser_lexer', 'BASIC_LEXER' );
ctx_ddl.set_attribute( 'textuser_lexer', 'base_letter', 'YES');
ctx_ddl.set_attribute( 'textuser_lexer', 'mixed_case', 'NO' );
ctx_ddl.set_attribute('textuser_lexer', 'skipjoins', '''-');
end;
/
--to enhance the %% searches
begin
ctx_ddl.create_preference('textuser_substring_pref',
'basic_wordlist');
ctx_ddl.set_attribute('textuser_substring_pref',
'substring_index','TRUE');
end;
/


create index t1_idx on t1(varchar2_col_t1)
indextype is ctxsys.context
parameters( 'datastore ctxsys.textuser_datastore lexer ctxsys.textuser_lexer wordlist ctxsys.textuser_substring_pref memory 50m');

Tom Kyte

Followup  

September 19, 2005 - 11:43 pm UTC

ops$tkyte@ORA9IR2> select varchar2_col_t1, varchar2_col_t2
  2  from t1, t2
  3  where t1.id = t2.id (+)
  4  and contains( varchar2_col_t1, '{%floyd-myer%}' ) > 0;

VARCHAR2_COL_T1
-------------------------------------------------------------------------------
VARCHAR2_COL_T2
-------------------------------------------------------------------------------
floydmyer
china

floyd-myer
france
 

{} when do we need to add these to the keyword?

September 20, 2005 - 3:36 pm UTC

Reviewer: A reader

So, when do I need to add "{" to the keyword..
cause my partial searches don't bring any thing back..
like '{%con%}' does not bring back any records...but '%con%'
does..and so does '{%oconner%}'

1 select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4* and contains( varchar2_col_t1, '{%oconner%}' ) > 0
textuser@dev> /

VARCHAR2_COL_T1 VARCHAR2_COL_T2
----------------------------------- -----------------------------------
oconner toronto
o'conner canada

textuser@dev> ed
Wrote file afiedt.buf

1 select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4* and contains( varchar2_col_t1, '{%con%}' ) > 0
textuser@dev> /

no rows selected

textuser@dev> ed
Wrote file afiedt.buf

1 select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4* and contains( varchar2_col_t1, '%con%' ) > 0
textuser@dev> /

VARCHAR2_COL_T1 VARCHAR2_COL_T2
----------------------------------- -----------------------------------
oconner toronto
o'conner canada

textuser@dev>




Use Of Curly

September 22, 2005 - 6:05 pm UTC

Reviewer: Mohini from Germany

Tom,

Not finding much info on curly..
when does it need to be added to the keyword..
From my followup above..
It is working with some searches but not the others...

Thanks...

Tom Kyte

Followup  

September 22, 2005 - 9:51 pm UTC

curly? can you clarify

Curly Braces {}

September 22, 2005 - 10:06 pm UTC

Reviewer: Mohini from Germany

I was not getting any hits for "floyd-myer"..and you suggested the following...what are the curly braces for..
Thanks:

ops$tkyte@ORA9IR2> select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '{%floyd-myer%}' ) > 0;

VARCHAR2_COL_T1
-------------------------------------------------------------------------------
VARCHAR2_COL_T2
-------------------------------------------------------------------------------
floydmyer
china

floyd-myer
france





Tom Kyte

Followup  

September 23, 2005 - 9:04 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/text.920/a96518/cqspcl.htm#20741 <code>

Search Text

February 25, 2006 - 3:30 am UTC

Reviewer: DEBASISH GHOSH from India

I have a table with name field and values are like
NAME
-------------
DEBASISHG
DEBASISH
DEBASISHGHOSH
DEBAS ISHG
D EBA SISH G.
DEBASISH GHOSH.
GHOSH DEBASISH
DEBASHIS
DEBASHISH
DEBABRATA
DEBARATI
.......
......
LIKE THAT
I WANT TO QUERY WITH THE STRING 'DEBASISH' AND THIS WILL RETURN ALL 9 ROWS THAT MATCHES WITH THE SEARCH STRING DO NOT USING SOUNDX FUNCTION.
PLEASE LET ME KNOW IN ADVANCE
DEBASISH


Tom Kyte

Followup  

February 25, 2006 - 11:17 am UTC

I only see 5 rows that match that.

You'll have to explain your matching algorith, what you had in mind. Sounds like you might have meant to say

After removing spaces (replace(str,' ','')) I would like to retrieve all rows where that new string is like '%DEBASISH%'


perhaps that is what you were looking for?

soundex would be entirely "not appropriate" since you have multiple words.

Text Search

February 27, 2006 - 4:40 am UTC

Reviewer: Debasish Ghosh from India

actully I want if 70% character is matched with the search string then it will return the entire field value.

I don't want to use soundx or loop like

for r in 1..no of records loop
for j 1..fieldlength loop
....
....
end loop;
end loop;
if there is mellion of records then this type of loop take plenty of time.


Tom Kyte

Followup  

February 27, 2006 - 7:12 am UTC

you'll need to be very much more "precise"

for you see, you have a bunch of WORDS in a sentence:

D EBA SISH G.


I see no way that matches your search string, unless and until you give an algorithm that says step 1: remove all whitespace from consideration.


and then you need to define what precisely is 70% of your search string. Leading 70%, trailing, middle, what.

The lack of detail here makes it impossible for anyone to say anything sensible.


February 27, 2006 - 8:03 am UTC

Reviewer: Debasish Ghosh from India

remove all whitespace from consideration.
and matches Leading 70% of the search string
i.e.
search string is '%DEBASISH%' ATLEAST 70% OF THIS STRING
FROM LEADING POSITION.

FIELD VALUE ARE AFTER REMOVE ALL WHITESPACE

DEBASISH GHOSH DEBASISHGHOSH
DEBA SISH_ GHOSH DEBASISH_GHOSH
D EBA SISH G. DEBASUSHG.
GHOSH. DEBASISH GHOSHDEBASISH
DEBASHISH G DEBASHISHG
G DEB A SHI SH GDEBASHISH
DEBARUN DEBARUN
DEBASISKUMAR DEBASISKUMAR
DEBAS SINGH DEBASSINGH
DEBA BOSE DEBABOSE
DEBARATI ROY DEBARATIROY

AND THE RESULT SHOULD BE

DEBASISH GHOSH
DEBA SISH_ GHOSH
D EBA SISH G.
GHOSH. DEBASISH
DEBASHISH G
G DEB A SHI SH
DEBASISKUMAR

THAKS FOR ADVANCE

Tom Kyte

Followup  

February 27, 2006 - 8:37 am UTC

then replace whitespace with nothing (as suggested)
and take 70% of the string (using substr)

and LIKE for it.


where replace( column, ' ', '' )
like '%' || substr( :bv, 1, ceil(length(:bv)*.7) ) || '%'




take the leading 70%, wrap it with '%' - match it to the column in question.



THANKS

February 28, 2006 - 12:33 am UTC

Reviewer: DEBASISH GHOSH from INDIA

THANKS A LOT.

search text

February 28, 2006 - 3:15 am UTC

Reviewer: G from Calcutta

it's run ok but some porblem
i have

DEBASISHG
DEBA_ SISH G
DEBA* SISH
DEBASHISH
DEBASISHG
GDEBASHISH
GHOSH DEBASISH

when i query through


  select str from cc where
     str like '%' || substr( 'DEBASISH', 1, ceil(length  
     ('DEBASISH')*.7) ) || '%'
SQL> /

STR
--------------------
DEBASISHG
DEBASISHG
GHOSH DEBASISH

only 3 rows selectd but  it should be

DEBASISHG
DEBASISHG
DEBASISH
DEBASISHG
GHOSHDEBASISH

truncate all characters other than a-z or A-Z or 0-9


 

Tom Kyte

Followup  

February 28, 2006 - 7:19 am UTC

and you cannot figure out based in the techniques provided what you might need to do.

read about

replace
translate
and in 10g, the regex functions which might be even more flexible for you

(requirements specification, I'm wondering what ever happened to that "fine art". Watching the evolution of this one is sort of funny)

I am getting extra rows in the following scenario

March 09, 2006 - 5:47 pm UTC

Reviewer: Srini from boston,MA USA

create table srini_names (nid number,name varchar(20));
create table srini_addrs (aid number,nid number, addr varchar2(50));

insert into srini_names values (1,'srini','x');
insert into srini_names values (2,'reko','x');
insert into srini_names values (3,'ron','x');
insert into srini_names values (4,'barry','x');

insert into srini_addrs values (1,1,'1 governors way');
insert into srini_addrs values (2,1,'10 speen street');
insert into srini_addrs values (3,2,'50 main street');
insert into srini_addrs values (4,2,'678 maple street');
insert into srini_addrs values (5,3,'891 brrok pkwy');
insert into srini_addrs values (6,3,'1 any governors');

CREATE OR REPLACE procedure srini_name_addr (p_id in rowid,p_lob IN OUT clob)
is
templob clob;
temp varchar2(4000);
begin

for c1 in (select nid,name from pbds.srini_names where rowid = p_id)
loop
dbms_lob.writeAppend( p_lob, length(c1.name)+1, c1.name||' ');
for c2 in (select addr from pbds.srini_addrs a where a.nid = c1.nid)
loop

--dbms_lob.append( templob, c2.addr );
--dbms_lob.append( p_lob, c2.addr );
dbms_lob.append( p_lob, c2.addr );

--temp := temp || c2.addr;

end loop;

end loop;
end;
/

create index srini_name_addr_idx on pbds.srini_names(dummy) indextype is
ctxsys.context parameters('datastore srini_name_addr_ds');

select name,addr from srini_names n, srini_addrs a where n.nid=a.nid and contains (dummy,'governors')>0;

returns
NAME ADDR
-------------------- -----------------------------------
srini 1 governors way
srini 10 speen street

as supposed to
NAME ADDR
-------------------- -----------------------------------
srini 1 governors way


Tom Kyte

Followup  

March 10, 2006 - 11:55 am UTC

and you don't see why?


one would need a complete example (i cannot run your example - even after the table correction, missing a preference) - but look at your function and "think about it"

you seem to be glueing ever address for a given name into a big string, so.... all rows with the same name - would have every address - by your design.


Best sometimes not to post non-functional code and say "why doesn't this work" when you don't also post "this is what I'm trying to accomplish" - because the code does exactly what you programmed it to do.

correction in table create script

March 09, 2006 - 5:50 pm UTC

Reviewer: Srini from boston,MA USA

create table srini_names (nid number,name varchar(20),dummy varchar(10));


March 10, 2006 - 1:40 pm UTC

Reviewer: Srini from Boston,MA

Apologies for not being clear ..here is the missing pref
begin
ctx_ddl.create_preference( 'srini_name_addr_ds', 'user_datastore' );
ctx_ddl.set_attribute( 'srini_name_addr_ds', 'procedure', 'srini_name_addr' );
end;
/

requirement is to type a text in single text box which should search names and addresses to return the name and address.
If i type a text , and the text is part of address , i should get the name and address with the text typed in .
normal query would be . ..
select name,addr from srini_names n,
srini_addrs a
where n.nid=a.nid(+)
and a.addr like '%governors%'
which returns
NAME ADDR
-------------------- ----------------
srini 1 governors way

I am trying to achive the same with oracle text with single index. Since i am driving from names table each name is getting appended with all the addresses for that given name .

May be i am not supposed to glue the columns this way .





Tom Kyte

Followup  

March 10, 2006 - 8:32 pm UTC

but I answered your question - look at your function.


You are concatenating them all together - look at your code!!!


Your code glues every address together. Rethink your logic, you don't want a double loop - you just want to query out two columns from a single row.

Also, you might look at a ctxcat index instead.

March 24, 2006 - 10:38 am UTC

Reviewer: Srini Akkala from boston,ma

Thank you very much . You said
"you just want to query out two columns from a single row."

I am not able to figure out how to do this

Any code samples would be really helpfull.

Thank you very much


Tom Kyte

Followup  

March 24, 2006 - 10:47 am UTC

stop storing them as a big string, look at your code.

multi columns Mixed structured query with TEXT search for best performance

August 17, 2006 - 3:23 pm UTC

Reviewer: Charlie Zhu from Victoria, BC Canada

Here is my story:

Oracle 10.1 or 10.2 are both OK.

I'd like to get Oracle TEXT work for Mixed structured query
for search conditions on many columns.

TEXT developer's guide says one CONTAINS (maybe on one TEXT index)
for best performance.

Book table(
Author varchar2(200),
Title varchar2(200),
Publisher varchar2(200),
Price number(8,2),
Add_date date)

My contains systax will be like:

contains('london WITHIN authorname AND life WITHIN titlename AND price < 10.00 AND add_date > TO_DATE(''17-01-2006'',''DD-MM-YYYY'')');

How to make it work?

Now I can query against 3 text/varchar2 columns, for each or together.
with multi_column_datastore AND section_group

1: long in AUTHORNAME and life in TitleName

variable l_string varchar2(4000)
exec :l_string := 'london WITHIN authorname AND life WITHIN titlename';
SELECT /*+ first_rows(100) */ rowdf,AUTHORNAME, titlename
FROM abedba.BOOK
WHERE CONTAINS (rowdf, :l_string) > 0;

2: london AND life across many columns

exec :l_string := 'london AND life';
SELECT /*+ first_rows(100) */ rowdf,AUTHORNAME, titlename
FROM abedba.BOOK
WHERE CONTAINS (rowdf, :l_string) > 0;


FYI,

exec ctx_ddl.drop_preference('book_multi');
begin
ctx_ddl.create_preference('book_multi','multi_column_datastore');
ctx_ddl.set_attribute('book_multi', 'columns','authorname,titlename');
end;
/
-- create the section preference
begin
ctx_ddl.drop_section_group ( group_name => 'book_section_group' );
end;
/

begin
ctx_ddl.create_section_group ( group_name => 'book_section_group' ,
group_type => 'basic_section_group' );
ctx_ddl.add_field_section ( group_name => 'book_section_group' ,
section_name => 'authorname', tag => 'authorname', visible => true );
ctx_ddl.add_field_section ( group_name => 'book_section_group' ,
section_name => 'titlename', tag => 'titlename', visible => true );
end;
/

create index book_text_index on book(rowdf)
indextype is ctxsys.context
parameters ('DATASTORE book_multi section group book_section_group');

Thanks a lot.

Tom Kyte

Followup  

August 17, 2006 - 3:26 pm UTC

i'm confused - i did not run your example, but isn't your example the answer to "how to make it work"?

Re: Multi columns Mixed structured query with TEXT search for best performance

August 17, 2006 - 3:52 pm UTC

Reviewer: Charlie Zhu from Victoria, BC Canada

No. I only make it work for TEXT/Varchar2 columns.

I do not know how to make it work include NUMBER, DATE columns, include structured query together in one CONTAINS.

Thanks for the quick response.

Tom Kyte

Followup  

August 17, 2006 - 4:19 pm UTC

numbers and dates are indexable with your multicolumn data store
</code> http://docs.oracle.com/docs/cd/B19306_01/text.102/b14218/cdatadic.htm#i1006391 <code>

just index them as well, use to_char to format the dates in whatever fashion you would like.

Re: Multi columns Mixed structured query with TEXT search for best performance

August 17, 2006 - 4:57 pm UTC

Reviewer: Charlie Zhu from Victoria, BC Canada

That's good to know.

How to write a CONTAINS expression for:
'life WITHIN titlename AND price < 10.00' ?

I only got '10.00 WITHIN price' works.

Thanks again,
Charlie

Tom Kyte

Followup  

August 18, 2006 - 7:51 am UTC

likely you are looking at using the ctxcat index then, less "text" functionality - more "structured"

</code> http://docs.oracle.com/docs/cd/B19306_01/text.102/b14218/csql.htm#sthref111 <code>

Re: CONTAINS syntax on multiple columns

August 18, 2006 - 12:18 pm UTC

Reviewer: Charlie Zhu from Victoria, BC Canada

You can only index one TEXT/VARCHAR2 column with catalog CTXCAT type TEXT index.

How can I index many VARCHAR2 and many NUMBER/DATE columns?
for Mixed structured query
to get the result set like:

contains('london WITHIN authorname AND life WITHIN titlename AND price < 10.00
AND add_date > TO_DATE(''17-01-2006'',''DD-MM-YYYY'')
ORDER BY price');

You may come to our site to have a look: </code> http://www.abebooks.com/ <code>
and click the "Advanced Search",
It's very common to let customer search book by Title, Author, Decription and Price at same time.
(We're using Endeca as TEXT search engine, but get data sync problem from Oracle database)

Thanks again.


Tom Kyte

Followup  

August 18, 2006 - 12:40 pm UTC

all i can do is point you to the documentation there, you have ctxcat searches (and a description of what it can and cannot do) and you have the contains searches.

sounds like you might be doing what I do. A mix of contains and relational.


select ..
from ...
where contains( ... ) > 0
and subject like :bind;


for example - when I use advanced search here on asktom - the query looks a bit like that.

Re: Multi columns Mixed structured query with TEXT search for best performance

August 18, 2006 - 1:23 pm UTC

Reviewer: Charlie Zhu from Victoria, BC Canada

I guess this discussion will continue for a while. :)

Here is one solution: Advanced MDATA - tips and tricks
</code> http://www.oracle.com/technology/products/text/htdocs/mdata_tricks.html

see "Mixed Queries with Range Predicates" and "Sorting"

I just don't like to re-invent the wheel, and try to get a easy fast solution.

the 2nd solution is progressive relaxation:
http://www.oracle.com/technology/products/text/htdocs/prog_relax.html <code>

Both these solution need granula To_Char(Number/Date) scheme design
and some PL/SQL coding to do the logical partition detection work.

For example,

select score(1), book_info from test_table
where contains (month_info, '
<query>
<textquery>
<progression>
<seq> mdata(month, 122003) </seq>
<seq> mdata(month, 112003) </seq>
<seq> mdata(month, 102003) </seq>
<seq> mdata(month, 092003) </seq>
<seq> mdata(month, 082003) </seq>
<seq> mdata(month, 072003) </seq>
<seq> mdata(month, 062003) </seq>
<seq> mdata(month, 052003) </seq>
<seq> mdata(month, 042003) </seq>
<seq> mdata(month, 032003) </seq>
<seq> mdata(month, 022003) </seq>
<seq> mdata(month, 012003) </seq>
</progression>
</textquery>
</query>
',1) > 0 and rownum <= 5;



Tom Kyte

Followup  

August 18, 2006 - 4:23 pm UTC

thanks for the input, appreciate that!

new TEXT search product with TripleHop (MatchPoint)

August 18, 2006 - 2:35 pm UTC

Reviewer: Charlie Zhu from Victoria, BC Canada

I know Oracle acquired TripleHop (MatchPoint), I’m interesting the roadmap.
Should I wait until the MatchPoint is built into Oracle database, to migrate our “Endeca” search engine to Oracle?
--Endeca is a light weight distribution TEXT search engine, we got 70 search servers now.
(Maybe that time Oracle can do better mixed structure query on many text and many number/date columns.)

BTW,
I did not get any rows returned from your Advanced Search page.
Just a message:
{gold} Approximately 356 records found based on text query.

Can I see your SQL text behind the search (if it’s possible)?


Tom Kyte

Followup  

August 18, 2006 - 4:29 pm UTC

I get hits on gold on the advanced search?

I got approximately 357 (since you added the word gold here..)

did you put in more critera?

all I do is add a where clause based on the inputs you give me, just like this:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>

Mixed Queries with Range Predicates

August 18, 2006 - 6:16 pm UTC

Reviewer: Charlie Zhu from Victoria, BC Canada

Good to know I can do some contribution to AskTom site!

MDATA and Progressive Relaxation work good on 50,000 rows, I'm going to benchmark it on 80 million rows data (real scale) and paste my sample code soon.

Different levels of mdata is good for date,
Progressive Relaxation is good for number(price ...)

Here are my search conditions on your Advanced Search,
--
Last Updated Between 01-AUG-2004 and 01-AUG-2006
Subject Like: search
Page Contains: gold
Order By: Last Updated

I just wonder how you translate it to SQL Contains...

Thanks and have a good weekend.

context search

August 24, 2006 - 3:57 am UTC

Reviewer: sreevani from India

I am using oracle context search for a name to search, but Iam not getting results for some names,although data is available in database.

Tom Kyte

Followup  

August 27, 2006 - 7:41 pm UTC

sorry?

I really don't know what else to say, given that the information provided by you is similar to me saying to you:

my car won't start, although it used to start.



oracle text

September 09, 2006 - 2:12 am UTC

Reviewer: sreevani from India

Reviewer: sreevani from India

I am using oracle context search for a name to search, but Iam not getting results for some names,although data is available in database.

U didn't get my point. My query is like this
select name fro tran_ec where contains(name,'(axx or a) and rama and mohan').

The data in the database is like this:
'A.Rama MOHAN'.

This is an example. But for some lots of data it is the behavior is same.i.e No rows selected. Mainly for initials starting with A and S.Please help me in this regard.

REgards
vani.







Tom Kyte

Followup  

September 09, 2006 - 12:19 pm UTC

When and if I locate "U", I'll be sure to let them know?



you give a completely and utterly incomplete example to work with.

your initial question above had no point - it was of the "my car won't start" type, no information, not a thing anyone could be expected to comment on.  think about it.

(a would be "a stopword" however, not really indexed.... you would have to have it stop being a stopword

http://docs.oracle.com/docs/cd/B19306_01/text.102/b14217/ind.htm#sthref263

ops$tkyte%ORA10GR2> create table t ( name varchar2(30) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 'A.Rama MOHAN' );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(name) indextype is ctxsys.context;

Index created.

ops$tkyte%ORA10GR2> select token_text from DR$T_IDX$I;

TOKEN_TEXT
----------------------------------------------------------------
MOHAN
RAMA

ops$tkyte%ORA10GR2> drop index t_idx;

Index dropped.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          ctx_ddl.create_stoplist( 'empty_stoplist', 'BASIC_STOPLIST' );
  3  end;
  4  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> create index t_idx on t(name) indextype is ctxsys.context parameters( 'stoplist empty_stoplist' );

Index created.

ops$tkyte%ORA10GR2> select token_text from DR$T_IDX$I;

TOKEN_TEXT
----------------------------------------------------------------
A
MOHAN
RAMA

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t where contains( name, '(axx or a) and rama and mohan' ) > 0;

NAME
------------------------------
A.Rama MOHAN
 

oracle text

September 09, 2006 - 2:13 am UTC

Reviewer: sreevani from India

Reviewer: sreevani from India

I am using oracle context search for a name to search, but Iam not getting results for some names,although data is available in database.

U didn't get my point. My query is like this
select name from tran_ec where contains(name,'(axx or a) and rama and mohan')>0.

The data in the database is like this:
'A.Rama MOHAN'.

This is an example. But for some lots of data it is the behavior is same.i.e No rows selected. Mainly for initials starting with A and S.Please help me in this regard.

REgards
vani.







SYNC to sync all the indexes

September 11, 2006 - 10:20 am UTC

Reviewer: Vinayak Awasthi from Zurich

Tom,
we need to call ctx_ddl.sync everytime the contents of the base table is changed for the column on which search is performed.
My question is should we call this procedure every time when a dml is performed.Scenario is we have an internal website where news items are continously updated by the news team.Do you recommend calling this function after every dml continously (through a trigger) or it can be done through some job. But the search will not return any data till the job has run and updated teh index.

Your advice on this please.

Tom Kyte

Followup  

September 11, 2006 - 10:41 am UTC

no, you, don't.

10g can sync on commit.

You can sync every N minutes (that is my approach on asktom).

How often I recreate the index

September 12, 2006 - 2:56 am UTC

Reviewer: sreevani from india

Tom,
How often I recreate the index for new inserted and updated records.

vani

Tom Kyte

Followup  

September 12, 2006 - 8:27 am UTC

??

10g XE not returning data

September 12, 2006 - 9:13 am UTC

Reviewer: Vinayak from Zurich

Hi Tom,
You said that 10g auto sync our index on commit.I am using Oracle 10g XE and set autocommit flag unchecked. I insert into the table , commits it explicitly and then run the query but it didn't returned any rows. Am I missing something there.

Here is my script:

create table oracle_text(text varchar2(100));
create index idx_oracle_text on oracle_text(text) INDEXTYPE IS CTXSYS.CONTEXT;

insert into oracle_text values('Keep good work going');
insert into oracle_text values('Knowledge sharing is gem');

commit;

select * from oracle_text where contains(text,'Keep')>0;
<<no data found>>

select * from oracle_text where contains(text,'sharing')>0;
<<no data found>>

Then I sync the indexes:

begin
ctx_ddl.sync_index('idx_oracle_text');
end;

and again tried the queries:

select * from oracle_text where contains(text,'Keep')>0;
Keep good work going

select * from oracle_text where contains(text,'sharing')>0;
Knowledge sharing is gem

Here is my version info:
select * from v$version;
-----------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


Yours inputs Tom...

Tom Kyte

Followup  

September 12, 2006 - 9:19 am UTC

you didn't enable the automatic syncronization

I said "it can", not that "it does by default"

Thanks!!!

September 12, 2006 - 9:24 am UTC

Reviewer: vinayak from Zurich

Tom, Thanks for that.
From where do we enable this automatic syncronization, something in init.ora file.

Tom Kyte

Followup  

September 12, 2006 - 10:51 am UTC

</code> http://docs.oracle.com/docs/cd/B19306_01/text.102/b14218/whatsnew.htm#sthref18 <code>

it is part of the create index

Question on multi_column_datastore

October 27, 2006 - 3:42 pm UTC

Reviewer: Manjunath from India

Hi Tom,

We have a requirement to search for strings that can be contained in multiple columns(lets say in 12 columns) in the same table. For this, an index on all these columns(using multi_column_datastore) works fine. However, if there is a need to search only in 5 columns(instead of 12), should another index be created or is there some other way?

Thanks and regards
Manjunath

Contains Instead of LIKE '%'

January 26, 2007 - 2:53 am UTC

Reviewer: Emad Kehail from Palestine

Hello Tom,

I hope you help me in this:

I have a table looks like this

create table subscribers (
id numer(10),
first_name varchar2(30),
father_name varchar2(30),
grandfather_name varchar2(30),
last_name varchar2(30))
/

The application is built using Oracle Forms. Many times, the end users are not so sure of the spelling of the name, therefore they use the "%" wildcard with name fields. This will be reflected to the queries the application will send them to the Oracle Server.

We have the following queries
1) select *
from subscribers
where last_name like '%family_name%';

2) select *
from subscribers
where last_name like 'family_name%';

3) select *
from subscribers
where last_name like '%family_name%' and first_name like '%first_name%';

4) select *
from subscribers
where last_name like 'family_name%' and first_name like 'first_name%';


As well as searching on the father_name and grandfather_name fields. But most of the search are on the first_name and the last_name.

These queries are killing the server since we have millions of records. BTree indexes will not help here because of the LIKE and the "%"

I am thinking to use Oracle Text here, but I am not sure whether I have to go for a CONTEXT index on each individual column, or I can use the MULTI_COLUMN_DATASTORE indexing.

Thanks

blast from the past ...

January 17, 2008 - 8:50 am UTC

Reviewer: LJ

On July 8, 2003 Tom Best posted about having a lot of open cursors left behind when using the CTX_DDL and the glue_them_together procedure you shared with us.

After a few follow ups on that, the thread changed to some other focus.

I have a 10.2.0.3 database and set up the Text feature exactly as the examples above (multi_column_datastore), and it works great, except it leaves a lot of cursors open. Like 2,000 of them.

Did anyone ever find a reason why, or any way to close the cursor? And possibly what cursor or cursors are in fact being left open?

CONTAINS Clause with OR and other joined tables gives wrong set of results

June 05, 2008 - 4:50 am UTC

Reviewer: VKOUL from WA USA

Hi Tom,

I am not able to make out why is it happenning.

From SYS schema:
-- Run as SYS START
create user test identified by test default tablespace users quota unlimited on users;

grant connect, resource to test;

grant ctxapp to test;

grant execute on ctx_ddl to test;



-- Run as SYS STOP

From the newly created user test do :


DECLARE
   ts  VARCHAR2(30) := 'USERS';
BEGIN

    BEGIN
       ctx_ddl.drop_preference('my_storage');
    EXCEPTION WHEN OTHERS THEN NULL;    -- Ignore errors if the preference is not there
    END;

    BEGIN
    ctx_ddl.drop_preference('my_lexer');
    EXCEPTION WHEN OTHERS THEN NULL;    -- Ignore errors if the preference is not there
    END;

    BEGIN
    ctx_ddl.drop_stoplist('my_stoplist');
    EXCEPTION WHEN OTHERS THEN NULL;    -- Ignore errors if the stop list is not there
    END;

    ctx_ddl.create_preference('my_storage','BASIC_STORAGE');
    ctx_ddl.set_attribute('my_storage','I_TABLE_CLAUSE','TABLESPACE '||ts);
    ctx_ddl.set_attribute('my_storage','K_TABLE_CLAUSE','TABLESPACE '||ts);
    ctx_ddl.set_attribute('my_storage','R_TABLE_CLAUSE','TABLESPACE '||ts);
    ctx_ddl.set_attribute('my_storage','N_TABLE_CLAUSE','TABLESPACE '||ts);
    ctx_ddl.set_attribute('my_storage','I_INDEX_CLAUSE','TABLESPACE '||ts);

    ctx_ddl.create_preference('my_lexer','BASIC_LEXER');
    ctx_ddl.set_attribute('my_lexer','INDEX_THEMES','FALSE');
    ctx_ddl.set_attribute('my_lexer','INDEX_TEXT','TRUE');
    ctx_ddl.set_attribute('my_lexer','ENDJOINS','+');

    -- Create empty stop list (noise words)
    CTX_DDL.CREATE_STOPLIST('my_stoplist', 'BASIC_STOPLIST');

    -- use this syntax to add words
    -- CTX_DDL.ADD_STOPWORD('my_stoplist', 'the');

END;
/

CREATE TABLE T1
(
  T1_PK    NUMBER(10)                 ,
  T1_NAME  VARCHAR2(900 BYTE) NOT NULL,
  T1_CODE  VARCHAR2(765 BYTE)         ,
  T1_ROOT  NUMBER(10)
)
/

CREATE INDEX FT_TABLE_NAME ON T1 (T1_NAME)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer my_lexer storage my_storage stoplist my_stoplist')
/

CREATE INDEX FT_TABLE_CODE ON T1 (T1_CODE)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer my_lexer storage my_storage stoplist my_stoplist')
/

CREATE INDEX IDX_TMX_ACTIVITY_6YHX9H0P ON T1 (T1_CODE, T1_PK)
/

CREATE INDEX IDX_TMX_ACTIVITY_6YHX9H0U ON T1 (T1_PK, T1_ROOT)
/

CREATE INDEX IDX_TMX_ACTIVITY_PATCH2 ON T1 (UPPER("T1_NAME"), T1_PK)
/

CREATE UNIQUE INDEX PK_T1 ON T1 (T1_PK)
/

ALTER TABLE T1 ADD (CONSTRAINT PK_T1 PRIMARY KEY (T1_PK) USING INDEX)
/

CREATE TABLE T3
(
  T3_PK         NUMBER(10)                     NOT NULL,
  T3_DOMAININD  NUMBER(1)                      NOT NULL
)
/

CREATE UNIQUE INDEX PK_T3 ON T3 (T3_PK)
/

ALTER TABLE T3 ADD (CONSTRAINT PK_T3 PRIMARY KEY (T3_PK) USING INDEX)
/

CREATE TABLE T2
(
  T2_ACTFK     NUMBER(10)                   NOT NULL,
  T2_DOMAINFK  NUMBER(10)                   NOT NULL,
  T2_PRMYIND   NUMBER(3)                    NOT NULL
)
/

CREATE INDEX IDX_T2_6YHX9H0J ON T2 (T2_ACTFK, T2_DOMAINFK, T2_PRMYIND)
/

CREATE UNIQUE INDEX PK_T2 ON T2 (T2_DOMAINFK, T2_ACTFK)
/

ALTER TABLE T2 ADD (CONSTRAINT PK_T2 PRIMARY KEY (T2_DOMAINFK, T2_ACTFK) USING INDEX)
/

ALTER TABLE T2 ADD (CONSTRAINT FK_T2_ACTFK_PLKKJS0G    FOREIGN KEY (T2_ACTFK   ) REFERENCES T1 (T1_PK) ON DELETE CASCADE,
                    CONSTRAINT FK_T2_DOMAINFK_PLKKJS0H FOREIGN KEY (T2_DOMAINFK) REFERENCES T3 (T3_PK) ON DELETE CASCADE
                   )
/

insert into t1
select rownum, 'name35 '||TO_CHAR(ROWNUM), 'name35', MOD(ROWNUM, 5)
from   all_objects
where  rownum < 36;

insert into t3
select rownum, 1
from all_objects where rownum < 10;

insert into t3
select rownum+10, 0
from all_objects where rownum < 10;

insert into t2
select rownum, 2, 1
from   all_objects
where  rownum < 36;

insert into t2
select rownum, 1, 1
from   all_objects
where  rownum < 36;

BEGIN
    FOR ind IN (SELECT DISTINCT pnd_index_name
                FROM   ctx_user_pending
               )
    LOOP
       ctxsys.ctx_ddl.sync_index(idx_name => ind.pnd_index_name, memory => '16M');
    END LOOP;
END;
/

commit;

COLUMN T1_Name      Format a15
COLUMN T1_Code      Format a15

PROMPT Pulling the whole set of data
PROMPT (There are 28 records with T3_PK = 1 and 28 records with T3_PK = 2)

SELECT T2_PrmyInd, T3_PK, T3_DomainInd, T1_Name, T1_Code
FROM   T1
INNER  
JOIN   T2 ON T1_Root     = T2_ActFK
INNER  
JOIN   T3 ON T2_DomainFK = T3_PK
WHERE  
       T2_PrmyInd    = 1
AND    
       T3_DomainInd  = 1
AND    
       (
        (contains(T1_Name, 'name35') > 0)
        OR
        (contains(T1_Code, 'name35') > 0)
       )
ORDER  BY T3_PK
/

T2_PRMYIND      T3_PK T3_DOMAININD T1_NAME         T1_CODE
---------- ---------- ------------ --------------- --------
         1          1            1 name35 1        name35
         1          1            1 name35 2        name35
         1          1            1 name35 4        name35
         1          1            1 name35 7        name35
         1          1            1 name35 9        name35
         1          1            1 name35 12       name35
         1          1            1 name35 14       name35
         1          1            1 name35 17       name35
         1          1            1 name35 19       name35
         1          1            1 name35 31       name35
         1          1            1 name35 29       name35
         1          1            1 name35 28       name35
         1          1            1 name35 27       name35
         1          1            1 name35 26       name35
         1          1            1 name35 24       name35
         1          1            1 name35 23       name35
         1          1            1 name35 22       name35
         1          1            1 name35 21       name35
         1          1            1 name35 34       name35
         1          1            1 name35 33       name35
         1          1            1 name35 32       name35
         1          1            1 name35 18       name35
         1          1            1 name35 16       name35
         1          1            1 name35 13       name35
         1          1            1 name35 11       name35
         1          1            1 name35 8        name35
         1          1            1 name35 6        name35
         1          1            1 name35 3        name35
         1          2            1 name35 1        name35
         1          2            1 name35 34       name35
         1          2            1 name35 33       name35
         1          2            1 name35 32       name35
         1          2            1 name35 31       name35
         1          2            1 name35 29       name35
         1          2            1 name35 28       name35
         1          2            1 name35 27       name35
         1          2            1 name35 26       name35
         1          2            1 name35 24       name35
         1          2            1 name35 23       name35
         1          2            1 name35 22       name35
         1          2            1 name35 21       name35
         1          2            1 name35 19       name35
         1          2            1 name35 18       name35
         1          2            1 name35 17       name35
         1          2            1 name35 16       name35
         1          2            1 name35 14       name35
         1          2            1 name35 13       name35
         1          2            1 name35 12       name35
         1          2            1 name35 11       name35
         1          2            1 name35 2        name35
         1          2            1 name35 4        name35
         1          2            1 name35 7        name35
         1          2            1 name35 9        name35
         1          2            1 name35 8        name35
         1          2            1 name35 6        name35
         1          2            1 name35 3        name35

56 rows selected.

SQL> 

PROMPT Filtering on T3_PK = 1, should give 28 records but it gives 2 records.

SELECT T2_PrmyInd, T3_PK, T3_DomainInd, T1_Name, T1_Code
FROM   T1
INNER  
JOIN   T2 ON T1_Root     = T2_ActFK
INNER  
JOIN   T3 ON T2_DomainFK = T3_PK
WHERE  
       T2_PrmyInd    = 1
AND    
       T3_DomainInd  = 1
AND    
       (
        (contains(T1_Name, 'name35') > 0)
        OR
        (contains(T1_Code, 'name35') > 0)
       )
AND    T3_PK         = 1
/

T2_PRMYIND      T3_PK T3_DOMAININD T1_NAME         T1_CODE
---------- ---------- ------------ --------------- --------
         1          1            1 name35 1        name35
         1          1            1 name35 2        name35

2 rows selected.

PROMPT Filtering on T3_PK = 1 after doing an inline view with ROWNUM > 0,
PROMPT so that it won't merge the queries, should give 28 records and it gives 28 records.

SELECT * FROM (
SELECT T2_PrmyInd, T3_PK, T3_DomainInd, T1_Name, T1_Code
FROM   T1
INNER  
JOIN   T2 ON T1_Root     = T2_ActFK
INNER  
JOIN   T3 ON T2_DomainFK = T3_PK
WHERE  
       T2_PrmyInd    = 1
AND    
       T3_DomainInd  = 1
AND    
       (
        (contains(T1_Name, 'name35') > 0)
        OR
        (contains(T1_Code, 'name35') > 0)
       )
AND    ROWNUM > 0
)
WHERE  T3_PK         = 1
/

T2_PRMYIND      T3_PK T3_DOMAININD T1_NAME         T1_CODE
---------- ---------- ------------ --------------- -------
         1          1            1 name35 1        name35
         1          1            1 name35 2        name35
         1          1            1 name35 3        name35
         1          1            1 name35 4        name35
         1          1            1 name35 6        name35
         1          1            1 name35 7        name35
         1          1            1 name35 8        name35
         1          1            1 name35 9        name35
         1          1            1 name35 11       name35
         1          1            1 name35 12       name35
         1          1            1 name35 13       name35
         1          1            1 name35 14       name35
         1          1            1 name35 16       name35
         1          1            1 name35 17       name35
         1          1            1 name35 18       name35
         1          1            1 name35 19       name35
         1          1            1 name35 21       name35
         1          1            1 name35 22       name35
         1          1            1 name35 23       name35
         1          1            1 name35 24       name35
         1          1            1 name35 26       name35
         1          1            1 name35 27       name35
         1          1            1 name35 28       name35
         1          1            1 name35 29       name35
         1          1            1 name35 31       name35
         1          1            1 name35 32       name35
         1          1            1 name35 33       name35
         1          1            1 name35 34       name35

28 rows selected.

SQL> 



Is there something I am missing while taking creating these fulltext indexes.

Observations :

1. If we put the CONTAINS before all other conditions then results are coming as expected.
OR
2. If we take out one CONTAINS on the T1_CODE out, the results are coming as expected.

SELECT T2_PrmyInd, T3_PK, T3_DomainInd, T1_Name, T1_Code
FROM   T1
INNER
JOIN   T2 ON T1_Root     = T2_ActFK
INNER
JOIN   T3 ON T2_DomainFK = T3_PK
WHERE
       T2_PrmyInd    = 1
AND
       T3_DomainInd  = 1
AND
       (
        (contains(T1_Name, 'name35') > 0)
--        OR
--        (contains(T1_Code, 'name35') > 0)
       )
AND    T3_PK         = 1
/

T2_PRMYIND      T3_PK T3_DOMAININD T1_NAME         T1_CODE
---------- ---------- ------------ --------------- --------
         1          1            1 name35 34       name35
         1          1            1 name35 33       name35
         1          1            1 name35 32       name35
         1          1            1 name35 31       name35
         1          1            1 name35 29       name35
         1          1            1 name35 28       name35
         1          1            1 name35 27       name35
         1          1            1 name35 26       name35
         1          1            1 name35 24       name35
         1          1            1 name35 23       name35
         1          1            1 name35 22       name35
         1          1            1 name35 21       name35
         1          1            1 name35 19       name35
         1          1            1 name35 18       name35
         1          1            1 name35 17       name35
         1          1            1 name35 16       name35
         1          1            1 name35 14       name35
         1          1            1 name35 13       name35
         1          1            1 name35 12       name35
         1          1            1 name35 11       name35
         1          1            1 name35 9        name35
         1          1            1 name35 8        name35
         1          1            1 name35 7        name35
         1          1            1 name35 6        name35
         1          1            1 name35 4        name35
         1          1            1 name35 3        name35
         1          1            1 name35 2        name35
         1          1            1 name35 1        name35

28 rows selected.

SQL> 

SELECT T2_PrmyInd, T3_PK, T3_DomainInd, T1_Name, T1_Code
FROM   T1
INNER
JOIN   T2 ON T1_Root     = T2_ActFK
INNER
JOIN   T3 ON T2_DomainFK = T3_PK
WHERE
       (
        (contains(T1_Name, 'name35') > 0)
        OR
        (contains(T1_Code, 'name35') > 0)
       )
AND    T2_PrmyInd    = 1
AND
       T3_DomainInd  = 1
AND
       T3_PK         = 1

/

T2_PRMYIND      T3_PK T3_DOMAININD T1_NAME         T1_CODE
---------- ---------- ------------ --------------- --------
         1          1            1 name35 1        name35
         1          1            1 name35 2        name35
         1          1            1 name35 3        name35
         1          1            1 name35 4        name35
         1          1            1 name35 6        name35
         1          1            1 name35 7        name35
         1          1            1 name35 8        name35
         1          1            1 name35 9        name35
         1          1            1 name35 11       name35
         1          1            1 name35 12       name35
         1          1            1 name35 13       name35
         1          1            1 name35 14       name35
         1          1            1 name35 16       name35
         1          1            1 name35 17       name35
         1          1            1 name35 18       name35
         1          1            1 name35 19       name35
         1          1            1 name35 21       name35
         1          1            1 name35 22       name35
         1          1            1 name35 23       name35
         1          1            1 name35 24       name35
         1          1            1 name35 26       name35
         1          1            1 name35 27       name35
         1          1            1 name35 28       name35
         1          1            1 name35 29       name35
         1          1            1 name35 31       name35
         1          1            1 name35 32       name35
         1          1            1 name35 33       name35
         1          1            1 name35 34       name35

28 rows selected.

SQL> 
SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL> 



Could it be an Oracle bug ?

Thanks

Tom Kyte

Followup  

June 05, 2008 - 10:00 am UTC

before I even look at this - a challenge for you

HOW SMALL CAN YOU MAKE THIS

I know it could be smaller.

when I have to hit page down over and over - it is just too big - I'm sure you don't need 80% of the stuff there to demonstrate the issue.

for Vkoul: works in 11g, better ways

June 05, 2008 - 2:39 pm UTC

Reviewer: Barbara Boehmer from Riverside County, CA USA

Vkoul,

I ran your script on 11g and it returns 28 rows as it should, not just the 2 that you got in 9i.  I have noticed in 9i, 10g, and 11g, that using multiple contains clauses with multiple context indexes causes problems, including sometimes just getting an error instead of results.  There are some things that seem to help, such as using the non-ANSI join syntax and putting the contains clauses first and sometimes using inline views or hints.  However, it is best to minimize the contains clauses by either using a user_datastore with a procedure to concatenate the values or if the text columns are all in one table, as in your case, you can just use a multi_column_datastore.  Below, I have provided a run of your script on 11g, followed by suggested query syntax with your existing indexes, followed by a query using a multi_column_datastore.  Also, there is an Oracle Text forum on the OTN forums, where you can post such questions questions about Oracle Text:

http://forums.oracle.com/forums/forum.jspa?forumID=71

It is usually best to try posting your Oracle Text problem there first.  Tom Kyte usually has a backlog of questions, so we consider him a scarce resource, and try to ask him only when the rest of us can't figure it out or agree amongst ourselves.

Regards,
Barbara

SYS@orcl_11g> -- your test:
SYS@orcl_11g> select banner from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SYS@orcl_11g> create user test identified by test default tablespace users quota unlimited on users;

User created.

SYS@orcl_11g> 
SYS@orcl_11g> grant connect, resource to test;

Grant succeeded.

SYS@orcl_11g> 
SYS@orcl_11g> grant ctxapp to test;

Grant succeeded.

SYS@orcl_11g> 
SYS@orcl_11g> grant execute on ctx_ddl to test;

Grant succeeded.

SYS@orcl_11g> 
SYS@orcl_11g> connect test/test
Connected.
TEST@orcl_11g> 
TEST@orcl_11g> 
TEST@orcl_11g> DECLARE
  2   ts  VARCHAR2(30) := 'USERS';
  3  BEGIN
  4  
  5    BEGIN
  6       ctx_ddl.drop_preference('my_storage');
  7    EXCEPTION WHEN OTHERS THEN NULL;    -- Ignore errors if the preference is not there
  8    END;
  9  
 10    BEGIN
 11    ctx_ddl.drop_preference('my_lexer');
 12    EXCEPTION WHEN OTHERS THEN NULL;    -- Ignore errors if the preference is not there
 13    END;
 14  
 15    BEGIN
 16    ctx_ddl.drop_stoplist('my_stoplist');
 17    EXCEPTION WHEN OTHERS THEN NULL;    -- Ignore errors if the stop list is not there
 18    END;
 19  
 20    ctx_ddl.create_preference('my_storage','BASIC_STORAGE');
 21    ctx_ddl.set_attribute('my_storage','I_TABLE_CLAUSE','TABLESPACE '||ts);
 22    ctx_ddl.set_attribute('my_storage','K_TABLE_CLAUSE','TABLESPACE '||ts);
 23    ctx_ddl.set_attribute('my_storage','R_TABLE_CLAUSE','TABLESPACE '||ts);
 24    ctx_ddl.set_attribute('my_storage','N_TABLE_CLAUSE','TABLESPACE '||ts);
 25    ctx_ddl.set_attribute('my_storage','I_INDEX_CLAUSE','TABLESPACE '||ts);
 26  
 27    ctx_ddl.create_preference('my_lexer','BASIC_LEXER');
 28    ctx_ddl.set_attribute('my_lexer','INDEX_THEMES','FALSE');
 29    ctx_ddl.set_attribute('my_lexer','INDEX_TEXT','TRUE');
 30    ctx_ddl.set_attribute('my_lexer','ENDJOINS','+');
 31  
 32    -- Create empty stop list (noise words)
 33    CTX_DDL.CREATE_STOPLIST('my_stoplist', 'BASIC_STOPLIST');
 34  
 35    -- use this syntax to add words
 36    -- CTX_DDL.ADD_STOPWORD('my_stoplist', 'the');
 37  
 38  END;
 39  /

PL/SQL procedure successfully completed.

TEST@orcl_11g> 
TEST@orcl_11g> CREATE TABLE T1
  2  (
  3    T1_PK NUMBER(10)     ,
  4    T1_NAME VARCHAR2(900 BYTE) NOT NULL,
  5    T1_CODE VARCHAR2(765 BYTE)    ,
  6    T1_ROOT NUMBER(10)
  7  )
  8  /

Table created.

TEST@orcl_11g> 
TEST@orcl_11g> CREATE INDEX FT_TABLE_NAME ON T1 (T1_NAME)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS('lexer my_lexer storage my_storage stoplist my_stoplist')
  4  /

Index created.

TEST@orcl_11g> 
TEST@orcl_11g> CREATE INDEX FT_TABLE_CODE ON T1 (T1_CODE)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS('lexer my_lexer storage my_storage stoplist my_stoplist')
  4  /

Index created.

TEST@orcl_11g> 
TEST@orcl_11g> CREATE INDEX IDX_TMX_ACTIVITY_6YHX9H0P ON T1 (T1_CODE, T1_PK)
  2  /

Index created.

TEST@orcl_11g> 
TEST@orcl_11g> CREATE INDEX IDX_TMX_ACTIVITY_6YHX9H0U ON T1 (T1_PK, T1_ROOT)
  2  /

Index created.

TEST@orcl_11g> 
TEST@orcl_11g> CREATE INDEX IDX_TMX_ACTIVITY_PATCH2 ON T1 (UPPER("T1_NAME"), T1_PK)
  2  /

Index created.

TEST@orcl_11g> 
TEST@orcl_11g> CREATE UNIQUE INDEX PK_T1 ON T1 (T1_PK)
  2  /

Index created.

TEST@orcl_11g> 
TEST@orcl_11g> ALTER TABLE T1 ADD (CONSTRAINT PK_T1 PRIMARY KEY (T1_PK) USING INDEX)
  2  /

Table altered.

TEST@orcl_11g> 
TEST@orcl_11g> CREATE TABLE T3
  2  (
  3    T3_PK      NUMBER(10)       NOT NULL,
  4    T3_DOMAININD  NUMBER(1)       NOT NULL
  5  )
  6  /

Table created.

TEST@orcl_11g> 
TEST@orcl_11g> CREATE UNIQUE INDEX PK_T3 ON T3 (T3_PK)
  2  /

Index created.

TEST@orcl_11g> 
TEST@orcl_11g> ALTER TABLE T3 ADD (CONSTRAINT PK_T3 PRIMARY KEY (T3_PK) USING INDEX)
  2  /

Table altered.

TEST@orcl_11g> 
TEST@orcl_11g> CREATE TABLE T2
  2  (
  3    T2_ACTFK     NUMBER(10)    NOT NULL,
  4    T2_DOMAINFK  NUMBER(10)    NOT NULL,
  5    T2_PRMYIND   NUMBER(3)    NOT NULL
  6  )
  7  /

Table created.

TEST@orcl_11g> 
TEST@orcl_11g> CREATE INDEX IDX_T2_6YHX9H0J ON T2 (T2_ACTFK, T2_DOMAINFK, T2_PRMYIND)
  2  /

Index created.

TEST@orcl_11g> 
TEST@orcl_11g> CREATE UNIQUE INDEX PK_T2 ON T2 (T2_DOMAINFK, T2_ACTFK)
  2  /

Index created.

TEST@orcl_11g> 
TEST@orcl_11g> ALTER TABLE T2 ADD (CONSTRAINT PK_T2 PRIMARY KEY (T2_DOMAINFK, T2_ACTFK) USING INDEX)
  2  /

Table altered.

TEST@orcl_11g> 
TEST@orcl_11g> ALTER TABLE T2 ADD (CONSTRAINT FK_T2_ACTFK_PLKKJS0G    FOREIGN KEY (T2_ACTFK   ) REFERENCES T1
  2  (T1_PK) ON DELETE CASCADE,
  3      CONSTRAINT FK_T2_DOMAINFK_PLKKJS0H FOREIGN KEY (T2_DOMAINFK) REFERENCES T3
  4  (T3_PK) ON DELETE CASCADE
  5     )
  6  /

Table altered.

TEST@orcl_11g> 
TEST@orcl_11g> insert into t1
  2  select rownum, 'name35 '||TO_CHAR(ROWNUM), 'name35', MOD(ROWNUM, 5)
  3  from   all_objects
  4  where  rownum < 36;

35 rows created.

TEST@orcl_11g> 
TEST@orcl_11g> insert into t3
  2  select rownum, 1
  3  from all_objects where rownum < 10;

9 rows created.

TEST@orcl_11g> 
TEST@orcl_11g> insert into t3
  2  select rownum+10, 0
  3  from all_objects where rownum < 10;

9 rows created.

TEST@orcl_11g> 
TEST@orcl_11g> insert into t2
  2  select rownum, 2, 1
  3  from   all_objects
  4  where  rownum < 36;

35 rows created.

TEST@orcl_11g> 
TEST@orcl_11g> insert into t2
  2  select rownum, 1, 1
  3  from   all_objects
  4  where  rownum < 36;

35 rows created.

TEST@orcl_11g> 
TEST@orcl_11g> BEGIN
  2    FOR ind IN (SELECT DISTINCT pnd_index_name
  3         FROM   ctx_user_pending
  4        )
  5    LOOP
  6       ctxsys.ctx_ddl.sync_index(idx_name => ind.pnd_index_name, memory => '16M');
  7    END LOOP;
  8  END;
  9  /

PL/SQL procedure successfully completed.

TEST@orcl_11g> 
TEST@orcl_11g> commit;

Commit complete.

TEST@orcl_11g> 
TEST@orcl_11g> COLUMN T1_Name    Format a15
TEST@orcl_11g> COLUMN T1_Code    Format a15
TEST@orcl_11g> 
TEST@orcl_11g> 
TEST@orcl_11g> 
TEST@orcl_11g> PROMPT Pulling the whole set of data
Pulling the whole set of data
TEST@orcl_11g> PROMPT (There are 28 records with T3_PK = 1 and 28 records with T3_PK = 2)
(There are 28 records with T3_PK = 1 and 28 records with T3_PK = 2)
TEST@orcl_11g> 
TEST@orcl_11g> SELECT T2_PrmyInd, T3_PK, T3_DomainInd, T1_Name, T1_Code
  2  FROM   T1
  3  INNER
  4  JOIN   T2 ON T1_Root     = T2_ActFK
  5  INNER
  6  JOIN   T3 ON T2_DomainFK = T3_PK
  7  WHERE
  8       T2_PrmyInd   = 1
  9  AND
 10       T3_DomainInd  = 1
 11  AND
 12       (
 13        (contains(T1_Name, 'name35') > 0)
 14        OR
 15        (contains(T1_Code, 'name35') > 0)
 16       )
 17  ORDER  BY T3_PK
 18  /

T2_PRMYIND      T3_PK T3_DOMAININD T1_NAME         T1_CODE
---------- ---------- ------------ --------------- ---------------
         1          1            1 name35 1        name35
         1          1            1 name35 33       name35
         1          1            1 name35 32       name35
         1          1            1 name35 31       name35
         1          1            1 name35 29       name35
         1          1            1 name35 28       name35
         1          1            1 name35 27       name35
         1          1            1 name35 26       name35
         1          1            1 name35 24       name35
         1          1            1 name35 23       name35
         1          1            1 name35 22       name35
         1          1            1 name35 21       name35
         1          1            1 name35 19       name35
         1          1            1 name35 18       name35
         1          1            1 name35 17       name35
         1          1            1 name35 16       name35
         1          1            1 name35 14       name35
         1          1            1 name35 13       name35
         1          1            1 name35 12       name35
         1          1            1 name35 11       name35
         1          1            1 name35 9        name35
         1          1            1 name35 8        name35
         1          1            1 name35 7        name35
         1          1            1 name35 6        name35
         1          1            1 name35 4        name35
         1          1            1 name35 3        name35
         1          1            1 name35 2        name35
         1          1            1 name35 34       name35
         1          2            1 name35 31       name35
         1          2            1 name35 1        name35
         1          2            1 name35 26       name35
         1          2            1 name35 16       name35
         1          2            1 name35 34       name35
         1          2            1 name35 14       name35
         1          2            1 name35 24       name35
         1          2            1 name35 13       name35
         1          2            1 name35 29       name35
         1          2            1 name35 12       name35
         1          2            1 name35 23       name35
         1          2            1 name35 11       name35
         1          2            1 name35 32       name35
         1          2            1 name35 9        name35
         1          2            1 name35 22       name35
         1          2            1 name35 8        name35
         1          2            1 name35 28       name35
         1          2            1 name35 7        name35
         1          2            1 name35 21       name35
         1          2            1 name35 6        name35
         1          2            1 name35 33       name35
         1          2            1 name35 4        name35
         1          2            1 name35 19       name35
         1          2            1 name35 3        name35
         1          2            1 name35 27       name35
         1          2            1 name35 2        name35
         1          2            1 name35 18       name35
         1          2            1 name35 17       name35

56 rows selected.

TEST@orcl_11g> 
TEST@orcl_11g> PROMPT Filtering on T3_PK = 1, should give 28 records but it gives 2 records. (ON MY SYSTEM IT DOES GIVE 28)
Filtering on T3_PK = 1, should give 28 records but it gives 2 records. (ON MY SYSTEM IT DOES GIVE 28)
TEST@orcl_11g> 
TEST@orcl_11g> SELECT T2_PrmyInd, T3_PK, T3_DomainInd, T1_Name, T1_Code
  2  FROM   T1
  3  INNER
  4  JOIN   T2 ON T1_Root     = T2_ActFK
  5  INNER
  6  JOIN   T3 ON T2_DomainFK = T3_PK
  7  WHERE
  8       T2_PrmyInd   = 1
  9  AND
 10       T3_DomainInd  = 1
 11  AND
 12       (
 13        (contains(T1_Name, 'name35') > 0)
 14        OR
 15        (contains(T1_Code, 'name35') > 0)
 16       )
 17  AND    T3_PK   = 1
 18  /

T2_PRMYIND      T3_PK T3_DOMAININD T1_NAME         T1_CODE
---------- ---------- ------------ --------------- ---------------
         1          1            1 name35 1        name35
         1          1            1 name35 2        name35
         1          1            1 name35 3        name35
         1          1            1 name35 4        name35
         1          1            1 name35 6        name35
         1          1            1 name35 7        name35
         1          1            1 name35 8        name35
         1          1            1 name35 9        name35
         1          1            1 name35 11       name35
         1          1            1 name35 12       name35
         1          1            1 name35 13       name35
         1          1            1 name35 14       name35
         1          1            1 name35 16       name35
         1          1            1 name35 17       name35
         1          1            1 name35 18       name35
         1          1            1 name35 19       name35
         1          1            1 name35 21       name35
         1          1            1 name35 22       name35
         1          1            1 name35 23       name35
         1          1            1 name35 24       name35
         1          1            1 name35 26       name35
         1          1            1 name35 27       name35
         1          1            1 name35 28       name35
         1          1            1 name35 29       name35
         1          1            1 name35 31       name35
         1          1            1 name35 32       name35
         1          1            1 name35 33       name35
         1          1            1 name35 34       name35

28 rows selected.

TEST@orcl_11g> 
TEST@orcl_11g> -- suggested syntax with existing indexes:
TEST@orcl_11g> EXEC   DBMS_STATS.GATHER_TABLE_STATS (USER, 'T1')

PL/SQL procedure successfully completed.

TEST@orcl_11g> EXEC   DBMS_STATS.GATHER_TABLE_STATS (USER, 'T2')

PL/SQL procedure successfully completed.

TEST@orcl_11g> EXEC   DBMS_STATS.GATHER_TABLE_STATS (USER, 'T3')

PL/SQL procedure successfully completed.

TEST@orcl_11g> SELECT T2.T2_PrmyInd, T3.T3_PK, T3.T3_DomainInd, T1.T1_Name, T1.T1_Code
  2  FROM   T1, T2, T3
  3  WHERE  (contains (T1_Name, 'name35') > 0
  4        OR
  5        contains (T1_Code, 'name35') > 0)
  6  AND    T1.T1_Root     = T2.T2_ActFK
  7  AND    T2.T2_DomainFK  = T3.T3_Pk
  8  AND    T2.T2_PrmyInd   = 1
  9  AND    T3.T3_DomainInd = 1
 10  AND    T3_pK     = 1
 11  /

T2_PRMYIND      T3_PK T3_DOMAININD T1_NAME         T1_CODE
---------- ---------- ------------ --------------- ---------------
         1          1            1 name35 1        name35
         1          1            1 name35 2        name35
         1          1            1 name35 3        name35
         1          1            1 name35 4        name35
         1          1            1 name35 6        name35
         1          1            1 name35 7        name35
         1          1            1 name35 8        name35
         1          1            1 name35 9        name35
         1          1            1 name35 11       name35
         1          1            1 name35 12       name35
         1          1            1 name35 13       name35
         1          1            1 name35 14       name35
         1          1            1 name35 16       name35
         1          1            1 name35 17       name35
         1          1            1 name35 18       name35
         1          1            1 name35 19       name35
         1          1            1 name35 21       name35
         1          1            1 name35 22       name35
         1          1            1 name35 23       name35
         1          1            1 name35 24       name35
         1          1            1 name35 26       name35
         1          1            1 name35 27       name35
         1          1            1 name35 28       name35
         1          1            1 name35 29       name35
         1          1            1 name35 31       name35
         1          1            1 name35 32       name35
         1          1            1 name35 33       name35
         1          1            1 name35 34       name35

28 rows selected.

TEST@orcl_11g> 
TEST@orcl_11g> -- a better way:
TEST@orcl_11g> DROP INDEX ft_table_name
  2  /

Index dropped.

TEST@orcl_11g> DROP INDEX ft_table_code
  2  /

Index dropped.

TEST@orcl_11g> BEGIN
  2  
  3    BEGIN
  4       ctx_ddl.drop_preference ('my_multi');
  5    EXCEPTION WHEN OTHERS THEN NULL;    -- Ignore errors if the preference is not there
  6    END;
  7  
  8    ctx_ddl.create_preference ('my_multi', 'MULTI_COLUMN_DATASTORE');
  9    ctx_ddl.set_attribute ('my_multi', 'COLUMNS', 't1_name, t1_code');
 10  
 11  END;
 12  /

PL/SQL procedure successfully completed.

TEST@orcl_11g> CREATE INDEX ft_table_name_code ON t1 (t1_name)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('lexer     my_lexer
  5    storage    my_storage
  6    stoplist   my_stoplist
  7    datastore  my_multi')
  8  /

Index created.

TEST@orcl_11g> EXEC   DBMS_STATS.GATHER_TABLE_STATS (USER, 'T1')

PL/SQL procedure successfully completed.

TEST@orcl_11g> EXEC   DBMS_STATS.GATHER_TABLE_STATS (USER, 'T2')

PL/SQL procedure successfully completed.

TEST@orcl_11g> EXEC   DBMS_STATS.GATHER_TABLE_STATS (USER, 'T3')

PL/SQL procedure successfully completed.

TEST@orcl_11g> SELECT T2.T2_PrmyInd, T3.T3_PK, T3.T3_DomainInd, T1.T1_Name, T1.T1_Code
  2  FROM   T1, T2, T3
  3  WHERE  contains (t1_name, 'name35') > 0
  4  AND    T1.T1_Root     = T2.T2_ActFK
  5  AND    T2.T2_DomainFK  = T3.T3_Pk
  6  AND    T2.T2_PrmyInd   = 1
  7  AND    T3.T3_DomainInd = 1
  8  AND    T3_pK     = 1
  9  /

T2_PRMYIND      T3_PK T3_DOMAININD T1_NAME         T1_CODE
---------- ---------- ------------ --------------- ---------------
         1          1            1 name35 1        name35
         1          1            1 name35 2        name35
         1          1            1 name35 3        name35
         1          1            1 name35 4        name35
         1          1            1 name35 6        name35
         1          1            1 name35 7        name35
         1          1            1 name35 8        name35
         1          1            1 name35 9        name35
         1          1            1 name35 11       name35
         1          1            1 name35 12       name35
         1          1            1 name35 13       name35
         1          1            1 name35 14       name35
         1          1            1 name35 16       name35
         1          1            1 name35 17       name35
         1          1            1 name35 18       name35
         1          1            1 name35 19       name35
         1          1            1 name35 21       name35
         1          1            1 name35 22       name35
         1          1            1 name35 23       name35
         1          1            1 name35 24       name35
         1          1            1 name35 26       name35
         1          1            1 name35 27       name35
         1          1            1 name35 28       name35
         1          1            1 name35 29       name35
         1          1            1 name35 31       name35
         1          1            1 name35 32       name35
         1          1            1 name35 33       name35
         1          1            1 name35 34       name35

28 rows selected.

TEST@orcl_11g> 
   






continuation

June 05, 2008 - 2:43 pm UTC

Reviewer: Barbara Boehmer from Riverside County, CA USA

Apparently, my post was too long, so the results of the last query were cut off, so here it is again:

TEST@orcl_11g> SELECT T2.T2_PrmyInd, T3.T3_PK, T3.T3_DomainInd, T1.T1_Name, T1.T1_Code
2 FROM T1, T2, T3
3 WHERE contains (t1_name, 'name35') > 0
4 AND T1.T1_Root = T2.T2_ActFK
5 AND T2.T2_DomainFK = T3.T3_Pk
6 AND T2.T2_PrmyInd = 1
7 AND T3.T3_DomainInd = 1
8 AND T3_pK = 1
9 /

T2_PRMYIND T3_PK T3_DOMAININD T1_NAME T1_CODE
---------- ---------- ------------ --------------- ---------------
1 1 1 name35 1 name35
1 1 1 name35 2 name35
1 1 1 name35 3 name35
1 1 1 name35 4 name35
1 1 1 name35 6 name35
1 1 1 name35 7 name35
1 1 1 name35 8 name35
1 1 1 name35 9 name35
1 1 1 name35 11 name35
1 1 1 name35 12 name35
1 1 1 name35 13 name35
1 1 1 name35 14 name35
1 1 1 name35 16 name35
1 1 1 name35 17 name35
1 1 1 name35 18 name35
1 1 1 name35 19 name35
1 1 1 name35 21 name35
1 1 1 name35 22 name35
1 1 1 name35 23 name35
1 1 1 name35 24 name35
1 1 1 name35 26 name35
1 1 1 name35 27 name35
1 1 1 name35 28 name35
1 1 1 name35 29 name35
1 1 1 name35 31 name35
1 1 1 name35 32 name35
1 1 1 name35 33 name35
1 1 1 name35 34 name35

28 rows selected.

TEST@orcl_11g>

CONTAINS OR WRONG result

June 05, 2008 - 3:53 pm UTC

Reviewer: VKOUL from WA USA

Thanks everbody jumping on this.

1. We cannot change stuff at this time as there are millions of statements like this in packages.
2. We use ANSI JOINS throughout for a valid reason, we cannot go back on that.
3. Defining multi-column preferences is not an option as it would require a substancial change on code side followed by regression test.
4. We cannot simply ask our customers to go on 11g.

Is there a patch from Oracle on this ?

Thanks
Tom Kyte

Followup  

June 05, 2008 - 5:47 pm UTC

please utilize support (and I'd suggest a SMALLER TEST CASE, you can (should, will) make it smaller)


this is not support here

to identify if this is a known problem with a patch, you'll need to work with support to identify it.

CONTAINS OR WRONG result

June 05, 2008 - 4:08 pm UTC

Reviewer: VKOUL from WA USA

The problem does not go away when you use NON-ANSI join, it is still there. If you take one CONTAINS out, then stuff comes back correct at least for this query.

FORCE an ORDERED HINT fetches correct results too with multiple CONTAINS for this query for ANSI or NON-ANSI JOIN.

I do not think ANSI JOIN is a problem here. It is something to do with Query Plan. It looks like that problem is somewhere deep inside.

<code>
SQL> SELECT T2.T2_PrmyInd, T3.T3_PK, T3.T3_DomainInd, T1.T1_Name, T1.T1_Code
  2  FROM   T1, T2, T3
  3  WHERE  T1.T1_Root        = T2.T2_ActFK
  4  AND    T2.T2_DomainFK  = T3.T3_Pk
  5  AND    T2.T2_PrmyInd   = 1
  6  AND    T3.T3_DomainInd = 1
  7  AND    (contains (t1_name, 'name35') > 0
  8          OR
  9          contains(T1_Code, 'name35') > 0
 10         )
 11  AND    T3_pK        = 1
 12  /

T2_PRMYIND      T3_PK T3_DOMAININD T1_NAME         T1_CODE
---------- ---------- ------------ --------------- -------
         1          1            1 name35 1        name35
         1          1            1 name35 2        name35

SQL> 

SQL> l
  1  SELECT --+ ORDERED
  2         T2.T2_PrmyInd, T3.T3_PK, T3.T3_DomainInd, T1.T1_Name, T1.T1_Cod
  3  FROM   T1, T2, T3
  4  WHERE  T1.T1_Root        = T2.T2_ActFK
  5  AND    T2.T2_DomainFK  = T3.T3_Pk
  6  AND    T2.T2_PrmyInd   = 1
  7  AND    T3.T3_DomainInd = 1
  8  AND    (contains (t1_name, 'name35') > 0
  9          OR
 10          contains(T1_Code, 'name35') > 0
 11         )
 12* AND    T3_pK        = 1
SQL> 
SQL> /

T2_PRMYIND      T3_PK T3_DOMAININD T1_NAME         T1_CODE
---------- ---------- ------------ --------------- -------
         1          1            1 name35 1        name35
         1          1            1 name35 2        name35
         1          1            1 name35 3        name35
         1          1            1 name35 4        name35
         1          1            1 name35 6        name35
         1          1            1 name35 7        name35
         1          1            1 name35 8        name35
         1          1            1 name35 9        name35
         1          1            1 name35 11       name35
         1          1            1 name35 12       name35
         1          1            1 name35 13       name35
         1          1            1 name35 14       name35
         1          1            1 name35 16       name35
         1          1            1 name35 17       name35
         1          1            1 name35 18       name35
         1          1            1 name35 19       name35
         1          1            1 name35 21       name35
         1          1            1 name35 22       name35
         1          1            1 name35 23       name35
         1          1            1 name35 24       name35
         1          1            1 name35 26       name35
         1          1            1 name35 27       name35
         1          1            1 name35 28       name35
         1          1            1 name35 29       name35
         1          1            1 name35 31       name35
         1          1            1 name35 32       name35
         1          1            1 name35 33       name35
         1          1            1 name35 34       name35

28 rows selected.

SQL> 

</code>

for Vkoul

June 05, 2008 - 4:31 pm UTC

Reviewer: Barbara Boehmer from Riverside County, CA USA

Vkoul,

If you really want to track down the source of the problem, you can try comparing explained plans and do some tracing. However, I believe that you are going to find that any solution that uses multiple contains clauses with or conditions may or may not produce the correct results, depending on the plan chosen. The only realistic solutions involve using just one contains clause. One other method of using just one contains clause is to create a materialized view that concatenates the two columns to be searched and create your context index on the materialized view.

Barbara

CONTAINS OR WRONG result

June 05, 2008 - 11:35 pm UTC

Reviewer: VKOUL from WA USA

The problem only happens on 9.2.0.7 and in 9.2.0.8 the queries give right results. Oracle replied back that this problem is due to BUG 3393866 on 9.2.0.7.

Thanks all for your help.

create one text index on mutlple column and multiple tables, and meet outofmemory isue.

March 11, 2009 - 7:36 am UTC

Reviewer: lsllcm

I try to create one text index on mutlple column and multiple tables, and meet outofmemory isue. The db is oracle 10.2.0.4 linux version.

Below is my test case:


1.
connect jacky/jacky


2.
grant select on b1permit to ctxsys;
grant select on b3addres to ctxsys;
grant select on b3parcel to ctxsys;


3. connect ctxsys/ctxsys, create procedure
create or replace procedure full_text_index( p_id in rowid, p_lob IN
OUT clob )
as
begin
for x in ( select dept_code,id1,id2,id3,text1
from jacky.ta
where rowid = p_id )
loop
dbms_lob.writeappend( p_lob, length(x.text1), x.text1);
for y in ( select text2
from jacky.tb
where dept_code = x.dept_code
and id1 = x.id1
and id2 = x.id2
and id3 = x.id3
)
loop
dbms_lob.writeAppend( p_lob, length(y.text2), y.text2 );
end loop;
for y in ( select text3
from jacky.tc
where dept_code = x.dept_code
and id1 = x.id1
and id2 = x.id2
and id3 = x.id3
)
loop
dbms_lob.writeAppend( p_lob, length(y.text3), y.text3);
end loop;


end loop;
end;
/


4. connect jacky/jacky
exec ctx_ddl.drop_preference('foo_user_datastore');
exec ctx_ddl.drop_preference('my_lexer');
begin
ctx_ddl.create_preference( 'foo_user_datastore',
'user_datastore' );
ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure',
'ctxsys.full_text_index' );
end;
/
begin
ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
end;
/


5. create text index
create index t1_idx on b1permit(B1_ACCESS_BY_ACA)
indextype is ctxsys.context
parameters( 'datastore foo_user_datastore lexer my_lexer memory
1073741824');


6. get out of memory error


ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drueixe
ORA-04030: out of process memory when trying to allocate 65548 bytes
(CTX PRM heap,draccbx:message buffer)
ORA-06512: at "CTXSYS.DRUE", line 191
ORA-06512: at "CTXSYS.DRUE", line 49
ORA-06512: at "CTXSYS.DRUE", line 147
ORA-06512: at "CTXSYS.D
ORA-04030: out of process memory when trying to allocate 16940 bytes
(pga heap,kgh stack)
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

Tom Kyte

Followup  

March 12, 2009 - 7:30 am UTC

so, what would I need to do to reproduce this? If you cannot give that to me, I'll have to refer you to support. You give no test case (no tables, no instructions as to how to populate tables) I can use.

create one text index on mutlple column and multiple tables, and meet outofmemory isue

March 17, 2009 - 4:20 am UTC

Reviewer: Jacky

I cannot replicate the issue with simple data as all_objects. I have gzip the data as 1m file. But I cannot upload here. Sorry my abruptness, I only can mail it to you (I get your mail from oracle site).

My database is linux 10.2.0.4 32bit.

I simply the test case as below:
-----------------------test case -------------------
create table t1 (object_name varchar2(100));

connect jacky/jacky
grant select on t1 to ctxsys;

connect ctxsys/ctxsys
create or replace procedure full_text_index( p_id in rowid, p_lob IN OUT clob )
as
begin
for x in ( select b1_special_text
from accela.t1
where rowid = p_id )
loop
dbms_lob.writeappend( p_lob, length(x.b1_special_text), x.b1_special_text);

end loop;
end;
/

grant execute on full_text_index to jacky;

connect jacky/jacky

exec ctx_ddl.drop_preference('foo_user_datastore');

exec ctx_ddl.drop_preference('my_lexer');

begin
ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'ctxsys.full_text_index' );
end;
/

begin
ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
end;
/

alter table t1 add c1 char(1);

create index t1_idx on t1(c1)
indextype is ctxsys.context
parameters( 'datastore foo_user_datastore memory 1073741824');

Thanks

create one text index on mutlple column and multiple tables, and meet outofmemory isue

March 17, 2009 - 9:26 pm UTC

Reviewer: Jacky

I cannot replicate the issue with simple data as all_objects. I have gzip the data as 1m file. But
I cannot upload here. Sorry my abruptness, I only can mail it to you (I get your mail from oracle
site).

My database is linux 10.2.0.4 32bit.

I simply the test case as below:
-----------------------test case -------------------
import table t1 from email attachemnt dump file

connect jacky/jacky
grant select on t1 to ctxsys;

connect ctxsys/ctxsys
create or replace procedure full_text_index( p_id in rowid, p_lob IN OUT clob )
as
begin
for x in ( select b1_special_text
from accela.t1
where rowid = p_id )
loop
dbms_lob.writeappend( p_lob, length(x.b1_special_text), x.b1_special_text);

end loop;
end;
/

grant execute on full_text_index to jacky;

connect jacky/jacky

exec ctx_ddl.drop_preference('foo_user_datastore');

exec ctx_ddl.drop_preference('my_lexer');

begin
ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'ctxsys.full_text_index' );
end;
/

begin
ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
end;
/

alter table t1 add c1 char(1);

create index t1_idx on t1(c1)
indextype is ctxsys.context
parameters( 'datastore foo_user_datastore memory 1073741824');

Thanks

problem with &

July 16, 2009 - 4:39 pm UTC

Reviewer: Branka Bowman from VA, USA

When I execute
exec ASP.quick_application_search('S & H',:p_rc)
I get "Enter value for h: " because it is after "&"
How can I solve this?
If I use AND, everuthing work fine.
Tom Kyte

Followup  

July 16, 2009 - 6:02 pm UTC

SQL> set define off

problem with &

July 16, 2009 - 4:52 pm UTC

Reviewer: Branka Bowman from VA, USA

I have problem with & when using your example
1 select varchar2_col, clob_col1, clob_col2
2 from t1, t2, t3
3 where t1.id = t2.id
4 and t1.id = t3.id
5* and contains( varchar2_col, 'this & that & thing' ) > 0
/
Enter value for that:
Enter value for thing:
old 5: and contains( varchar2_col, 'this & that & thing' ) > 0
new 5: and contains( varchar2_col, 'this ' ) > 0

If I use AND instead, it work fine

Tom Kyte

Followup  

July 16, 2009 - 6:03 pm UTC

SQL> set define off


& is a 'special' character to sqlplus by default, just disable it.

problem with special characters. "-"

July 23, 2009 - 1:36 pm UTC

Reviewer: Branka from VA, USA

Tom,

I used you example to test problem that I have.  I have to search a lot of text that has "-" special character.
I used your example to see if it would work in your procedure, and it does not.  In your case I get nothing back, in my I get too many data back. 
Why oracle text has problem with "-" character, and how can I solve it.  In my search, I wanted to search for that exactly pattern, (F-123-A), and I tried with "F-123-A", thinking that if would help, but it did not.

branka>  select     varchar2_col, clob_col1, clob_col2
  2   from t1, t2, t3
  3   where t1.id = t2.id
  4     and t1.id = t3.id
  5    and contains( varchar2_col, 'this & that & thing' ) > 0;

VARCHAR2_COL
--------------------------------------------------------------------------------
CLOB_COL1
----------------------------------------------------------------------------------------------------
CLOB_COL2
----------------------------------------------------------------------------------------------------
this
That
and the other thing


branka> update t1 set VARCHAR2_COL='BB' where id=1;

1 row updated.

branka> commit;

Commit complete.

branka> drop index t1_idx;

Index dropped.

branka> 
branka> 
branka> exec ctx_ddl.drop_preference('foo_user_datastore');

PL/SQL procedure successfully completed.

branka> exec ctx_ddl.drop_preference('my_lexer');

PL/SQL procedure successfully completed.

branka> 
branka> begin
  2         ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
  3         ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'glue_them_together' );
  4      end;
  5  /    

PL/SQL procedure successfully completed.

branka> 
branka>  begin
  2         ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
  3         ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
  4         ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
  5      end;
  6  /    

PL/SQL procedure successfully completed.

branka>     
branka>     
branka>      create index t1_idx on t1(varchar2_col)
  2          indextype is ctxsys.context
  3          parameters( 'datastore foo_user_datastore lexer my_lexer');

Index created.

branka> 
branka> 
branka>   select     varchar2_col, clob_col1, clob_col2
  2   from t1, t2, t3
  3   where t1.id = t2.id
  4     and t1.id = t3.id
  5    and contains( varchar2_col, 'BB & that & thing' ) > 0;

VARCHAR2_COL
--------------------------------------------------------------------------------
CLOB_COL1
----------------------------------------------------------------------------------------------------
CLOB_COL2
----------------------------------------------------------------------------------------------------
BB
That
and the other thing


branka> update t1 set VARCHAR2_COL='F-123-A' where id=1;

1 row updated.

branka> commit;

Commit complete.

branka> drop index t1_idx;

Index dropped.

branka> 
branka> 
branka> exec ctx_ddl.drop_preference('foo_user_datastore');

PL/SQL procedure successfully completed.

branka> exec ctx_ddl.drop_preference('my_lexer');

PL/SQL procedure successfully completed.

branka>  begin
  2         ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
  3         ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'glue_them_together' );
  4      end;
  5  /    

PL/SQL procedure successfully completed.

branka> 
branka>  begin
  2         ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
  3         ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
  4         ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
  5      end;
  6  /    

PL/SQL procedure successfully completed.

branka>     
branka>     
branka>      create index t1_idx on t1(varchar2_col)
  2          indextype is ctxsys.context
  3          parameters( 'datastore foo_user_datastore lexer my_lexer');

Index created.

branka> select     varchar2_col, clob_col1, clob_col2
  2   from t1, t2, t3
  3   where t1.id = t2.id
  4     and t1.id = t3.id
  5    and contains( varchar2_col, 'F-123-A & that & thing' ) > 0;

no rows selected

Tom Kyte

Followup  

July 26, 2009 - 7:04 am UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:688976960025

read about printjoins

Tuning of using "Contains" on multiple columns on multiple tables

September 10, 2010 - 4:11 am UTC

Reviewer: Sri from London

Hi Tom,

I have gone throught this post and could not find if it is possible to tune "contains" clause on two tables each having 2 columns which are indexed. I'll explain what I am looking for.

I have 2 very long tables (table1 and table2) each having 2 varchar2 columns (table1 = t1_col1, t1_col2 and table2 = t2_col1, t2_col2). There is no key field join existing between these tables (e.g. PK say id etc). My requirement is to produce report from these two tables, and select records where (table2.t2_col1 is a part of table1.t1_col1) AND (table2.t2_col2 is a part of table1.t1_col2). Now to do this, I've these options -

A) Using LIKE

select *
from table1, table2
where table2.t2_col1 like '%'||table1.t1_col1||'%' AND
table2.t2_col2 like '%'||table1.t1_col2||'%';

B) Using INSTR

select *
from table1, table2
where instr(table1.t1_col1,table2.t2_col1) AND
instr(table1.t1_col2,table2.t2_col2);

C) Using CONTAINS clause (after context indexing both columns of both tables)

select *
from table1, table2
where contains instr(table1.t1_col1,table2.t2_col1) > 0 AND
contains(table1.t1_col2,table2.t2_col2) > 0;

My query is is there any better way of doing this in terms of performance as all of these are incurring FTS.

Any help will be greatly appreciated.

Best regards.

Tom Kyte

Followup  

September 13, 2010 - 7:33 am UTC

of course this will take a full scan of at least one of the tables - full scans are NOT evil.

But your query is bordering on evil - think about it, ugh.


the best you can hope for - short of a cartesian product - would be a full scan of the one table - with an index lookup for every single row that comes back - into the other table.

Sort of like this procedural psuedo code:

for x in (select * from t1)
loop
   for y in (select rowid from t2 where <some lookup>)
   loop
       get the row from t2 by rowid and output t1 x t2
   end loop
end loop


assuming you have a schema such as:

ops$tkyte%ORA11GR2> create table t1 ( c1 varchar2(100), c2 varchar2(100) );

Table created.

ops$tkyte%ORA11GR2> create table t2 ( c1 varchar2(100), c2 varchar2(100) );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t1 values ( 'hello', 'world' );

1 row created.

ops$tkyte%ORA11GR2> insert into t2 values ( 'this is hello', 'and this is world' );

1 row created.

ops$tkyte%ORA11GR2> insert into t2 values ( 'this is hello again', 'and this is world again' );

1 row created.

ops$tkyte%ORA11GR2> insert into t2 values ( 'this is something else', 'and this is another thing' );

1 row created.

ops$tkyte%ORA11GR2> insert into t2 values ( 'this is something else again', 'and this is another thing again' );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create index t2_c1_idx on t2(c1) indextype is ctxsys.context;

Index created.

ops$tkyte%ORA11GR2> create index t2_c2_idx on t2(c2) indextype is ctxsys.context;

Index created.


You can query:

ops$tkyte%ORA11GR2> select t1.*, cast( multiset(
  2  select rowid
  3    from t2
  4   where contains( c1, t1.c1 ) > 0
  5   intersect
  6  select rowid
  7    from t2
  8   where contains( c2, t1.c2 ) > 0
  9  ) as sys.odcivarchar2List ) rids
 10  from t1
 11  /

C1
-------------------------------------------------------------------------------
C2
-------------------------------------------------------------------------------
RIDS
-------------------------------------------------------------------------------
hello
world
ODCIVARCHAR2LIST('AAAU12AAEAAAIDPAAA', 'AAAU12AAEAAAIDPAAB')


that mimics most of the psuedo code above, we have to flatten the result now:

ops$tkyte%ORA11GR2> select c1, c2, column_value
  2    from (
  3  select t1.*, cast( multiset(
  4  select rowid
  5    from t2
  6   where contains( c1, t1.c1 ) > 0
  7   intersect
  8  select rowid
  9    from t2
 10   where contains( c2, t1.c2 ) > 0
 11  ) as sys.odcivarchar2List ) rids
 12  from t1
 13       ), TABLE( rids )
 14  /

C1
-------------------------------------------------------------------------------
C2
-------------------------------------------------------------------------------
COLUMN_VALUE
-------------------------------------------------------------------------------
hello
world
AAAU12AAEAAAIDPAAA

hello
world
AAAU12AAEAAAIDPAAB


and then we can join to it:

ops$tkyte%ORA11GR2> select t1.c1, t1.c2, t2.c1, t2.c2
  2    from (
  3  select c1, c2, column_value
  4    from (
  5  select t1.*, cast( multiset(
  6  select rowid
  7    from t2
  8   where contains( c1, t1.c1 ) > 0
  9   intersect
 10  select rowid
 11    from t2
 12   where contains( c2, t1.c2 ) > 0
 13  ) as sys.odcivarchar2List ) rids
 14  from t1
 15       ), TABLE( rids )
 16           ) t1, t2
 17   where t2.rowid = chartorowid( column_value )
 18  /

C1
-------------------------------------------------------------------------------
C2
-------------------------------------------------------------------------------
C1
-------------------------------------------------------------------------------
C2
-------------------------------------------------------------------------------
hello
world
this is hello
and this is world

hello
world
this is hello again
and this is world again




But bear in mind - math, physics, speed of light - things like that will prevent this sort of search from being "fast" in any sense of the word.

Think about what if...

a) the index lookup is fast (1/1000th of a second - that would be really fast, probably faster than it can be)

b) t1 has 1,000,000 rows - pretty small table - what I use to test on my LAPTOP with...


then it would take at least 16.666 minutes (assuming the full scan of t1 takes 0.00000 seconds).


And if the index takes 1/100th of a second (probably more realistic....), you are talking 2.7777 hours....


You might consider parallelizing that if you have the resources...

re: comparing 2 tables and 2 columns

September 14, 2010 - 4:14 pm UTC

Reviewer: Stew Ashton from Paris, France


Here's a suggestion for doing one compare rather than two, plus reducing somewhat the number of LIKE comparisons. Sure gobbles up the CPU! (Sorry about no output, I'm at home and formatting it correctly is time-consuming...)
drop table t1;
create table t1(c1 varchar2(100), c2 varchar2(100), other varchar2(300));
insert into t1
select 'c1 ' || level, 'c2 ' || (level-1), rpad('-', 300, '-')
from dual connect by level <= 10000;
drop table t2;
create table t2(c1 varchar2(100), c2 varchar2(100), other varchar2(300));
insert into t2
select 'c1 ' || (level*10+1), 'c2 ' || (level*10), rpad('-', 300, '-')
from dual connect by level <= 10000;


create index idx_t1 on t1(length(c1), length(c2),c1||'%§%'||c2) compress 2;
create index idx_t2 on t2(length(c1), length(c2),'%'||c1||'%§%'||c2||'%') compress 2;
 
select /*+ gather_plan_statistics */ count(*) from t1, t2 
where length(t2.c1) <= length(t1.c1) 
and length(t2.c2) <= length(t1.c2) 
and t1.c1||'%§%'||t1.c2 like '%'||t2.c1||'%§%'||t2.c2||'%';

select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS LAST'));

Correction

September 15, 2010 - 1:23 am UTC

Reviewer: Stew Ashton from Paris, France

Sorry, there were some unnecessary '%' on t1 (in the index and in the query).
create index idx_t1 on t1(length(c1), length(c2),c1||'§'||c2) compress 2;
create index idx_t2 on t2(length(c1), length(c2),'%'||c1||'%§%'||c2||'%') compress 2;
 
select /*+ gather_plan_statistics */ count(*) from t1, t2 
where length(t2.c1) <= length(t1.c1) 
and length(t2.c2) <= length(t1.c2) 
and t1.c1||'§'||t1.c2 like '%'||t2.c1||'%§%'||t2.c2||'%';

Using "Oracle Text" CONTAINS syntax on multiple columns

September 15, 2010 - 1:26 am UTC

Reviewer: Sri from London

Hi Tom,

Your worked example was most useful and I understand it fully.

However I think somehow in your reply it is coming up with version of 8.1.7 so probably in my original comment I did some mistake as my actual version where I am trying this is 10.2.0.4. [I think the person who raised this thread in first place had this version and it is carrying forward from there].

On trying your worked example (t1/t2) in two different databases (10.2.0.2/10.2.0.4) I get this "ORA-03001: unimplemented feature". I'm assuming that this example was meant to be run on 8.1.7 which is causing this ?

Many Thanks again.

Regards.
Tom Kyte

Followup  

September 15, 2010 - 8:12 am UTC

I did mine in 11gr2 - which accepted the set operation in the multi-set but 10g did not (interest, union, etc...)

You can try this instead:

ops$tkyte%ORA10GR2> select t1.c1, t1.c2, t2.c1, t2.c2
  2    from (
  3  select c1, c2, column_value
  4    from (
  5  select t1.*, cast( multiset(
  6  select rowid
  7    from t2
  8   where contains( c1, t1.c1 ) > 0
  9     and contains( c2, t1.c2 ) > 0
 10  ) as sys.odcivarchar2List ) rids
 11  from t1
 12       ), TABLE( rids )
 13           ) t1, t2
 14   where t2.rowid = chartorowid( column_value )
 15  /


It'll use the index on either C1 or C2 to "find" the row that might have your hit and then use an access on that same row to process the contains.

Using "Oracle Text" CONTAINS syntax on multiple columns

September 15, 2010 - 1:28 am UTC

Reviewer: Sri from London

Sorry forgot to mention that, the error ORA-03001 comes up at the first select statment ie.

SELECT t1.*, CAST( MULTISET(
SELECT ROWID
FROM t2
WHERE contains( c1, t1.c1 ) > 0
INTERSECT
SELECT ROWID
FROM t2
WHERE contains( c2, t1.c2 ) > 0
) AS sys.odcivarchar2List ) rids
FROM t1;

Thanks.

Using "Oracle Text" CONTAINS syntax on multiple columns

September 22, 2010 - 8:01 am UTC

Reviewer: Sri from London

Many thanks Tom, this is what I was after.

Cheers.

String tokenaizer

January 20, 2012 - 3:03 am UTC

Reviewer: Ramki

Hi Tom,

I am looking for String tokenizer and search a row based on token.

We have formula like : lstraf.cs_u_pln_udp_octet_rec_lsbts+lstraf.cs_u_pln_udp_octet_sent_lsbts

I want to search for "cs_u_pln_udp_octet_sent_lsbts"

select * from omc.test where contains( formula, 'cs_u_pln_udp_octet_rec_lsbts' ) > 0;

I tried following :

drop table test;
create table test ( formula varchar2 (400) );
insert into test values ('lstraf.cs_u_pln_udp_octet_rec_lsbts+lstraf.cs_u_pln_udp_octet_sent_lsbts');
create index t_idx on test(formula) indextype is ctxsys.context;
select token_text from dr$t_idx$i;


TOKEN_TEXT
--------------------
CS
LSBTS
LSTRAF
OCTET
PLN
REC
SENT
U
UDP

9 rows selected

Tokens are splited by "_"

I want tokes to be :

lstraf
cs_u_pln_udp_octet_rec_lsbts
cs_u_pln_udp_octet_sent_lsbts

i.e split by [+,-,*,%,^, ),(,. ]

How can I do this.
Tom Kyte

Followup  

January 20, 2012 - 9:48 am UTC

search this site for the word

printjoins



contains in hierarchical SQL

January 25, 2012 - 3:35 am UTC

Reviewer: A reader

Hi Tom,

I am try use contains in hierarchical SQL.
But I am getting following error

SQL Error: ORA-20000: Oracle Text error:
DRG-10599: column is not indexed

I created sample table & data.
I my application this table will have 30,000 rows , using regexp_substr ( ) is slow,taking ~10 min. So I am trying to tokenize my formula column , and make it faster.

drop table t ;
CREATE TABLE T ( id VARCHAR2(10), formula VARCHAR2(50));

insert into t values ('f1', 'KPI1' );
insert into t values ('f2', 'KPI2' );
insert into t values ('f3', 'KPI3' );
insert into t values ('f4', 'KPI4' );
insert into t values ('f5', 'KPI5' );
INSERT INTO T VALUES ('f6', 'KPI6' );

insert into t values ('f7', '( f1 * f2 )' );
insert into t values ('f8', '( f4 + f3 ) ' );
insert into t values ('f9', '( f3 * f4 ) + f5' );
insert into t values ('f10','( f5 + f6 ) / f1' );

insert into t values ('f11','( f7 * f6 )' );
insert into t values ('f12','( f8 * f2 )' );

commit;

create index txt_formula_T on T(formula) indextype is ctxsys.context; --parameters('lexer my_lexer');

SELECT t.id , t.formula
from t
where contains (t.formula, 'f2' ) >0;

--- Contains is working


select ID, prior(ID),FORMULA, level
FROM T
start with id in (select id from t )
connect by nocycle regexp_substr(formula, '[^a-zA-Z0-9_]' ||prior(id) ||'[^a-zA-Z0-9_]') is not null;

-- regexp_substr ( ) works fine

select ID, prior(ID),FORMULA, level
FROM T
start with id in (select id from t )
connect by contains (formula, prior(id) ) >0;


SQL Error: ORA-20000: Oracle Text error:
DRG-10599: column is not indexed

Regards
ramki
Tom Kyte

Followup  

January 26, 2012 - 10:05 am UTC

sorry, the contains operation is not going to work there. You are going to want to do that one procedurally - I would suggest "compiling" that table when you modify it - you won't be modifying it over and over and over again (but you'll probably be querying it over and over and over again - much more than modifying it I would guess)


I would have yet another table you "compile" this table into after you modify it - so that you just have to retrieve a single formula at runtime to evaluate.

More to Explore

DBMS_LOB

More on PL/SQL routine DBMS_LOB here