Hi Tom

I plan to develop an application for name and address search using Oracle text in 9i. I would like to know the concept behind the fuzzy match, how Oracle really does this fuzzy matching. If Oracle does not disclose the concept then at least what to except (and what not to expect) from the fuzzy match results.

This would be of great help for me to use the tool effectively in my application.

Thanks and regards

Pratap

Tom,

In the exapmple given:
SELECT ......FROM TITLE
WHERE ........Title LIKE '%Biography%'
we know the wild card search here wil search any combination of 'Biography' with any letters in front of or after it such as: 'my-Biography', 'BiographyForJohn'

If you use interMedia Text or Oracle Text search like shown in your example:

create index search_idx
on Title(title)
indextype is ctxsys.context
/

Now, you can search like this:

select *
from title
where contains( title, 'Biography' ) > 0

1. Does this select query search only for 'Biography' or it can search for 'my-Biography', 'BiographyForJohn' also? What kind of setting you have to use to make the text search possible like wild card search? Please give an example.
2. Is it dynamic ie seach sting can be anything eg Biblography?
3. In Database Configuration Assistant, oracle 9i, there is option for interMedia Text, Oracle Text, Does Oracle Text option will install all the text search capability you want or you have to choose both of them?

Thank you very much.

Tom,

Thank you so much for your prompt answer. I reviewed documents you have mentioned about near, fuzzy etc. Thats is a great help!

I have a table called OBJECT_DATA with 389 Million rows in it.
CREATE TABLE "CENTRA"."OBJECTS_DATA" ("OBJECT_ID" NUMBER NOT NULL,"ATTRIBUTE_ID" NUMBER NOT NULL, "FIELD_VALUE" VARCHAR2(80 byte), 
CONSTRAINT "CPK_OBJECTS_DATA" PRIMARY KEY("OBJECT_ID", 
    "ATTRIBUTE_ID")

When I do a query like this:
10:00:54 SQL> Select count(object_id) from objects where lower(title) like '%cable%';

COUNT(OBJECT_ID)
----------------
           87886

Elapsed: 00:00:44.02
SQL> Select object_id from objects where lower(title) like '%cable%';

87886 rows selected.

Elapsed: 00:11:12.09

It is very slow because of full table scan. So I installed intrMedia and Text from 9i R2 and created an Oracle Text index on title:
create index search_idx
on objects(title)
indextype is ctxsys.context;

I did the select qurey again:
13:44:00 SQL> select count(object_id) from objects where lower(title) like '%cable%';

COUNT(OBJECT_ID)
----------------
           87886

Elapsed: 00:00:44.02

SQL>Select object_id from objects where contains(title, ‘cable’) >0;
86733 rows selected.

Elapsed: 00:13:09.07

It was an index scan, but the queries takes more time to finish. 
1. Would you please tell me why it is getting slower with text index search? 
2. what can I do to speed up the search if interMedia text does or does not help here?
3. I tried this query with and without setting the pga and setting work area policy to auto/manual. Apprently by setting the above work area to auto further slowed down the query. Please note that I even tried pga b/w 25 MB to 500 MB. I used your queries to calculate pga mentioned under another heading.
Here is my init param:
pga_aggregate_target=25165824 # 25 MB. I tried upto 500  MB
sort_area_size=16777216
sort_area_retained_size=8000000
workarea_size_policy=AUTO #I tried auto and manual both.

Please also advise how to  configure pga for this issue.
I hope I am not asking too much :)


 

Tom,

I have a requirement to search data using wildcard for a column which has max length of varchar2(100). I want to use index search as full table scan is very slow while I am doing wild card searches.

Which one option is better between following

1) Using Intermedia Text
2) Using shadow column which has index on it and obviously I need to maintain this shadow column using trigger on the table.

Or is there any other better option that you can think of?

Tom,

Sorry, I just realized that shadow column will not be useful in this case.

As you asked:(
Also, is the wildcard search supposed to find %wn% or %wn c% in this string?)

Yes, wildcard search supposed to find both strings. I tested this, If I use intermedia text then it will use index for both strings.

What do you mean by this?
(The %wn c% would be problematic since that would not be something that can be gotten from that index.)

Do you mean that it will be problematic even if use intermedia text?

Thanks for your help.

Thanks a lot for your quick response.

So what is your final word on this?

As you said Intermedia Text is also expensive to fulfill the above requirement, so can you think of any better option/trick that we can use over here?

Thanks again.

Hi Tom,
The search conditions like "%wn c%" are very common with the search engines like google. So is there a different technology at play here? Or what we see is really "%wn" and "c%" (there aren't many results with exact match of "%wn c%", anyway).
Oracle Text and google searches are quite similar otherwise, both will ignore words that are too common like "in","and". Oracle Text gives an option of customisation of this as well.
Thanks

Tom,

I've heard previously (probably erroneously) that using LIKE without specifying a wildcard character is like using a direct =, i.e.

SELECT 1 FROM dual WHERE dummy LIKE 'x'

However, we have a lot of queries which utilise the LIKE operator against numeric columns, and from my testing it seems to be always treating the LIKE as a string operator, i.e.
SQL> DESC T
 Name         Null?    Type
 ------------ -------- ---------
 X                     NUMBER

SQL> EXPLAIN PLAN FOR
  2  SELECT x FROM t WHERE x LIKE 1;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | T           |       |       |       |
--------------------------------------------------------------------

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

   1 - filter(TO_CHAR("T"."X") LIKE '1')

Note: rule based optimization

14 rows selected.

As you can see, the TO_CHAR is applied to the column since it treats it always as a string. 

So, my question is, assuming that LIKE does always treat the
numeric as a string, what is your approach for ensuring that 
indexes on numeric columns which need to be searched, and which always uses LIKE, gets used? Is the answer (other than to rewrite the code, to use an = if no wildcards exist in the search condition) solely a function-based index?

Thanks in advance
 

you use LIKE on numbers?!?


anyway, you can create a function based index on to_char(T.X)



ops$tkyte@ORA9IR2> create table t as select * from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2> create index t_idx on t(to_char(object_id));
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where object_id like '5';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=98)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=98)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
 

Unfortunately, the application involves custom querying screens where the user is free to query on anything. In order to cater for this, the resultant SQL is generated dynamically, and is of the form, 'WHERE column LIKE :1', which works great
when the user queries on a character column, they can put anything from 'x' to '%x%x%' (don't ask!).

The problem is when this approach is used against numeric columns, then the indexes on the columns are not utilised, but they would be if the predicate was 'WHERE column = :1'.

Oh well, looks like a re-code of the application then for numeric columns!

Thanks for your help.

Would Oracle Text help for queries involving

where column not like '%something%'

Thanks

Tom,
I am trying to find from all tables and all columns in our database, which column has spaces in it. Not Nulls but spaces. Could be one space, 2 spaces or n..spaces.

Can we find it using LIKE operator? if so, what's the WildCard character for finding all spaces?

Thanks for your help

Tom,
using '% %' , won't it return spaces with any character on either side?
I just want to find columns with nothing else but Blank spaces [of any length]

Thanks,

ops$tkyte@ORA10GR1> create table t ( a int, x varchar2(5) );

Table created.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> insert into t values ( 1, null );

1 row created.

ops$tkyte@ORA10GR1> insert into t values ( 2, 'x x' );

1 row created.

ops$tkyte@ORA10GR1> insert into t values ( 3, ' x' );

1 row created.

ops$tkyte@ORA10GR1> insert into t values ( 4, 'x ' );

1 row created.

ops$tkyte@ORA10GR1> insert into t values ( 5, ' ' );

1 row created.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> column xxx format a5
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> select a,
  2         '"' || x || '"' ,
  3             '"' || translate( x, ' x', 'x ' ) || '"',
  4             '"' || rpad('x',length(x),'x') || '"' xxx
  5    from t
  6   where translate( x, ' x', 'x ' ) = rpad('x',length(x),'x' );

         A '"'||X| '"'||TR XXX
---------- ------- ------- -----
         5 " "     "x"     "x"

 

select 1
from dual
where trim(' ') is null

so in your case

where trim(COLUMN_NAME) is null

sorry, forgot to include the check to make sure it doesn't grab nulls.

so mine would be.

select 1
from dual
where trim(' ') is null
and ' ' is not null

which translates into

where trim(COLUMN_NAME) is null
and COLUMN_NAME is NOT NULL

for your purposes.

SELECT *
FROM MYTABLE
WHERE LENGTH( REPLACE( COLUMN, ' ', '' ) ) = 0

REPLACE( NULL, x, y ) = NULL
LENGTH( NULL ) = NULL
NULL = 0 -> NULL -> Not True (it's not false either)

Specially Tyler for his simple solution for finding Spaces in a varchar2.

Thanks

Tom,

I have the following scenario:
select -1 from vin_test A where A.business_name like ('%3 PRODUCTS INC%')
AND A.out_of_business_id != ('O');

VIN_TEST has an index on BUSINESS_NAME
there are 153K rows in the table of which only 1 is 3 PRODUCTS INC
134k have a out_of_business_id != ('O')
when I trace the query it uses the index without the
AND A.out_of_business_id != ('O');
but does not use it when I add it back in. Is this expected? Can I force it to always use the index. I tried adding the INDEX hint & it still ingored it.

Thanks

Tom,
Which is faster ?
title Like '%Biography%'
or Contains( title, 'Biography' ) > 0
or an intersection of two indexes ?

Scenario 1
I use a Like predicate with title. title is column 2 in a multi column index named myindex.
Another column in my query is column 1 of myindex and the query is indicated in an explain as using myindex via that column.
an intermedia index on title doesn't exist.
Scenario 2
myindex does not exist
an intermedia index on title exists.
Scenario 3
Both myindex and an intermedia index on title exists.

thank you

Hi

I have rows like this

'CON_ID 213434 OFF'
'CON_ID 1082374 OFF'
'CON_ID 664343 OFF'

I would like to get the numbers only so it would be

213434
1082374
664343

I have been able to produce the output by using several instr substr combinations, looks really awful and not readble.

Is there any easier way to do it?

beauty is in the eye of the beholder I guess.

ops$tkyte%ORA10GR2> select substr( x, instr( x, ' ', 1, 1 )+1,
  2                 instr( x, ' ', 1, 2 )- instr( x, ' ', 1, 1 ) )
  3    from t;

SUBSTR(X,INSTR(X,'',
--------------------
123425


doesn't hurt my eyes.  What hurts is that you seem to have loaded at least three attributes munged into a single string as a single attribute.  That looks awful and not usable to me :)  that should have been corrected at data entry (load) time.

you could look into regex (regular expressions) as well if you like, but the substr/instr works and can be every elegantly "hidden" via a view 

I used this for a query that used "... like '%...%'"

select /*+first_rows*/...

this way the index is used even through the 'like' clause. another option is to use the "/*+index*/" hint.

this solution requires an application code change but is good if you don't have context.

Hi Tom

Thanks for your great site serving as oracle knowledge hub and special thanks for your books, I bought all now. We have a query as below.

SELECT b.title,b.bookname, bp.purchase_site
FROM book b, book_purchases bp
WHERE title LIKE '%' || var1 || '%' 
AND bookname LIKE '%' || var2 || '%' 
AND b.book_id = bp.book_id

(Oracle version : 9iR2)
book is master table with 400 rows. Oracle thinks that after evaluating predicates produces only 1 row and uses merge catesian join on the detailed table which has 10000 rows. This is very slow for cases where user searches for very generic title and booknames resulting in 300 * 10000 rows in catesian product step of runtime execution plan. Can this be improved in anyways ? Can Oracle Text help in this scenario to provide better estimates ? Your guidance is most appreciated. This is my first query on this site, please let me know if these details are sufficient or an example with autotrace and tkprof output would be preferrable. Apologies if this question is supposed to be a new question, I will wait for my chance.

NOTE: LIKE '%biography%' should work similar to SQL LIKE syntax ignoring case, not word searching i.e. should output only 'My Biography', 'BusinessBiography', 'biography of Tom' etc.

Hi Tom

Here is autotrace output. I could not reproduce cartesian merge join, it's using nested loops now. I shall check what has changed in the environment, but autotrace guesses 1 row. Could you figure anything wrong from this ? Please let me know if more details are required.

tom@HOMEDB92> desc book;
Name Null? Type
-----------------------------------------------------
BOOK_ID NOT NULL VARCHAR2(10)
TITLE NOT NULL VARCHAR2(50)
BOOK_NAME NOT NULL VARCHAR2(100)
AUTHOR NOT NULL VARCHAR2(100)
PUBLISHER NOT NULL VARCHAR2(100)

tom@HOMEDB92> desc book_purchases;
Name Null? Type
-----------------------------------------------------
PURCHASE_ID NOT NULL VARCHAR2(30)
BOOK_ID NOT NULL VARCHAR2(10)
PURCHASE_SITE NOT NULL VARCHAR2(100)
SELLER_ID NOT NULL VARCHAR2(10)
PRICE NOT NULL NUMBER(5,2)
PURCHASE_DATE NOT NULL DATE
SHIP_STATUS NOT NULL VARCHAR2(1)
SHIP_DATE DATE

INDEXES CREATED
---------------
CREATE INDEX idx_bk_title ON book(title);
CREATE INDEX idx_bk_name ON book(book_name);

tom@HOMEDB92> BEGIN
2 dbms_stats.delete_schema_stats(USER);
3 dbms_stats.gather_schema_stats(USER,estimate_percent => NULL,method_opt => NULL,cascade => TRUE);
4 END;
5 /

PL/SQL procedure successfully completed.
tom@HOMEDB92> select count(*) from book where book_name like '%' || :v_name || '%';

COUNT(*)
----------
400

tom@HOMEDB92> select count(*) from book where title like '%' || :v_title || '%';

COUNT(*)
----------
400

tom@HOMEDB92> select count(*) from book;

COUNT(*)
----------
400

tom@HOMEDB92> select count(*) from book_purchases;

COUNT(*)
----------
10000

tom@HOMEDB92> set autotrace traceonly;
tom@HOMEDB92> variable v_title varchar2(50);
tom@HOMEDB92> variable v_name varchar2(100);
tom@HOMEDB92> exec :v_title := 'TITLE';

PL/SQL procedure successfully completed.

tom@HOMEDB92> exec :v_name := 'BOOK';

PL/SQL procedure successfully completed.

tom@HOMEDB92> SELECT b.title,
2 b.book_name,
3 bp.purchase_site
4 FROM book b,
5 book_purchases bp
6 WHERE b.title LIKE '%' || :v_title || '%'
7 AND b.book_name LIKE '%' || :v_name || '%'
8 AND b.book_id = bp.book_id
9 /

10000 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=40 Bytes=2720)
1 0 NESTED LOOPS (Cost=34 Card=40 Bytes=2720)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BOOK' (Cost=3 Card=1 Bytes=39)
3 2 INDEX (RANGE SCAN) OF 'IDX_BK_NAME' (NON-UNIQUE) (Cost=2 Card=4)
4 1 TABLE ACCESS (FULL) OF 'BOOK_PURCHASES' (Cost=31 Card=40 Bytes=1160)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
79974 consistent gets
0 physical reads
0 redo size
142230 bytes sent via SQL*Net to client
7825 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed


TKPROF Report
***************************
Rows Row Source Operation
------- -------------------------------------------------- 10000 NESTED LOOPS
400 TABLE ACCESS BY INDEX ROWID BOOK
400 INDEX RANGE SCAN IDX_BK_NAME (object id 30002)
10000 TABLE ACCESS FULL BOOK_PURCHASES

 Tom,

  I haven´t yet found a solution for my problem. I have a query like this:

  select ...
  from ...
  where
        nombreplanopers LIKE '%|%'  ||           INTS_PACK_GENERAL.INTS_FUNC_TEXTOPLANO(:v5) ||
               '%|%'||  INTS_PACK_GENERAL.INTS_FUNC_TEXTOPLANO(:v6) || '%' 

  and I want to transform it into a new predicate using Intermedia Text, but I don´t know how can I do it because Intermedia ignores the '|' symbol and I want to keep it in my searches. As example, this is another table in the same situation:

    SQL> select * from t;

C
--------------------
How#Now#Red#Cow
How Now Red Cow
How # Now Red Cow
jorge|perez
on-demand

SQL> SELECT * FROM  t WHERE  CONTAINS (c,'{jorge}', 1) > 0;

C
--------------------
jorge|perez      ----> OK


SQL> SELECT * FROM  t WHERE  CONTAINS (c,'%' || '|' || '%') > 0;

C
--------------------
How#Now#Red#Cow
How Now Red Cow              NO!  
How # Now Red Cow
jorge|perez
on-demand

 I don´t want avoid the '|' symbol. How can I do this?

   Thank you Tom!