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

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, 
 

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

 
 

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

- 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 
 

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

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) 
 

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. 
 

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; 
 

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

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
 
 

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
 
 

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, 
 

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? 
 

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;
PL/SQL procedure successfully completed.
SQL> create index search_idx on T(col1)
  2  indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M');
Index created.
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> select * from t where contains( col1,'%750776%')>0;
no rows selected
SQL> drop index search_idx;
Index dropped.
SQL> create index search_idx on T(col1)
  2  indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M');
Index created.
SQL> select * from t where contains( col1,'%750776%')>0;
COL1                      COL2
------------------------- ---------------
36IDDZ7507768NJ           3984397384
37IPZD7507761NJ           3984388384
36IPZD7507762NJ           3984399384
36IPZD7507761NJ           3984399384
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. 
 

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) 
 

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


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 

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
  

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 

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

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 

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 

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
(: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*
 

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

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

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

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 

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

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');
begin
  ctx_ddl.create_preference('"SEARCH_IDX_FIL"','NULL_FILTER');
begin
  ctx_ddl.create_section_group('"SEARCH_IDX_SGP"','NULL_SECTION_GROUP');
begin
  ctx_ddl.create_preference('"SEARCH_IDX_LEX"','BASIC_LEXER');
begin
  ctx_ddl.create_preference('"SEARCH_IDX_WDL"','BASIC_WORDLIST');
  ctx_ddl.set_attribute('"SEARCH_IDX_WDL"','SUBSTRING_INDEX','YES');
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');
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');
begin
  ctx_output.start_log('SEARCH_IDX_LOG');
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;
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. 

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  

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. 

"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 
 

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

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

kindly advice. 

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

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. 

Comment
  
  
Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.