Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69

    Unanswered: To trigger or not to trigger

    Oracle 11.1

    I have an issue where the vendor application inserts the timestamp for all records in a proprietary char format. Plus Time Zone Diff

    APPTIMESTAMP
    TMZDIFF

    I need to convert this into DATE format for ease of reporting and in order to use range partitioning.

    I have a function to do this
    function(APPTIMESTAMP, TMZDIFF) = DATE

    so I wanted to add a column REALDATE and use a BEFORE INSERT TRIGGER to populate this column.

    My only issue is, I'm concerned about the impact/performance of this trigger and if there is a better way of doing this because some tables will have over 10 million inserts a day. I was also looking at making this column a default value but it doesn't appear Oracle will support functions for column defaults.


    Thanks,

    Charlie

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I need to convert this into DATE format for ease of reporting
    It is unwise & wasteful to store computed value in a static table.
    Why not CREATE VIEW that includes the desired date column?
    This does not change the original table & does not "waste" disk space.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69
    Quote Originally Posted by anacedent View Post
    >I need to convert this into DATE format for ease of reporting
    It is unwise & wasteful to store computed value in a static table.
    Why not CREATE VIEW that includes the desired date column?
    This does not change the original table & does not "waste" disk space.
    Agreed. But in this case I want to implement range partitioning because some of these tables have exceeded 100 million records. Ideally use Interval partitioning by day. So I need to convert to a date datatype. Unless I am missing something...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post result via COPY & PASTE from SQL below

    SELECT * FROM V$VERSION;

    BTW, 100 million rows is not considered large by me or others.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69
    Quote Originally Posted by anacedent View Post
    post result via COPY & PASTE from SQL below

    SELECT * FROM V$VERSION;

    BTW, 100 million rows is not considered large by me or others.
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    PL/SQL Release 11.1.0.7.0 - Production
    "CORE 11.1.0.7.0 Production"
    TNS for Solaris: Version 11.1.0.7.0 - Production
    NLSRTL Version 11.1.0.7.0 - Production


    One table is over 600 million records, either way, the prune job (DELETES through application) has become ineffective and we would like to move to dropping partitions instead.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    don't use a trigger, use a virtual column (new to 11g) and calculate on the fly. The VC can also be used for partitioning

    Oracle 11g Database New Features
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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