locked
Is trigger and sequence necessary for oracle table? RRS feed

  • Question

  • User-163012697 posted

    Hi all,

    I am having table which contains around 50,000 of records.  Now these records are inserted simply without any trigger or sequence created for that particular oracle database table.

    And one more thing I am not referencing it's serial number  anywhere in my code nor in query. So serial number in my table are as 1,2,4,8,11.....  (i.e. not in a proper way due to dml operations like delete.).

    So is it necessary for me to have a insert before trigger on this table using sequence.??

    My concern is that since that serial number is not needed, then why to give database more load by finding the last inserted serial no and insert the serial+1 number.

    Wednesday, January 27, 2016 5:23 AM

All replies

  • User197322208 posted

    then why to give database more load by finding the last inserted serial no and insert the serial+1 number.

    Because this is the way that a novice programmer does.(been there, done that)  He does not think about multiple users that use the application in the same time.

    Wednesday, January 27, 2016 6:05 AM
  • User-163012697 posted

    Dear ignatandrei,

      So what you are upto? Should I go for trigger or not?

    And ya one more thing, in one of my table there are more than 25 Crores records and serial numbers are added in the way as I said?

    And because of this it is creating to much load to table.

    First check the last inserted id, thereafter incr the id to +1 and then insert the record.     And since this is on realtime .... because of this some times cpu utilization also get much high some times upto 100%.

    Wednesday, January 27, 2016 6:51 AM
  • User269602965 posted

    I do not use Trigger and match.

    I create a Sequence object and then INSERT XXXX_SEQ.NEXT_VAL which auto increments.

    Oracle resolves contention if more than one user trying to insert at the same time.

    Sunday, January 31, 2016 7:12 PM