From: kennedy-downwithspammersfamily@attbi.net   
      
   "Michael"  wrote in message   
   news:e7b6653e.0405162104.73cd4549@posting.google.com...   
   > "Jim Kennedy"  wrote in message   
   news:...   
   > > "Michael"  wrote in message   
   > > news:e7b6653e.0405161236.7d7dfc87@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'   
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)