This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
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 Murach's Java Servlets and JSP this week in the Servlets 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: 30068
    
149

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: 30068
    
149

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: 18541
    
    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: 18541
    
    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.
 
wood burning stoves
 
subject: Using rowid in join, is it right technique?
 
Similar Threads
Hibernate join and key tag
complex data type in axis 2 web service
One-to-One mapping problem
[Hibernate] How do you map value objects?
editing records