 | |
#1
 
| |||
| |||
|
Function Query -
10-24-2008
, 10:51 AM
#2
| |||
| |||
|
|
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).
#3
| |||
| |||
|
|
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).
#4
| |||
| |||
|
|
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).
#5
| |||
| |||
|
|
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).
#6
| |||
| |||
|
|
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; |
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
#7
| |||
| |||
|
|
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; |
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
#8
| |||
| |||
|
|
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; |
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
#9
| |||
| |||
|
|
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; |
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
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.