Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: faster SELECT when using LIKE '%...%'

    Is there a way to speed up a select like:

    Code:
    select * 
    from adress_table
    where addr_line1 like '%FIRST AVE%'
    I realize why the INDEX is not helping out, but I was wondering if there was a way that others have found that could speed this up. For example, creating an index on each word in the address?

    I'm at the extreme starting point, and just looking for pointers on where to head from here.

    Thanks,
    Chuck

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    One method I've tried that seems to be a bit faster is to do this:

    Code:
    SELECT * 
    FROM adress_table
    WHERE INSTR( addr_line1, 'FIRST AVE' ) <> 0;
    Of course, you could create a ConText index:

    Code:
    CREATE INDEX emp_resume_idx 
       ON emp(resume) INDEXTYPE IS ctxsys.context;
    Then you could execute a query like this:

    Code:
    SELECT resume 
    FROM emp 
    WHERE CONTAINS (resume, 'DBA') > 0;
    The only problem witha ConText index is that you have rebuild it as often as you update/insert data to keep it completely up to date.

    Hope this helps!
    JoeB
    save disk space, use smaller fonts

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •