ROWID’s in Netezza

What is a ROWID?

ROWID is an invisible column associated with each row. It is used to identify a specific record in the database.

Few important points about ROWID’s:

The rowid is guranteed to be unique within a table and unique across the entire system, but not necessarily sequential within a table.

When you run the nzload command, the Netezza host creates records and assigns rowids. The SPUs can also create records and assign rowids. This happens when you use the command CREATE TABLE <tablename> AS SELECT

The system gives the host and each of the SPUs a block of sequential rowids that they can assign. When they use up a block, the system gives them another block, which explains why the rowids within a table are not always sequential.

The system stores the rowid with each database record. It is an 8-byte integer value. You can use the rowid keyword in a query to select, update, or delete records. For example:

SELECT rowid, lname FROM employee_table;

UPDATE employee_table SET lname = ‘John Smith’ WHERE rowid = 234567;

Querying by some other field, such as name, might be difficult if you have 10 John Smiths in the database.

In a new installation, the initial rowid value is 100,000. The next available rowid value is stored in the /nz/data/RowCounter file.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: