Avoid Commit after every Insert that requires a SELECT
Hi everybody,
Here is the problem:
I have a table of generator alarms which is populated daily. On daily basis there are approximately 50,000 rows to be inserted in it.
Currently i have one month's data in it ... Approximately 900,000 rows.
here goes the main problem.
before each insert command, whole table is checked if the record does not exist already. Two columns "SiteName" and "OccuranceDate" are checked... this means, these two columns are making a unique record when checked together with an AND operation in WHERE clause.
we have also implemented partition on this table. and it is basically partitioned on the basis of OccuranceDate and each partition has 5 days' data.
say
01-Jun to 06 Jun
07-Jun to 11 Jun
12-Jun to 16 Jun
and so on
26-Jun to 30 Jun
NOW:
we have a commit command within the insertion loop, and the each row is committed once inserted, making approximately 50,000 commits daily.
Question:
Can we commit data after say each 500 inserted rows, but my real question is can we Query the records using SELECT which are Just Inserted but not yet committed ?
a friend told me that, you can query the records which are inserted in the same connection session but not yet committed.
Can any one help ?
Sorry for the long question but it was to make u understand the real issue. :(
Khalid Mehmood Awan
khalidmehmoodawan @ gmail.com
Edited by: user5394434 on Jun 30, 2009 11:28 PM