Re: Use/Dont Use ROWID in queries

zahgurim_at_ix.netcom.com wrote:

>Readers:
 

>I was told recently that ROWID should not be used in queries since it is not
>unique, especially if the tables are clustered [whatever that means] and you
>loose the rowid when you do an export/import of the data.
 

>I am conufused on this matter and was wondering if anyone could help clarify
>this for me. I always heard that you should use ROWID whenever it is possible
>since it's the fastest way to manipulate records and other reasons.

>Thanks for you time in advance!
 

>Happy Holidays

Sounds half right. It's true that imp/exp will change ROWIDs. That's because they contain the exact address of the row within the database (datafile#, block#, row#). So ROWIDs *will* be unique in non-clustered tables.

Clusters, or clustered tables, are a means of storing related rows from multiple tables in the same data blocks. They're useful for tables that are often joined because there's less i/o needed to locate the related rows. Also, each cluster key value (joined columns) is stored only once, not on each row, so you save some storeage space too. The downside is when you need to query just one table, especially with a full table scan, you've got more disk real estate to cover because you've got to skip over all the rows from the other tables in the cluster.

I can't think of any reason why ROWIDs wouldn't be unique in a cluster but then again I'm not using them yet. And even if they aren't, as long as they're unique within each individual clustered table, it shouldn't cause any problems.

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Never share a foxhole with anyone braver than yourself!