Re: Substring searching on large table

Toni,
  the problem you have is that using substrings and   index is very likely NOT to be used.

  Oracle offers the CONTEXT option. This would enhance   the performance.

  I have always warned the user to use substrings, and     if they do they have to expect longer querytimes.

  In one project the cst refused to buy the CONTEXT option,     so after a business analysis it was decided that it     should be possible to search for words within the     attribute (column), but not for substrings of words.

    The rest was easy ... with every insert/update/delete

      a message was send into a Queue containing the
      ROWID and the attribute. The receipient
      dequeued the message and parsed the attribute and
      stored the words with the ROWID in an Index
      Organized table (IOT). As I designed it using ROWID
      no Cluster was needed.

      Update and Deletes maintained the IOT.

      I chose a asynchronous Queue knowing that the
      maintanence of the IOT would take longer then
      the cst was prepared to wait for the transaction.

    The query into that attribute was redirected to that     IOT, the result (ROWID) was used to get a resultset     for that attribute.

  This is of course a very specialized application, but   as I said ... the trade off when using LIKE is performance.

Regards
Dante

In article <7hms0r$ge6$1_at_news6.svr.pol.co.uk>,   "anthony burns" <tonyburns_at_trenchave9.freeserve.co.uk> wrote:
> I have a problem with searching on a very large table 3 million +
rows. I
> want to find substrings within a particular field within the table eg:
>
> select name from customer where street like '%AVENUE%'
>
> does anyone have any tips for this type of query. The query takes
about
> 30secs at the minute which is way too long, any tips would be much
> appreciated,
>
> cheers,
>
> Tony.
>
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Sun May 16 1999 - 15:53:26 CDT