Re: Function Query

On Oct 24, 10:51 am, artme..._at_gmail.com wrote:
> Hi,
>
> I have a complex query which uses a regular expression.  Is it
> possible to somehow create an index or something to speed up the
> queries?  We're on 10g.  I've read a bit about Oracle Text but not
> sure if that is for us....
>
>     'SELECT * FROM (
>        SELECT ticker, comp_name FROM (
>          SELECT ticker, comp_name, 1 ord, ROW_NUMBER()
>          OVER (PARTITION BY DECODE(ticker,NULL,1,0) ORDER BY ticker)
> cnt
>          FROM master_table
>          WHERE REGEXP_LIKE (UPPER(ticker), ''^' ||  p_pattern || ''')
>          UNION
>          SELECT ticker, comp_name, 2 ord, ROW_NUMBER()
>          OVER (PARTITION BY DECODE(ticker,NULL,1,0) ORDER BY
> comp_name) cnt
>          FROM master_table
>          WHERE REGEXP_LIKE (UPPER(comp_name), ''^' || p_pattern || '|
> ' || p_pattern || ''')
>          )
>        ORDER BY ord
>        )
>     WHERE rownum <= ' || p_num_recs;
>
> Thank you.

Well, to close this, I decided to use Oracle Text and have it working (just about). Received on Fri Oct 24 2008 - 12:59:08 CDT