2 Replies Latest reply: Dec 3, 2012 5:10 PM by 946515 RSS

    to cast or not to cast total varchar2 length of function resultsfor index?

      I have a table where a set of four fields plus a status must have only one 'current' status but may have many records of 'history' status. Meanwhile there's a synthetic key from a sequence independent from status. I wrote a function to index like this:

      create unique index funcindex on mytable
      (case status
      when 'history'
      then to_char(synthetic_key)
      when 'current'
      then col1 || col2 || col3 || col4

      This appears to work, however, I'm curious how a data type is assigned to it and whether that matters. The synthetic key is a number but it is converted with "to_char" which doesn't give a max length. cols 1 - 4 are varchar2's of varying lengths.
      Is there any need or benefit to adding up the lengths of cols 1 - 4 and casting the result of the case as varchar2(sometotal) or should the system really be left to figure it out on its own?

      PS - I'm using 11g and there's nothing special about the table; just a sequence and pk on the synth key.