Why not native Oracle Text functionality?
Jack Wells, March 26, 2005 - 1:14 pm UTC
Whew! Very nice and elegant. I know I can use this somewhere (just need to find a place).
It is surprising, though, that Oracle Text doesn't already have a native "preference function" to use as an indextype which does a "string to words" conversion. Seems so powerful and generic at the same time. Why don't you submit this to Oracle TEXT team so they can include it in the next release!
Thanks again for all you do,
Jack
Why?
A reader, March 26, 2005 - 6:35 pm UTC
select * from t where contains( col1, '{36IPZD750776}' ) > 0
and (col1 like '36IPZD750776%');
I dont understand the second predicate above. The first one says look for the string anywhere in the column. The second one says look for columns that start with the string. Doesnt that contradict each other?
March 26, 2005 - 6:42 pm UTC
contradict?
no...
we are using the text index to find the rows that contain the string 36IPZD750776.
we use like to make sure it meets the specification the end user supplied.
if it contradicted -- there would be zero rows returned, but there were rows returned
A reader, March 26, 2005 - 9:01 pm UTC
I still dont get it. That query will return rows only for records where col1 starts with 36IPZD750776 (the LIKE ensures that). But the user specified anything that contains the string 36IPZD750776. The contains already does that. So why is the LIKE needed?
Why not use a builtin functionality?
Ofir Manor, March 27, 2005 - 3:40 am UTC
Hi Tom,
I wonder why you hand-code a functionality that is already supplied? If you want to make '%xxx%' queries faster, all you need to do is ask the text index to prepare for it, by indexing all the substrings as well. There is no need to manually write a procedural code to implement this feature... Did I miss anything?
begin
ctx_ddl.create_preference('SUBSTRING_PREF',
'BASIC_WORDLIST');
ctx_ddl.set_attribute('SUBSTRING_PREF',
'SUBSTRING_INDEX','TRUE');
end;
drop index search_idx;
create index search_idx on T(col1)
indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M');
..
Then this query will do:
select * from t where contains( col1,'%750776%')>0
There are some more details and preference attributes to play with here, plus an example:
</code>
http://download-west.oracle.com/docs/cd/B14117_01/text.101/b10730/cdatadic.htm#sthref750 <code>
How about that?
March 27, 2005 - 10:07 am UTC
excellent. What they've done there is slightly different than what I suggested. They break the string into two pieces:
ops$tkyte@ORA9IR2> select * from DR$SEARCH_IDX$P order by length(pat_part1);
PAT_PART1 PAT_PART2
--------------- ---------------
36IPZD7507761NJ
3 6IPZD7507761NJ
36 IPZD7507761NJ
36I PZD7507761NJ
36IP ZD7507761NJ
36IPZ D7507761NJ
36IPZD 7507761NJ
36IPZD7 507761NJ
36IPZD75 07761NJ
36IPZD750 7761NJ
36IPZD7507 761NJ
36IPZD75077 61NJ
36IPZD750776 1NJ
13 rows selected.
and then query:
SELECT LTRIM(PAT_PART1||PAT_PART2) FROM "OPS$TKYTE"."DR$SEARCH_IDX$P" WHERE PAT_PART2 LIKE :lkexpr
when there is a leading %
sweet, I did not know that -- or (I too learn something new about Oracle every day -- and probably forget two things I used to know).
Thanks very much for the followup, that looks very promising.
Thanks
Zia Hydari, March 27, 2005 - 6:07 pm UTC
I appreciate the response and the follow-up.
Thanks
Zia Hydari
hydari _at_ mit.edu
I am not a database developer but this piece forms a part of the bigger problem that I am trying to solve. I hope to get a better buy-in now on my overall solution.
A reader, March 27, 2005 - 6:40 pm UTC
I still dont get it. That query will return rows only for records where col1 starts with 36IPZD750776 (the LIKE ensures that). But the user specified anything that contains the string 36IPZD750776. The contains already does that. So why is the LIKE needed?
March 27, 2005 - 6:47 pm UTC
No, the original poster said:
36IPZD750776%
%750776%
36%7507761NJ
I wrote the contains, the contains I wrote did not have the leading edge check. the LIKE did that for me.
very interesting thread
Doug, March 27, 2005 - 11:10 pm UTC
This is very interesting.. I am going to spend some time on this. Can I go down a slight sidetrack and ask how these ctxsys indexes work under the covers?
March 28, 2005 - 7:31 am UTC
alter session set sql_trace=true;
select * from t where contains(...) > 0;
edit trace file.....
in short, they work like an inverted list index. the text is parsed and stored with a blob per word (the word could be repeated over and over in the index over time).
When you say "where contains(col,'text') > 0", it takes the text and queries this index table with the keyword/blob, the blob points to the documents of interest.
It is sort of like a bitmap index.
most useful stuff
Matthias Rogel, March 28, 2005 - 3:35 am UTC
*****
Re:
Jagjeet Singh, March 28, 2005 - 7:17 am UTC
Sir,
I just want to know Why this apprach is not good.
Because you did not suggested it.
The only problem with index scanning is.. if %
is at the leading character.
We can just added an extra character before the value.
-- put this logic in trigger .. :new.col := 'X'||:new.col
and on query
Select * from tab where col = 'X'||%750776%' ;
Thanks,
March 28, 2005 - 8:02 am UTC
and what would that accomplish exactly?
everything starts with X -- all 100,000,000 records. like 'X%....' would scan them all.
How to search this string
satya, March 28, 2005 - 7:45 am UTC
Hello Tom,
How do you solve this problem .
I have create a function which will except two param and return varchar2 field
First Param is String and second param is Keyword.
Let say The following patters strings
A) XYZ:123456;ABC:99999
B) ABC:99999;XYZ:123456;
C) ABC:99999;XYZ:123456
D) ABC:99999;
E) ABC:99999
Keywords are the following may be in the following order
i) XYZ
ii) ABC
The user can pass the following combinations
1) (A,i) or (B,i) REsult is 123456
2) (A,ii) or (B,ii) or (c,ii) or (D,ii) or (E,ii) => 99999
I use the INSTR key word get the result but the function is returning value only for string B and D
Could you please suggest me something
Thanks
Satya
March 28, 2005 - 8:03 am UTC
not understanding what (A,i) or (B,i) means.
create table
insert into table
your attempted query
better explanation of what you mean...
then we can take a look.
here is again
satya, March 28, 2005 - 8:30 am UTC
Hello Tom,
How do you solve this problem .
I have to create a function which will accept two param and return varchar2 field(keyword)
First Param is String and second param is Keyword.
Let say The following patters strings
A) XYZ:123456;ABC:99999
B) ABC:99999;XYZ:123456;
C) ABC:99999;XYZ:123456
D) ABC:99999;
E) ABC:99999
Keywords are the following may be in the following order
i) XYZ
ii) ABC
The user can pass the following combinations
1) (A,i) or (B,i) REsult is 123456
2) (A,ii) or (B,ii) or (c,ii) or (D,ii) or (E,ii) => 99999
Where A,B,C,D,E are strings that is "XYZ:123456;ABC:99999"
Let us say the function is mykeyword(mystring varchar2, mykey varchar2) return varchar2;
SQL> select mykeyword('XYZ:123456;ABC:99999','ABC') from dual ; Expected answer will be 99999
SQL> select mykeyword('ABC:99999;XYZ:123456;','ABC') from dual ; Expected answer will be 99999
SQL> select mykeyword('ABC:99999','ABC') from dual ; Expected answer will be 99999
I use the INSTR key word get the result but the function is returning value only for string B and D
Could you please suggest me something
Thanks
Satya
March 28, 2005 - 9:11 am UTC
ops$tkyte@ORA9IR2> variable pat varchar2(5)
ops$tkyte@ORA9IR2> exec :pat := 'ABC'
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select x, substr( a, 1, instr(a,';')-1 )
2 from (
3 select x, substr( x, instr( x, :pat || ':' )+4 ) || ';' a
4 from t
5 where instr(x,:pat||':') > 0
6 )
7 /
X SUBSTR(A,1,INSTR(A,';')-1)
------------------------- --------------------------
XYZ:123456;ABC:99999 99999
ABC:99999;XYZ:123456; 99999
ABC:99999;XYZ:123456 99999
ABC:99999; 99999
ABC:99999 99999
ops$tkyte@ORA9IR2> exec :pat := 'XYZ';
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> /
X SUBSTR(A,1,INSTR(A,';')-1)
------------------------- --------------------------
XYZ:123456;ABC:99999 123456
ABC:99999;XYZ:123456; 123456
ABC:99999;XYZ:123456 123456
normalize?
Jon, March 28, 2005 - 8:33 am UTC
> 36IPZD7507761NJ, 3984399384
> 36IPZD7507762NJ, 3984399384
> 37IPZD7507761NJ, 3984388384
> 36IDDZ7507768NJ, 3984397384
>
> 36IPZD750776%
> %750776%
> 36%7507761NJ
Shouldn't col1 really be about 4 columns? From the examples, it looks like the first two numbers mean something. Break that into a column. Then the next four seem to mean something else; another column. 7507761 looks like a part number; third column. NJ is the state; fourth column. Most column values are probably low in cardinality too so bitmap indexes would work well here.
Once you have it in a more normalized structure, then you can use any tool to query the data in an efficient manner.
Adding more details ...
Zia Hydari, March 28, 2005 - 8:38 am UTC
- There are NO create/delete/update operations during the 12 hour period when search needs to perform optimally.
- There would be a nightly feed; my guesstimate is that we will have about 4 hours for any processing of the data (building the index etc).
- Leading and trailing wild-card searches would make the highest percentage of searches with embedded wild-card expected to form a small percentage (<10%) of the total number of searches.
Is any tweaking possible based on this additional info?
Zia Hydari
hydari _at_ mit.edu
March 28, 2005 - 9:12 am UTC
using the substr index with a simple contains would be the approach to benchmark.
if the length varies
s, March 28, 2005 - 9:56 am UTC
Hello Tom,
How do you solve this problem .
I have to create a function which will accept two param and return varchar2
field(keyword)
First Param is String and second param is Keyword.
Let say The following patters strings
A) XYZ:123456;ABC:99999
B) ABC:99999;XYZ:123456;
C) ABC:99999;XYZ:123456
D) ABC:99999;
E) ABC:99999
Keywords are the following may be in the following order
i) XYZ
ii) ABC
The user can pass the following combinations
1) (A,i) or (B,i) REsult is 123456
2) (A,ii) or (B,ii) or (c,ii) or (D,ii) or (E,ii) => 99999
Where A,B,C,D,E are strings that is "XYZ:123456;ABC:99999"
Let us say the function is mykeyword(mystring varchar2, mykey varchar2)
return varchar2;
SQL> select mykeyword('XYZ:123456;ABC:99999','ABC') from dual ; Expected
answer will be 99999
SQL> select mykeyword('ABC:99999;XYZ:123456;','ABC') from dual ; Expected
answer will be 99999
SQL> select mykeyword('ABC:99999','ABC') from dual ; Expected answer will
be 99999
I use the INSTR key word get the result but the function is returning value only
for string B and D
Could you please suggest me something
Thanks
Satya
Followup:
ops$tkyte@ORA9IR2> variable pat varchar2(5)
ops$tkyte@ORA9IR2> exec :pat := 'ABC'
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select x, substr( a, 1, instr(a,';')-1 )
2 from (
3 select x, substr( x, instr( x, :pat || ':' )+4 ) || ';' a
4 from t
5 where instr(x,:pat||':') > 0
6 )
7 /
X SUBSTR(A,1,INSTR(A,';')-1)
------------------------- --------------------------
XYZ:123456;ABC:99999 99999
ABC:99999;XYZ:123456; 99999
ABC:99999;XYZ:123456 99999
ABC:99999; 99999
ABC:99999 99999
ops$tkyte@ORA9IR2> exec :pat := 'XYZ';
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> /
X SUBSTR(A,1,INSTR(A,';')-1)
------------------------- --------------------------
XYZ:123456;ABC:99999 123456
ABC:99999;XYZ:123456; 123456
ABC:99999;XYZ:123456 123456
Thanks Tom
But Actually the values XYZ or not fixed lenght
that is some time it XYZXYZ or XYZXY or XYZ
so the string will be
ABC:99999;XYZXYZ:123456
ABC:99999;XYZX:123456
How do we solve in this case
Thanks again
March 28, 2005 - 10:52 am UTC
use length(:pat)+1 instead of 4 then?
string search
Rahul, June 10, 2005 - 2:53 am UTC
Hi Tom,
Its never the case that you search for a topic and dont learn something new each time. This site is like the Bible for all the DBA's. I read this post and few other but could not find some thing specific to solve this problem.
How do i search for a string in a schema. Mean how do i get the table and column details containing a particular string. Is their any utility availbale?
Thanks,
Rahul.
June 10, 2005 - 8:35 am UTC
there is no "database grep" like that -- it is highly unusual to ask to search "every table and every column looking for a string" in structured information.
but you can always automate things:
create or replace procedure search_schema( p_string in varchar2 )
authid current_user
as
l_cols long;
l_where long;
l_cursor sys_refcursor;
l_cname varchar2(4000);
begin
dbms_application_info.set_client_info( '%' || p_string || '%' );
for x in ( select table_name from user_tables )
loop
l_cols := 'case when 1=0 then ''x'' ';
l_where := ' where ( 1=0 ';
for y in ( select '"' || column_name || '"' column_name
from user_tab_columns
where table_name = upper(x.table_name)
and (data_type in ( 'CHAR', 'DATE', 'FLOAT', 'NCHAR', 'NUMBER', 'NVARCHAR2', 'VARCHAR2' )
or data_type like 'INTERVAL%' or data_type like 'TIMESTAMP%' )
)
loop
l_cols := l_cols || ' when ' || y.column_name ||
' like sys_context(''userenv'',''client_info'') then ' ||
' ''' || y.column_name || '''';
l_where := l_where || ' or ' || y.column_name || ' like sys_context(''userenv'',''client_info'') ';
end loop;
open l_cursor for 'select ' || l_cols || 'else null end cname from ' ||
x.table_name || l_where || ') and rownum=1';
fetch l_cursor into l_cname;
if ( l_cursor%found )
then
dbms_output.put_line( x.table_name || ' ' || l_cname );
end if;
close l_cursor;
end loop;
end;
/
(might not be perfect, hacked together from other bits of code)
Thanks Tom
rahul, June 13, 2005 - 12:36 am UTC
Hi Tom,
Its impossible to belive that any problem can hold you for a long time. I dont remember any time when i have asked for a solution and didn't get one.
Just one doubt could we have used Oracles "ultra search".
I have not read anything about it but the name suggest it can help for search. Just a thought.
Thanks,
Rahul.
Searching a string in a Database schema
Abir Bhattacharya, June 17, 2005 - 3:01 am UTC
Hi Tom
This is in regards to the procedure you had supplied (search_schema) using sys_refcursor . I have got a similar requirement but my DB happens to be in Oracle 8i which is not supported in 8i. I tried using REF CURSOR by defining a type in a separate package and trying implement that in the stored proc itself . even if the proc do compile up but I am unable to get the desired result.
Code Snippet :
CREATE OR REPLACE PACKAGE Types AS
TYPE cursor_type IS REF CURSOR;
END Types;
CREATE OR REPLACE procedure search_schema( p_string in varchar2)
authid current_user
as
l_cols long;
l_where long;
/*l_cursor sys_refcursor;*/
l_cursor Types.cursor_type;
l_cname varchar2(4000);
begin
dbms_application_info.set_client_info( '%' || p_string || '%' );
for x in ( select table_name from user_tables )
loop
l_cols := 'case when 1=0 then ''x'' ';
l_where := ' where ( 1=0 ';
for y in ( select '"' || column_name || '"' column_name
from user_tab_columns
where table_name = upper(x.table_name)
and (data_type in ( 'CHAR', 'DATE', 'FLOAT', 'NCHAR','NUMBER', 'NVARCHAR2', 'VARCHAR2' )
or data_type like 'INTERVAL%' or data_type like'TIMESTAMP%' )
)
loop
l_cols := l_cols || ' when ' || y.column_name ||
' like sys_context(''userenv'',''client_info'') then ' ||
' ''' || y.column_name || '''';
l_where := l_where || ' or ' || y.column_name || ' like
sys_context(''userenv'',''client_info'') ';
end loop;
open l_cursor for 'select ' || l_cols || 'else null end cname from ' || x.table_name || l_where || ') and rownum=1';
fetch l_cursor into l_cname;
if ( l_cursor%found )
then
dbms_output.put_line( x.table_name || ' ' || l_cname );
end if;
close l_cursor;
end loop;
end;
June 17, 2005 - 2:49 pm UTC
SQL> @test
Package created.
Procedure created.
SQL> set serveroutput on size 1000000
SQL> exec search_schema( 'KING' )
EMP "ENAME"
PL/SQL procedure successfully completed.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.2.1 Production
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
it works?
memory for wordlist
mohini, September 06, 2005 - 5:02 pm UTC
9i (Release 2)
create index search_idx on T(col1)
indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M');
How does one come up with the memory part of this parameter..
didn't find any info in the docs.
Thanks
Could you please help me
sat, September 12, 2005 - 12:04 pm UTC
Could you help me how to do this
Assume that a column conmtaing the following data
1) XYZ:123;XYZ:345;
function should return ABC key word is XYZ
2) XYZ:123;ABC:789;
keyword's are XYZ ,ABC (valid keyword)
function should return error if more than KEY word found it is ERROR
error message -> more than one key word found
3) XYZ:123;A12:789;
keyword's are XYZ and invalid keyword is A12
function should return error because A12 is not valid KEY word
I have to insert these errors in error table . 2 and 3 rd conditions are handle this way
if keyword in ('XYZ','ABC') then
if morethan onekey word is error
else
return the value ex: condition 1
end if
else
error message A12 is not valid key word.
end if;
September 12, 2005 - 2:23 pm UTC
didn't get the ABC comment in #1, ABC didn't come into play.
you'll have to parse the string and implement the logic. this is just a procedural thing you need to write.
How to search for rows that have % stored in a column
A reader, October 04, 2005 - 11:34 pm UTC
Tom,
Let say you have a table with following data.
create table t (c varchar2(10));
insert into t values ('Test%Row4');
insert into t values ('Test%Row2');
insert into t values ('TestRow3');
insert into t values ('TestRow2');
insert into t values ('TestRow1');
How can I write a select statement using "like", to get the rows that have "%" stored in the column ?
Thank you so much in advance
October 05, 2005 - 7:01 am UTC
ops$tkyte@ORA9IR2> select * from t where c like '%\%%' escape '\';
C
----------
Test%Row4
Test%Row2
escape 'any character', set up an escape character for your like clause.
How to search for rows that have % stored in a column
A reader, October 04, 2005 - 11:35 pm UTC
Tom,
Let say you have a table with following data.
create table t (c varchar2(10));
insert into t values ('Test%Row4');
insert into t values ('Test%Row2');
insert into t values ('TestRow3');
insert into t values ('TestRow2');
insert into t values ('TestRow1');
How can I write a select statement using "like", to get the rows that have "%" stored in the column ?
Thank you so much in advance
Intersect two clobs
SH, October 30, 2005 - 9:02 pm UTC
Reader:
you can use the below after indexing the field using Oracle Text: select * from t where CONTAINS(c,'%Test\%%')> 0 escape '\';
select '\&1' from dual;
'&
--
&1
====================================
Hi Tom,
Excellent site!
I have the following structure:
create table t1 (t1_id number, t1_seqnos clob);
create table t2 (n number);
insert into t2 values(453);
insert into t2 values(233214);
insert into t2 values(1324);
insert into t2 values(3);
insert into t1 values (1 ,',453,4999,233214,5555,0,99,3,');
insert into t1 values (2,',233214,134,');
insert into t1 values (3,',2,4,5,7,8,245,56,3465,');
insert into t1 values (4,',4444,7636,32,5,6,8,');
select * from t1;
T1_ID T1_SEQNOS
---------------- ------------------------------------------------------------------------
1 ,453,4999,233214,5555,0,99,3,
2 ,233214,134,
3 ,2,4,5,7,8,245,56,3465,
4 ,4444,7636,32,5,6,8,
select * from t2; --sqlstr provided
N
----------------
453
233214
1324
3
Please let know whats the BEST way to:
update T1
set T1_SEQNOS = (comma separated set of numbers which are present in T1_SEQNOS and also present in sqlstr provided)
where T1_ID=&id;
eg. it should update T1.T1_SEQNOS = ',453,233214,3,' where T1_ID=1
I used dynamic sql to get the seqnos from T2 in a cursor and then check if each of those seqnos is present in T1.T1_SEQNOS.
ctx_ddl.create_preference('adsc_lexer','BASIC_LEXER');
ctx_ddl.set_attribute('adsc_lexer','printjoins',',');
create index ind_1 on t1(T1_SEQNOS) indextype is ctxsys.context parameters('lexer adsc_lexer');
select 1 from t1 where T1_ID=1 and CONTAINS(T1_SEQNOS,'%453%')> 0;
But its giving wrong results:
> select * from t1;
T1_ID T1_SEQNOS
---------------- -------------------------------------------
1 ,453,4999,233214,5555,0,99,3,
2 ,233214,134,
3 ,2,4,5,7,8,245,56,3465,
4 ,4444,7636,32,5,6,8,
> select T1_SEQNOS from t1 where T1_ID=1 and CONTAINS(T1_SEQNOS,'%,5,%')> 0;
T1_SEQNOS
------------------------------------------------------------,453,4999,233214,5555,0,99,3,
> select T1_SEQNOS from t1 where T1_ID=1 and CONTAINS(T1_SEQNOS,'%,134,%')> 0;
T1_SEQNOS
------------------------------------------------------------
,453,4999,233214,5555,0,99,3,
CRM query
A reader, December 13, 2005 - 8:19 am UTC
Hi
I have seen in many CRM users issues queries such as
select *
from contacts
where surname like '%GORDON%
These sort of query is quite slow since we cannot use indexes. Is it a good idea in an OLTP system use CTXSYS.CONTEXT indextype for such queries?
December 13, 2005 - 9:41 am UTC
you certainly can - sure, just bear in mind that the ctxsys.context index will always be "a little behind". it is generally not maintained in real time.
context index maintenance
A reader, December 13, 2005 - 9:53 am UTC
Hi
I am sort of aware the maintenance of context index is different however I am not able to find what sort of maintenance we need for these indexes in the documents.
I read this:
Index maintenance is necessary after your application inserts, updates, or deletes documents in your base table. Index maintenance involves synchronizing and optimizing your index.
However, if your application performs DML operations (inserts, updates, or deletes) on your base table, you must synchronize your index. You can synchronize your index manually with the CTX_DDL.SYNC_INDEX PL/SQL procedure.
What can go wrong with this sort of maintenance in a production system?
December 13, 2005 - 12:51 pm UTC
nothing should "go wrong".
It'll obviously add some additional amount of workload (the extent of which you'll discover when you *test* this)
Some Confusion
Su Baba, December 13, 2005 - 3:06 pm UTC
I'm confused as to why the first select statement not returning anything?
SQL> create table t ( col1 varchar2(25), col2 varchar2(15) );
Table created.
SQL>
SQL> grant select on t to ctxsys;
Grant succeeded.
SQL>
SQL>
SQL> begin
2 ctx_ddl.create_preference('SUBSTRING_PREF',
3 'BASIC_WORDLIST');
4 ctx_ddl.set_attribute('SUBSTRING_PREF',
5 'SUBSTRING_INDEX','TRUE');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> create index search_idx on T(col1)
2 indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M');
Index created.
SQL>
SQL> INSERT INTO t VALUES ('36IPZD7507761NJ', '3984399384');
1 row created.
SQL> INSERT INTO t VALUES ('36IPZD7507762NJ', '3984399384');
1 row created.
SQL> INSERT INTO t VALUES ('37IPZD7507761NJ', '3984388384');
1 row created.
SQL> INSERT INTO t VALUES ('36IDDZ7507768NJ', '3984397384');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select * from t where contains( col1,'%750776%')>0;
no rows selected
SQL> drop index search_idx;
Index dropped.
SQL>
SQL> create index search_idx on T(col1)
2 indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M');
Index created.
SQL>
SQL> select * from t where contains( col1,'%750776%')>0;
COL1 COL2
------------------------- ---------------
36IDDZ7507768NJ 3984397384
37IPZD7507761NJ 3984388384
36IPZD7507762NJ 3984399384
36IPZD7507761NJ 3984399384
SQL>
December 13, 2005 - 5:21 pm UTC
you did not sync the index after inserting the rows.
sync index
A reader, December 13, 2005 - 7:10 pm UTC
you have to sync the index everytime you insert?!
December 13, 2005 - 7:30 pm UTC
only if you want to have the index synced every time (documented in the text guide)
I sync my index every 30 minutes personally.
sync index
A reader, December 14, 2005 - 2:16 am UTC
Hi
What does sync operation do....? Does it acquire a table lock or affects application operation? I have been looking the docs but doesnt say what happens
December 14, 2005 - 8:17 am UTC
it reads the table that tells text what rows have been modified/added/removed and updates their contents in the index.
your application continues, the index is modified.
What if You Can't Use Oracle Text?
John Gilmore, December 14, 2005 - 4:19 am UTC
These solutions all rely upon the use of Oracle Text. Is there anything that can be done to improve the performance of matching strings like '%ABC' if you can only use ANSI SQL syntax?
December 14, 2005 - 8:31 am UTC
if you can only use ansi sql syntax - the base syntax that is supported by more than one vendor, you have already doomed yourself to abysmal performance.
think of a world with no analytics, group by rollup, case statements, outer joins, and so on (because while they are all ansi, not everyone has them - you are basically stuck with something akin to SQL-89, maybe even as bold as SQL-89 with the declarative integrity stuff added in :(
if you were doing this "generically", I support you would have to store a shadow column whereby you stuffed the bytes in backwards upon insert so you can search for
where backwards_string like 'CBA%'
or come up with some other equally "creative" technique.
sync index
A reader, December 14, 2005 - 9:38 am UTC
Hi
If sync index needs to do a full table scan I guess for a latge table (more than 20 million) it wouldnt be a good idea to use context indexes?
Also, what happen if I make 1000 inserts, instance failure? Will sync able to sync the index?
December 14, 2005 - 10:03 am UTC
but it won't need to do a full tablescan.
tell you what - set up your example, run the sync with sql trace on and you can *see* what it does.
If you committed the 1,000 inserts, the inserts will be there and can be sync'ed the next time a sync is done (this is one of the primary features of a database.... that once you commit, it is there)
WILD CHARD OPERATOR +
BARANITHARAN V, October 26, 2006 - 1:57 am UTC
WANT TE EXPLANATION ABOUT THE + OPERATOR WITH EXAMPLES.
October 26, 2006 - 11:50 am UTC
I WANT TO LET YOU KNOW ABOUT CAPSLOCK - it is a key typically on the left hand side of your keyboard, it would appear that you hit it - hit it again so it doesn't appear that you are shouting at us.
so, where do you see "+" being a "wildcard" character?
%, sure, +?
A reader, May 14, 2007 - 6:38 pm UTC
This is what Oracle doc says about substring indexing. Can you tell us more about the bolded parts? Thank you.
BEGIN
ctx_ddl.create_preference('SUBSTRING_PREF',
'BASIC_WORDLIST');
ctx_ddl.set_attribute('SUBSTRING_PREF',
'SUBSTRING_INDEX','TRUE');
END;
/Substring indexing has the following impact on indexing and disk resources:
- Index creation and DML processing is up to 4 times slower
- The size of the substring index created is approximately the size of the $X index on the word table.- Index creation with substring_index enabled requires more rollback segments during index flushes than with substring index off. Oracle recommends that you do either of the following when creating a substring index:
* make available double the usual rollback or
* decrease the index memory to reduce the size of the index flushes to disk
May 14, 2007 - 9:23 pm UTC
look above at the example, you'll see how it stores the data and why this index option takes necessarily "more space" than normal.
indexing the table for a search
saradha, July 23, 2007 - 4:37 pm UTC
Tom,
Can you please give some advice on indexing a table
where any column or combination of columns from a table
will be used in the WHERE clause.
Basically, the search will be done from any one
or multiple columns of the table.
In such cases, How do we index the table ?
The table:
ID NOT NULL NUMBER(12)
NAME NOT NULL VARCHAR2(80)
DESCRIPTION NOT NULL VARCHAR2(4000)
FILE_NAME NOT NULL VARCHAR2(256)
FILE_SIZE NOT NULL NUMBER(6)
MIME_TYPE NOT NULL VARCHAR2(80)
CREATED_BY NOT NULL NUMBER(12)
CREATED NOT NULL DATE
FILE_PATH VARCHAR2(1024)
CATEGORY VARCHAR2(80)
AUTHOR VARCHAR2(512)
For example, the end user might search by AUTHOR
or by NAME or by CREATED and CREATED_BY.
thanks in advance.
saradha
Troubleshooting search_schema (10gR2)
Stephan Uzzell, February 28, 2008 - 10:03 pm UTC
Hi Tom,
I found your search_schema routine a while ago and admired it. Now that I actually had an opportunity to use it, I'm running into an issue: on some tables in my schema, it is failing out
BEGIN search_schema(4773.12,'G'); END;
*
ERROR at line 1:
ORA-00939: too many arguments for function
ORA-06512: at "OPERA40303LATEST.SEARCH_SCHEMA", line 25
ORA-06512: at line 1
I've played with the code a little bit. I removed some datatypes I wasn't interested in; I added output for the tables that didn't contain the value (so I could see where it was failing); I added a second parameter to filter the table name so I wasn't looking at anything else.
Line 25 of my code is:
open l_cursor for 'select ' || l_cols || 'else null end cname from ' ||
I know what tables are having the issue (and I don't really think they contain what I need anyway), but I'd like to know why - and how to figure out why.
Any advice?
Thanks,
Stephan
March 01, 2008 - 10:11 am UTC
how many columns does this table have - likely hitting the limit in CASE.
You could work around that by breaking the case into "more than one"
ops$tkyte%ORA10GR2> declare
2 l_stmt long := 'create table t ( c1 varchar2(20)';
3 begin
4 for i in 2 .. 126
5 loop
6 l_stmt := l_stmt || ', c' || i || ' varchar2(20)';
7 end loop;
8 l_stmt := l_stmt || ' )';
9 execute immediate l_stmt;
10 end;
11 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec search_schema( 'foobar' )
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t purge;
Table dropped.
ops$tkyte%ORA10GR2> declare
2 l_stmt long := 'create table t ( c1 varchar2(20)';
3 begin
4 for i in 2 .. 127
5 loop
6 l_stmt := l_stmt || ', c' || i || ' varchar2(20)';
7 end loop;
8 l_stmt := l_stmt || ' )';
9 execute immediate l_stmt;
10 end;
11 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec search_schema( 'foobar' )
BEGIN search_schema( 'foobar' ); END;
*
ERROR at line 1:
ORA-00939: too many arguments for function
ORA-06512: at "OPS$TKYTE.SEARCH_SCHEMA", line 26
ORA-06512: at line 1
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure search_schema( p_string in varchar2 )
2 authid current_user
3 as
4 l_cols long;
5 l_where long;
6 l_cursor sys_refcursor;
7 l_cname varchar2(4000);
8 begin
9 dbms_application_info.set_client_info( '%' || p_string || '%' );
10 for x in ( select table_name from user_tables )
11 loop
12 l_cols := '''hit = ''';
13 l_where := ' where ( 1=0 ';
14 for y in ( select '"' || column_name || '" ' column_name
15 from user_tab_columns
16 where table_name = upper(x.table_name)
17 and (data_type in ( 'CHAR', 'DATE', 'FLOAT', 'NCHAR', 'NUMBER', 'NVARCHAR2', 'VARCHAR2' )
18 or data_type like 'INTERVAL%' or data_type like 'TIMESTAMP%' )
19 order by column_id
20 )
21 loop
22 l_cols := l_cols || ' || case when ' || y.column_name ||
23 ' like sys_context(''userenv'',''client_info'') then ' ||
24 ' ''' || y.column_name || ''' end';
25 l_where := l_where || ' or ' || y.column_name ||
26 ' like sys_context(''userenv'',''client_info'') ';
27 end loop;
28 open l_cursor for 'select ' || l_cols || ' cname from ' ||
29 x.table_name || l_where || ') and rownum=1';
30 fetch l_cursor into l_cname;
31 if ( l_cursor%found )
32 then
33 dbms_output.put_line( x.table_name || ' ' || l_cname );
34 end if;
35 close l_cursor;
36 end loop;
37 end;
38 /
Procedure created.
ops$tkyte%ORA10GR2> drop table t purge;
Table dropped.
ops$tkyte%ORA10GR2> declare
2 l_stmt long := 'create table t ( c1 varchar2(20)';
3 begin
4 for i in 2 .. 200
5 loop
6 l_stmt := l_stmt || ', c' || i || ' varchar2(20)';
7 end loop;
8 l_stmt := l_stmt || ' )';
9 execute immediate l_stmt;
10 end;
11 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> insert into t (c1,c100,c200) values ( 1,1,1);
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec search_schema( '1' )
T hit = "C1" "C100" "C200"
PL/SQL procedure successfully completed.
Yes, but...
Stephan Uzzell, March 01, 2008 - 7:20 pm UTC
You were exactly right, Tom - it was the number of columns. Would you believe that table has 447 columns?
So we now get a slightly different error:
BEGIN search_schema('4773.12','G'); END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "OPERA40303LATEST.SEARCH_SCHEMA", line 21
ORA-06512: at line 1
The full case statement is too big to fit in l_cols. I tried making l_cols a clob to see if I could fit more that way, but the it wouldn't even compile:
Errors for PROCEDURE SEARCH_SCHEMA:
LINE/COL ERROR
-------- -----------------------------------------------------------------
27/9 PL/SQL: Statement ignored
27/27 PLS-00382: expression is of wrong type
Which is the open cursor line...
Whattaya think? Fixable?
Thanks as always,
Stephan
March 01, 2008 - 10:20 pm UTC
choices are:
a) convert it to use dbms_sql, you can put the query into an array, the array can be of enormous size.
b) change the code to make it fit into 32k (eg: don't list the column that caused the problem, just do the search, that'll likely fit in 32k)
c) get to 11g where native dynamic sql takes lobs
Text Index
Sameer, September 28, 2009 - 11:51 am UTC
Dear Sir,
I am a great fan of your site and your 2 books, expert one on one and effective oracle by design. Each time I stumble upon an issue in Oracle I make it a point to check your site. I don't even have to do googling for oracle these days. Your site is an excellent source for anything and everything under the sun for Oracle.
Thanks to your SQL tuning and tkprof discussion threads, I was able to improve the perf of our system from over a minute to under a second, without even posting the issue here. I do have an issue in Text indexing now, but this discussion has given me some good leads to experiment.
Thanks Again.
- Sameer
how to handle conditional input params
JGP, October 28, 2009 - 1:52 pm UTC
Tom, The sql below runs fast in 10gR2 using domain index, but when I uncomment the two commented lines, it reverts back to full table scan and takes longer. Any insight you can provide?
select
pharmacy_name
FROM pharmacy_test
WHERE 1=1
and
(
(:v_search_pharmacy_name is not null and
contains (pharmacy_name, '%' || :v_search_pharmacy_name || '%') > 0
)
-- or
-- :v_search_pharmacy_name is null )
;
Here are the two explain plans (first w/o null check, second with null check):
*SELECT STATEMENT ALL_ROWSCost: 11,811 Bytes: 142,940 Cardinality: 4,084 *
* 1 TABLE ACCESS FULL TABLE REPORTUSER.PHARMACY_TEST Cost: 11,811 Bytes: 142,940 Cardinality: 4,084*
*SELECT STATEMENT ALL_ROWSCost: 14 Bytes: 1,435 Cardinality: 41 *
* 3 FILTER *
* 2 TABLE ACCESS BY INDEX ROWID TABLE REPORTUSER.PHARMACY_TEST Cost: 14 Bytes: 1,435 Cardinality: 41 *
* 1 DOMAIN INDEX INDEX (DOMAIN) REPORTUSER.PHARMACY_TEST_IX1 Cost: 4*
October 28, 2009 - 4:31 pm UTC
well, it needs two plans here - one for when :bind is not null (use contains) and one when :bind is null (full scan)
ops$tkyte%ORA10GR2> set echo on
ops$tkyte%ORA10GR2> /*
ops$tkyte%ORA10GR2> drop table t;
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t ( x int, y varchar2(2000) );
ops$tkyte%ORA10GR2> create index t_idx on t(y) indextype is ctxsys.context;
ops$tkyte%ORA10GR2> insert into t values ( 'hello world' );
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T', numrows => 100000 );
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable y varchar2(2000)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @trace
ops$tkyte%ORA10GR2> alter session set events '10046 trace name context forever, level 12';
Session altered.
ops$tkyte%ORA10GR2> select * from t where :y is not null and contains(y,'%'||:y||'%') > 0
2 union all
3 select * from t where :y is null;
no rows selected
ops$tkyte%ORA10GR2> exec :y := 'ello'
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select * from t where :y is not null and contains(y,'%'||:y||'%') > 0
2 union all
3 select * from t where :y is null;
no rows selected
<b>Note in the following (tkprof run with aggregate=no) the first query hits only the full scan tree and the second hits only the index access - the row source operation shows that.</b>
select * from t where :y is not null and contains(y,'%'||:y||'%') > 0
union all
select * from t where :y is null
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 563
Rows Row Source Operation
------- ---------------------------------------------------
0 UNION-ALL (cr=3 pr=0 pw=0 time=154 us)
0 FILTER (cr=0 pr=0 pw=0 time=8 us)
0 TABLE ACCESS BY INDEX ROWID T (cr=0 pr=0 pw=0 time=0 us)
0 DOMAIN INDEX T_IDX (cr=0 pr=0 pw=0 time=0 us)
0 FILTER (cr=3 pr=0 pw=0 time=119 us)
0 TABLE ACCESS FULL T (cr=3 pr=0 pw=0 time=112 us)
********************************************************************************
select * from t where :y is not null and contains(y,'%'||:y||'%') > 0
union all
select * from t where :y is null
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 15 0 0
Fetch 1 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 15 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 563
Rows Row Source Operation
------- ---------------------------------------------------
0 UNION-ALL (cr=23 pr=0 pw=0 time=41548 us)
0 FILTER (cr=23 pr=0 pw=0 time=41511 us)
0 TABLE ACCESS BY INDEX ROWID T (cr=23 pr=0 pw=0 time=41505 us)
0 DOMAIN INDEX T_IDX (cr=23 pr=0 pw=0 time=41482 us)
0 FILTER (cr=0 pr=0 pw=0 time=8 us)
0 TABLE ACCESS FULL T (cr=0 pr=0 pw=0 time=0 us)
how to handle conditional input params
JGP, October 29, 2009 - 7:29 am UTC
So essentially what you're saying is split these into two separate queries. Problem is I have 6 optional variables that can be null. That would be a lot of nested if-else statements. Is there a better way than that?
October 29, 2009 - 10:11 am UTC
how to handle conditional input params
JGP, October 29, 2009 - 1:41 pm UTC
Thanks for the link.. very useful.Do you use Oracle Text and substring indexes for your Advanced Search as well?
Thanks,
JGP
October 29, 2009 - 2:00 pm UTC
not substring, I do keyword searching only...
but yes, I use text..
OK
Siva, November 03, 2009 - 4:28 am UTC
Hi Tom,
I need a query which serves the following purpose
" I want the table names and column names where columns have a value of "Insurance" "
Could you please help?
November 09, 2009 - 2:24 pm UTC
that is very vague.
you really want to search ALL TABLES, ALL COLUMNS in the entire database for a table/column name that has the value 'insurance' in it?
Really?
ctl-f for
database grep
on this page.
Already answered
JerrQ, November 07, 2009 - 5:07 pm UTC
If you look back up the screen, you'll see Tom already answered your question.
Followup June 10, 2005 - 8am Central time zone:
there is no "database grep" like that -- it is highly unusual to ask to search "every table and
every column looking for a string" in structured information.
but you can always automate things:
create or replace procedure search_schema( p_string in varchar2 )
authid current_user
as
l_cols long;
l_where long;
l_cursor sys_refcursor;
l_cname varchar2(4000);
begin
dbms_application_info.set_client_info( '%' || p_string || '%' );
for x in ( select table_name from user_tables )
loop
l_cols := 'case when 1=0 then ''x'' ';
l_where := ' where ( 1=0 ';
for y in ( select '"' || column_name || '"' column_name
from user_tab_columns
where table_name = upper(x.table_name)
and (data_type in ( 'CHAR', 'DATE', 'FLOAT', 'NCHAR', 'NUMBER', 'NVARCHAR2',
'VARCHAR2' )
or data_type like 'INTERVAL%' or data_type like 'TIMESTAMP%' )
)
loop
l_cols := l_cols || ' when ' || y.column_name ||
' like sys_context(''userenv'',''client_info'') then ' ||
' ''' || y.column_name || '''';
l_where := l_where || ' or ' || y.column_name || ' like
sys_context(''userenv'',''client_info'') ';
end loop;
open l_cursor for 'select ' || l_cols || 'else null end cname from ' ||
x.table_name || l_where || ') and rownum=1';
fetch l_cursor into l_cname;
if ( l_cursor%found )
then
dbms_output.put_line( x.table_name || ' ' || l_cname );
end if;
close l_cursor;
end loop;
end;
/
(might not be perfect, hacked together from other bits of code)
Martin Rose, December 07, 2010 - 7:32 am UTC
/|\
| http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:37336026927381#53268468119391 Tom, back up there somewhere, you said you had an idea for storing data backwards in a shadow column to solve the leading wild-card issue using just plain ANSI SQL.
I've tried to fathom out what that algorithm might be, but I can't work it out.
I can see it will work where the sub-string being searched for is either at the beginning of the main string or at the end, but not how it works when the sub-string is in the middle.
What did you have in mind when you wrote this (assuming you can remember as it is over 5 years ago now) ?
December 07, 2010 - 11:11 am UTC
... I can see it will work where the sub-string being searched for is either at the beginning of the main string or at the end, but not how it works when the sub-string is in the middle. ..
exactly, I said "leading wildcard" - not "leading and trailing".
For leading and trailing, you'll want to use a substring prefix index with TEXT
wildcard search
A reader, December 07, 2010 - 4:53 pm UTC
excellent
veiw the existing attributes of ctx index
Tim, January 30, 2013 - 7:07 am UTC
My father told me there is no such thing as a stupid question, so here it goes:
If I wanted to do the idea mentioned far above:
begin
ctx_ddl.create_preference('SUBSTRING_PREF',
'BASIC_WORDLIST');
ctx_ddl.set_attribute('SUBSTRING_PREF',
'SUBSTRING_INDEX','TRUE');
end;
How can I tell what other attributes are set? I googled around and check the docs but couldn't find a way to tell if these are already set, or other attributes are set, so if I do the next steps:
drop index search_idx;
create index search_idx on T(col1)
indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M');
..
I need to add back in the existing parameters that existed before I dropped the index, but I'd like to know what those attributes and parameter values are.
Do you know where these attributes can be found or queried?
Thanks,
Tim
January 31, 2013 - 9:53 am UTC
here is a way to get it:
ops$tkyte%ORA11GR2> begin
2 ctx_ddl.create_preference('SUBSTRING_PREF',
3 'BASIC_WORDLIST');
4 ctx_ddl.set_attribute('SUBSTRING_PREF',
5 'SUBSTRING_INDEX','TRUE');
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> create table t ( col1 varchar2(50) );
Table created.
ops$tkyte%ORA11GR2> create index search_idx on T(col1)
2 indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M');
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select dbms_metadata.get_ddl( 'INDEX', 'SEARCH_IDX' ) from dual;
DBMS_METADATA.GET_DDL('INDEX','SEARCH_IDX')
-------------------------------------------------------------------------------
CREATE INDEX "OPS$TKYTE"."SEARCH_IDX" ON "OPS$TKYTE"."T" ("COL1")
INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('wordlist SUBSTRING_PREF MEMORY
50M')
follow up
A reader, February 01, 2013 - 4:38 am UTC
Thanks Tom.
What if another DBA created the parameter and attributes for 'SUBSTRING_PREF'? If I use the metadata package I'll only be able to see that the index contains a parameter called 'SUBSTRING_PREF'. How can I tell what the definition of 'SUBSTRING_PREF' is retroactively? In other words, if you didn't create the parameters and attributes assigned to the index, how can you find out the details of those parameters and attributes?
Thanks again,
Tim
February 01, 2013 - 8:57 am UTC
this will show you all of the attributes of the index:
ops$tkyte%ORA11GR2> set long 500000
ops$tkyte%ORA11GR2> select ctx_report.create_index_script( 'SEARCH_IDX' ) from dual;
CTX_REPORT.CREATE_INDEX_SCRIPT('SEARCH_IDX')
--------------------------------------------------------------------------------
begin
ctx_ddl.create_preference('"SEARCH_IDX_DST"','DIRECT_DATASTORE');
end;
/
begin
ctx_ddl.create_preference('"SEARCH_IDX_FIL"','NULL_FILTER');
end;
/
begin
ctx_ddl.create_section_group('"SEARCH_IDX_SGP"','NULL_SECTION_GROUP');
end;
/
begin
ctx_ddl.create_preference('"SEARCH_IDX_LEX"','BASIC_LEXER');
end;
/
begin
ctx_ddl.create_preference('"SEARCH_IDX_WDL"','BASIC_WORDLIST');
ctx_ddl.set_attribute('"SEARCH_IDX_WDL"','SUBSTRING_INDEX','YES');
end;
/
begin
ctx_ddl.create_stoplist('"SEARCH_IDX_SPL"','BASIC_STOPLIST');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','Mr');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','Mrs');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','Ms');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','a');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','all');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','almost');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','also');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','although');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','an');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','and');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','any');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','are');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','as');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','at');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','be');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','because');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','been');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','both');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','but');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','by');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','can');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','could');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','d');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','did');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','do');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','does');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','either');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','for');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','from');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','had');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','has');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','have');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','having');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','he');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','her');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','here');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','hers');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','him');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','his');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','how');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','however');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','i');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','if');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','in');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','into');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','is');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','it');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','its');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','just');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','ll');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','me');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','might');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','my');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','no');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','non');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','nor');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','not');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','of');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','on');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','one');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','only');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','onto');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','or');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','our');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','ours');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','s');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','shall');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','she');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','should');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','since');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','so');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','some');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','still');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','such');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','t');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','than');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','that');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','the');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','their');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','them');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','then');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','there');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','therefore');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','these');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','they');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','this');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','those');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','though');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','through');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','thus');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','to');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','too');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','until');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','ve');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','very');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','was');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','we');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','were');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','what');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','when');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','where');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','whether');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','which');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','while');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','who');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','whose');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','why');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','will');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','with');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','would');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','yet');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','you');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','your');
ctx_ddl.add_stopword('"SEARCH_IDX_SPL"','yours');
end;
/
begin
ctx_ddl.create_preference('"SEARCH_IDX_STO"','BASIC_STORAGE');
ctx_ddl.set_attribute('"SEARCH_IDX_STO"','R_TABLE_CLAUSE','lob (data) store as
(cache)');
ctx_ddl.set_attribute('"SEARCH_IDX_STO"','I_INDEX_CLAUSE','compress 2');
end;
/
begin
ctx_output.start_log('SEARCH_IDX_LOG');
end;
/
create index "OPS$TKYTE"."SEARCH_IDX"
on "OPS$TKYTE"."T"
("COL1")
indextype is ctxsys.context
parameters('
datastore "SEARCH_IDX_DST"
filter "SEARCH_IDX_FIL"
section group "SEARCH_IDX_SGP"
lexer "SEARCH_IDX_LEX"
wordlist "SEARCH_IDX_WDL"
stoplist "SEARCH_IDX_SPL"
storage "SEARCH_IDX_STO"
')
/
begin
ctx_output.end_log;
end;
/
sweet! thanks
A reader, February 08, 2013 - 3:57 am UTC
Perfect! Many thanks Tom
single character search
Mike R., March 20, 2013 - 9:19 am UTC
Hi Tom,
I've used the suggestions in this thread
begin
ctx_ddl.create_preference('SUBSTRING_PREF',
'BASIC_WORDLIST');
ctx_ddl.set_attribute('SUBSTRING_PREF',
'SUBSTRING_INDEX','TRUE');
end;
drop index search_idx;
create index search_idx on T(col1)
indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M');
and see a vast improvement for searches like %dog, %dog% on a CLOB column but only minimal improvement for a search for %d
I understand that a search for %d will need to look at every word that has a 'd' in it except at the beginning of the word, but is there a way to make this type of query faster?
Thanks for your advice.
March 25, 2013 - 10:43 am UTC
Yes, we can optimize %xxxxx% and xx% searches but for this one - a "ends with" search, I'm not aware of any options to optimize that one. We don't have a way to 'reverse' the text keys right now as far as I know.
PREFIX_INDEX?
Mike R., April 10, 2013 - 11:40 am UTC
Hi Tom,
Would somethnig like this perhaps help in the search for leading wildcards with a single character (like %d)?
begin
ctx_ddl.create_preference('WC_WORDLIST', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('WC_WORDLIST','PREFIX_INDEX','TRUE');
ctx_ddl.set_attribute('WC_WORDLIST','PREFIX_MIN_LENGTH', '1');
ctx_ddl.set_attribute('WC_WORDLIST','PREFIX_MAX_LENGTH', '6');
ctx_ddl.set_attribute('WC_WORDLIST','SUBSTRING_INDEX', 'TRUE');
end;
/
Or is the PREFIX_MIN_LENGTH used for some other purpose?
Thanks
Mike
is this a bug
Lal Cyril, January 12, 2018 - 11:42 am UTC
I tried the following sample
created a sample table with id and description column and populated some data (word for the id number- just for sample)
create table template as
select level tmp_id, (to_char(to_date(level,'j'), 'jsp')) tmp_name from dual connect by level<100000;
Then created an text index as found in this blog
begin
ctx_ddl.create_preference('SUBSTRING_PREF',
'BASIC_WORDLIST');
ctx_ddl.set_attribute('SUBSTRING_PREF',
'SUBSTRING_INDEX','TRUE');
end; drop index search_idx;
create index search_idx on template(tmp_name)
indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M'); Then i tried fecthing the data for different numbers
select * from template where contains( tmp_name,'two',1)>0;
Its giving results for all number words exept for 'one'
select * from template where contains( tmp_name,'one',1)>0;
Whats the issue with 'one' in this case?
Is this a bug
I tried this sample in latest 12c Release2 database
Also can you please let me know whether Oracle Text feature needs separate license? and whether its available with Standard edition?
Thanks very much for your time.
build text index from another schema
Rajeshwaran Jeyabal, October 17, 2018 - 9:02 am UTC
---run this create table in SCOTT schema.
create table t(col1 varchar2(25), col2 varchar2(15));
insert into t(col1,col2) values('36IPZD7507761NJ','3984399384');
insert into t(col1,col2) values('36IPZD7507762NJ','3984399384');
insert into t(col1,col2) values('37IPZD7507761NJ','3984388384');
insert into t(col1,col2) values('36IDDZ7507768NJ','3984397384');
commit;
----------------
then login as DBA account and do this.
demo@ORA11G> alter session set current_schema=SCOTT;
Session altered.
demo@ORA11G> begin
2 ctx_ddl.create_preference('SUBSTRING_PREF','BASIC_WORDLIST');
3 ctx_ddl.set_attribute('SUBSTRING_PREF','SUBSTRING_INDEX','TRUE');
4 end;
5 /
PL/SQL procedure successfully completed.
demo@ORA11G> create index scott.search_idx on scott.T(col1)
2 indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M');
create index scott.search_idx on scott.T(col1)
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: SUBSTRING_PREF
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366
demo@ORA11G>though we have created the preferece, it ended up with this error "preference does not exist: SUBSTRING_PREF"
So could you help us to understand, what are we missing here?
any possiblity of text indexing from DBA accounts?
October 31, 2018 - 2:03 am UTC
From the docs:
"Caution:
CTX_DDL.CREATE_PREFERENCE does not respect the current schema as set by ALTER SESSION SET current_schema. Therefore, if you need to create or delete a preference owned by another user, then you must explicitly state this, and you must have the CREATE ANY TABLE system privilege."
So you need a schema:
SQL> exec ctx_ddl.create_preference('SCOTT.SUBSTRING_PREF','BASIC_WORDLIST');
PL/SQL procedure successfully completed.
SQL> conn scott/tiger
Connected.
SQL> select * from CTX_USER_PREFERENCES;
PRE_NAME
-----------------------------------------------------------------------------
PRE_CLASS
-----------------------------------------------------------------------------
PRE_OBJECT
-----------------------------------------------------------------------------
SUBSTRING_PREF
WORDLIST
BASIC_WORDLIST
docs to read.
Rajeshwaran Jeyabal, October 31, 2018 - 4:23 am UTC
November 01, 2018 - 10:54 am UTC
Well, the dev guide gives an overview of the product, explains how to use it, and has a getting started section. Sounds like a good place to get started to me!
The reference is really a blow-by-blow description of all the syntax for all the operators and statements. Not sure I'd want to "read" this.
is this a "trick"
oj, March 31, 2022 - 9:21 am UTC
I found this odd, but it worked in our case, resulting in an INDEX RANGE SCAN of col1 below;
select * from big_table where col1 like '%'||:bindvar
...by following this;
https://blog.tuningsql.com/index-range-scan-vs-leading-wildcard/ ...is it a "trick" as described ? Any idea how that works ? Is it really INDEX RANGE scanning or effectively full scanning the index ?
I'm just trying to picture it - if I was looking up the index in the back of a book, and I'm thinking I would need to scan the entire index to do this....
March 31, 2022 - 10:40 am UTC
Yep, it's a trick!
As discussed in the post, it works because the first execution peeks at the bind variable value (with no leading wildcard). Generating plans is expensive, so the optimizer tries to avoid doing this. So from then on the optimizer sticks with this plan using the index.
But relying on this is incredibly risky.
If the current plan ages out of the cache, you need to run the query without the leading wildcard before any users do. The chances of doing this will be close to zero for most.
The plan may also change for other reasons (e.g. adding/removing indexes, sensitivity to other binds, ...). At which point you're very likely to lose the peeked bind without a leading wildcard.
It's also worth noting that using an index for leading wildcard searches could be slow. If this filters relatively few values, you may be better off with a full table scan or different index anyway.
That said, this could be useful way to start SQL Plan Management. Using the index query enables you to get the index plan, which you can then lock in place by making it a baseline.