• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using rowid in join, is it right technique?

 
D Rog
Ranch Hand
Posts: 472
Linux Objective C Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34396
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
D Rog
Ranch Hand
Posts: 472
Linux Objective C Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You mean that rowid can't be indexed and any join on it will issue just full table scan?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34396
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 472
Linux Objective C Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 472
Linux Objective C Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 206
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 472
Linux Objective C Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 472
Linux Objective C Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic