WITH ROWIDS Option

Nonfragmented tables contain a hidden column called rowid. Its integer value defines the physical location of a row. To include a rowid column in a fragmented table, you must explicitly request it by using the WITH ROWIDS clause in CREATE TABLE (or ADD ROWIDS in ALTER TABLE, or WITH ROWIDS in ALTER FRAGMENT INIT).

The rowid in a row of a fragmented table does not identify a physical location for the row in the same way that a rowid in a non-fragmented table does.

When you use the WITH ROWIDS option to add a new rowid column to a fragmented table, the database server assigns a unique rowid number to each row and creates an index that it can use to find the physical location of the row. Performance using this access method is comparable to using a SERIAL, BIGSERIAL, or SERIAL8 column. The rowid value of a row cannot be updated, but remains stable during the existence of the row. Each row requires an additional four bytes to store the rowid column after you specify the WITH ROWIDS option.

Recommendation: When creating new applications, use primary keys as a method of row identification instead of using rowid values.

Copyright© 2020 HCL Technologies Limited