By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,704 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,704 IT Pros & Developers. It's quick & easy.

Oracle Text with Numbers

P: n/a
Hello,

I need to search in a number column for particular "subnumbers". For
example I have a column with 3453454 in it an I like to searh for the
number "53" in it. I know I could use

select * from table where number_column like '%53%'

but since the table is rather big I'd like to use Oracle Text for it
and query like

select * from table where contains(number_column, 53) > 0

but above query would return NULL after converting the number column
to a varchar2 column! Only full numbers are indexed and therefore only
a search on the full number 3453454 would yield a result. What are my
options to make above query with "contains" clause work?

Thanks in advance
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"Michael" <ms*******@hotmail.com> wrote in message
news:e7**************************@posting.google.c om...
Hello,

I need to search in a number column for particular "subnumbers". For
example I have a column with 3453454 in it an I like to searh for the
number "53" in it. I know I could use

select * from table where number_column like '%53%'

but since the table is rather big I'd like to use Oracle Text for it
and query like

select * from table where contains(number_column, 53) > 0

but above query would return NULL after converting the number column
to a varchar2 column! Only full numbers are indexed and therefore only
a search on the full number 3453454 would yield a result. What are my
options to make above query with "contains" clause work?

Thanks in advance

Oracle text won't help since Oracle Text is looking for words not
substrings. I can't think of a Mathmatical transformation that would then
use an index on the column. Is it always 53 or could it be any substring?
If it was always 53 then you could have a function based index. I think you
are going to have to do:
select * from table where to_char(number_column) like '%53%' and have to do
a full table scan.
Jim
Jul 19 '05 #2

P: n/a
"Jim Kennedy" <ke****************************@attbi.net> wrote in message news:<utQpc.61946$z06.8535347@attbi_s01>...
"Michael" <ms*******@hotmail.com> wrote in message
news:e7**************************@posting.google.c om...
Hello,

I need to search in a number column for particular "subnumbers". For
example I have a column with 3453454 in it an I like to searh for the
number "53" in it. I know I could use

select * from table where number_column like '%53%'

but since the table is rather big I'd like to use Oracle Text for it
and query like

select * from table where contains(number_column, 53) > 0

but above query would return NULL after converting the number column
to a varchar2 column! Only full numbers are indexed and therefore only
a search on the full number 3453454 would yield a result. What are my
options to make above query with "contains" clause work?

Thanks in advance

Oracle text won't help since Oracle Text is looking for words not
substrings. I can't think of a Mathmatical transformation that would then
use an index on the column. Is it always 53 or could it be any substring?
If it was always 53 then you could have a function based index. I think you
are going to have to do:
select * from table where to_char(number_column) like '%53%' and have to do
a full table scan.
Jim


Thanks for your prompt reply.

it could be any substring, thats why I need sth.like above query

select * from table where contains(number_column, '234') > 0

there must be a (better) solution to avoid the full table scan. Any
other opinions,suggestions?

Thanks, Michael
Jul 19 '05 #3

P: n/a

"Michael" <ms*******@hotmail.com> wrote in message
news:e7**************************@posting.google.c om...
"Jim Kennedy" <ke****************************@attbi.net> wrote in message

news:<utQpc.61946$z06.8535347@attbi_s01>...
"Michael" <ms*******@hotmail.com> wrote in message
news:e7**************************@posting.google.c om...
Hello,

I need to search in a number column for particular "subnumbers". For
example I have a column with 3453454 in it an I like to searh for the
number "53" in it. I know I could use

select * from table where number_column like '%53%'

but since the table is rather big I'd like to use Oracle Text for it
and query like

select * from table where contains(number_column, 53) > 0

but above query would return NULL after converting the number column
to a varchar2 column! Only full numbers are indexed and therefore only
a search on the full number 3453454 would yield a result. What are my
options to make above query with "contains" clause work?

Thanks in advance

Oracle text won't help since Oracle Text is looking for words not
substrings. I can't think of a Mathmatical transformation that would then use an index on the column. Is it always 53 or could it be any substring? If it was always 53 then you could have a function based index. I think you are going to have to do:
select * from table where to_char(number_column) like '%53%' and have to do a full table scan.
Jim


Thanks for your prompt reply.

it could be any substring, thats why I need sth.like above query

select * from table where contains(number_column, '234') > 0

there must be a (better) solution to avoid the full table scan. Any
other opinions,suggestions?

Thanks, Michael


Think about it. How would one index a number (even represented as a string)
to be able to quickly search for any matching substring? (Your use of
contains as an example isn't how contains works. It is contains in a
document or string of characters delimited by spaces. 12345 isn't delimited
by spaces to search for 234 so nothing would be returned.) If this isn't
an OLTP system and if you have a limited number of decimal places (eg all
numbers are less than 1 million) then you could split the number into
seperate columns and use a bitmapped index on each column. Like:

myNumberCol Hundred1000s ten1000s thousands hundreds tens ones
12345 0 1 2
3 4 5
543215 5 4 3
2 1 5

then the select would be
select * from mytable where (Hundred1000s='2' and ten1000s='3' and thousands
='4') or
(ten1000s='2' and thousands='3'
and hundreds ='4') or
(thousands='2' and hundreds
='3' and tens ='4') or
(hundreds ='2' and tens ='3'
and ones='4')

Using a bitmapped index would be fast. (only 10 values in a column and it
can and them together) But don't do it in an oltp environment.
Jim


Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.