Avoid Commit after every Insert that requires a SELECT

[KhalidMehmoodAwan]

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 -------- 06 Jun
07-Jun -------- 11 Jun
12-Jun -------- 16 Jun
.........
26-Jun -------- 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

 

[ChrisHunt]

Your friend is correct - within the same session you can SELECT rows which have been inserted but not yet COMMITted. They only become visible to other sessions after a commit.

Rather than SELECTing first and then inserting, you may be better off creating a unique index on (SiteName, OccuranceDate) and ignoring duplicate inserts with a bit of PL/SQL:

BEGIN
   INSERT INTO my_table
          (SiteName,OccuranceDate)
   VALUES (somewhere,sometime)
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      NULL;
END;

Alternatively, you could use Oracle's (relatively) new MERGE command

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[LKBrwnDBA]

And yes, you can commit data after say each 500 (or more) inserted rows.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

????? ???? ? ????? ??? ?? ?? - ?????. ???? ?? ?????? ???? ???.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Beilstwh]

if the version of your database is 10G or above then use can use dml error logging on the insert after creating your unique index on sitname and occurancedate. See the following link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2226

Bill
Lead Application Developer
New York State, USA