Re: Help on rowid (SE 5.0) - Reminder

: tonytd@ttyrwhit.demon.co.uk (Tony Tyrwhitt-Drake) writes: : In article <4f54f0$hic@cssun.mathcs.emory.edu>, javi@psi.ernet.in (javeed anwar t) says: : : >What I want to know is : : > : >Suppose I do a partial scan or total scan on this table before and after : >deletion, how will be the performance of the system? I want to know because : >I doubt since the rowid's are not physically deleted it may degrade the : >performance in the second case. : > : >Also, let me know how the rowid behaves when that particular record is : >deleted from the table and how does an indexed scan filter the records? : : I get the feeling that you may have mis-understood the role of : rowids . : : A rowid is unique for the life of a table. If you drop the table : and reload it a particular row of data may have a new rowid. Otherwise : rowids start counting up from 1 and are never reused. : : The performance of a table may depend on how many rows are in it, : but not what the values of the rowids are. If the table is well : designed and indexed then performance may not even be dependant on : number of rows in the table. : : A rowid is just a unique key given to each row. Yes, but: If you delete a lot of rows, then do a select that scans the entire table, the engine will scan all deleted rows. This does take a lot more time than if you unload, delete it, creates it again and reloads the data. You may also do an alter index to cluster to achieve the same effect. That will create a temporary table containing all the data in the original table, so make sure you have enough diskspace. If you select via an index the performance will not suffer much (usually). You may get into a situation where records you want are spread all over the disk, in which case an unload/reload may help you even in this situation. You should however generally forget about rowid as such. Don't ever use it. It was never a good extension to any database, and from ver. 7 (or perhaps 6) and up of OnLine it no longer works as soon as you fragment a table. Every table should allways have a primary key. Use a serial field if you have no other natural candidate for a primary key. Nils.Myklebust@ccmail.telemax.no NM-data, Aasesvei 71, 1300 Sandvika, Norway My opinions are those of my company