Oracle insert update expensive

I never use the ID field for lookup, because my application is always based on working with the Name field. Again - I know that in the second example the ID is changed, but it does not matter for my application. The bigger the table number of and size of columns the more expensive it becomes to delete and insert rather than update.

Besides, it is plain wrong from a business point of view. Consider how much harder it would be to understand a notional audit trail on that table. There are some scenarios involving bulk updates of all the rows in a table where it is faster to create a new table using CTAS from the old table applying the update in the the projection of the SELECT clause , dropping the old table and renaming the new table. The side-effects are creating indexes, managing constraints and renewing privileges, but it is worth considering.

One command on the same row should always be faster than two on that same row. These are the cases when. Just tried updating 43 fields on a table with 44 fields, the remaining field was the primary clustered key.

You have defined a primary key, it will likely automatically become a clustered index at least SQL Server does so. A cluster index means the records are physically laid on the disk according to the index.

DELETE operation itself won't cause much trouble, even after one record goes away, the index stays correct. But when you INSERT a new record, the DB engine will have to put this record in the correct location which under circumstances will cause some "reshuffling" of the old records to "make place" for a new one.

There where it will slow down the operation. An index especially clustered works best if the values are ever increasing, so the new records just get appended to the tail. Anything else is incorrect. If you're going to break the rules of how code should work, then you'd better have a damn good, quantified reason for it, and not a vague idea of "This way is faster", when you don't have any idea what "faster" is.

What if you have a few million rows. Each row starts with one piece of data, perhaps a client name. As you collect data for clients, their entries must be updated. Now, let's assume that the collection of client data is distributed across numerous other machines from which it is later collected and put into the database. If each client has unique information, then you would not be able to perform a bulk update; i. On the other hand, you could perform bulk inserts. So, the question might be better posed as follows: Is it better to perform millions of single updates, or is it better to compile them into large bulk deletes and inserts.

Since in-memory ops are mostly trivial anyways, given a hard-drive based database, an UPDATE can change a database field in-place on the hdd, while a delete would remove a row leaving an empty space , and insert a new row, perhaps to the end of the table again, it's all in the implementation. The other, minor, issue is that when you UPDATE a single variable in a single row, the other columns in that row remain the same.

Doing a lazy optimization on the background will allways free unused blocks and pack the table altogether. It depends on the product. Delete: a row must be removed, indexes updated, foreign keys checked and possibly cascade-deleted, etc. Insert: a row must be allocated - this might be in place of a deleted row, might not be; indexes must be updated, foreign keys checked, etc.

Update: one or more values must be updated; perhaps the row's data no longer fits into that block of the database so more space must be allocated, which may cascade into multiple blocks being re-written, or lead to fragmented blocks; if the value has foreign key constraints they must be checked, etc. Sure, maybe you have no FK constraints now, but will that always be true?

The crossed out row is deleted later. Both inserts and updates can cause page-splits in this way, they both effectively 'add' data, it's just that updates flag the old stuff out first. On top of this updates need to look up the row first, which for lots of data can take longer than the update.

Inserts will just about always be quicker, especially if they are either in order or if the underlying table doesn't have a clustered index. When inserting larger amounts of data into a table look at the current indexes - they can take a while to change and build. Adding values in the middle of an index is always slower. You can think of it like appending to an address book: Mr Z can just be added to the last page, while you'll have to find space in the middle for Mr M.

Doing the inserts first and then the updates does seem to be a better idea for several reasons. You will be inserting at a time of low transaction volume. Since inserts have more data, this is a better time to do it. Since you are using an id value which is presumably indexed for updates, the overhead of updates will be very low.

You would also have less data during your updates. Finally, test this out to see the actual performance on your server before making a final decision. For updates, the impact will be lessened if the updates are affecting fixed-length fields. If updating varchar or blob fields, you may add a cost of page splits during update when the new value surpasses the length of the old value.

I think inserts will run faster. They do not require a lookup when you do an update you are basically doing the equivalent of a select with the where clause. And also, an insert won't lock the rows the way an update will, so it won't interfere with any selects that are happening against the table at the same time. Define an external table that exactly matches your flat file. Then, you should be able to solve this with two passes, one insert, one update.

Note that you can't actually have columns named 'date' or 'number', so, the sql above isn't actually going to work as written. You'll have to change those column names. If you use an external table approach then you can join the data in the external table to itself to produce a single record that can then be inserted. Although the join is expensive, overall it ought to be an efficient process as long as the hash join I'd expect to be used stays in memory.

That assumes that there will always be an end date for every start date, and that the number does not already exist in the table -- if either of those conditions is not true then an outer join would be required in the former case, and a merge required in the latter.

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Insert and update rows from a file in oracle Ask Question.

Learn more. Asked 2 days ago. Active 2 days ago. Viewed 25 times. Improve this question. Please provide table definition incl. PK definition , sample data and expected result. Most probably you will need to use two statements. Can you give me the example, please — dodo.