On Jan 2, 3:06 am, James Borg <[email protected]> wrote: > In my opinion it all depends on idexes you have. > > If when doing a select/update you are using a where clause on an > indexed column then it will be fast, but if you use a where clause on > an unindexed column then it will be slower. Please note that if column > being updated is indexed then it will be a bit slower since that index > will need to be recaculated. > > On the other hand if you make an insert in a table that has no indexes > then it will be fast as records are simply added. But if you make an > insert in a table that has one or mores indexes then these indexes > must be updated with each row inserted and hence will become slower. > > Deletes is a bit of a compromise. When deleting using a where clause > on a column that is indexed then it will be fast to located row/s, but > after delete index will need to be recalculated. > > As a simply general rule it is best to have a lot of indexes when you > have a lot or selects and updates(on non-indexed columns example data > warehouse), but indexes must be kept to a minimum when you have a lot > of inserts and fewer selects/updates. > > James. > > On Jan 2, 4:46 am, Venu Yanamandra <[email protected]> wrote: > > > > > I also thought about it once again and think I might be wrong about the redo > > information. 'coz they all fall under DML, there should be redo recorded. > > But, somehow insert must be cheaper as there is no where clause to be > > validated when we consider the worst case scenario. i.e., delete can have > > where clause, while insert does not. > > > On Fri, Jan 1, 2010 at 9:41 PM, Venu Yanamandra > > <[email protected]>wrote: > > > > i think insert is cheaper than delete/update. > > > with insert, only the constraints are validated. > > > with delete, the records are searched, redo information is recorded, > > > etc. > > > with update, the records are searched, redo information is recorded, > > > the constraints are validated again, etc. > > > > -Venu > > > > On Nov 26 2009, 8:47 am, Niraj Singh The King > > > <[email protected]> wrote: > > > > As per my knowledge goes insert are less expensive as while doing update > > > and > > > > delete it needs to search for the records while inserting it dont needs > > > > until there are some constraints but still they are less expensive > > > > -- > > > > Thanks and Regards, > > > > Opus Software Solution(Pune) > > > > Niraj Singh Parihar > > > > Mobile Number : +919890492566. > > > > > On Thu, Nov 26, 2009 at 8:57 AM, Javier Montani <[email protected]> > > > wrote: > > > > > It also depends on how many records and indexes the table has. With > > > > > too > > > > > many records an update of a column that doesn't belong to an index > > > could be > > > > > faster than an insert which has to update all the indexes. > > > > > > 2009/11/25 Andrej Hopko <[email protected]> > > > > > > Hi, > > > > >> my standard knowledge is that inserts are cheap for database to > > > > >> run, but deleting and updating are expensive operations (if this you > > > > >> meant by fat operation) > > > > > >> difference is mainly due to usage of indexes and locks (thats my > > > > >> opinion) > > > > > >> hoppo > > > > > >> On 25.11.2009 23:16, noam gariani wrote: > > > > >> > Do you know what is fater? > > > > >> > To do update for a record , or insert a record to a db? > > > > > >> -- > > > > >> You received this message because you are subscribed to the Google > > > > >> Groups "Oracle PL/SQL" group. > > > > >> To post to this group, send email to [email protected] > > > > >> To unsubscribe from this group, send email to > > > > >> [email protected] > > > > >> For more options, visit this group at > > > > >>http://groups.google.com/group/Oracle-PLSQL?hl=en > > > > > > -- > > > > > You received this message because you are subscribed to the Google > > > > > Groups "Oracle PL/SQL" group. > > > > > To post to this group, send email to [email protected] > > > > > To unsubscribe from this group, send email to > > > > > [email protected] > > > > > For more options, visit this group at > > > > >http://groups.google.com/group/Oracle-PLSQL?hl=en-Hidequoted text - > > > > > - Show quoted text - > > > > -- > > > You received this message because you are subscribed to the Google > > > Groups "Oracle PL/SQL" group. > > > To post to this group, send email to [email protected] > > > To unsubscribe from this group, send email to > > > [email protected] > > > For more options, visit this group at > > >http://groups.google.com/group/Oracle-PLSQL?hl=en-Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text -
Funny how all of this discussion ignores the basic PCTFREE/PCTUSED mechanism used by tablespaces utilizing manual segment space management. That process affects both inserts and updates; for inserts not using the 'append' hint Oracle searches the freelists for populated data blocks to find an existing location to put the new row; should no existing 'hole' be large enough a new block will be populated with the inserted data and a new rowid will be generated. Should an existing spot in a populated data block be found then the data is inserted there and the rowid is assigned to the new data. This is not an instant process (meaning it does take time). On the update side of the question the row is found and the values updated and, if there is insufficient room to expand the row in the existing block the row is migrated to a new data block and a 'marker' is placed at the original row location pointing to the new 'home' of this data. And let's say we have an update which extends the row past the boundaries of the configured db_block_size; in such cases data equal to the block size minus the size of a pointer is located in the existing block (presuming there is room) or into a new block (if there is not) and the remainder (the 'overflow') is located in an additional block; such rows are chained. All of this activity takes time to complete and affects both inserts and updates. Deletes on such rows also involve more time as multiple data blocks must be updated to mark the data as deleted. There are cases one can create to prove inserts are 'faster' than updates/deletes, and there are cases which can prove inserts take as much time, or possibly more, than updates/deletes that depend upon databsae configuration, disk speed, disk latency, index configuration, instance configuration, sort_area_size, hash_area_size, volume of redo generation, average redo log switch time, archivelog generation, undo configuration, transactional activity in the database and query volume. The question is not as simple to answer as the currently posted responses might indicate as not all of the factors that can affect the processing time have been considered. The process is not much simpler using ASSM as the segment header bitmap must be searched and updated to reflect the changing data landscape; ASSM does reduce the bookkeeping work required by non-ASSM tablespaces but it doesn't eliminate the necessity of recording changes to the segments as data is added/removed. It may appear to be a simple question, but the simplistic responses provided do not address all aspects of the operations in question and cannot hope to definitively provide an answer. David Fitzjarrell -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
