Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Vittorio.

Asked: July 17, 2000 - 5:52 am UTC

Answered by: Tom Kyte - Last updated: March 01, 2010 - 8:22 am UTC

Category: Database - Version: 8.1.6

Viewed 1000+ times

You Asked

I have a table with a varchar2 column (Title) and a clob column (Body).
Can i perform a select with the contains command for searching on each columns ?

For example i want to search the word 'Oracle' on Title and Body.

Thanks.



and we said...

Yes you can -- you have 2 options here.

1) create two indexes, one on title and one on body. Your queries would then take the form:

select * from T where contains(title,'Oracle') > 0 OR
contains(body,'Oracle') > 0;


2) create a PLSQL function that returns returns the two fields glued together. It would look something like:

connect scott/tiger

create table foo (id number primary key, thetext clob,
dummycol varchar2(1) );

insert into foo(id, thetext) values(1, 'I was here');
insert into foo(id, thetext) values(2, 'I was here too');
insert into foo(id, thetext) values(3, 'And Again');
commit;

connect ctxsys/ctxsys

create or replace procedure foo_proc(
p_id in rowid,
p_lob IN OUT clob
)
is
begin
for c1 in (select thetext from scott.foo 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;
/

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


and you rated our response

  (51 ratings)

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

Reviews

September 28, 2001 - 10:10 am UTC

Reviewer: Sundararajan from India

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...

Tom Kyte

Followup  

September 28, 2001 - 2:12 pm UTC

umm, select * from t where contains( dummycol, 'the word' )


You originally asked in your question something along the lines of:

i have a table with columns c1, c2, c3, ....

I need to find all rows where c1 = value or c2 = value or c3 = value.

How can I do this efficiently.
....

I sent you here, showing how to index a FUNCTION. Look at #2 below and you see:

for c1 in (select thetext from scott.foo 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;

you would concatent c1, c2, c3, ... together with spaces in between and index that.


Tried copying ur example but not working

December 31, 2001 - 3:08 am UTC

Reviewer: Sushil Vyas from India

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.

Tom Kyte

Followup  

December 31, 2001 - 7:44 am UTC

so show us the steps you used as I showed you the steps I used. Perhaps the blocks of code that do the drop and add are failing for you as you don't have objects to drop. You don't mention whether you hit and errors.

make sure you haven't hit any ERRORS and if you did, correct them!

Custom Datastore and CTX_DOC.HIGHLIGHT

May 08, 2003 - 1:21 pm UTC

Reviewer: Angel Faus from Barcelona, Spain

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.


Tom Kyte

Followup  

May 09, 2003 - 12:09 pm UTC

if the cost of assembling the document (which it must do) is too expensive to do upon retrieval, that would be an approach.

I just reassemble upon retrieval myself here. In fact, every page is reassembled from its constituent parts each time.

How about catalog index

July 17, 2003 - 6:12 pm UTC

Reviewer: Jason from USA

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

Tom Kyte

Followup  

July 17, 2003 - 7:41 pm UTC

it is done a little differently, but in a manner, yes

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

April 19, 2004 - 10:09 am UTC

Reviewer: A reader


How to use & in contains keyword in context index

August 27, 2004 - 1:53 pm UTC

Reviewer: Renjith from India

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

Tom Kyte

Followup  

August 27, 2004 - 2:02 pm UTC

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
 
 

Vow!!!

August 27, 2004 - 5:28 pm UTC

Reviewer: A reader


Concatenation index very slow

March 07, 2005 - 12:15 pm UTC

Reviewer: Ashish from New York, NY

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?


Tom Kyte

Followup  

March 07, 2005 - 7:55 pm UTC

well, the creates and frees of the clobs will be the biggest thing here -- but do you need to index a function in this case?

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

might do it.

I worked

March 09, 2005 - 9:18 am UTC

Reviewer: A reader

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


using indexes for full sentence

March 15, 2005 - 6:35 am UTC

Reviewer: APL

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 Kyte

Followup  

March 15, 2005 - 8:21 am UTC

instead of &, use OR

'{oracle} or {database}'




using indexes for full sentence

March 15, 2005 - 6:36 am UTC

Reviewer: APL

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?

How about the string appearance order

April 04, 2005 - 3:34 pm UTC

Reviewer: Pauline from NY,USA

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 Kyte

Followup  

April 04, 2005 - 5:30 pm UTC

nope, the results are whatever they are. without an order by, the data should be considered in random order.

you can use SCORE() to sort by the context score or whatever you want.

April 05, 2005 - 9:34 am UTC

Reviewer: Pauline from NY,USA

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



Tom Kyte

Followup  

April 05, 2005 - 12:09 pm UTC

</code> http://otn.oracle.com/pls/db92/db92.docindex?remark=homepage#index-TEX <code>

It really helps

April 06, 2005 - 11:53 am UTC

Reviewer: Pauline

Thanks very much for the link.

Contains

May 11, 2005 - 12:06 pm UTC

Reviewer: mohini from Germany

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..



Tom Kyte

Followup  

May 11, 2005 - 12:34 pm UTC

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
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:37336026927381#37360199308431
for using leading percents.
 
 

Single Index verses multiple indices

May 11, 2005 - 1:34 pm UTC

Reviewer: Mohini from Germany

"
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 Kyte

Followup  

May 11, 2005 - 7:21 pm UTC

section searching, tag the elements, single index -- build the contains clause on the fly.

text deals with ands/ors nicely

and will do the leading % good too

A code utility from Oracle

May 11, 2005 - 5:47 pm UTC

Reviewer: Mohini from Germany

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.


Tom Kyte

Followup  

May 12, 2005 - 7:25 am UTC

the function you index can query anything it wants to query

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5225986313862#5280275400644 <code>

is an example of the function I index on this site.

Thanks for the pointer

May 11, 2005 - 9:36 pm UTC

Reviewer: mohini from Germany

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

Thanks

Indexed Procedure

May 12, 2005 - 11:45 am UTC

Reviewer: mohini from Germany

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...

Tom Kyte

Followup  

May 12, 2005 - 1:28 pm UTC

i have triggers on the base table to tickle the parent row, to cause the reindex to fire on the parent.

hmmm

May 12, 2005 - 4:15 pm UTC

Reviewer: mohini from germany

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?



Tom Kyte

Followup  

May 12, 2005 - 9:14 pm UTC

yes -- the index is on the 'top' table and I tickle the row from the lower tables.

User Profile Search

May 13, 2005 - 8:52 am UTC

Reviewer: David Rydzewski from Lexington, MA

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

varchar2 and a pdf blob

November 17, 2005 - 7:54 pm UTC

Reviewer: DT from Chicago

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?

Tom Kyte

Followup  

November 18, 2005 - 10:29 am UTC

I believe you would need two indexes for this - especially for the scoring, there is no way really to change the scoring - so you would need to have the score for the title and the score for the pdf - so as to prioritize the title.

Easier Way

February 20, 2006 - 9:02 am UTC

Reviewer: Phil from UK

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


Tom Kyte

Followup  

February 20, 2006 - 12:15 pm UTC

I don't see how you get from

a) function to return them concatenated (that could be the builtin concat() function)

b) a b*tree index on that function

c) to a text index???

You don't text index a b*tree index.

Missed the point

February 20, 2006 - 3:55 pm UTC

Reviewer: Phil from UK

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

Tom Kyte

Followup  

February 21, 2006 - 7:24 am UTC

why would you drop and rebuild?

Index sync

February 21, 2006 - 3:28 am UTC

Reviewer: Pawel from Krakow, Poland

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

Tom Kyte

Followup  

February 21, 2006 - 7:48 am UTC

2) you have a trigger on T that "tickles" the parent table. updates a dummy column or some existing "safe" column



March 27, 2006 - 12:29 pm UTC

Reviewer: A reader

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.

Tom Kyte

Followup  

March 27, 2006 - 3:19 pm UTC

you do not seem to have put any spaces in there - you are indexing the concatention of s1, s2 and s3 - not the three "words"

did you mean to do that?

Thanks a lot !!

March 27, 2006 - 4:08 pm UTC

Reviewer: A reader

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.

March 29, 2006 - 9:51 am UTC

Reviewer: A reader from Boston,MA

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 Kyte

Followup  

March 29, 2006 - 11:12 am UTC

when you update the dummy column - that causes the re-index of that row to be scheduled. You don't need a trigger on the parent.

May 03, 2006 - 11:46 am UTC

Reviewer: A reader

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
 

Tom Kyte

Followup  

May 03, 2006 - 1:23 pm UTC

it worked - too is a "stopword" (common word, not indexed by default)

scott@ORA10GR2> select * from foo where contains(dummycol,'too') > 0;

no rows selected

scott@ORA10GR2> select * from foo where contains(dummycol,'Again') > 0;

ID
----------
THETEXT
-------------------------------------------------------------------------------
D
-
3
And Again



these are the tokens that were indexed given your example:


scott@ORA10GR2> select token_text from dr$foo2_idx$i;

TOKEN_TEXT
----------------------------------------------------------------
Again
And
I



May 03, 2006 - 1:36 pm UTC

Reviewer: A reader

Thanks.

Oracle Text Error

July 14, 2006 - 3:48 pm UTC

Reviewer: Laxman Kondal from Springfield, VA

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.


Tom Kyte

Followup  

July 14, 2006 - 4:07 pm UTC


[tkyte@dellpe ~]$ oerr drg 10502
10502,0, "index %(1)s does not exist"
// *Cause: the specified index does not exist or you do not have
// access to it.
// *Action: Specify an existing index
// *Mnemonic: DL_POLICY_NOTXIST
//


apparently the index IDXSEARCH doesn't exist or you don't have the privilege to do whatever you are trying to do with it?

Oracle Text Error July 14, 2006

December 13, 2006 - 6:37 am UTC

Reviewer: Pulakesh Dey from INDIA

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.

Tom Kyte

Followup  

December 13, 2006 - 7:49 am UTC

and we have....


quite simply........

no clue what your "purpose" is........



my car won't start - why not?

(now, now we are even)

Tickling of parent table causes waits

December 21, 2006 - 11:14 am UTC

Reviewer: Freek from Leuven, Belgium

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?

Tom Kyte

Followup  

December 22, 2006 - 6:02 am UTC

think about it - you need to reindex a HIERARCHY, this will necessarily serialize at the parent record level.

How to add client_ip_adddress on ddltrigger?

January 02, 2007 - 6:26 am UTC

Reviewer: Pulakesh Dey from INDIA

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;

using contains in place of LIKE '%string%'

June 28, 2007 - 9:03 pm UTC

Reviewer: Avid Reader from Boston

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

Tom Kyte

Followup  

July 02, 2007 - 2:08 pm UTC

a) Oracle would expand %A and AMRO%, yes, that would be the problem (context works with "words", not a single string)

b) what about where company_alias like '%A B N AMRO%';


August 15, 2007 - 4:58 pm UTC

Reviewer: A reader from USA

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.


index ignoring diacritical marks

December 02, 2008 - 8:47 am UTC

Reviewer: joaquin gonzalez from Spain

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

Tom Kyte

Followup  

December 02, 2008 - 10:47 am UTC

what are all of your nls_settings - client, database

NLS settings (index ignoring diacritical marks)

December 02, 2008 - 11:27 am UTC

Reviewer: Joaquin Gonzalez from Spain

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

Tom Kyte

Followup  

December 02, 2008 - 11:48 am UTC

is your character a valid character in this character set

WE8MSWIN1252


and NLS_LANG will definitely be set, seems you are windows - look into your registry, it is always set.


160 in windows 1252 I'm pretty sure is a non-breaking space, not an a with a diacritical - I think you might have some "accidentally bad data" in there.

http://en.wikipedia.org/wiki/Windows-1252

NLS LANG

December 03, 2008 - 5:02 am UTC

Reviewer: Joaquin Gonzalez from Spain

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

Problem with Catsearch and special characters in UTF8

December 10, 2008 - 5:53 am UTC

Reviewer: Slavko Brkic from Sweden

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
Tom Kyte

Followup  

December 10, 2008 - 9:57 am UTC

turn on sql trace (10046 level 12 when you run the catsearch query and see what the query to find the terms is and what the inputs where to start debugging this

Problem with Catsearch and special characters in UTF8

December 10, 2008 - 9:14 am UTC

Reviewer: Slavko Brkic from Sweden

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 ?

Problem with Catsearch and special characters in UTF8

December 11, 2008 - 4:34 am UTC

Reviewer: Slavko Brkic from Sweden

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.



Tom Kyte

Followup  

December 11, 2008 - 7:41 am UTC

read the trace file, the binds are in there.

you are looking for the query that expands the token list.

Problem with Catsearch and special characters in UTF8

December 11, 2008 - 3:13 pm UTC

Reviewer: Slavko Brkic from Sweden

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)'

Tom Kyte

Followup  

December 11, 2008 - 9:09 pm UTC

sorry - I don't have the facility to reproduce - I don't know why it is using like to retrieve everything there - I'll suggest the forums on otn.oracle.com for this one.

Re: re-indexing issue - Reviewer: A reader from USA August 15, 2007 - 4pm US/Eastern

February 10, 2009 - 1:38 pm UTC

Reviewer: taro from Tokyo, Japan

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,
Tom Kyte

Followup  

February 11, 2009 - 9:30 am UTC

I cannot correct you if you are wrong, because I'm not sure what you are asking - what you are trying to solve.


In current releases the index can be maintained:

upon commit, automagically
on a schedule, which you can specify during the create index
on demand, when you feel like it, manually

Sorry not to have been clear in my previous post

February 17, 2009 - 5:02 am UTC

Reviewer: taro from Tokyo, Japan

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,

Text Indexing across tables

December 18, 2009 - 1:02 pm UTC

Reviewer: Chris Holt from Auburn, NH USA

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?
Tom Kyte

Followup  

December 18, 2009 - 1:14 pm UTC

see above

...
i have triggers on the base table to tickle the parent row, to cause the reindex to fire on the
parent.
.....

Tickle Parent table

December 18, 2009 - 1:51 pm UTC

Reviewer: Chris Holt from Auburn, NH USA

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!
Tom Kyte

Followup  

December 18, 2009 - 2:54 pm UTC

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


Text indexing with Label Security

December 21, 2009 - 5:06 am UTC

Reviewer: Rajaram Subramanian from London, United Kingdom

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?
Tom Kyte

Followup  

December 21, 2009 - 3:01 pm UTC

put it all together. policy and all.


It might well have to ignore the text index since the data in the index is not protected.

Triggers to keep index in sync

December 21, 2009 - 9:53 am UTC

Reviewer: Chris from Auburn, NH USA

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?
Tom Kyte

Followup  

December 21, 2009 - 3:12 pm UTC

hah, yes. I did not because in my application - the question and answer are immutable.... And they are in the parent record, inserted - never ever updated.

Further followup to my post from December 21 2009

January 04, 2010 - 10:48 am UTC

Reviewer: Rajaram Subramanian from London, United Kingdom

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

Followup further to my post earlier

February 23, 2010 - 11:07 am UTC

Reviewer: Rajaram Subramaninan from London, UK

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
Tom Kyte

Followup  

March 01, 2010 - 8:22 am UTC

I glanced at it, it was huge, it didn't really have any narration like "see, here I'm expecting X but I see Y".

But mostly because it was HUGE (surely, it need not be that large?). It should be as simple as create table, put on policy, plop a single row in there, use dbms_stats to fake out stats and show a plan...


but it almost certainly by design. You have label-ed the information in the table, we can only see a row if we are allowed to. In the index, we have the equivalent of an inverted list index - a single entry in the index points to hundreds/more rows. You could reconstruct a row instance from the index (see data from the row in the index itself) - so searching on that index might well be restricted (due to the fact that it points to many rows - not just one - hence it cannot have a label).

More to Explore

DBMS_LOB

More on PL/SQL routine DBMS_LOB here