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