Oracle Text with Numbers

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Michael
    #1

    Oracle Text with Numbers

    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
    Tags: None
    • Jim Kennedy
      #2
      Re: Oracle Text with Numbers


      "Michael" <mschott14@hotm ail.comwrote in message
      news:e7b6653e.0 405161236.7d7df c87@posting.goo gle.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


        Comment

        • Michael
          #3
          Re: Oracle Text with Numbers

          "Jim Kennedy" <kennedy-downwithspammer sfamily@attbi.n etwrote in message news:<utQpc.619 46$z06.8535347@ attbi_s01>...
          "Michael" <mschott14@hotm ail.comwrote in message
          news:e7b6653e.0 405161236.7d7df c87@posting.goo gle.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,sugges tions?

          Thanks, Michael

            Comment

            • Jim Kennedy
              #4
              Re: Oracle Text with Numbers


              "Michael" <mschott14@hotm ail.comwrote in message
              news:e7b6653e.0 405162104.73cd4 549@posting.goo gle.com...
              "Jim Kennedy" <kennedy-downwithspammer sfamily@attbi.n etwrote in message
              news:<utQpc.619 46$z06.8535347@ attbi_s01>...
              "Michael" <mschott14@hotm ail.comwrote in message
              news:e7b6653e.0 405161236.7d7df c87@posting.goo gle.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,sugges tions?
              >
              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




                Comment

                Previous template Next
                Working...