Hi,
 SQL> drop table t;

Table dropped.

SQL> create table t ( data varchar2(50) );

Table created.

SQL> insert into t values ( 'How#Now#Red#Cow' );

1 row created.

SQL> insert into t values ( 'How Now Red Cow' );

1 row created.

SQL> insert into t values ( 'How # Now Red Cow' );

1 row created.

SQL> commit;

Commit complete.

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

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object CTXSYS.CTX_DDL
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> conn sys/sys;
Connected.
SQL> grant dba to TEST;

Grant succeeded.

SQL> conn TEST/TEST;
Connected.
SQL>  exec ctx_ddl.drop_preference('my_lexer');
BEGIN ctx_ddl.drop_preference('my_lexer'); END;

*
ERROR at line 1:
ORA-20000: ConText error:
DRG-10700: preference does not exist: my_lexer
ORA-06512: at "CTXSYS.DRUE", line 122
ORA-06512: at "CTXSYS.CTX_DDL", line 80
ORA-06512: at line 1


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

SQL>  create index t_ind_ctx on t(data) indextype
  2      is ctxsys.context parameters('lexer my_lexer');
 create index t_ind_ctx on t(data) indextype
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: ConText error:
DRG-10503: table TEST.T cannot be indexed because it has no primary key
ORA-06512: at "CTXSYS.DRUE", line 122
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 34
ORA-06512: at line 1



SQL> alter table t add constraints pk_t primary key (data);

Table altered.(Why should i have primary key here ??)


SQL> create index t_ind_ctx on t(data) indextype
  2     is ctxsys.context parameters('lexer my_lexer');
create index t_ind_ctx on t(data) indextype
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: ConText error:
DRG-50704: Net8 listener is not running or cannot start external procedures
ORA-28575: unable to open RPC connection to external procedure agent
ORA-06512: at "CTXSYS.DRUE", line 122
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 34
ORA-06512: at line 1



SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
T_IND_CTX



SQL>  select token_text from DR$T_IND_CTX$I;

no rows selected

SQL>  select * from t where contains( data, 'How' ) > 0;
 select * from t where contains( data, 'How' ) > 0
                                       *
ERROR at line 1:
ORA-20000: ConText error:
DRG-10599: column is not indexed

----You can see above,index is available..but it says not indexed..why ??

Can I have your comments on above please ??

Thanks,
  Regards
    Ak

 

Tom,

What u have said is new and also understood the same.
Now in ur last query when u say contains(data,'#') you get only those rows which has got '#' sign.What about I getting few more rows additionally which do not have hash sign at all.
The difference here I see is that I am using more than one printjoins like

ctx_ddl.create_preference('my_lexer','BASIC_LEXER');
ctx_ddl.set_attribute('my_lexer','printjoins','#');
ctx_ddl.set_attribute('my_lexer','printjoins','_');
ctx_ddl.set_attribute('my_lexer','printjoins',',');

Is this a problem, if yes than how do we take care of search when there could be all these nonalphanumeric characters in a field.



Tks Sushil.

Hi tom,
  I tested again after adding primary key ,i am getting this
error again.
 

SQL> conn TEST/TEST;
Connected.
SQL> desc t
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- --------------------------------
 DATA                                                  NOT NULL VARCHAR2(50)

SQL> drop table t;

Table dropped.

SQL> 
SQL> 
SQL> create table t ( data varchar2(50) primary key);

Table created.

SQL> insert into t values ( 'How#Now#Red#Cow' );

1 row created.

SQL> insert into t values ( 'How Now Red Cow' );

1 row created.

SQL> insert into t values ( 'How # Now Red Cow' );

1 row created.

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

PL/SQL procedure successfully completed.

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

SQL> create index t_ind_ctx on t(data) indextype
  2      is ctxsys.context parameters('lexer my_lexer');
create index t_ind_ctx on t(data) indextype
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: ConText error:
DRG-50704: Net8 listener is not running or cannot start external procedures
ORA-28575: unable to open RPC connection to external procedure agent
ORA-06512: at "CTXSYS.DRUE", line 122
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 34
ORA-06512: at line 1


SQL> select index_name from user_indexes where index_name like 'T_IND_CT%';

INDEX_NAME
------------------------------
T_IND_CTX

SQL> select token_text from DR$T_IND_CTX$I;

no rows selected

SQL>  select * from t where contains( data, 'How' ) 
  2   > 0;
 select * from t where contains( data, 'How' )
                                       *
ERROR at line 1:
ORA-20000: ConText error:
DRG-10599: column is not indexed


SQL> SELECT COUNT(*) FROM T;

 COUNT(*)
---------
        3
please comment.

Ak


 

Tom,

I have a table which contains strings having some special characters. I did the steps as outlined above but still getting error. Please help.

I am getting problem for '&' character, please see at the extreme end.

tester@test8i> create table abc (text varchar2(4000));

Table created.

tester@test8i> insert into abc values('$60,000 to 80,000');

1 row created.

tester@test8i> insert into abc values('Assistant Controller/Comptroller');

1 row created.

tester@test8i> insert into abc values('Communication - Written');

1 row created.

tester@test8i> insert into abc values('Financial Services (Banks and Trusts)');

1 row created.

tester@test8i> set escape \
tester@test8i> insert into abc values('Financial Services (Insurance \& Others)');

1 row created.

tester@test8i> set escape off;
tester@test8i> exec ctx_ddl.drop_preference('my_lexer');

PL/SQL procedure successfully completed.

tester@test8i> 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.

tester@test8i> create index SEARCH_TEXT_IDX on
2 abc(TEXT)
3 indextype is ctxsys.context parameters('lexer my_lexer');

Index created.

tester@test8i> select * from abc where contains(text, '$60,000 to 80,000')>0;

TEXT
----------------------------------------------------------------------------------------------------
$60,000 to 80,000

tester@test8i> select * from abc where contains(text, 'Assistant Controller/Comptroller')>0;

TEXT
----------------------------------------------------------------------------------------------------
Assistant Controller/Comptroller

tester@test8i> select * from abc where contains(text, 'Communication \- Written')>0;

TEXT
----------------------------------------------------------------------------------------------------
Communication - Written

tester@test8i> select * from abc where contains(text, 'Financial Services (Banks and Trusts)')>0;
select * from abc where contains(text, 'Financial Services (Banks and Trusts)')>0
*
ERROR at line 1:
ORA-20000: interMedia Text error:
DRG-50900: text query parser error on line 1, column 38
DRG-50920: part of phrase not itself a phrase or equivalence


tester@test8i> select * from abc where contains(text, '{Financial Services (Insurance & Others)}')>0;
Enter value for others:
old 1: select * from abc where contains(text, '{Financial Services (Insurance & Others)}')>0
new 1: select * from abc where contains(text, '{Financial Services (Insurance )}')>0

no rows selected

Excellent information!

Hi Tom,

I have the following situation. I have a table as follows
CREATE TABLE "INVOICE_AUDIT_DATA_TEST"
(
VENDOR_ID VARCHAR2(25),
VENDOR_NAME VARCHAR2(240),
INVOICE_NUMBER VARCHAR2(50),
INVOICE_ID VARCHAR2(25),
INVOICE_DATE DATE,
INVOICE_AMOUNT VARCHAR2(25)
);

I have created CATALOG indexes on this table in the following fashion:

--Created an index set as follows
BEGIN
ctx_ddl.create_index_set('invoice_audit_set');
ctx_ddl.add_index('invoice_audit_set', 'vendor_id'); ctx_ddl.add_inde('invoice_audit_set', 'invoice_id');
ctx_ddl.add_index('invoice_audit_set', 'invoice_amount');
END;

--Created the catalog indexes based on the above index set
CREATE INDEX VNDNM_TST_SRCH_INDX ON INVOICE_AUDIT_DATA_TEST(VENDOR_NAME)
INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS ('index set invoice_audit_set')
/

CREATE INDEX INVNM_TST_SRCH_INDX ON INVOICE_AUDIT_DATA_TEST(INVOICE_NUMBER)
INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS ('index set invoice_audit_set')
/

CREATE INDEX INVAT_TST_SRCH_INDX ON INVOICE_AUDIT_DATA_TEST(INVOICE_AMOUNT)
INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS ('index set invoice_audit_set')
/

Now, I am running the following query:

select
distinct
a1.vendor_name vendor_name1,
pv.vendor_name vendor_name2,
a1.vendor_id vendor_id1,
pv.vendor_id vendor_id2,
ai.invoice_num,
ai.invoice_id
from
invoice_audit_data_test a1,
vendors pv,
invoices ai
where
pv.vendor_id = ai.vendor_id and
pv.end_date_active is null and
ai.invoice_date between to_date('01-JAN-2003','DD-MON-YYYY') and
to_date('31-JAN-2003','DD-MON-YYYY') and
pv.enabled_flag = 'Y' and
a1.vendor_id != ai.vendor_id and
a1.invoice_amount = ai.invoice_amount and
CATSEARCH(a1.vendor_name,pv.vendor_name,'order by vendor_id' ) > 0

The table "invoices" is the main invoices table from which I need to compare the invoices in the "invoice_audit_data_test table" to find any duplicates, based on the vendor_name column (from "vendors" table).

When I run this query I am getting the error:
"ORA-20000: Oracle Text error: DRG-10849: catsearch does not support functional invocation DRG-10599: column is not indexed"

Can you please tell me what is wrong with the query ? Since I have already indexed the required columns(invoice_audit_data_test) in the join conditions.

Thanks
Prashant.

make the example smaller, I cannot reproduce with:

ops$tkyte@ORA9IR2> select *
  2    from invoice_audit_data_test a1, dual
  3   where CATSEARCH(a1.vendor_name,dual.dummy,'order by vendor_id' ) > 0
  4  /
 
no rows selected
 
 

How can i replace the spaces in a phrase with '|' in a string? I mean using ctx_ddl package or is there any methos to replace all the spaces in the passing phrase to '|' ?

i need to cteate a table of dates.within that the first month of year will be APRIL(APRIL-MAR)NOT JAN-DEC AND my tables contents like this

DAY MONTH_ID QUARTER_ID YEAR
15-04-2005 200501 200501 2005
16-04-2005 200501 200501 2005
17-04-2005 200501 200501 2005
18-04-2005 200501 200501 2005
- --- -----
- --------
17-07-2005 200504 200502 2005
18-07-2005 200504 200502 2005
- --- -----
- --------
17-01-2006 200512 200504 2005
18-01-2006 200512 200504 2005


ie
i want generate this type for 20 yrs(ie 01-jan-2000 to 31-dec-2020)
can u plz help me to solve this/can u give me a piece of code

Hi Tom,

We have a table which contains a COMMENTS field of type
varchar2. The application includes a COMMENTS search
where the user enters 1 or more keywords and the query
is supposed to return records where the COMMENTS match
the keywords. The keywords are the only filter to the
query (i.e. no other fields are used in the WHERE clause).

I am trying to decide which would be better, an index of
type CONTEXT or CTXCAT.

I have been reading the docs but am still unclear on a
couple of things.

1) The doc describes CONTEXT as:
"Use this index to build a text retrieval
application when your text consists of
large coherent documents."
What does "large coherent documents" mean?

2) Since this is an OLTP, I tend to prefer CTXCAT
since DMLs will automatically update the index
(as against manual update in CONTEXT). Is this
a valid reason to use CTXCAT?

Any other thoughts will be greatly appreciated.
Thank you again.

Hi Tom,

Please let me know if you want a small yet complete sample of this problem, or if I should posty a new question.

Our application (a website) uses the catsearch function as an optional "keyword" search along with about a dozen other optional fields.

Typically we construct a query like:

select * From t
where catsearch(keyowrds,'red',null)>0
and field1 = 1
and field2 = 2

But as all of the parameters are optional setting up sub-indexes (and using the structured query parameter) on the keyword domain index is immpracticle.

Do you have any recommendations for this type of "mixed" query? It is interesting to note that (for performance reasons) I generally have to repeat the catsearch function in the where clause!

eg

select * From t
where catsearch(keyowrds,'red',null)>0
and catsearch(keyowrds,'red',null)>0
and field1 = 1
and field2 = 2

It would appear that the CBO doesn't particularly handle these types of queries well.

I raised this question on the Oracle forum but all they had to say was "use the structured query component."

Kind Regards

Hi Tom,

Referring to the following from the Oracle Text manual:
----------------------------------------------------------
>> 3.3.7 Creating a CTXCAT Index
The CTXCAT indextype is well-suited for indexing small text fragments and related information. If created correctly, this type of index can give better structured query performance over a CONTEXT index.
----------------------------------------------------------
What does the "small text fragments" mean? What are the restrictions (types, size limits etc) for creating and using CTXCAT type of index?

In the example below regarding to CONTEXT index it is clear what restrictions applied to the CONTEXT type of index:
----------------------------------------------------
3.3.6.2 Default CONTEXT Index Example
... The text column can be of type CLOB, BLOB, BFILE, VARCHAR2, or CHAR.
----------------------------------------------------

I do believe it must be included into the manual.

Thanks,

onsh76

Tom,
Will it be acceptable to index a VARCHAR2(2000) column with CTXCAT type? Can you give us some examples to clarify "small is relative" situation.

Tnx,

onsh76

Tom:

I have this query where a user enters a keyword and I find all stock with a description that contains that keyword.

SELECT stock_number,description from stock_item
where contains(description,i_keyword) > 0

This will do an exact match. I want to use a soundex function where if a user enters "batteries" or "bateries" or "batery" or "battery" it will find all records with a keyword "battery".

SELECT stock_number,description from stock_item
where contains(soundex(description),i_keyword) > 0

1. Why the query does not retrieve anything and what do i need to do?

2. Is it possible to use a partial word search with intermedia like if a user enters "batt" it will find the recrods with words "battery"?

thank you

I have been looking at the doc for oracle text. Is there any way to use bind variables in the contains and/or catsearch query operators?

I have been looking at the doc for oracle text. Is there any way to use bind variables in the contains and/or catsearch query operators?

Tom:

I have a query that uses contains to search for keywork like query above.

When I update the record (any field) I found that the search afterwards fails to find it? THE DBA updates the intermedia index every night. Is it because of that??
Id there a way around this?

For example, whe i seach for "battery" in a record with description

Screw, 4 - 40 x 1/4" Battery Door

it finds it.

if i update the record, it stops finding it.

Thank you

TOm:

1. Would it be best to sync the index using a "after update trigger" for any record rather than every few mins when no updates may never happen?

2. How an i find out if I can sync the index myself using the developer account? or is it something that only a DBA can use?

thank you

Hi, Tom

I'm trying to figure out a way to sort of emulate a "sync" of a CTXCAT index in an Oracle 9.2.0.3 base (I know, CTXCAT indexes are updated on the fly and don't need sync - that's what I'm trying to avoid.)

Situation is this:

We have a webshop where I use a CTXCAT index to allow customers to search for an item (and hopefully buy it :-)

I have a table with a column (varchar2(4000)) that simply contains a concatenated string of all searchwords relevant to the item-number in question. Also the table contains columns defining which language the searchwords are in and which main-group. These columns are the main reason for using CTXCAT index rather than CONTEXT because all queries will have a structured query part on these columns.

The table has 1.5 million rows and the corresponding dr$index_name$i table has 33 million rows. It works amazingly well - my web search page practically flies :-)

Now for the problem :

The table is loaded with search words using a complex query using lots of "with" subqueries, hierarchial queries, hash joins of 9 different tables and lots more. When I build the table using that query in a CTAS it takes about 2 hours. Building the CTXCAT index afterwards takes another hour.

Each night I do the same query and build a temporary table of searchwords. I compare the temporary table to the original table and do a delete statement, an update statement and an insert statement to update the searchwords.
The CTXCAT index is automatically updated in that process. Works fine if not too many changes have been made in the searchwords.

Now someone discovers that customers don't find the items if they search for 'auto' rather than 'car', so a synonym search word (not a CONTEXT synonym but synonyms in my own tables which are used in the complex query) is created to make 'auto' equal to 'car'.
That one change updates 1 million out of the 1.5 million records concatenating the word 'auto' to the string of searchwords.

That update takes forever because the CTXCAT index is updated for each row with a delete from dr$index_name$i followed by inserts into dr$index_name$i. I estimate 3-4 days based on data from longops after running 2 hours - then I killed the process...

A faster way would have been to drop the CTXCAT index, update the searchword table and re-create the CTXCAT index.
But that would mean an hour or two where customers would be unable to search for items in our webshop - not acceptable :-)


Is it possible to do my delete/update/insert of the searchword table but have the CTXCAT index syncronization be sort of "deferred"? So that I could issue an "alter index index_name rebuild online" after the updates?

Hope you can think of a way :-)


Regards

Kim Berg Hansen

Hi again

Short addendum to my question about "deferring" rebuild of a CTXCAT index.

I just got an idea of disabling the dr$ trigger while I do the updates. It might not be entirely "supported" but it might do the trick?

I'll experiment with the idea. If you have any pros and cons to disabling the trigger I'd be very grateful :-)


Thanks

Kim Berg Hansen

Hi, Tom

Thanks for your reply.

Just a quick summary of what I've discovered for your information:

The trigger disable works, but I cannot do an alter index rebuild online anyway - I get an error if I use the online keyword on a CTXCAT index. Then I find one sentence in the 10g documentation stating that rebuild online is only supported for CONTEXT index :-( (The 9.2 documentation does not state that limitation, but I guess that would be classified as a doc bug :-)

I have tested using CONTEXT index as an alternative, but it simply is not as fast as a CTXCAT (averages 3-6 times slower on the same data). Of course that's not always the case, but for my data a CTXCAT is like tailor made :-)

Your idea of two tables I presume is something along the way of having a synonym pointing to my table, so that I can build an alternate table complete with CTXCAT index and point the synonym to the alternate table after building. That is definitely a possible solution.

So now I have two solutions: One is to accept that perhaps 4-5 times a year searching will be disabled an hour or so for the customers - the other is to allocate an extra 6-10 GB space for the double table solution (space only used 4-5 times a year.)

Anyway - that's not your problem to decide that :-)
Thanks for the hint about two tables.


Regards

Kim Berg Hansen

Is there any point in binding the structured query component of the following style of query:

select count(*) from
some_table_with_ctx_index_with_an_index_set
where
catsearch(keyword,:search_value,:structured_query)>0
/

Of course binding search_values like 'RED' and 'BLUE' makes sense but binding structued compontents like:

'order_date > to_date('01-JAN-2006') and customerId=3'
'customerId=4'
'some_field_in_the_index_set > 7'

etc etc

Is not going to help parsing at all... is it?

Regards
James

This is in response to "binding the structured query component of catsearch"

Tom,

My understanding is that the originating query will be soft parsed (if properly binded) but the query that is dynamically build by CTX will be always hard parsed since the bind variable get's "expanded" on that level for some reason with structured clause of CTXCAT index.

Let's say this is the originating query (good bind -- get's soft parsed):

CATSEARCH (text_column, :s_string, :s_clause) > 0

where binds are as follows:
(:s_string = 'some string')
(:s_clause = 'X = 3')

now CTX does the following with it (not good):

select /*+ INDEX_ASC(i "DR$.............") */ dr$rowid
from
"SR"."DR$...........$I" i where dr$token = :token and
dr$token_type = :ttype and X = 3 order by dr$token ASC,
dr$token_type ASC, X ASC, dr$rowid ASC

Note that CTX "expands" :s_clause into X = 3 causing hard parse if :s_clause is always different.

What's your take on this?

Thank you,
- Vitaliy

Tom,

I am using Oracle version 9.2.0.1.0 and trying to use Text indexes for certain OLTP queries and I observed that I couldn't search for double truncated strings. Below you can find the test case:

SQL> create table text_index_table (description varchar2(100));
SQL> create index TX_TEXT_INDEX_TABLE on text_index_table(description) indextype is ctxsys.ctxcat;
SQL> insert into text_index_table values('Oracleacquired Peoplesoft');
SQL> insert into text_index_table values('NowOracle acquired Siebel');
SQL> insert into text_index_table values('Oraclewill acquire next?');
SQL> commit;

SQL> select * from text_index_table where catsearch(description,'oracle',null) > 0;

no rows selected

SQL> select * from text_index_table where catsearch(description,'oracle%',null) > 0

no rows selected

SQL> select * from text_index_table where catsearch(description,'oracle*',null) > 0

DESCRIPTION
--------------------------------------------------------------------------------
Oracleacquired Peoplesoft
Oraclewill acquire next?

SQL> select * from text_index_table where catsearch(description,'*oracle*',null) > 0

DESCRIPTION
--------------------------------------------------------------------------------
Oracleacquired Peoplesoft
Oraclewill acquire next?

SQL> select * from text_index_table where catsearch(description,'**oracle*',null) > 0

DESCRIPTION
--------------------------------------------------------------------------------
Oracleacquired Peoplesoft
NowOracle acquired Siebel
Oraclewill acquire next?

SQL> drop index tx_text_index_table;

SQL> begin
 ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
 ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
 ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH',1);
 ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', 20);
 ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'TRUE');
 end;

SQL> create index tx_text_index_table on text_index_table(description) indextype is ctxsys.ctxcat
   parameters ('wordlist mywordlist');

SQL> select * from text_index_table where catsearch(description,'*oracle*',null) > 0

DESCRIPTION
--------------------------------------------------------------------------------
Oracleacquired Peoplesoft
Oraclewill acquire next?

Even after enabling prefix/substring index the search doesn't return the row 'NowOracle...' Is there something I am doing wrong? Also I searched the Oracle document for ** as wildcard operator in CATSEARCH but coudn't find any reference. Is this wildcard undocumented feature? If this works should one create prefix/substring index? Please advice.

Thanks 

Tom,

But I am using CTXCAT index type not CONTEXT index and I read CTXCAT is transactional. Anyway I created index after inserting but still the same results.

Thanks

Recursive SQL of CTXCAT and bind variables (query clause):

drop table t;

create table t(
search_string varchar2(40),
query_clause number(15));

begin
ctx_ddl.drop_index_set('t');
end;
/

begin
ctx_ddl.create_index_set('t');
ctx_ddl.add_index ('t', 'query_clause');
end;
/

create index t_ctx01 on
t (search_string)
indextype is ctxsys.ctxcat
parameters ('index set t')
/


insert into t values('search 1 string',1);
insert into t values('search 2 string',2);
insert into t values('search 3 string',3);
insert into t values('search 4 string',4);
insert into t values('search 5 string',5);

commit;

variable x varchar2(200);
exec :x := 'query_clause=4';

alter session set sql_trace=true;

select * from t
where
CATSEARCH ( search_string, 'string', :x ) > 0;

alter session set sql_trace=false;

##
## resulted in the following recursive SQL generated by CTX:
##

select /*+ INDEX_ASC(i "DR$T_CTX0101") */ dr$rowid
from
"SR"."DR$T_CTX01$I" i where dr$token = :token and dr$token_type = :ttype
and QUERY_CLAUSE = 4 order by dr$token ASC, dr$token_type ASC,
QUERY_CLAUSE ASC, dr$rowid ASC

NOTE:
QUERY_CLAUSE = 4 (as expected it used a literal instead of bind variable)

##
## Question:
##

IS there any way to use bind variables for the query clause portion of CTXCAT index search?

Regards,
-Vitaliy

Tom,

I was wondering if you were able to look at the issue I described above.

Thank you,
- Vitaliy

You can use a context to "bind" here, similar to this:

for this simple example, you could just:

ops$tkyte@ORA10GR2> variable x varchar2(200);<b>
ops$tkyte@ORA10GR2> exec :x := 'query_clause=sys_context(''userenv'',''client_info'')';
</b>
PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> alter session set sql_trace=true;

Session altered.

ops$tkyte@ORA10GR2><b>
ops$tkyte@ORA10GR2> exec dbms_application_info.set_client_info(4);
</b>
PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select * from t
  2  where
  3    CATSEARCH ( search_string, 'string', :x ) > 0;

SEARCH_STRING                            QUERY_CLAUSE
---------------------------------------- ------------
search 4 string                                     4
 

Tom,

You never fail to amaze me!

Thank you.

Could the problem that Prashant was having in the thread above be related to the fact that he created an index on vendor_name, invoice_number, and invoice_amount but when he was adding the indexes to the index_set he added the columns vendor_id, invoice_id, and invoice_amount?

Hi, Tom
I have a table, indexed for using Oracle Text:

CREATE TABLE MY_TABLE
(
MY_FIELD VARCHAR(2000) not null
);

exec ctx_ddl.create_preference('MY_LEXER', 'basic_lexer');
exec ctx_ddl.set_attribute('MY_LEXER', 'printjoins', '_');

create index SEARCH_IDX on MY_TABLE(MY_FIELD)
  indextype is ctxsys.context
  parameters ('lexer MY_LEXER stoplist ctxsys.empty_stoplist sync (on  commit)');

And I want to find rows, which contains 'code='.
Certanly, query like this:

select * from my_table where contains(my_field, 'code=')>0

generates following error:

ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 7

How can I search for query operators in text correctly?

ops$tkyte%ORA10GR2> insert into my_table values ( 'hello world' );

1 row created.

ops$tkyte%ORA10GR2> insert into my_table values ( 'this is where the code= something' );

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from my_table where contains( my_field, '{code=}' ) > 0;

MY_FIELD
-------------------------------------------------------------------------------
this is where the code= something

Hi Tom, have looked around your site and can't find what I need, so am tagging along with a related thread.

I have a mostly query online system. It works great. But now the powers that be want to make it historical, eg. they want to ask point in time questions (show me what the claim looked like on date X). I recognize this as affectionately the BOB BARKER lookup (find the row closest to this date but not after). I have the data in "old school" audit tables so getting the answer is possible. I suggested TOTAL RECALL and an upgrade to 11g but the infrastructure team said "sorry no money for 11g research till 2009". Nothing like waiting 18 months to use current features...

Anyway, my users want to do two kinds of queries: 1) basic key lookups with a date, and 2) description searches with a date.

I can get a TRULY MAGICAL PLAN out of Oracle for the single key lookups with a date, but not for the description search lookups. I figured out I can make the system faster overall anyway just by utilizing the CTXCAT index type (this system was built in an early varsion of 8 but is now on 9i), but the CTXCAT index used in description searches won't give me the wonderful FIRST ROW/INDEX RANGE SCAN(MIN/MAX) plans I get with the simple key lookups that is so awesome.

here is a test case that I think will clarify for you. I am hoping you will do one of two things:

1) you will show me how to rewrite my historical description search in a way to do the awesome plan or

2) tell me I am just asking way too much for now and take the good performance the CTXCAT index has to offer and be happy.

SQL> set echo on
SQL> set linesize 999
SQL> set pagesize 999
SQL> set trimspool on
SQL> set trimout on
SQL> 
SQL> --
SQL> -- clean up
SQL> --
SQL> set autotrace off
SQL> drop table clm_a
  2  /

Table dropped.

SQL> begin
  2   ctx_ddl.drop_index_set ('clm_a_desc_search');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- create me a test table to check out query plan ideas
SQL> -- load it with 10,000 rows and try to distribute the data
SQL> -- notice this is an "AUDIT" table (old school)
SQL> -- which means it has a row every time the main row changed
SQL> --
SQL> create table clm_a
  2  nologging
  3  as
  4  select mod(i,1000) clm_id
  5      ,lpad(i,255,i) clm_desc
  6      ,lpad(i,4000,i) clm_this
  7      ,lpad(i,4000,i) clm_that
  8      ,lpad(i,4000,i) clm_andtheotherthing
  9      ,sysdate-i a_date
 10  from (
 11       select rownum i
 12       from dual
 13       connect by level <= 10000
 14     )
 15  /

Table created.

SQL> desc clm_a
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 CLM_ID                                       NUMBER
 CLM_DESC                                     VARCHAR2(255)
 CLM_THIS                                     VARCHAR2(4000)
 CLM_THAT                                     VARCHAR2(4000)
 CLM_ANDTHEOTHERTHING                         VARCHAR2(4000)
 A_DATE                                       DATE

SQL> 
SQL> --
SQL> -- make a unique index on what constitutes a unique row
SQL> --
SQL> create unique index clm_a_uk1 on clm_a(clm_id,a_date)
  2  /

Index created.

SQL> alter table clm_a add constraint clm_a_uk1 unique (clm_id,a_date)
  2  /

Table altered.

SQL> 
SQL> --
SQL> -- pick one of the keys for us to find with a description search
SQL> -- we will in the end do two kinds of searches (clm_id, vs. clm_desc)
SQL> --
SQL> update clm_a set clm_desc = 'bla bla bla fast foods bla bla bal'
  2  where clm_id = 1
  3  /

10 rows updated.

SQL> 
SQL> commit
  2  /

Commit complete.

SQL> 
SQL> --
SQL> -- show us some data so we see what we are looking at
SQL> -- notice for this "key" we have a nice distrubtion of dates across time
SQL> -- this is good for testing historical queries
SQL> --
SQL> select clm_id,a_date,clm_desc
  2  from clm_a
  3  where clm_id = 1
  4  order by a_date
  5  /

    CLM_ID A_DATE               CLM_DESC
---------- -------------------- ------------------------------
         1 16-jun-1983 21:31:30 bla bla bla fast foods bla bla
         1 12-mar-1986 21:31:30 bla bla bla fast foods bla bla
         1 06-dec-1988 21:31:30 bla bla bla fast foods bla bla
         1 02-sep-1991 21:31:30 bla bla bla fast foods bla bla
         1 29-may-1994 21:31:30 bla bla bla fast foods bla bla
         1 22-feb-1997 21:31:30 bla bla bla fast foods bla bla
         1 19-nov-1999 21:31:30 bla bla bla fast foods bla bla
         1 15-aug-2002 21:31:30 bla bla bla fast foods bla bla
         1 11-may-2005 21:31:30 bla bla bla fast foods bla bla
         1 05-feb-2008 21:31:30 bla bla bla fast foods bla bla

10 rows selected.

SQL> 
SQL> --
SQL> -- build an index set to augment an upcomming CTXCAT index
SQL> -- am wondering if the order of columns matters here?
SQL> --
SQL> begin
  2   ctx_ddl.create_index_set ('clm_a_desc_search');
  3   ctx_ddl.add_index ('clm_a_desc_search','a_date');
  4   ctx_ddl.add_index ('clm_a_desc_search','clm_id');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- create a CTXCAT index for searching our descriptions
SQL> -- and augment it with the clm_id and a_date columns
SQL> --
SQL> create index clm_a_desc_search on clm_a(clm_desc)
  2  indextype is CTXSYS.CTXCAT
  3  parameters ('index set clm_a_desc_search')
  4  /

Index created.

SQL> 
SQL> --
SQL> -- compute stats on the table and its indexes
SQL> --
SQL> exec dbms_stats.gather_table_stats(user,'CLM_A',cascade=>true)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- do some formatting to make looking at results easier
SQL> --
SQL> col clm_desc format a30 trunc
SQL> col clm_this format a10 trunc
SQL> col clm_that format a10 trunc
SQL> col clm_andtheotherthing format a10 trunc
SQL> 
SQL> --
SQL> -- set up to get plans and timings
SQL> --
SQL> set autotrace on
SQL> 
SQL> --
SQL> -- do the single key historical query
SQL> -- this is known as the BOB BARKER lookup
SQL> -- notice the query plan is almost magical
SQL> -- in its ability to go straigt the the BOB BARKER row
SQL> --
SQL> select *
  2  from clm_a
  3  where (clm_id,a_date) = (
  4            select clm_id,max(a_date)
  5            from clm_a
  6            where clm_id = 1
  7            and a_date <= sysdate-1000
  8            group by clm_id
  9          )
 10  /

    CLM_ID CLM_DESC                       CLM_THIS   CLM_THAT   CLM_ANDTHE A_DATE
---------- ------------------------------ ---------- ---------- ---------- --------------------
         1 bla bla bla fast foods bla bla 1001100110 1001100110 1001100110 11-may-2005 21:31:30


Execution Plan
----------------------------------------------------------
Plan hash value: 374544856

--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |     1 | 12270 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | CLM_A     |     1 | 12270 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN            | CLM_A_UK1 |     1 |       |     1   (0)| 00:00:01 |
|   3 |    SORT GROUP BY NOSORT        |           |     1 |    12 |     2   (0)| 00:00:01 |
|   4 |     FIRST ROW                  |           |     9 |   108 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)| CLM_A_UK1 |     9 |   108 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(("CLM_ID","A_DATE")= (SELECT "CLM_ID",MAX("A_DATE") FROM "CLM_A"
              "CLM_A" WHERE "A_DATE"<=SYSDATE@!-1000 AND "CLM_ID"=1 GROUP BY "CLM_ID"))
   5 - access("CLM_ID"=1 AND "A_DATE"<=SYSDATE@!-1000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
      12806  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> --
SQL> -- now do a clm_desc historical query
SQL> -- we can get any number of "keys" if you will
SQL> -- but for each we only want one row
SQL> -- in this test we will only get one "key" and
SQL> -- will again have to find the BOB BARKER row for it
SQL> -- but this time no magical BOB BARKER query plan
SQL> -- just the plane jane CTXCAT lookup (still fast and usable but...)
SQL> --
SQL> select *
  2  from clm_a
  3  where (clm_id,a_date) in (
  4      select clm_id,max(a_date)
  5      from clm_a
  6      where CATSEARCH (clm_desc,'fast foods','a_date <= sysdate-1000') > 0
  7      group by clm_id
  8           )
  9  /

    CLM_ID CLM_DESC                       CLM_THIS   CLM_THAT   CLM_ANDTHE A_DATE
---------- ------------------------------ ---------- ---------- ---------- --------------------
         1 bla bla bla fast foods bla bla 1001100110 1001100110 1001100110 11-may-2005 21:31:30


Execution Plan
----------------------------------------------------------
Plan hash value: 4109132636

-----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |     1 | 12292 |   405   (1)| 00:00:05 |
|   1 |  NESTED LOOPS                   |                   |       |       |            |          |
|   2 |   NESTED LOOPS                  |                   |     1 | 12292 |   405   (1)| 00:00:05 |
|   3 |    VIEW                         | VW_NSO_1          |   401 |  8822 |     3  (34)| 00:00:01 |
|   4 |     HASH GROUP BY               |                   |   401 |   109K|     3  (34)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| CLM_A             |   500 |   136K|     2   (0)| 00:00:01 |
|*  6 |       DOMAIN INDEX              | CLM_A_DESC_SEARCH |       |       |            |          |
|*  7 |    INDEX UNIQUE SCAN            | CLM_A_UK1         |     1 |       |     0   (0)| 00:00:01 |
|   8 |   TABLE ACCESS BY INDEX ROWID   | CLM_A             |     1 | 12270 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("CTXSYS"."CATSEARCH"("CLM_DESC",'fast foods','a_date <= sysdate-1000')>0)
   7 - access("CLM_ID"="CLM_ID" AND "A_DATE"="MAX(A_DATE)")


Statistics
----------------------------------------------------------
        206  recursive calls
          0  db block gets
        504  consistent gets
          1  physical reads
          0  redo size
      12806  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> spool off

I think I understand how the single key lookup is able to go straight to the BOB BARKER row. A brilliant piece of optimization if you ask me.

But is it possiblee in searches where we have result with multiple "keys", and get Oracle to go straight to the BOB BARKER row for each key? I don't think it is possible yet although was hoping for some kind of skip scan business or possible index compression might turn the trick but I also figure I am blowing smoke too.

Thanks, Kevin

thanks so much for an amazlingly fast answer. This is exactly what i wanted to know. Now I can forgo various testing and research avenues and instead concentrate on the basics I need to get right.

Kevin

Tom,

When I searched on 'C++', all the rows that have 'C', 'C==', 'C&&', etc. were returned.
How can I find just the rows that have 'C++'?


Thanks

Tom,

I found the answer right here in the article by do the following to take care of the '+' issue:

EXEC ctx_ddl.set_attribute('my_lexer','printjoins','+');

and
EXEC ctx_ddl.set_attribute('my_lexer','printjoins','+_');
to cover the '+' and '_'.

My application needs to handle all characters like these. What other characters need to be included in the above statement? Thanks.

Column renames in views cause problem with catsearch.  Short test case follows.  Am I doing something dumb?  Is this just the way it is?  If so, the how do I reference the columns in the view I am querying against?

SQL> create table dept (dept_id number not null,dept_name varchar2(30),dept_desc varchar2(4000))
  2  /

Table created.

SQL> 
SQL> create or replace view dept_vw as
  2  select dept_id
  3        ,dept_name d_name  -- notice rename of column
  4        ,dept_desc
  5  from dept
  6  /

View created.

SQL> 
SQL> set linesize 70
SQL> desc dept
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 DEPT_ID                             NOT NULL NUMBER
 DEPT_NAME                                    VARCHAR2(30)
 DEPT_DESC                                    VARCHAR2(4000)

SQL> desc dept_vw
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 DEPT_ID                             NOT NULL NUMBER
 D_NAME                                       VARCHAR2(30)
 DEPT_DESC                                    VARCHAR2(4000)

SQL> 
SQL> exec  ctx_ddl.create_index_set('test1');

PL/SQL procedure successfully completed.

SQL> exec  ctx_ddl.add_index       ('test1', 'dept_name');

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> CREATE INDEX dept_ctx_i1 ON dept (dept_desc)
  2    INDEXTYPE IS ctxsys.CTXCAT
  3    PARAMETERS ('INDEX SET test1')
  4  /

Index created.

SQL> 
SQL> 
SQL> insert into dept values (1,'ABC','Sales');

1 row created.

SQL> insert into dept values (2,'XYZ','Sales');

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> select *
  2  from dept
  3  where catsearch(dept_desc,'Sales','dept_name = ''ABC''') > 0
  4  /

   DEPT_ID DEPT_NAME                      DEPT_DESC
---------- ------------------------------ ----------
         1 ABC                            Sales

SQL> 
SQL> 
SQL> select *
  2  from dept_vw
  3  where catsearch(dept_desc,'Sales','d_name = ''ABC''') > 0
  4  /
select *
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-10844: index cannot execute this structured predicate efficiently
DRG-10845: column D_NAME is not indexed

--
-- this is where if falls apart for me
-- d_name is a valid column in the view
-- dept_name is the original column in the table that is indexed
-- d_name is just a rename of dept_name
-- is text search not able to grasp the rename of dept_name to d_name?
--


SQL> select *
  2  from dept_vw
  3  where catsearch(dept_desc,'Sales','dept_name = ''ABC''') > 0
  4  /

   DEPT_ID D_NAME                         DEPT_DESC
---------- ------------------------------ ----------
         1 ABC                            Sales

SQL> 
--
-- yes this works but how intuitive is it
-- to have to put in a query a column name that does not
-- exist in any of the objects the query is based on
-- for dept_name is nowhere to be found in dept_vw
--
-- what am I doing wrong?
-- how can I reference d_name instead of dept_name?
--
-- this is killing my development workload because it means
-- I can not implement CTXCAT indexes in a transparent manner
-- can you help me?
--

I feel stupid for not seeing this when I looked. It is amazing at times how one can read the manuals without opening the eyes.

Thanks, once gain you provide a conclusive answer from which I can move forward.

Kevin

Hi Tom,

Is there a way to use LEXER parameters to use for special characters like )([],

I want to do a search like this

select * from table1
where contains (col_name, '%(940TEXT%') > 0;

I included ()[] in lexer. Still gives me an error.
Any easy way to handle other than using { or \
Kindly suggest.

Thanks

ops$tkyte%ORA11GR2> create table t ( data varchar2(50) );

Table created.

ops$tkyte%ORA11GR2> insert into t values ( 'hello (940TEXT stuff...world' );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 'hello 940TEXT stuff...world' );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( 'How Now Red Cow' );

1 row created.

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

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 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%ORA11GR2> create index t_ind_ctx on t(data) indextype
  2  is ctxsys.context parameters('lexer my_lexer');

Index created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from t
  2  where contains (data, '{%(940TEXT%}') > 0;

DATA
--------------------------------------------------
hello (940TEXT stuff...world

Hi Tom,

Is there a way to use LEXER parameters to use for special characters like )([],

I want to do a search like this

select * from table1
where contains (col_name, '%(940TEXT%') > 0;

I included ()[] in lexer. Still gives me an error.
Any easy way to handle other than using { or \
Kindly suggest.

Thanks