Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Hartland, CT USA
    Posts
    3

    Unanswered: Best practice question

    When designing a database that tracks data by accounting period, is it generally better to split the fields into a year field (varchar2(4)) and a period field (varchar2(2)), or is it better to concatenate the fields into a single varchar2(7) or varchar2(6) (with or without a hyphen, i.e. '2004-05' or '200405')?

    I find that the concatenation in a view or query of year and period, especially when joining to another table via year and period, is slow but required in order to get the data sorted correctly. We are dealing with millions of records.

    Is there a way to take advantage of separate fields and use a dual field index? If so, how would I write the join clause to take advantage of such an index? My join clause now is typically "where t1.year = t2.year and t1.period = t2.period"

  2. #2
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote Originally Posted by wayneessel
    When designing a database that tracks data by accounting period, is it generally better to split the fields into a year field (varchar2(4)) and a period field (varchar2(2)), or is it better to concatenate the fields into a single varchar2(7) or varchar2(6) (with or without a hyphen, i.e. '2004-05' or '200405')?

    I find that the concatenation in a view or query of year and period, especially when joining to another table via year and period, is slow but required in order to get the data sorted correctly. We are dealing with millions of records.

    Is there a way to take advantage of separate fields and use a dual field index? If so, how would I write the join clause to take advantage of such an index? My join clause now is typically "where t1.year = t2.year and t1.period = t2.period"
    You can store them both ways. Enter separately in two fields and concatenate (hyphen or not) in one field in a trigger. Create an index on the concatenated field.
    But if you really want to be hight tech then use function based index
    http://download-west.oracle.com/docs...a_acc.htm#2185

    Good luck.
    My way or the highway. Yeah

  3. #3
    Join Date
    Dec 2003
    Location
    Hartland, CT USA
    Posts
    3

    Thumbs up Best practice question

    Thanks. I did not realize that function indices were possible. That looks like a good way to go. I prefer that over a trigger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •