Function Query

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
       )

Thank you. Received on Fri Oct 24 2008 - 10:51:28 CDT