aspose file tools*
The moose likes JDBC and the fly likes Using rowid in join, is it right technique? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using rowid in join, is it right technique?" Watch "Using rowid in join, is it right technique?" New topic
Author

Using rowid in join, is it right technique?

D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

I'm quite new to databases, so my question is bit silly. JDBC 4.0 introduced support of row id, so it means I can get rid of primary key id I used before?
For example
table customer
name string
address number

table address
street string
zip number

query:


Is it right approach?
[ March 03, 2007: Message edited by: D Rog ]

Retire your iPod and start with HD Android music player Kamerton | Minimal J2EE container is here | Light weight full J2EE stack | and build tool | Co-author of "Windows programming in Turbo Pascal"
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

No. The database doesn't cont guarantee to store your records in a particular order. You still need a logical (or generated) primary key.

rowId is useful when you want to limit the results returned.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

You mean that rowid can't be indexed and any join on it will issue just full table scan?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

While it can't be indexed, this wasn't what I was trying to point out. I was trying to point out that using rowid could well give you a different row than you are expecting. So using rowid would be incorrect (which is more important than slow.)

The database does not promise to store rows in a certain order or access them in a certain order.
D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

Have you looked in JDBC 4.0 specification? I can know upfront if a particular database supports rowid and how. I mean rowid can have the same entire record life time or can be changed and so on. Interesting thing that rowid can work better than index, since database can directly access record using rowid. So I believe it will work even faster than index. Anyway, thank you for the thoughts.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18154
    
    8

You're suggesting that instead of a primary key that might contain an arbitrary auto-number or something like that, you could use row ID as the primary key instead?

I suppose you could. But using that row ID as a foreign key in a different table could be problematic. The database I do a lot of work on allows you to reorganize a table to get rid of deleted rows. When you do that, the row ID of many rows will change. That would break all the foreign keys in the other tables with no way of repairing them.
D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

I understand, you are talking about ROWID_VALID_SESSION and below. However if database reports me as ROWID_VALID_FOREVER, then I can utilize the feature without fear. I agree that benefits I'm getting from that not so dramatic and a portability can be compromised.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18154
    
    8

Originally posted by D Rog:
I understand, you are talking about ROWID_VALID_SESSION and below.
Am I? Well, I haven't read the JDBC 4.0 spec (because it isn't likely to be relevant to me for at least two years). I was just giving a general example.
vu lee
Ranch Hand

Joined: Apr 19, 2005
Posts: 189
Using rowid is faster than using index since rowid contains a physical address pointed to the record whereas index contains indirect pointer(search the index table to locate primary key, etc..). This is how I understand the difference between primary key and rowid. Primary key uniquely identify a record within a table, whereas rowid uniquely identify a record within the same database instance.

I suppose you could. But using that row ID as a foreign key in a different table could be problematic. The database I do a lot of work on allows you to reorganize a table to get rid of deleted rows. When you do that, the row ID of many rows will change. That would break all the foreign keys in the other tables with no way of repairing them.


If database permits using rowid as primary key, then I suppose the integrity contraint would take care of it.
D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

Originally posted by Paul Clapham:
Am I? Well, I haven't read the JDBC 4.0 spec (because it isn't likely to be relevant to me for at least two years). I was just giving a general example.

Most of specification're inveted by us, so we can speak confident without reading them.
D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

Originally posted by vu lee:

....

If database permits using rowid as primary key, then I suppose the integrity contraint would take care of it.
Thanks,
It's a good point.
 
 
subject: Using rowid in join, is it right technique?
 
Similar Threads
One-to-One mapping problem
Hibernate join and key tag
editing records
complex data type in axis 2 web service
[Hibernate] How do you map value objects?