Hi Tom,
Thanks for your explanation
Still you are not explaining how to glue two or more columns and how to query it
What is the use of dummycol of varchar2(1) and how it used in Intermedia index?

It will be great if you articulate further as to how to retrieve result from two columns...

Hi Tom,

I am not able to figure out why after doing exactly what is shown by you when i query 0 rows are returned.

I have copied all the steps of this example but at the end when i say
select * from foo where contains(dummycol,'too') > 0

the records fetched shoud be 1 ideally but here i see is 0 rows returned. Also i see that the transaction has got some data to be either commited or rollback.

Please explain this.

Hi Tom,

We are using this very same approach in our site and it works great.

We use also CTX_DOC.HIGHLIGHT to generate document summaries in the text queries (just like Google or any other search engine does). It works fine but we have found that calling CTX_DOC.HIGHLIGHT in an custom-datastore-index will call the original datastore procedure.

This is quite a load, because it means copying all the document text fields from the original table to a temporary location, and this is done for every document the appears in the results page.

Do you think that storing the "concatenated" text in an extra field and mantaining this one with triggers would solve the problem?

Many thanks.

p.s. Great site.. really.

Can we concatinate multiple columns in creating catalog indexes? Thanks.

How to use ampersand (&) or any special character inside the contains keyword ?
for eg: select id from table where
contains(company_nm, '%at&t% or %yahoo%') > 0

The query is not giving results for at&t. If % symbols are removed, then it is giving everything starting with 'at' and 't' . Kindly suggest a solution

ops$tkyte@ORA9IR2> create table t ( x varchar2(100) );
 
Table created.
 
ops$tkyte@ORA9IR2> set define off
ops$tkyte@ORA9IR2> insert into t values ( 'at&t' );
 
1 row created.
 
ops$tkyte@ORA9IR2> create index t_idx on t(x) indextype is ctxsys.context;
 
Index created.
 
ops$tkyte@ORA9IR2> select token_text from DR$T_IDX$I;
 
TOKEN_TEXT
----------------------------------------------------------------
T
 
<b>So, note that at&t isn't actually indexed, AT is a stop word, & is a break character -- only T makes it in</b>


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t where contains( x, 'AT\&T' ) > 0;
 
X
-------------------------------------------------------------------------------
at&t
 
ops$tkyte@ORA9IR2> select * from t where contains( x, '{AT&T}' ) > 0;
 
X
-------------------------------------------------------------------------------
at&t

<b>we can however get it to index at&t which would be preferable</b>

 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop index t_idx;
 
Index dropped.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2      ctx_ddl.create_preference('my_lexer','BASIC_LEXER');
  3      ctx_ddl.set_attribute('my_lexer','printjoins','&');
  4  end;
  5  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(x) indextype is ctxsys.context parameters( 'lexer my_lexer' );
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select token_text from DR$T_IDX$I;
 
TOKEN_TEXT
----------------------------------------------------------------
AT&T
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t where contains( x, 'AT\&T' ) > 0;
 
X
-------------------------------------------------------------------------------
at&t
 
ops$tkyte@ORA9IR2> select * from t where contains( x, '{AT&T}' ) > 0;
 
X
-------------------------------------------------------------------------------
at&t
 
 

I am trying to create the context index using function
(like foo_proc) to concatenate 2 columns on a table with
3.6 million records. The concatenated columns are "subject
varchar2(255)" and "body varchar2(4000)".

Here is the function:

create or replace procedure p_get_message_text(
p_id in rowid,
p_lob IN OUT clob
)
is
begin
for c1 in (select message_subject||' '||message_text thetext from message where rowid = p_id)
loop
-- assemble into p_lob ANY data you want
-- indexed.
dbms_lob.copy( p_lob, c1.thetext,
dbms_lob.getlength( c1.thetext ));
end loop;
end;
/

The session long ops is suggesting that it will take 22
hours to create this index. It is already 30 minutes and
it has done only 2% work.
Does it take this much time? or I am doing something wrong?

I did not know that there is a MULTI_COLUMN_DATASTORE
option which is new 9i.

I created a text index on a particular column using CTXSYS.CONTEXT. I am using the query
select * from tutype where contains(url, 'oracle database')>0;
to find the strings.
Here i am getting records only with 'oracle database'. But i want records with oracle and database. We can use
contains(url,'{oracle}& {database}') > 0;
But i am passing this values dynamically and its length can vary ? How we can use the 'OR' condition in the passing string?

I created a text index on a particular column using CTXSYS.CONTEXT. I am using the query
select * from tutype where contains(url, 'oracle database')>0;
to find the strings.
Here i am getting records only with 'oracle database'. But i want records with oracle and database. We can use
contains(url,'{oracle}& {database}') > 0;
But i am passing this values dynamically and its length can vary ? How we can use the 'OR' condition in the passing string?

Tom,
I was wondering what is string appearance order when using
context index to search ? When I do something like:

SQL>select full_title_nm from sales where contains (search_column,'art within full_title_nm')>0;

I see :

FULL_TITLE_NM                                                                   
--------------------------------------------------------------------------------
November 2004 Swiss Art                                                         
Aboriginal Art                                                                  
20th Century Decorative Art and Design                                          
Belgian Art CANCELLED                                                           
Modern and Contemorary Art                                                      
Japanese Works of Art                                                           
European Sculpture & Works of Art                                               
Art Décoratifs du XXième siècle                                                 
LATIN AMERICAN ART                                                              
ARCADE Furniture, Decorative Worksof Art & Carpets                              
African, Oceanic and Pre-Colmbian Art                                           

FULL_TITLE_NM                                                                   
--------------------------------------------------------------------------------
Russian Paintings and Works of Art                                              
Russian Pictures & Works of Art                                                 
SCHWEIZER KUNST / SWISS ART                                                     
Chinese & Korean Works of Art                                                   
French & Continental Furniture including European Works of Art,Tapestries & Carp
ets                                                                             
                                                                                
American Art                                                                    
Chinese & Japanese Works of Art                                                 
Modern British & Irish Art                                                      
Contemporary Art                       

FULL_TITLE_NM                                                                   
--------------------------------------------------------------------------------
Modern British and Irish Art                                                    
Modern and Contemporary Art Including Property of the Estateof Vera G. List     
Arcade Furniture and Decorative Works of Art                                    
Impressionist & Modern Art                                                      
CONTEMPORARY ART PART II                                                        
Israeli Art                                                                     
CONTEMPORARY ART PART I                                                         
Contemporary Art                                                                
IMPRESSIONIST & MODERN ART PART II                                              
SURREALIST ART                                                                  
GERMAN AND AUSTRIAN ART                       

When Oracle search the string with 'art' by context index, is there any sort order for 'art' itself (lower case first then uppercase; exact word first then come with 'art' plus other letters)? Also is there any sort order for other words prior/after the word of 'art'? 


Thanks.
 

Tom,
Thanks very much for your answer. Could you also give some link or example for using score() function?

Thanks very much for the link.

Oracle Version: 9i (Release 2)
Hi Tom,
I like the first option that you suggested..
...
select * from T where contains(title,'Oracle') > 0 OR
contains(body,'Oracle') > 0;
...

Since, our requirements are performing these case-insensitive searches on demand..i.e. decision will be taken
on the fly to add a contain clause for a column.
Few questions...
1. My query just dies as I add my first "OR" (It skips both of the domain indices).
***All of the tables and indicies have stats on them.
2. Since these columns are getting updated during the day...so how do we keep these indices in sync all the time.
3. the resultset is not catching rows when I do a like search for example:
contains(title,'%Oracle%') > 0

Thanks..

1) i would not suggest that approach, it would be best to have a single index.

2) i schedule a job to run every n minutes to sync the index (ctx_ddl call)

3) are you sure?

  
ops$tkyte@ORA9IR2> create table t( text varchar2(70) );
 
Table created.
 
ops$tkyte@ORA9IR2> insert into t values ( 'how cool is oracle now' );
 
1 row created.
 
ops$tkyte@ORA9IR2> create index t_idx on t(text) indextype is ctxsys.context;
 
Index created.
 
ops$tkyte@ORA9IR2> select * from t where contains( text, '%Oracle%' ) > 0;
 
TEXT
----------------------------------------------------------------------
how cool is oracle now


and see

for using leading percents.
 
 

"
1) i would not suggest that approach, it would be best to have a single index.
"
Having a single index like that doesn't seem very flexible for
a query that needs to be built on the fly...
Comments?
Let me explain a little..
There are two kinds of application searches that I am trying to improve:

1.
--class_name_p = 'ENG' and dept_name_p = 'LITER' parameters are passed
select * from classes c, dept d
where c.deptid = d.deptid
and upper(c.class_name) like '%ENG%'
and upper(d.dept_name) like '%LITER%';

2.
--Keyword_p = 'ENG' parameter is passed
select * from classes c, dept d
where c.deptid = d.deptid
and
(
(UPPER(c.class_name) like '%ENG%')
OR
(UPPER(d.dept_name) like '%ENG%')
);

Thanks a bunch..

Tom,
Here is a utility from Oracle on Oracle.com:
The Concatenated Datastore - A Utility for Oracle Text
</code> http://www.oracle.com/technology/sample_code/products/text/htdocs/concatenated_text_datastore/cdstore_readme.html <code>

Seems really nice...
But it doesn't explain a way to create a data store on columns from different tables..like (emp.ename and dept.dname)
Any ideas?

Thanks.

Completely forgot about your "expert one on one"...
Ch 17 - Intermedia ....
I will start reading about section searching..

Thanks

I looked at your example to build a procedure with columns of
different tables...
Question:
After then creating the preference (to look at your procedure) and lexer..
Where would the column to be indexed reside...
You put that in your ask_tom.WWC_ASK_INDEXED_QUESTIONS$ table..
since that is your driving table (Parent to all the rest)?

index_ask_tom

grant execute on index_ask_tom to scott;
grant execute on ctx_ddl to scott;


connect scott/tiger

begin
ctx_ddl.drop_preference('foo_user_datastore');
ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );

ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'index_ask_tom' );
end;
/

begin
ctx_ddl.drop_preference('my_lexer');
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 questions_idx on ask_tom.WWC_ASK_INDEXED_QUESTIONS$(dummycol) indextype is ctxsys.context parameters(
'datastore foo_user_datastore lexer my_lexer');

Thanks a bunch...

Ask_tom_questions
Ask_tom_answers
Ask_tom_followups

So, you have triggers on (Ask_tom_answers and Ask_tom_followups and any other child tables)
columns that needs to be in the index..
and when they get updated..you update the dummycol in Ask_tom_questions tables?

Just a comment that I used this technique on a user_profile table for a website and it worked quite well. The table had many columns about the user including favorites books, hometown, name, etc. The function combined all the information into an xml representation so that search would work over all columns using one index but would also enable section searching like Hometown = Lexington and Favorite book = Expert One on One. Also used MDATA for some exact match searching like birth date.

Thanks for the info!

Dave

I don't think there is a good answer for this, but just in case:

What if I have a title in a varchar2 field and the body (a pdf file) in a blob field.

I would like to perform search and return results if the word(s) appear in either the title or the blob.

One additional catch. For documents wherein the search words are in the title, I would like the score to be higher then the score for documents wherein the search words are only in the body (pdf).

Then I would like to sort by the score.

Any ideas regarding this?

Hi Tom

Great article. Can you just remove any doubt that this approach is wrong as there must be a flaw!

Can I not just create a function which returns the columns concatenated together, create a function based index on this and then create a text index on that?

If this works, how would you recommend the rebuild process is tackled? I guess after any DML the fn based index and then the text index would need rebuilding?

Cheers

Phil

OK - I was being lazy! I guess it is asking a bit much to have it like a b-tree.

On the sync question - is there a faster way for this approach rather than periodically dropping and rebuilding the whole index?

Thanks

Phil

Hello,
I've found what I was looking for!

I only wonder if there is any method to force synchronization of text index for few rows on base table.
Let say:
1. I've index with user_datastore and my own procedure returning text for indexing. Procedure is collecting data from base table and another related table (say table "T").
2. Base table is not updated, but table "T" is updated - so ctx_ddl.sync_index will not update index for this row (would it?)
3. I know rowid's of rows in base table that relates to changed data - so I want to tell Oracle Text to update index for these rows.
Should I make dummy update of any column for these rows? Or is there any other method?

Regards
Pawel

Hi Tom,

I have a table of the following structure

create table t1
(
id number,
s1 varchar2(255),
s2 varchar2(255),
s3 varchar2(255)
);

I wanted to do an intermedia search on all three columns together, so my query was

select count(*) from t1
where contains(s1, 'thetext') > 0 OR
contains (s2, 'thetext') > 0 OR
contains (s3, 'thetext') > 0;

I got the count as 480 but the time taken was approximately 23 secs.

So I went with your approach of indexing a procedure.

I did the following :

create or replace procedure s_proc(
p_id in rowid,
p_lob IN OUT varchar2
)
is
begin
for c1 in (select s1 || s2 || s3 stext from user.t1 where rowid = p_id)
loop
-- assemble into p_lob ANY data you want
-- indexed.
p_lob := c1.stext;
end loop;
end;
/


begin
ctx_ddl.drop_preference('s_datastore');
ctx_ddl.create_preference( 's_datastore', 'user_datastore' );

ctx_ddl.set_attribute( 's_datastore', 'procedure', 's_proc' );
end;
/

-- Created preference my_lexer too

alter table t1 add dummycol varchar2(1);

create index idx_t1_ on t1(dummycol)
indextype is ctxsys.context parameters('datastore s_datastore lexer my_lexer');

But now when I do

select count(*) from t1
where contains(dummycol, 'thetext') > 0

is giving only 69 when I am expecting 480.

What is the problem here?

Thanks.

You were right on the dot about that Tom. I was indeed missing the concatenation of the spaces and it worked after that.

Thank you again.

refering to one of your followup
"i have triggers on the base table to tickle the parent row, to cause the reindex to fire on the parent. "


Do i need to create trigger on parent table to reindex (ctx_dll.syncindex)
or reindex happens automatically , when i do a update or dummy update on parent table .?

Tom,

I am trying to follow your example on the first response and not sure what i am doing wrong

SQL> connect scott/tiger
Connected.
SQL> create table foo (id number primary key, thetext clob,
  2                    dummycol varchar2(1) );

Table created.

SQL>
SQL> insert into foo(id, thetext) values(1, 'I was here');

1 row created.

SQL> insert into foo(id, thetext) values(2, 'I was here too');

1 row created.

SQL> insert into foo(id, thetext) values(3, 'And Again');

1 row created.

SQL> commit;

Commit complete.

SQL> grant all on foo to ctxsys;

Grant succeeded.

SQL> connect ctxsys/ctxsys
Connected.
SQL>
SQL> create or replace procedure foo_proc(
  2      p_id in rowid,
  3      p_lob IN OUT clob
  4  )
  5  is
  6  begin
  7      for c1 in (select thetext from scott.foo where rowid = p_id)
  8      loop
  9          -- assemble into p_lob ANY data you want
 10          -- indexed.
 11          dbms_lob.copy( p_lob, c1.thetext,
 12                         dbms_lob.getlength( c1.thetext ));
 13      end loop;
 14  end;
 15  /

Procedure created.

SQL> grant execute on foo_proc to scott;

Grant succeeded.

SQL> grant execute on ctx_ddl to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL> begin
  2  ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
  3
  4  ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'foo_proc' );
  5  end;
  6  /
begin
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10761: procedure FOO_PROC does not exist
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 56
ORA-06512: at line 4


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

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 foo2_idx on foo(dummycol) indextype is ctxsys.context parameters(
  2  'datastore foo_user_datastore lexer my_lexer');

Index created.


SQL> select * from foo where contains(dummycol,'too') > 0;

no rows selected
 

Thanks.

Hi Tom,

We have this error and unable to figure it out, and I have not worked on Oracle text. Could please help me to understant what is this and what's the action required. We are using Oracle 9i.

ORA-20000: Oracle Text error:
DRG-10502: index IDXSEARCH does not exist
ORA-06512: at "CTXSYS.DRUE", line 157
ORA-06512: at "CTXSYS.CTX_DDL", line 1408
ORA-06512: at "SADMIN.PTEXTINDEX", line 90

Thanks and regards.

I have gone through this review but it is not clear because after I faced this problem I have tested the index. It is very much accessable from the user I made this index. I was running the procedure from the same user but still I have faced this problem. I have tested it's accessibility but still it is not working. The solve given here is a generic solve but it is not solving my purpose.

Hi Tom,

I have followed your approach to search on multiple columns in multiple tables and it works like a charm, but I have a side effect:
By tickling the dummy column in the parent table from every child table that is indexed, every time I change a child table and the corresponding parent record is locked by another user I have to wait for him/her to commit (or rollback). This happens in a Forms environment. Do you have an easy solution for that?

I have gone through as per bellow written way to generate a log for all ddl execution but ip address mission from the table DDL_LOGGER what to do. I want it to make generalise because we have different version of oracle here. So, the solve should be generic one.

CREATE OR REPLACE TRIGGER DDLTrigger
AFTER DDL ON DATABASE
BEGIN
INSERT INTO DDL_LOGGER
(UserName, DDLDate, DDLObjType, DDLObjOwner, DDLObjName, DDLEvent,client_ip) VALUES
(ora_login_user, sysdate, ora_dict_obj_type,
ora_dict_obj_owner, ora_dict_obj_name, ora_sysevent,ora_client_ip_address);
END;

Hi Tom

our users want a contains feature that works like the where clause having LIKE '%string%'.

So if they provide the search text as NATION they want to get back all company names that have NATION in it (irespective of whether its a whole word or part of a word ) so all of the following :

THE NATION COMPANY
THE NATIONAL COMPANY
THE INTERNATIONAL COMPANY

should be returned

We are using Oracle Text indexes to speed the text search - but to accomadate the above requirement we are forced to do

contains (alias.company_alias, '%NATION%') > 0

It works fine for cases as given above - however if the user is looking for the Companies with the text 'A B N AMRO' anywhere in the name

Then our query becomes :

contains (alias.company_alias, '%A B N AMRO%') > 0

and that gives error message :

ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms

Couple of q's

a) Is oracle trying to expand the wildcard before matching them to the text index? That is - it is trying to comeup with all English words which match the template %A and since there are so many - that is what is causing the problem ?

b) Can you pls suggest some options that we could explore


Thanks as always for your response

I followed the steps in this forum to create index the function that glued the data together because I 
frequently search on 'MRRH_DOCUMENT_NUMBER and MRRH_MATERIAL_DESCRIPTION fields in MRR_HEADERS table' as if they were a single item. 

Two problems:
1) "UPDATE mrr_headers SET dummycol=dummycol" does not seem to sync the index. So I had to run "EXECUTE ctx_ddl.sync_index('mrr_idx')" after each insert/update to the table.

2) Table DR$MRR_IDX$I seems to always grow after each sync as in step1. I thought DR$MRR_IDX$I should be :
-- deleted for records deleted from MRR_HEADERS
-- updated for records updated in MRR_HEADERS
-- inserted for records inserted into MRR_HEADERS

I ran the following steps:
===========================================================
connect scott/tiger

alter table mrr_headers add (dummycol varchar2(1) );

connect ctxsys/ctxsys

create or replace procedure mrr_proc(
    p_id in rowid,
    p_lob IN OUT clob
)
is
begin
    for c1 in (select upper(MRRH_DOCUMENT_NUMBER)||' '||upper(MRRH_MATERIAL_DESCRIPTION)||' ' thetext from bpst430.mrr_headers where rowid = p_id) 
    loop
        dbms_lob.copy( p_lob, c1.thetext, dbms_lob.getlength( c1.thetext ));
    end loop;
end;
/

grant execute on mrr_proc to public;

connect sys/manager

grant ctxapp to public;

connect scott/tiger

exec ctx_ddl.drop_preference('mrr_user_datastore');
exec ctx_ddl.create_preference( 'mrr_user_datastore', 'user_datastore' );
exec ctx_ddl.set_attribute( 'mrr_user_datastore', 'procedure', 'mrr_proc' );
exec ctx_ddl.drop_preference('my_lexer');
exec ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
exec ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
exec ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
==================================================================================================
scott@FREBPXX1> create index mrr_idx on mrr_headers(dummycol) indextype is ctxsys.context parameters( 'datastore mrr_user_datastore lexer my_lexer');

Index created.

scott@FREBPXX1> select token_text from DR$MRR_IDX$I;

TOKEN_TEXT
----------------------------------------------------------------
00001
00002
Architectural
Concrete
MRR
Shotcrete
Test

7 rows selected.

scott@FREBPXX1> insert into mrr_headers(mrrh_document_number, mrrh_document_date,mrrh_document_status, mrrh_bjob_jobnumber, mrrh_jdat_joblocation, mrrh_Date) 
values (' test_NUMBER',sysdate,'IN process',423432,'J5492243',sysdate);


1 row created.


scott@FREBPXX1> UPDATE mrr_headers SET dummycol=dummycol;

3 rows updated.


scott@FREBPXX1> select token_text from DR$MRR_IDX$I;

TOKEN_TEXT
----------------------------------------------------------------
00001
00002
Architectural
Concrete
MRR
Shotcrete
Test

7 rows selected.

scott@FREBPXX1> EXECUTE ctx_ddl.sync_index('mrr_idx');

PL/SQL procedure successfully completed.

scott@FREBPXX1> select token_text from DR$MRR_IDX$I;

TOKEN_TEXT
----------------------------------------------------------------
00001
00002
Architectural
Concrete
MRR
Shotcrete
Test
00001
00002
Architectural
Concrete
MRR
NUMBER
Shotcrete
TEST
Test

16 rows selected.

scott@FREBPXX1> delete mrr_headers where mrrh_document_number=' TEST_NUMBER';

1 row deleted.


scott@FREBPXX1> UPDATE mrr_headers SET dummycol=dummycol;

2 rows updated.

scott@FREBPXX1> select token_text from DR$MRR_IDX$I;

TOKEN_TEXT
----------------------------------------------------------------
00001
00002
Architectural
Concrete
MRR
Shotcrete
Test
00001
00002
Architectural
Concrete
MRR
NUMBER
Shotcrete
TEST
Test

16 rows selected.

scott@FREBPXX1> EXECUTE ctx_ddl.sync_index('mrr_idx');

PL/SQL procedure successfully completed.

scott@FREBPXX1>  select token_text from DR$MRR_IDX$I;

TOKEN_TEXT
----------------------------------------------------------------
00001
00002
Architectural
Concrete
MRR
Shotcrete
Test
00001
00002
Architectural
Concrete
MRR
NUMBER
Shotcrete
TEST
Test
00001
00002
Architectural
Concrete
MRR
Shotcrete
Test

23 rows selected.

scott@FREBPXX1> insert into mrr_headers(mrrh_document_number, mrrh_document_date,mrrh_document_sta
tus, mrrh_bjob_jobnumber, mrrh_jdat_joblocation, mrrh_Date) 
  2  values ('NUMBER 99',sysdate,'IN process',423432,'J5492243',sysdate);


1 row created.


scott@FREBPXX1> UPDATE mrr_headers SET dummycol=dummycol;

3 rows updated.


scott@FREBPXX1>  select token_text from DR$MRR_IDX$I;

TOKEN_TEXT
----------------------------------------------------------------
00001
00002
Architectural
Concrete
MRR
Shotcrete
Test
00001
00002
Architectural
Concrete
MRR
NUMBER
Shotcrete
TEST
Test
00001
00002
Architectural
Concrete
MRR
Shotcrete
Test

23 rows selected.

scott@FREBPXX1>  EXECUTE ctx_ddl.sync_index('mrr_idx');

PL/SQL procedure successfully completed.


scott@FREBPXX1> select token_text from DR$MRR_IDX$I;

TOKEN_TEXT
----------------------------------------------------------------
00001
00002
Architectural
Concrete
MRR
Shotcrete
Test
00001
00002
Architectural
Concrete
MRR
NUMBER
Shotcrete
TEST
Test
00001
00002
Architectural
Concrete
MRR
Shotcrete
Test
00001
00002
99
Architectural
Concrete
MRR
NUMBER
Shotcrete
Test

32 rows selected.

scott@FREBPXX1> delete mrr_headers where mrrh_document_number='NUMBER 99';

1 row deleted.

scott@FREBPXX1> EXECUTE ctx_ddl.sync_index('mrr_idx');

PL/SQL procedure successfully completed.

scott@FREBPXX1> select TOKEN_TEXT,TOKEN_FIRST,TOKEN_LAST,TOKEN_COUNT from DR$MRR_IDX$I;

TOKEN_TEXT                                                                TOKEN_FIRST           TOKEN_LAST          TOKEN_COUNT
---------------------------------------------------------------- -------------------- --------------
00001                                                                               2                    2                    1
00002                                                                               1                    1                    1
Architectural                                                                       2                    2                    1
Concrete                                                                            2                    2                    1
MRR                                                                                 1                    2                    2
Shotcrete                                                                           1                    1                    1
Test                                                                                2                    2                    1
00001                                                                               5                    5                    1
00002                                                                               3                    3                    1
Architectural                                                                       5                    5                    1
Concrete                                                                            5                    5                    1
MRR                                                                                 3                    5                    2
NUMBER                                                                              4                    4                    1
Shotcrete                                                                           3                    3                    1
TEST                                                                                4                    4                    1
Test                                                                                5                    5                    1
00001                                                                               7                    7                    1
00002                                                                               6                    6                    1
Architectural                                                                       7                    7                    1
Concrete                                                                            7                    7                    1
MRR                                                                                 6                    7                    2
Shotcrete                                                                           6                    6                    1
Test                                                                                7                    7                    1
00001                                                                              10                   10                    1
00002                                                                               8                    8                    1
99                                                                                  9                    9                    1
Architectural                                                                      10                   10                    1
Concrete                                                                           10                   10                    1
MRR                                                                                 8                   10                    2
NUMBER                                                                              9                    9                    1
Shotcrete                                                                           8                    8                    1
Test                                                                               10                   10                    1

32 rows selected.

Hi Tom,

I want to create a domain index ignoring diacritical marks like (á,é,ì,ü,...), but it doesn't work. Here's my script:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> drop table test;
 
Tabla borrada.
 
SQL> begin
  2    ctx_ddl.drop_preference('my_lexer');
  3  end;
  4  / 
 
Procedimiento PL/SQL terminado correctamente.
 
SQL>
SQL> create table test (x varchar(30));
 
Tabla creada.
 
SQL> insert into test values ('rápido');
 
1 fila creada.
 
SQL> commit;
 
Confirmaci¾n terminada.
 
SQL>
SQL>
SQL> begin
  2    ctx_ddl.create_preference('my_lexer', 'BASIC_LEXER');
  3    ctx_ddl.set_attribute('my_lexer', 'base_letter', 'yes');
  4  end;
  5  / 
 
Procedimiento PL/SQL terminado correctamente.
 
SQL> commit;
 
Confirmaci¾n terminada.
 
SQL>
SQL>
SQL> CREATE INDEX test_idx
  2      ON test(x)
  3      INDEXTYPE IS CTXSYS.CONTEXT
  4  parameters ('lexer my_lexer Sync (on commit)');
 
-ndice creado.
 
SQL>
SQL> select x, dump(x) from test;
X                              DUMP(X)
------------------------------ -------------------------------------
rápido                         Typ=1 Len=6: 114,160,112,105,100,111
 
1 fila seleccionada.
 
SQL>
SQL> select * from test where  contains(x,'rapido',1)>0;
 
ninguna fila seleccionada
 
SQL>
SQL> select token_text from dr$test_idx$i;
TOKEN_TEXT
----------------------------------------------------------------
PIDO
R
 
2 filas seleccionadas.



Why Oracle is indexing "rápido" in two pieces, "R" and "PIDO"? I guess that is the reason why "contains(x,'rapido',1)>0" does not find any row.
Am I doing anything wrong related to the baisc_lexer?

Many thanks.

Joaquin Gonzalez

These are my settings (no %NLS_LANG% defined):

SQL> select * from NLS_SESSION_PARAMETERS;
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   SPANISH
NLS_TERRITORY                  SPAIN
NLS_CURRENCY                   Ç
NLS_ISO_CURRENCY               SPAIN
NLS_NUMERIC_CHARACTERS         ,.
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                dd/mm/yyyy hh24:mi:ss
NLS_DATE_LANGUAGE              SPANISH
NLS_SORT                       SPANISH
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              Ç
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 filas seleccionadas.

SQL> select * from NLS_DATABASE_PARAMETERS;
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8MSWIN1252
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.3.0

20 filas seleccionadas.

Anything wrong?

Joaquin Gonzalez

Thanks Tom,

It was a problem with sqlplus.exe (not happening to sqlplusw.exe) displaying wrong caracters, because of the code page it was using I guess.

I've solved executing one of these:
"set NLS_LANG=american_america.US8PC437" or "chcp 1252", before sqlplus.exe.

Many Thanks.

Joaquin Gonzalez

Hi,

We have the following problems with catsearch:

In: searchAllSongs Sqlcode: -29902 SqlErrm: ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms;

The searchString that is being sent to the DB is (the upside down questino mark is really a trademark sign): note¿*

When removing the trademark sign the query works fine.

I then decided put the trademarksign in the skipjoin and recreated the textindex. But the error is still being produced.

select dump('¿') from dual;
Typ=96 Len=3: 226,132,162

Code which is generating the skipjoin:
ctx_ddl.set_attribute(mainViewName || '_' || suffix || '_SpecChLex', 'skipjoins', '`-=[];''\,./~!@#$%^&*()_+{}:"|<>?§&acute;&uml;&frac12;&frac14;&frac34;&curren;£¿©¿®');

When i check the user_source it seems that it has been compiled properly (see bold part which is representing the trademark sign):
SELECT DUMP(text, 1010) DMP FROM user_source e where text like '%skipjoin%'
Typ=1 Len=150 CharacterSet=AL32UTF8: 32,32,32,32,99,116,120,95,100,100,108,46,115,101,116,95,97,116,116,114,105,98,117,116,101,40,109,97,105,110,86,105,101,119,78,97,109, 101,32,124,124,32,39,95,39,32,124,124,32,115,117,102,102,105,120,32,124,124,32,39,95,83,112,101,99,67,104,76,101,120,39,44,32,39,115, 107,105,112,106,111,105,110,115,39,44,32,39,96,45,61,91,93,59,39,39,92,44,46,47,126,33,64,35,36,37,94,38,42,40,41,95,43,123,125,58,34 ,124,60,62,63,194,167,194,180,194,168,194,189,194,188,194,190,194,164,194,163,226,130,172,194,169,226,132,162,194,174,39,41,59,10

If a characther is in a skipjoin I thought that it would be removed before the search is executed. Is this wrong? Or is the problem that the trademark sign has a length of 3? I am experiencing similar problems with ohter signs which has a length biger than 1.

Do you have an idea what it could be?

Thanks,

Slavko

More on above issue:
My investigations shows following:

if i search on:

note¿* (is really a trademark sign)

it translates it to following search:
note *
which means we search on note AND "everything else" the everything else causing the "wildcard query expansion error"

I would have thought if I did not have it in the skipjoin it would have searched on note¿*.
After putting it in the skipjoin and recreating the index I expected it to search on note*.
However in both cases it is as if it replaces the trademark sign with a space.

This happens with other signs as well. Such as:
©¾¿® (third sign is a euro sign)

Any idea ?

Hi Tom,
Here is the TKProf. I cannot read anything out of it as I do not see what is being passed on further to the text index tables.

With regards, Slavko

TKPROF: Release 10.2.0.1.0 - Production on To Dec 11 10:04:41 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Trace file: orcl_ora_1596.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

alter session set events '10046 trace name context forever, level 12'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 244  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

SELECT * 
FROM
 mv_song_se WHERE CATSEARCH (searchtext, 'note¿*', NULL) > 0


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.01       0.27          0         15          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.27          0         15          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 244  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  MAT_VIEW ACCESS BY INDEX ROWID MV_SONG_SE_ROBIN (cr=0 pr=0 pw=0 time=0 us)
      0   DOMAIN INDEX  MV_SONG_SE_ROBIN_IND (cr=0 pr=0 pw=0 time=0 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net break/reset to client                   4        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

SELECT/*+DYNAMIC_SAMPLING(0) INDEX(T "DR$MV_SONG_SE_ROBIN_IND$X")*/ DISTINCT 
  DR$TOKEN FROM "MEDIA_CORE"."DR$MV_SONG_SE_ROBIN_IND$I" T WHERE DR$TOKEN 
  LIKE :lkexpr ESCAPE '\' and (DR$TOKEN_TYPE IN (0, 4, 6, 604) OR 
  (DR$TOKEN_TYPE BETWEEN 16 AND 74) OR                                     
  (DR$TOKEN_TYPE BETWEEN 616 AND 674))


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.15          0          0          0           0
Fetch       10      0.48       0.51          0       2834          0       10240
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.48       0.66          0       2834          0       10240

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 244     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
   5120  SORT UNIQUE NOSORT (cr=1417 pr=0 pw=0 time=2851403 us)
 475993   INDEX RANGE SCAN DR$MV_SONG_SE_ROBIN_IND$X (cr=1417 pr=0 pw=0 time=1904228 us)(object id 73733)




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.01       0.27          0         15          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.01       0.27          0         15          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       6        0.00          0.00
  SQL*Net message from client                     6       20.85         35.14
  SQL*Net break/reset to client                   4        0.00          0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.15          0          0          0           0
Fetch       10      0.48       0.51          0       2834          0       10240
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.48       0.66          0       2834          0       10240

Misses in library cache during parse: 0

    5  user  SQL statements in session.
    0  internal SQL statements in session.
    5  SQL statements in session.
********************************************************************************
Trace file: orcl_ora_1596.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
       5  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       5  SQL statements in trace file.
       3  unique SQL statements in trace file.
     107  lines in trace file.
      15  elapsed seconds in trace file.

This is the trace file. The onlu value I can see is a '%' which would indicate a full free wildcard search i.e. '%'. I am a bit unsure about reading this file however.

With regards,
Slavko

Dump file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1596.trc
Thu Dec 11 10:03:46 2008
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 3
CPU                 : 2 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:180M/2015M, Ph+PgF:1801M/3906M, VA:1283M/2047M
Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 20

Windows thread id: 1596, image: ORACLE.EXE (SHAD)


*** ACTION NAME:() 2008-12-11 10:03:46.345
*** MODULE NAME:(SQL*Plus) 2008-12-11 10:03:46.345
*** SERVICE NAME:(orcl) 2008-12-11 10:03:46.345
*** SESSION ID:(146.3529) 2008-12-11 10:03:46.345
=====================
PARSING IN CURSOR #2 len=69 dep=0 uid=244 oct=42 lid=244 tim=265251453882 hv=3164292706 ad='2ff3ff5c'
alter session set events '10046 trace name context forever, level 12'
END OF STMT
EXEC #2:c=0,e=41,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=265251453878
WAIT #2: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=265251673556
WAIT #2: nam='SQL*Net message from client' ela= 863 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=265251688966
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=265251692775
*** 2008-12-11 10:04:00.876
WAIT #0: nam='SQL*Net message from client' ela= 14281846 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=265265978814
=====================
PARSING IN CURSOR #1 len=73 dep=0 uid=244 oct=3 lid=244 tim=265266012299 hv=2753226331 ad='2ffaecbc'
SELECT * FROM mv_song_se WHERE CATSEARCH (searchtext, 'note¿*', NULL) > 0
END OF STMT
PARSE #1:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=265266012294
BINDS #1:
=====================
PARSING IN CURSOR #4 len=322 dep=1 uid=244 oct=3 lid=244 tim=265266101610 hv=1402451868 ad='33609ef4'
SELECT/*+DYNAMIC_SAMPLING(0) INDEX(T "DR$MV_SONG_SE_ROBIN_IND$X")*/ DISTINCT DR$TOKEN FROM "MEDIA_CORE"."DR$MV_SONG_SE_ROBIN_IND$I" T WHERE DR$TOKEN LIKE :lkexpr ESCAPE '\' and (DR$TOKEN_TYPE IN (0, 4, 6, 604) OR (DR$TOKEN_TYPE BETWEEN 16 AND 74) OR                                     (DR$TOKEN_TYPE BETWEEN 616 AND 674))
END OF STMT
PARSE #4:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=265266101605
BINDS #4:
kkscoacd
 Bind#0
  oacdty=01 mxl=128(127) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=01 csi=178 siz=128 off=0
  kxsbbbfp=08a9c510  bln=128  avl=01  flg=05
  value="%"
EXEC #4:c=0,e=126249,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=265266301750
FETCH #4:c=31250,e=40837,p=0,cr=229,cu=0,mis=0,r=1024,dep=1,og=1,tim=265266346671
FETCH #4:c=31250,e=20536,p=0,cr=115,cu=0,mis=0,r=1024,dep=1,og=1,tim=265266393058
FETCH #4:c=125000,e=96464,p=0,cr=531,cu=0,mis=0,r=1024,dep=1,og=1,tim=265266493835
FETCH #4:c=31250,e=74647,p=0,cr=414,cu=0,mis=0,r=1024,dep=1,og=1,tim=265266609123
FETCH #4:c=0,e=23589,p=0,cr=128,cu=0,mis=0,r=1024,dep=1,og=1,tim=265266637071
EXEC #1:c=218750,e=598253,p=0,cr=1432,cu=0,mis=0,r=0,dep=0,og=1,tim=265266675365
ERROR #1:err=29902 tim=26525340
WAIT #1: nam='SQL*Net break/reset to client' ela= 5 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=265266700125
WAIT #1: nam='SQL*Net break/reset to client' ela= 123 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=265266719147
WAIT #1: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=265266723376
WAIT #1: nam='SQL*Net message from client' ela= 1918 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=265266737958
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=64068 op='MAT_VIEW ACCESS BY INDEX ROWID MV_SONG_SE_ROBIN (cr=0 pr=0 pw=0 time=0 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=73731 op='DOMAIN INDEX  MV_SONG_SE_ROBIN_IND (cr=0 pr=0 pw=0 time=0 us)'
WAIT #0: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=265266772916
WAIT #0: nam='SQL*Net message from client' ela= 311 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=265266790339
=====================
PARSING IN CURSOR #5 len=73 dep=0 uid=244 oct=3 lid=244 tim=265266794959 hv=2753226331 ad='2ffaecbc'
SELECT * FROM mv_song_se WHERE CATSEARCH (searchtext, 'note¿*', NULL) > 0
END OF STMT
PARSE #5:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=265266794953
BINDS #5:
STAT #4 id=1 cnt=5120 pid=0 pos=1 obj=0 op='SORT UNIQUE NOSORT (cr=1417 pr=0 pw=0 time=2866773 us)'
STAT #4 id=2 cnt=475993 pid=1 pos=1 obj=73733 op='INDEX RANGE SCAN DR$MV_SONG_SE_ROBIN_IND$X (cr=1417 pr=0 pw=0 time=1904210 us)'
=====================
PARSING IN CURSOR #2 len=322 dep=1 uid=244 oct=3 lid=244 tim=265266826881 hv=1402451868 ad='33609ef4'
SELECT/*+DYNAMIC_SAMPLING(0) INDEX(T "DR$MV_SONG_SE_ROBIN_IND$X")*/ DISTINCT DR$TOKEN FROM "MEDIA_CORE"."DR$MV_SONG_SE_ROBIN_IND$I" T WHERE DR$TOKEN LIKE :lkexpr ESCAPE '\' and (DR$TOKEN_TYPE IN (0, 4, 6, 604) OR (DR$TOKEN_TYPE BETWEEN 16 AND 74) OR                                     (DR$TOKEN_TYPE BETWEEN 616 AND 674))
END OF STMT
PARSE #2:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=265266826876
BINDS #2:
kkscoacd
 Bind#0
  oacdty=01 mxl=128(127) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=01 csi=178 siz=128 off=0
  kxsbbbfp=08a9c510  bln=128  avl=01  flg=05
  value="%"
EXEC #2:c=0,e=27563,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=265266874315
FETCH #2:c=31250,e=40517,p=0,cr=229,cu=0,mis=0,r=1024,dep=1,og=1,tim=265266918813
FETCH #2:c=15625,e=20755,p=0,cr=115,cu=0,mis=0,r=1024,dep=1,og=1,tim=265266944138
FETCH #2:c=109375,e=97253,p=0,cr=531,cu=0,mis=0,r=1024,dep=1,og=1,tim=265267045936
FETCH #2:c=78125,e=75478,p=0,cr=414,cu=0,mis=0,r=1024,dep=1,og=1,tim=265267125962
FETCH #2:c=31250,e=23768,p=0,cr=128,cu=0,mis=0,r=1024,dep=1,og=1,tim=265267154153
EXEC #5:c=281250,e=344348,p=0,cr=1417,cu=0,mis=0,r=0,dep=0,og=1,tim=265267158717
ERROR #5:err=29902 tim=26525389
WAIT #5: nam='SQL*Net break/reset to client' ela= 4 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=265267167100
WAIT #5: nam='SQL*Net break/reset to client' ela= 121 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=265267171272
WAIT #5: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=265267175333
WAIT #5: nam='SQL*Net message from client' ela= 1877 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=265267181231
STAT #5 id=1 cnt=0 pid=0 pos=1 obj=64068 op='MAT_VIEW ACCESS BY INDEX ROWID MV_SONG_SE_ROBIN (cr=0 pr=0 pw=0 time=0 us)'
STAT #5 id=2 cnt=0 pid=1 pos=1 obj=73731 op='DOMAIN INDEX  MV_SONG_SE_ROBIN_IND (cr=0 pr=0 pw=0 time=0 us)'
WAIT #0: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=265267194145
*** 2008-12-11 10:04:22.954
WAIT #0: nam='SQL*Net message from client' ela= 20859756 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=265288058245
XCTEND rlbk=0, rd_only=1
STAT #2 id=1 cnt=5120 pid=0 pos=1 obj=0 op='SORT UNIQUE NOSORT (cr=1417 pr=0 pw=0 time=2851403 us)'
STAT #2 id=2 cnt=475993 pid=1 pos=1 obj=73733 op='INDEX RANGE SCAN DR$MV_SONG_SE_ROBIN_IND$X (cr=1417 pr=0 pw=0 time=1904228 us)'

First of all, many thanks to Tom for this pointed article.

I was facing the same problem as a reader from USA has posted.
Referring to the documentation, we need to either re-index ourselves or let oracle to create jobs to achieve this task automatically.

http://download.oracle.com/docs/html/B10701_02/text.htm

----- quote -----
When the Oracle CM SDK schema is created during configuration, two DBMS_JOBS are set up: Sync Job and Optimize Job.

Note:
Sync Job and Optimize Job are only automatically created when a new schema is created. If you are upgrading from an existing schema, these jobs will not be created automatically.

Sync Job

Sync Job will periodically call the 'ctx_ddl.sync_index()' method. This method indexes the documents that were created or updated since the last run. By default, this job is set up to run every 30 minutes.
----- quote -----


Please correct me if I am getting wrong.
Regards,

Sorry not to have been clear, I just wanted to clarify if my understanding of Index maintenance is correct. I could solve my problem with index with setting it to maintain upon commit.

Thanks for your confirmation.
Regards,

You provided a great example of indexing across tables:

create or replace procedure index_ask_tom( p_id in rowid, p_lob IN OUT clob )
as
begin
for x in ( select * from ask_tom.WWC_ASK_INDEXED_QUESTIONS$ where rowid = p_id )
loop
dbms_lob.copy( p_lob, x.text, dbms_lob.getLength(x.text) );
for y in ( select '<!-- ' || REVIEW_TITLE || ' ' || REVIEWER_NAME || ' ' ||
REVIEWER_LOCATION || ' ' subj,
review_comments
from ask_tom.WWC_ASK_QUESTION_REVIEWS$ where displayid = x.displayid )
loop
dbms_lob.writeAppend( p_lob, length(y.subj), y.subj );
dbms_lob.append( p_lob, y.review_comments );
end loop;
for y in ( select followup_comments
from ask_tom.WWC_ASK_REVIEW_FOLLOWUPS$
where displayid = x.displayid )
loop
dbms_lob.writeAppend( p_lob, 1, ' ' );
dbms_lob.append( p_lob, y.followup_comments );
end loop;
dbms_lob.writeAppend( p_lob, length(' -->'), ' -->' );
end loop;
end;
/

My question is how do you maintain this index? For example, if an answer is updated how does this get in the "sync queue" since the change isn't to the table the index is on?

Thanks Tom! I missed that sorry.
So what type of trick do you need to do that would cause it to be synced?
Do you have an example of what the trigger would need to look like?
Sorry if this is above but I looked serveral times and don't see an example of the trigger.
I don't have any data I can modify per say but there something I can do that just marks it to be synced?
Thanks again!

ops$tkyte%ORA10GR2> create table foo (id number primary key, thetext clob, dummycol varchar2(1) );

Table created.

ops$tkyte%ORA10GR2> create table bar (pk number primary key, id references foo, thetext clob );

Table created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure foo_proc(
  2      p_id in rowid,
  3      p_lob IN OUT clob
  4  )
  5  is
  6  begin
  7      for c1 in (select id, thetext from foo where rowid = p_id)
  8      loop
  9                  p_lob := c1.thetext;
 10                  for c2 in (select thetext from bar where id = c1.id)
 11                  loop
 12                          p_lob := p_lob || ' ' || c2.thetext;
 13                  end loop;
 14      end loop;
 15  end;
 16  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec ctx_ddl.drop_preference('foo_user_datastore');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec ctx_ddl.drop_preference('my_lexer');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2  ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
  3  ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'ops$tkyte.foo_proc' );
  4  end;
  5  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> 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%ORA10GR2>
ops$tkyte%ORA10GR2> create index foo_idx on foo(dummycol) indextype is ctxsys.context parameters( 'datastore foo_user_datastore lexer my_lexer sync(on commit)');

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace trigger bar
  2  after insert or delete or update on bar
  3  for each row
  4  begin
  5          if (inserting or updating)
  6          then
  7                  update foo set dummycol = dummycol where id = :new.id;
  8          end if;
  9          if (deleting or updating)
 10          then
 11                  update foo set dummycol = dummycol where id = :old.id;
 12          end if;
 13  end;
 14  /

Trigger created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into foo (id,thetext)values ( 1, 'parent 1' );

1 row created.

ops$tkyte%ORA10GR2> insert into bar (pk,id,thetext) values ( 100, 1, 'hello world' );

1 row created.

ops$tkyte%ORA10GR2> insert into bar (pk,id,thetext) values ( 101, 1, 'analytics rock' );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into foo (id,thetext)values ( 2, 'parent 2' );

1 row created.

ops$tkyte%ORA10GR2> insert into bar (pk,id,thetext) values ( 200, 2, 'sometimes triggers are ok' );

1 row created.

ops$tkyte%ORA10GR2> insert into bar (pk,id,thetext) values ( 201, 2, 'not very often, but occasionally' );

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from foo where contains( dummycol, 'analytics' ) > 0;

        ID
----------
THETEXT
-------------------------------------------------------------------------------
D
-
         1
parent 1



ops$tkyte%ORA10GR2> select * from foo where contains( dummycol, 'triggers' ) > 0;

        ID
----------
THETEXT
-------------------------------------------------------------------------------
D
-
         2
parent 2

Tom,
I would like to apologize if you deem this as a new question and also a very long post. To avoid any ambiguity I have to go in bit more detail.

I am having an issue with one of the query when text index is not been used. Please find the steps required to re-create the problem.

Scenario is as follows :
We have a DB which is OLS and VPD enabled and a bunch of users who can only create data but they cannot read the data. There is another bunch of users who has the policy to read the data but cannot select the table directly. So we have packages created in the respectivev schema and execute privilege is been granted to the Schema which can read the data. Packages are created with the default rights (Authid Definer).

So let's assume Schema A and Schema B can create data but cannot see the data. Schema A has few direct select access for tables defined in Schema B. Schema C has the OLS policy to read the data from Schema A and Schema B.
But to simply the problem I have created only two schemas test_user1 and test_user2. I am trying to select a table owned by test_user1 from test_user2. I am able to see the correct plan (i.e) domain index been used when selecting the table from test_user1 but when I execute the same query from test_user2 I am not able to achieve the same plan. I tried enabling the 10053 trace and it is not been considered. Any thoughts on this.

I have created the policy and label as described in the followup from David Knox dated September 26 2004.

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

Please find the scripts to re-create the problem.
Os Version : Solaris 10
Oracle Version : 10.2.0.4 with OLS enabled

conn / as sysdba
create user test_user1 identified by password;
grant connect, resource to test_user1;
create user test_user2 identified by password;
grant connect, resource to test_user2;
conn test_user1/password
drop table test_tab;  
create table test_tab
( 
 test_sno number(4) not null,
 test_xml xmltype,
 constraint pk_test_tab primary key(test_sno)
);
create index test_xml_ti on test_tab
(
 test_xml
)
indextype is ctxsys.context;
exec dbms_stats.gather_table_stats(NULL,'TEST_TAB');

Rem Before Label policy were created.

explain plan for
select * from test_tab t
where contains(t.test_xml,'Bob',1) > 0;

select * from table(dbms_xplan.display);

grant select on test_tab to test_user1;

conn test_user2/password
explain plan for
select * from test_tab t
where contains(t.test_xml,'Bob',1) > 0;
select * from table(dbms_xplan.display);

Rem Label Policy created as per the steps given by David Knox followup dated September 26 2004 and test_tab table is been added to that policy.

conn test_user1/password
explain plan for
select * from test_tab t
where contains(t.test_xml,'Bob',1) > 0;

select * from table(dbms_xplan.display);

conn test_user2/password

explain plan for
select * from test_tab t
where contains(t.test_xml,'Bob',1) > 0;

select * from table(dbms_xplan.display);

Rem You could see at this step plans will be different

conn lbacsys/lbacsys

exec sa_policy_admin.disable_table_policy('OLS_MV','TEST_USER1','TEST_TAB');

conn test_user2/password
explain plan for
select * from test_tab t
where contains(t.test_xml,'Bob',1) > 0;

select * from table(dbms_xplan.display);

Rem After the policy been removed the plans will be reverted to the old plan (i.e) using the domain text index

When I try to execute the same queries by disabling the policy on the table I am able to see oracle using the domain text index whereas with the policy enabled I am not able to. I would like to know is this a limitation or am I missing something here.

I know that I have not inserted any data in any of the tables but the actual table contains atleast few million rows and I could see it is performing quite a lot of consistent gets and physical reads which is causing a lot of grief.

Your thoughts on this will be very helpful.

Regards
Rajaram
P.S : I am very well aware FTS is not evil and Index is not always good. But in this case I don't want oracle to do an FTS on the table and filter the values using contains. I would like to know why it is ignoring the domain text index?

Thanks Tom for example of the trigger!
Just one more thing. Since the index is created on a dummy column there probably needs to be a trigger added to the foo table as well since a change to text field in that table won't trigger a sync either, correct?

Hi Tom,

Trust you had a good christmas.

Happy new year 2010.

Further to my post dated December 21 2009 I have created a working example of the issue. I have already posted the steps required to recreate the problem on orafaq forum. I am furnishing the details as below :

http://www.orafaq.com/forum/mv/msg/153210/436281/94420/#msg_436281

If you any trouble in downloading the attachment please do let me know. I will post it inline.

When you have time could you please look into this and let me know your thoughts on this.

Regards

Rajaram Subramanian

Hi Tom,

Sorry to bother you again. Did you had a chance to look into the issue I have mentioned earlier dated 21.12.2009?

Thanks in advance

Rajaram