Sundararajan, September 28, 2001 - 10:10 am UTC
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...
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
Sushil Vyas, December 31, 2001 - 3:08 am UTC
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.
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
Angel Faus, May 08, 2003 - 1:21 pm UTC
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.
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
Jason, July 17, 2003 - 6:12 pm UTC
Can we concatinate multiple columns in creating catalog indexes? Thanks.
July 17, 2003 - 7:41 pm UTC
A reader, April 19, 2004 - 10:09 am UTC
How to use & in contains keyword in context index
Renjith, August 27, 2004 - 1:53 pm UTC
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
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!!!
A reader, August 27, 2004 - 5:28 pm UTC
Concatenation index very slow
Ashish, March 07, 2005 - 12:15 pm UTC
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 worked
A reader, March 09, 2005 - 9:18 am UTC
I did not know that there is a MULTI_COLUMN_DATASTORE
option which is new 9i.
using indexes for full sentence
APL, March 15, 2005 - 6:35 am UTC
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?
March 15, 2005 - 8:21 am UTC
instead of &, use OR
'{oracle} or {database}'
using indexes for full sentence
APL, March 15, 2005 - 6:36 am UTC
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
Pauline, April 04, 2005 - 3:34 pm UTC
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.
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.
Pauline, April 05, 2005 - 9:34 am UTC
Tom,
Thanks very much for your answer. Could you also give some link or example for using score() function?
April 05, 2005 - 12:09 pm UTC
It really helps
Pauline, April 06, 2005 - 11:53 am UTC
Thanks very much for the link.
Contains
mohini, May 11, 2005 - 12:06 pm UTC
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..
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
Mohini, May 11, 2005 - 1:34 pm UTC
"
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..
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
Mohini, May 11, 2005 - 5:47 pm UTC
Thanks for the pointer
mohini, May 11, 2005 - 9:36 pm UTC
Completely forgot about your "expert one on one"...
Ch 17 - Intermedia ....
I will start reading about section searching..
Thanks
Indexed Procedure
mohini, May 12, 2005 - 11:45 am UTC
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...
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
mohini, May 12, 2005 - 4:15 pm UTC
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?
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
David Rydzewski, May 13, 2005 - 8:52 am UTC
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
DT, November 17, 2005 - 7:54 pm UTC
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?
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
Phil, February 20, 2006 - 9:02 am UTC
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
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
Phil, February 20, 2006 - 3:55 pm UTC
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
February 21, 2006 - 7:24 am UTC
why would you drop and rebuild?
Index sync
Pawel, February 21, 2006 - 3:28 am UTC
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
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
A reader, March 27, 2006 - 12:29 pm UTC
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.
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 !!
A reader, March 27, 2006 - 4:08 pm UTC
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.
A reader, March 29, 2006 - 9:51 am UTC
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 .?
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.
A reader, May 03, 2006 - 11:46 am UTC
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
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
A reader, May 03, 2006 - 1:36 pm UTC
Thanks.
Oracle Text Error
Laxman Kondal, July 14, 2006 - 3:48 pm UTC
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.
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
Pulakesh Dey, December 13, 2006 - 6:37 am UTC
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.
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
Freek, December 21, 2006 - 11:14 am UTC
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?
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?
Pulakesh Dey, January 02, 2007 - 6:26 am UTC
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%'
Avid Reader, June 28, 2007 - 9:03 pm UTC
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
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%';
A reader, August 15, 2007 - 4:58 pm UTC
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
joaquin gonzalez, December 02, 2008 - 8:47 am UTC
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
December 02, 2008 - 10:47 am UTC
what are all of your nls_settings - client, database
NLS settings (index ignoring diacritical marks)
Joaquin Gonzalez, December 02, 2008 - 11:27 am UTC
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
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
Joaquin Gonzalez, December 03, 2008 - 5:02 am UTC
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
Slavko Brkic, December 10, 2008 - 5:53 am UTC
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', '`-=[];''\,./~!@#$%^&*()_+{}:"|<>?§´¨½¼¾¤£¿©¿®');
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
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
Slavko Brkic, December 10, 2008 - 9:14 am UTC
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
Slavko Brkic, December 11, 2008 - 4:34 am UTC
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.
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
Slavko Brkic, December 11, 2008 - 3:13 pm UTC
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)'
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
taro, February 10, 2009 - 1:38 pm UTC
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,
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
taro, February 17, 2009 - 5:02 am UTC
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
Chris Holt, December 18, 2009 - 1:02 pm UTC
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?
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
Chris Holt, December 18, 2009 - 1:51 pm UTC
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!
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
Rajaram Subramanian, December 21, 2009 - 5:06 am UTC
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?
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
Chris, December 21, 2009 - 9:53 am UTC
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?
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
Rajaram Subramanian, January 04, 2010 - 10:48 am UTC
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
Rajaram Subramaninan, February 23, 2010 - 11:07 am UTC
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
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).