"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
