Re: Function Query

From: Peter Nilsson <airia_at_acay.com.au>
Date: Sun, 26 Oct 2008 19:00:11 -0700 (PDT)
Message-ID: <6e9bf294-5c61-4a10-b590-29c6005b4c56@t39g2000prh.googlegroups.com>

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 (
>        SELECTticker, comp_name FROM (
>          SELECTticker, comp_name, 1 ord, ROW_NUMBER()
>          OVER (PARTITION BY DECODE(ticker,NULL,1,0) ORDER BYticker)
> cnt
>          FROM master_table
>          WHERE REGEXP_LIKE (UPPER(ticker), ''^' ||  p_pattern || ''')
>          UNION
>          SELECTticker, 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;

Did you try my previous suggestion...?

  with
    x as

      (select distinct
         ticker, comp_name,
         case
           when regexp_like(ticker,    '^mot| mot', 'i') then 1
           when regexp_like(comp_name, '^mot| mot', 'i') then 2
         end x
       from master_table)

  select ticker, comp_name
    from (select x.*, row_number() over (order by x.x) rn

            from x x
           where x.x is not null)

  where rn <= 10;

--
Peter

Received on Sun Oct 26 2008 - 21:00:11 CDT