I am aware that MySQL and PostgreSQL[1] do not have that concept, so I am interested in finding out of there is an open-source SQL database that does have the concept.
[1] It was later pointed out that PostgreSQL does have the rowid pseudo-column.
Asked
Active
Viewed 703 times
3
-
2That seems like an awfully specific request to discount a whole database over. If you post the specific problem that you're trying to solve, someone might have an alternative solution. – Tom H May 03 '09 at 04:14
-
Is your question really about ROWID, or about ROWNUM? The ROWNUM of a record can vary from query to query, but the ROWID does not. – DCookie May 03 '09 at 17:14
-
I'm not actually trying to decide which database to use. This question was just inspired through a discussion I had with an Oracle friend. To whether or not I mean ROWID or ROWNUM, my Oracle friend said ROWID, so I'll assume that I do not mean ROWNUM. – James Mishra May 03 '09 at 19:56
3 Answers
5
PostgreSQL does have this concept.
See here for a brief list of pseudocolumns in PostgreSQL, out of which ctid is of interest to you:
ctid (tuple identifier)The identifier which describes the physical location of the tuple within the database. A pair of numbers are represented by the ctid: the block number, and tuple index within that block.
That is direct analog of Oracle's rowid.
As for MySQL, physical location of a row is not available for the front end.
In MyISAM, rowid is just a file offset from the beginning, and that's what is stored in the index leaves.
In InnoDB, tables are index organized by design, that means they always have some kind of a primary key, and the indexes over an InnoDB table use that PRIMARY KEY as a row pointer.
This is also true for Oracle's index organized tables, for which a rowid is not a physical pointer to a block in a datafile, but rather a such called logical ROWID, or UROWID
If you select a ROWID from an INDEX ORGANIZED table in Oracle, you will see that it has a different format (something like *BAEBwPICwQL+). This is in fact an encoded PRIMARY KEY value.
Note that if you have not defined any column as a PRIMARY KEY, MySQL will create a hidden surrogate PRIMARY KEY over which you will never have any control.
That's why you should always create some kind of a PRIMARY KEY in an InnoDB table: it's free, and you get control over the column.
Quassnoi
- 413,100
- 91
- 616
- 614
-
It does? I Googled for the answer, and found a forum post saying that PostgresQL does not have that concept. – James Mishra May 03 '09 at 19:57
-
-
By the way: by ROWID in Oracle you mean ROWID (physical pointer to a data block containing a record) or ROWNUM (incremental row number in a dataset returned from a SELECT query)? These are different things. – Quassnoi May 03 '09 at 20:22
0
SQLite has an auto incremented "ROWID" column which you can access using ROWID, _ROWID, or OID. If you define an integer primary key than they will be aliased.
Steven Canfield
- 7,312
- 5
- 35
- 28
-
Not quite the same thing- the oracle feature is the row # of the results of a particular query. So if you order by the id desc, the first result would still have rowid #1. – Joel Coehoorn May 03 '09 at 03:52
-
Ah I didn't understand that. Is there a use case for that (where you couldn't just do it by hand) ? – Steven Canfield May 03 '09 at 03:54
-
It's a way to get TOP(x) in a db that doesn't support that structure if you use a subquery: SELECT * from ( SELECT * myTable ORDER BY interestingField ) WHERE rowid > 101 – Gary.Ray May 03 '09 at 04:27
-
Sorry - that query should be: SELECT * from ( SELECT FROM * myTable ORDER BY interestingField ) WHERE rowid < 101 – Gary.Ray May 03 '09 at 04:29
-
1ROWID is NOT the same thing as ROWNUM. ROWNUM is the relative order of a result in a query. ROWID is a physical identifier that is static, unless records are physically relocated. It's almost always unique in the database. – DCookie May 03 '09 at 17:12