Store in table pointers to rows from another table instead of using foreign keys

i'm just curious if it's possible to just use pointers the c/java/etc. way instead of joining tables based on foreign key. And since we're on this topic, does p开发者_StackOverflow中文版ostgres or any other RDBMS use pointers, when joining, to quickly locate a row referenced by a foreign key instead of searching within the whole table containing FKs?

---

An essential feature of the relational model of data is that it does not use pointers, structural links or address-based data access in its external, logical view of the database. Abolishing the use of pointers was in fact one of Codd's main motivations for inventing the relational model in 1969.

Pointers are of course used internally by most relational / SQL based DBMSs.

---

Oracle has something called ROWID which stores the physical address of a row in a table. They look something like AAAA8mAALAAAAQkAAA which can be broken down into:

• Data object nr (segment) [AAAA8m]
• Data file in tablespace [AAL]
• Data Block (within the data file) [AAAAQk]
• The row (within the block) [AAA]

Using the rowid is the fastest way of physically locating any row in a single block read. They are used internally, but can be used directly by the developer. Looking up a row by primary key, results in one (or a few) index reads to find the rowid, and then the database will locate the block and extract the row. If one knows the rowid, no index lookups are needed.

Having said that, one of the pillars of relational databases is that the model is supposed to be independent of physical storage (no pointers). Also, it enables a specific implementation (of a dbms) to optimize and provide additional features transparently.

Each time you use a rowid, a Unicorn will die, and poop falls down from heaven. For example, if you shrink a table, or update a partitioned table (causing a row to move to another partition) or if you are rebuilding a table, or export/import a table, or... or... or... the rowid will change.

In conclusion: rowid-based access does not provide enough benefits over regular indexed access to be worth the risk of the inevitable failure. Know that they exist and what they are, but don't ever rely on them.