ThatJeffSmith

Row, Row, RowID Your Record, Gently Update the Table

Power Tip: Let Someone Else (Toad) Row For You!


You may have heard the term ‘ROWID’ many times without really stopping to think about what purpose it serves. The end user – or even the application developer themselves – will rarely have a need to interface with a ROWID. Even so, ROWIDs do a lot of cool things.

According to Oracle, ‘They are the fastest way to access a single row’.

In a really small nutshell, ROWID gives us the location of the data for a given row in a table. The datafile – then the position of the row in the block, then the data block in the datafile. As you can imagine, this data is UNIQUE. So, each row in your table will have a unique ROWID. This is only because the data for 2 rows can’t exist in the same space.

It’s not uncommon for a table to lack any unique columns or PKs or unique indexes. But, if you know the ROWID, then you will have at least 1 unique differentiator for that record.

It’s also important to remember that a record’s ROWID is not guaranteed to remain unchanged.

Since it’s a PSEUDO column, it’s not actually stored in the database. That means we can’t INSERT|UPDATE|DELETE a ROWID. But, we can use it in a SELECT or WHERE.

Sidebar!

Ok, what Oracle is saying here is that ROWIDs aren’t stored in a table. They can and WILL be stored in an INDEX. Indexes allow us to quickly jump to the records we are looking for. However, the ROWID is dynamically assembled as you retrieve that data. Clear as mud? No? Tom Kyte explains it here.

Two Cool Things You Can Do with ROWID

1. Use it to delete duplicate rows from your table (handy when you’re lacking that PK or unique attribute)
2. Use it to edit SELECT results in Toad for Oracle

Deleting Records
Add ‘ROWID’ to your SELECT string. Once you’ve identified the records you want to delete, just record those values. Then issue your ‘DELETE FROM TABLE WHERE ROWID in (…)’ command(s).

I’ve covered this topic in detail on ToadWorld – go read the blog post.

Editing Table/Views From a SELECT query in the Toad Editor

Easily edit records from your SELECT by adding ROWID


Once you have your query finished, simply add ‘ROWID’ to the SELECT list. Once Toad has the list of ROWIDs for each record returned, the data grid will toggle ‘Green’ to indicate the data can be edited. This is a nice convenient ‘hack’ for fixing data as you query it.

Of course there are some exceptions to this. If you query a view and include ROWID, you may get a

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

Your view could be pulling data from multiple source tables. These tables need a PK apparently. This Oracle’s way of saying that it can’t determine the the ROWID based on what you are querying. Also, your row could be pulling from multiple sources, so there would be theoretically multiple ROWIDs. Toad will let you edit View data directly in the Schema Browser Data Grids if it can. If it’s not allowing you to do so, it’s because of this Oracle design requirement. Notice I didn’t call it a limitation – I think it’s cool you can do this at all!

Morbidly Curious, Want to See the Actual RowIDs?

Simply toggle on the ROWID selection in the data grid Column Selector. And here’s another piece of Toad trivia for you. When you ask Toad to make the Data Grids ‘Read Only’ in the application options, what Toad is doing in the background is NOT querying the ROWIDs when pulling data for the tables. Without the ROWID, Toad won’t let you touch the records.

Tell Toad You Want to See the ROWIDs