Home » Server Options » Text & interMedia » Fulltext functional lookup on BLOB (Oracle 10g SE)
Fulltext functional lookup on BLOB [message #546073] Mon, 05 March 2012 03:54 Go to next message
munis428
Messages: 1
Registered: March 2012
Junior Member
Hi,

does Oracle support functional lookup in fulltext index (CONTEXT) on a BLOB column? It works fine on a VARCHAR2 column but I can't get it working on a BLOB.

As an example, assume that there's 'id' column (primary key), fulltext-indexed 'textV' column (varchar2) and fulltext-indexed 'textB' column (blob). If I perform
select * from tbl where id in (1,2) and contains(textV, 'something')>0;

Oracle picks 2 rows by 'id' and then performs functional lookup to see whether these 2 rows match the Oracle Text query. If I do the same on a 'textB' column, Oracle pulls many rows from Oracle Text query and then hash-joins it with rows with ids 1 and 2. Both index and table are quite big (with possibly many matched rows for fulltext search) so this performs much slower than functional lookup.

Using query hints made no difference.
Re: Fulltext functional lookup on BLOB [message #546083 is a reply to message #546073] Mon, 05 March 2012 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 58911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A text search on BINARY data seems silly to me.

Regards
Michel
Re: Fulltext functional lookup on BLOB [message #546235 is a reply to message #546073] Mon, 05 March 2012 21:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
If you upgrade to Oracle 11g, then you can use FILTER BY and SDATA to query both columns with one index hit, similar to the way you would use catsearch with an index set. Please see the demonstration below.

SCOTT@orcl_11gR2> create table tbl
  2    (id     number primary key,
  3  	textb  blob)
  4  /

Table created.

SCOTT@orcl_11gR2> insert all
  2  into tbl values (1, utl_raw.cast_to_raw ('something'))
  3  into tbl values (2, utl_raw.cast_to_raw ('something'))
  4  select * from dual
  5  /

2 rows created.

SCOTT@orcl_11gR2> insert into tbl
  2  select distinct object_id,
  3  	    utl_raw.cast_to_raw ('something ' || object_name)
  4  from   user_objects
  5  where  object_id > 2
  6  /

740 rows created.

SCOTT@orcl_11gR2> create index tbl_idx on tbl (textb)
  2  indextype is ctxsys.context
  3  FILTER BY ID
  4  /

Index created.

SCOTT@orcl_11gR2> set autotrace on explain
SCOTT@orcl_11gR2> select id, utl_raw.cast_to_varchar2 (textb)
  2  from   tbl
  3  where  contains
  4  	      (textb,
  5  	       'something AND
  6  		(SDATA (ID = 1) OR
  7  		 SDATA (ID = 2))') > 0
  8  /

        ID
----------
UTL_RAW.CAST_TO_VARCHAR2(TEXTB)
------------------------------------------------------------------------------------------------------------------------
         1
something

         2
something


2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4198504031

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |  2027 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TBL     |     1 |  2027 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | TBL_IDX |       |       |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("CTXSYS"."CONTAINS"("TEXTB",'something AND            (SDATA (ID
              = 1) OR             SDATA (ID = 2))')>0)

Note
-----
   - dynamic sampling used for this statement (level=2)

SCOTT@orcl_11gR2>

Re: Fulltext functional lookup on BLOB [message #546236 is a reply to message #546083] Mon, 05 March 2012 21:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Mon, 05 March 2012 02:39
A text search on BINARY data seems silly to me.


It is common to use Oracle Text to index and search things like pdf and word documents that have been loaded into a blob column.
Re: Fulltext functional lookup on BLOB [message #546245 is a reply to message #546236] Mon, 05 March 2012 23:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If Oracle can do it, I'm really amazed!
Does it take care of these tools tags (like the search box of Word, that color, font...)?

Regards
Michel

[Updated on: Mon, 05 March 2012 23:13]

Report message to a moderator

Re: Fulltext functional lookup on BLOB [message #546387 is a reply to message #546245] Tue, 06 March 2012 07:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Mon, 05 March 2012 21:12


Does it take care of these tools tags (like the search box of Word, that color, font...)?


Yes, the ctxsys.auto_filter filters out everything but the text of the document, then just indexes the text.

edit: removed the word default as auto_filter is not default but must be specified

[Updated on: Tue, 06 March 2012 08:10]

Report message to a moderator

Re: Fulltext functional lookup on BLOB [message #546390 is a reply to message #546387] Tue, 06 March 2012 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 58911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wow! I'm really impressed, they do a good job!

Regards
Michel
Re: Fulltext functional lookup on BLOB [message #546391 is a reply to message #546390] Tue, 06 March 2012 08:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
Here is a link that shows what document formats are supported on what platforms, if you're interested.

http://docs.oracle.com/cd/E11882_01/text.112/e24436/afilsupt.htm
Re: Fulltext functional lookup on BLOB [message #546398 is a reply to message #546391] Tue, 06 March 2012 08:58 Go to previous message
Michel Cadot
Messages: 58911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks, it is a domain where I know nothing, I will read it with interest.

Regards
Michel
Previous Topic: ORA-06521-PL/SQL: Error mapping function while running catsearch
Next Topic: full text search in oracle (merged 2)
Goto Forum:
  


Current Time: Wed Aug 27 14:24:29 CDT 2014

Total time taken to generate the page: 0.16731 seconds