• Questions
• # Fast wild-card searching

Thanks for the question, Zia.

Asked: March 25, 2005 - 8:05 pm UTC

Answered by: Tom Kyte - Last updated: November 01, 2018 - 10:54 am UTC

Category: Database - Version: 8.1.7

Viewed 50K+ times! This question is

What is the best way to implement a solution to the following problem so that wild-card searching is very fast.

PROBLEM: Two column tabular data with ~100 million rows of the form given below. Searching is on the first column. The number of search requests are in the order of 100,000 in a twelve hour period with peak usage during a 4 hour period.

Column 1: varchar(25)
Column 2: varchar(15)

36IPZD7507761NJ, 3984399384
36IPZD7507762NJ, 3984399384
37IPZD7507761NJ, 3984388384
36IDDZ7507768NJ, 3984397384

SEARCH: Could have leading, trailing or embedded wild-cards e.g. the following search strings should all match to the first row above.

36IPZD750776%
%750776%
36%7507761NJ

## and we said...

see the followup below by Ofir Manor for an even better concept..

think about it -- with leading %, you have to search the entire set, unless we break that field out.

We can use TEXT to index this. There are two approaches:

a) store the string 36IPZD7507761NJ as 3 36 36I 36IP 36IPZ 36IPZD 36IPZD7 36IPZD75 36IPZD750 36IPZD7507 36IPZD75077 3
6IPZD750776 36IPZD7507761 36IPZD7507761N 36IPZD7507761NJ 6 6I 6IP 6IPZ 6IPZD 6I
PZD7 6IPZD75 6IPZD750 6IPZD7507 6IPZD75077 6IPZD750776 6IPZD7507761 6IPZD750776
1N 6IPZD7507761NJ I IP IPZ IPZD IPZD7 IPZD75 IPZD750 IPZD7507 IPZD75077 IPZD750
776 IPZD7507761 IPZD7507761N IPZD7507761NJ P PZ PZD PZD7 PZD75 PZD750 PZD7507 P
ZD75077 PZD750776 PZD7507761 PZD7507761N PZD7507761NJ Z ZD ZD7 ZD75 ZD750 ZD750
7 ZD75077 ZD750776 ZD7507761 ZD7507761N ZD7507761NJ D D7 D75 D750 D7507 D75077
D750776 D7507761 D7507761N D7507761NJ 7 75 750 7507 75077 750776 7507761 750776
1N 7507761NJ 5 50 507 5077 50776 507761 507761N 507761NJ 0 07 077 0776 07761 07
761N 07761NJ 77 776 7761 7761N 7761NJ 76 761 761N 761NJ 61 61N 61NJ 1 1N 1NJ N
NJ J
and index that string as if it were words.

b) index a function that returns that big string -- saving us from having to persist the big string.

I'll demonstrate A and B here:

a)

ops\$tkyte@ORA9IR2> create table t ( col1 varchar2(25), col2 varchar2(15), col1_shadow varchar2(4000) );
Table created.

extra column to hold the expanded string...

ops\$tkyte@ORA9IR2> create view v as select col1, col2 from t;
View created.

just for my use -- to generate data below...

ops\$tkyte@ORA9IR2>
ops\$tkyte@ORA9IR2>
ops\$tkyte@ORA9IR2> create or replace procedure string_to_words( p_string in varchar2, p_return in out varchar2 )
2 as
3 l_n number;
4 l_piece varchar2(255);
5 begin
6 l_n := length(p_string);
7 p_return := ' ';
8 for i in 1 .. l_n
9 loop
10 for j in 1 .. l_n-i+1
11 loop
12 l_piece := substr(p_string,i,j)||' ';
13 if ( nvl(instr( p_return, ' ' || l_piece ),0) = 0 )
14 then
15 p_return := p_return || l_piece;
16 end if;
17 end loop;
18 end loop;
19 end;
20 /

Procedure created.

turn the string into a "paragraph" of words

ops\$tkyte@ORA9IR2> create or replace trigger t_trigger
2 before insert or update on t for each row
3 begin
5 end;
6 /

Trigger created.

ops\$tkyte@ORA9IR2> insert into t values ( '36IPZD7507761NJ', '3984399384', null );
1 row created.

ops\$tkyte@ORA9IR2> exec gen_data( 'V', 10000 );
PL/SQL procedure successfully completed.

ops\$tkyte@ORA9IR2> commit;
Commit complete.

ops\$tkyte@ORA9IR2> create index search_idx on T(col1_shadow)
2 indextype is ctxsys.context parameters( 'datastore s2w_user_datastore lexer my_lexer memory 50m')
3 /
Index created.

you would now accept the user index and build a string to pass into contains (BIND into contains in real life of course!). You would break the string up on '%' and create an ANDed list of words

ops\$tkyte@ORA9IR2> select col1,col2 from t where contains( col1_shadow, '{36IPZD750776}' ) > 0 and (col1 like '36IPZD750776%');

COL1 COL2
------------------------- ---------------
36IPZD7507761NJ 3984399384

ops\$tkyte@ORA9IR2> select col1,col2 from t where contains( col1_shadow, '{750776}' ) > 0 and (col1 like '%750776%');

COL1 COL2
------------------------- ---------------
36IPZD7507761NJ 3984399384

ops\$tkyte@ORA9IR2> select col1,col2 from t where contains( col1_shadow, '{36} and {7507761NJ}' ) > 0 and (col1 like '36%7507761NJ');

COL1 COL2
------------------------- ---------------
36IPZD7507761NJ 3984399384

that'll use the index on col1_shadow in order to find all of the rows that might match -- and then use the SQL like clause to find the ones that do (order of terms matters, a leading % matters and the like clause cleans that up)

If you don't like the shadow column, you can do this (this is similar to what i do on this site -- I have an index that pulls data from three tables to construct a page)

ops\$tkyte@ORA9IR2> create table t ( col1 varchar2(25), col2 varchar2(15) );
Table created.

ops\$tkyte@ORA9IR2> grant select on t to ctxsys;
Grant succeeded.

ops\$tkyte@ORA9IR2> @su ctxsys
ctxsys@ORA9IR2> create or replace procedure string_to_words( p_id in rowid, p_return in out varchar2 )
2 as
3 l_n number;
4 l_piece varchar2(255);
5 begin
6 for x in ( select * from ops\$tkyte.t where rowid = p_id )
7 loop
8 l_n := length(x.col1);
9 p_return := ' ';
10 for i in 1 .. l_n
11 loop
12 for j in 1 .. l_n-i+1
13 loop
14 l_piece := substr(x.col1,i,j)||' ';
15 if ( nvl(instr( p_return, ' ' || l_piece ),0) = 0 )
16 then
17 p_return := p_return || l_piece;
18 end if;
19 end loop;
20 end loop;
21 end loop;
22 end;
23 /
Procedure created.

ctxsys@ORA9IR2> grant execute on string_to_words to ops\$tkyte;
Grant succeeded.

ctxsys@ORA9IR2> @connect /

ops\$tkyte@ORA9IR2> begin
2 ctx_ddl.drop_preference('s2w_user_datastore');
3 end;
4 /
PL/SQL procedure successfully completed.

ops\$tkyte@ORA9IR2> begin
2 ctx_ddl.create_preference('s2w_user_datastore',
3 'user_datastore' );
4
5 ctx_ddl.set_attribute( 's2w_user_datastore', 'procedure', 'STRING_TO_WORDS' );
6 end;
7 /
PL/SQL procedure successfully completed.

ops\$tkyte@ORA9IR2> begin
2 ctx_ddl.drop_preference('my_lexer');
3 end;
4 /
PL/SQL procedure successfully completed.

ops\$tkyte@ORA9IR2> begin
2 ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
3 ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
4 ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'NO' );
5 end;
6 /
PL/SQL procedure successfully completed.

ops\$tkyte@ORA9IR2> insert into t values ( '36IPZD7507761NJ', '3984399384' );
1 row created.

ops\$tkyte@ORA9IR2> exec gen_data( 'T', 10000 );
PL/SQL procedure successfully completed.

ops\$tkyte@ORA9IR2> commit;
Commit complete.

ops\$tkyte@ORA9IR2> create index search_idx on T(col1)
2 indextype is ctxsys.context parameters( 'datastore s2w_user_datastore lexer my_lexer memory 50m')
3 /
Index created.

ops\$tkyte@ORA9IR2> select * from t where contains( col1, '{36IPZD750776}' ) > 0 and (col1 like '36IPZD750776%');

COL1 COL2
------------------------- ---------------
36IPZD7507761NJ 3984399384

ops\$tkyte@ORA9IR2> select * from t where contains( col1, '{750776}' ) > 0 and (col1 like '%750776%');

COL1 COL2
------------------------- ---------------
36IPZD7507761NJ 3984399384

ops\$tkyte@ORA9IR2> select * from t where contains( col1, '{36} and {7507761NJ}' ) > 0 and (col1 like '36%7507761NJ');

COL1 COL2
------------------------- ---------------
36IPZD7507761NJ 3984399384

You'll want to check out parallel index creation and other options in the TEXT documentation as well for 100,000,000 records

## and you rated our response

(50 ratings)

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

# Reviews

### Why not native Oracle Text functionality?

March 26, 2005 - 1:14 pm UTC

Reviewer: Jack Wells from Boca Raton, FL USA

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?

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?

Followup

March 26, 2005 - 6:42 pm UTC

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

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?

March 27, 2005 - 3:40 am UTC

Reviewer: Ofir Manor from Israel

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:

Followup

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

March 27, 2005 - 6:07 pm UTC

Reviewer: Zia Hydari from Boston, MA

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.

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?

Followup

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.

March 27, 2005 - 11:10 pm UTC

Reviewer: Doug from CT, USA

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?

Followup

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

March 28, 2005 - 3:35 am UTC

Reviewer: Matthias Rogel from Kaiserslautern, Germany

*****

### Re:

March 28, 2005 - 7:17 am UTC

Reviewer: Jagjeet Singh from INDIA

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 %

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,

Followup

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

March 28, 2005 - 7:45 am UTC

Reviewer: satya from USA

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

Followup

March 28, 2005 - 8:03 am UTC

not understanding what (A,i) or (B,i) means.

create table
insert into table
better explanation of what you mean...

then we can take a look.

### here is again

March 28, 2005 - 8:30 am UTC

Reviewer: satya from U

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

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?

March 28, 2005 - 8:33 am UTC

Reviewer: Jon

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

March 28, 2005 - 8:38 am UTC

Reviewer: Zia Hydari from Boston, MA

- 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

Followup

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

March 28, 2005 - 9:56 am UTC

Reviewer: s

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
SQL>  select mykeyword('ABC:99999;XYZ:123456;','ABC') from dual ;  Expected
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

Followup

March 28, 2005 - 10:52 am UTC

use length(:pat)+1 instead of 4 then?

### string search

June 10, 2005 - 2:53 am UTC

Reviewer: Rahul from New Delhi

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.

Followup

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

June 13, 2005 - 12:36 am UTC

Reviewer: rahul from New Delhi

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

June 17, 2005 - 3:01 am UTC

Reviewer: Abir Bhattacharya from India

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;

Followup

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

September 06, 2005 - 5:02 pm UTC

Reviewer: mohini from germany

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

September 12, 2005 - 12:04 pm UTC

Reviewer: sat from USA

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;

Followup

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

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

Followup

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

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

October 30, 2005 - 9:02 pm UTC

Reviewer: SH from US

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.

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

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?

Followup

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

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.

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?

Followup

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

December 13, 2005 - 3:06 pm UTC

Reviewer: Su Baba from Ca, USA

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>

Followup

December 13, 2005 - 5:21 pm UTC

you did not sync the index after inserting the rows.

### sync index

December 13, 2005 - 7:10 pm UTC

you have to sync the index everytime you insert?!

Followup

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

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

Followup

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?

December 14, 2005 - 4:19 am UTC

Reviewer: John Gilmore from Southend on Sea, United Kingdom

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?

Followup

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

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?

Followup

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 +

October 26, 2006 - 1:57 am UTC

Reviewer: BARANITHARAN V from BANGALORE, INDIA.

WANT TE EXPLANATION ABOUT THE + OPERATOR WITH EXAMPLES.

Followup

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, +?

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

Followup

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

July 23, 2007 - 4:37 pm UTC

Tom,

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.

Followup

July 24, 2007 - 9:22 am UTC

http://docs.oracle.com/docs/cd/B19306_01/text.102/b14217/ind.htm#sthref101

probably a ctxcat index.

or, if the table is read only, read mostly (static reference information or loaded on a schedule), single column bitmap indexes on each column you anticipate using in the where clause.

### Troubleshooting search_schema (10gR2)

February 28, 2008 - 10:03 pm UTC

Reviewer: Stephan Uzzell from Columbia, MD USA

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.

Thanks,
Stephan

Followup

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

March 01, 2008 - 7:20 pm UTC

Reviewer: Stephan Uzzell from Columbia, MD USA

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

Followup

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

September 28, 2009 - 11:51 am UTC

Reviewer: Sameer from Pune, India

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

October 28, 2009 - 1:52 pm UTC

Reviewer: JGP from MD USA

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*

Followup

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

October 29, 2009 - 7:29 am UTC

Reviewer: JGP from MD USA

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?

Followup

October 29, 2009 - 10:11 am UTC

dynamic sql probably.

that is how I handle my advanced search on this site.

### how to handle conditional input params

October 29, 2009 - 1:41 pm UTC

Reviewer: JGP from MD USA

Thanks for the link.. very useful.Do you use Oracle Text and substring indexes for your Advanced Search as well?

Thanks,
JGP

Followup

October 29, 2009 - 2:00 pm UTC

not substring, I do keyword searching only...

but yes, I use text..

### OK

November 03, 2009 - 4:28 am UTC

Reviewer: Siva

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

Followup

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

November 07, 2009 - 5:07 pm UTC

Reviewer: JerrQ from Killaloe

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)

December 07, 2010 - 7:32 am UTC

Reviewer: Martin Rose

/|\
|
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) ?

Followup

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

For leading and trailing, you'll want to use a substring prefix index with TEXT

### wildcard search

December 07, 2010 - 4:53 pm UTC

excellent

### veiw the existing attributes of ctx index

January 30, 2013 - 7:07 am UTC

Reviewer: Tim

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

Followup

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;

-------------------------------------------------------------------------------

CREATE INDEX "OPS\$TKYTE"."SEARCH_IDX" ON "OPS\$TKYTE"."T" ("COL1")
INDEXTYPE IS "CTXSYS"."CONTEXT"  PARAMETERS ('wordlist SUBSTRING_PREF MEMORY
50M')

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

Followup

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

February 08, 2013 - 3:57 am UTC

Perfect! Many thanks Tom

### single character search

March 20, 2013 - 9:19 am UTC

Reviewer: Mike R.

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?

Followup

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?

April 10, 2013 - 11:40 am UTC

Reviewer: Mike R. from Dallas

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

January 12, 2018 - 11:42 am UTC

Reviewer: Lal Cyril

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.

Followup

January 12, 2018 - 2:49 pm UTC

"One" is part of the default stoplist:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ccref/oracle-text-supplied-stoplists.html#GUID-963AB8F0-6244-4D37-A1D9-4AAC2974BEA1

This means it's excluded from the index.

To resolve this, you can create your own stoplist and use that:

exec ctx_ddl.create_stoplist('new_stop', 'basic_stoplist');

alter index search_idx rebuild
parameters ('replace stoplist new_stop');

select * from template where contains( tmp_name,'one',1)>0
and    rownum <= 5;

TMP_ID   TMP_NAME
1 one
21 twenty-one
31 thirty-one
41 forty-one
51 fifty-one

### build text index from another schema

October 17, 2018 - 9:02 am UTC

Reviewer: Rajeshwaran Jeyabal

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

Followup

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

October 31, 2018 - 4:23 am UTC

Reviewer: Rajeshwaran Jeyabal

Team,

i am new to Oracle Text and where should i begin with?

go with Oracle Text application developers guide
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ccapp/index.html

or go with Oracle Text reference first?
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ccref/index.html