Re: Oracle Text with Numbers

"Michael" <mschott14_at_hotmail.com> wrote in message news:e7b6653e.0405162104.73cd4549_at_posting.google.com...
> "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message
news:<utQpc.61946$z06.8535347_at_attbi_s01>...
> > "Michael" <mschott14_at_hotmail.com> wrote in message
> > news:e7b6653e.0405161236.7d7dfc87_at_posting.google.com...
> > > 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'

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 Received on Mon May 17 2004 - 07:49:46 CEST