insert/update

[bobby]

hi ,
i am using

INSERT INTO tablename (col1, curr_flg , start_dt, end_date)
VALUES (myseq.NEXTVAL, :1, :2,:3);

-
in my first insert it insert 100 rows
now i wanna update end_ date to start_dt when curr_flg is 1

i am some question
1) when i just do simple insert its like 300 rows/sec
2) when i update it its alomost 0 rows/sec anf i have to stop the process
3) in one table i update all 100 rows where insetred i mean 100 in first insert and 100 again (100+100)
plz advise.
Thanks
bobby

[rasi]

hi

search for insert/update discussion in this forum this was covered so many times.

Try not to do insert and update in the same job. Seperate the inserts and updates into two streams and then have a separate jobs to do update and inserts.

Thanks
Siva

[bobby]

HI,
THATS WHT I AM TRYING TO DO 2 OUTPUT TARGET
THNKS
BOBBY

[rasi]

Write those targets to a text file and then create a job to do your insert and update

Thanks
Siva

[Neil C]

Bobby, check your indexes on tablename. If curr_flg is not the initial field in an index, then you will be doing a table space scan on the whole of tablename to do each update. If the table is large, performance will be very bad. How large is the table?

Please do an explain on your SQL to find out how it is accessing the table. It is good practice to run an explain on every bit of new SQL that you code to ensure that it will perform as you expect.