Does any RDBMS "inline" a 1:0-1 relationship's dependent table?

Question

In the case of a 1:0-1 relationship between a principal table p and dependent table d, when commonly-used queries will read from both p and d, do any RDMBs inline d to eliminate the need to store d separately (and maintain d's indexes, and other costs).

I want to invoke Codd's paper where he suggests that tables could be entirely abstract - whereas right now I'm not aware of any RDBMS that treats tables as an abstraction; they all treat SQL TABLE objects as 1:1 representations of an on-disk rowstore structure (such that two SQL TABLE objects (sharing a PK) can't share a single rowstore, nor can multiple rowstores be used to represent a single TABLE... _{excepting SQL PARTITION, of course}).

Because surely a SQL TABLE relation object is intended as an abstraction and the RDMBS is free to choose the most appropriate on-disk representation, which might include inlining dependent tables, to quote Codd:

https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf
The relational view (or model) of data described in Section 1 appears to be superior in several respects to the graph or network model presently in vogue for noninferential systems. It provides a means of describing data with its natural structure only-that is, without superimposing any additional structure for machine representation purposes

For example:

CREATE TABLE principal (
    principalId int         NOT NULL IDENTITY,
    foo         varchar(50) NOT NULL,
    bar         bigint      NOT NULL,

    CONSTRAINT PK_principal PRIMARY KEY ( principalId )
);

CREATE TABLE dependent (
    principalId int         NOT NULL,
    baz         varchar(50) NOT NULL,
    qux         bigint      NOT NULL,

    CONSTRAINT PK_dependent PRIMARY KEY ( principalId ),
    CONSTRAINT PK_dependent_to_principal FOREIGN KEY ( principalId ) REFERENCES principal ( principalId )
);

The above can be considered equivalent to:

CREATE TABLE principal (
    principalId int         NOT NULL IDENTITY,
    foo         varchar(50) NOT NULL,
    bar         bigint      NOT NULL,

    d_baz       varchar(50)     NULL,
    d_qux       bigint          NULL,

    CONSTRAINT PK_principal PRIMARY KEY ( principalId ),

    CONSTRAINT CK_dependent CHECK (
        ( d_baz IS     NULL AND q_qux IS     NULL )
        OR
        ( d_baz IS NOT NULL AND q_qux IS NOT NULL )
    )
);

...which presumably would have better DML performance as it has to maintain only the PK_principal index, instead of PK_principal and PK_dependent indexes when dependent data is added or removed.

It also means that concurrent applications wouldn't need to take a lock on multiple tables during a transaction, and maintains proximal-locality which I assume would greatly benefit performance.

Answer 1

The most common one's don't automatically do this, as far as I'm aware, and probably for good reason. Your question is under the assumption that one would always want to query for all of the columns between the two tables. If that were the case, why even have two tables to begin with?

"It also means that concurrent applications wouldn't need to take a lock on multiple tables during a transaction" - True but the time to take the lock on the single "inlined" table would be approximately proportionally longer since more data needs to be read from disk, written into memory, and then processed concurrently. In fact, a single longer running transaction could be worse for overall performance than two shorter locks, in some scenarios.

"maintains proximal-locality which I assume would greatly benefit performance" - This is a moot point these days with modern hardware and modern database systems with how data is stored and located. I doubt proximal location makes any tangible difference compared to the usual bottlenecks with data management and querying.

Answer 2

The two-table version (p and d) is the only possible relationally correct version on the logical level. And while Codd fiercely advocated nulls throughout his life as a solution to the "missing data" problem that fitted his relational model perfectly well, these days that is almost universally (perhaps not among coders but certainly among database professionals) considered his single biggest mistake.

Codd's main goal has always been to achieve physical data independence, that is, separation of concerns between the logical level and the physical level. The extent to which that goal has become a reality is to this day still almost completely negligible.

Here's Codd in his 1990 book on the RM/V2 :

In the late 1960s, I decided to take a close look at how databases were being designed. At that time it was clear to me that there did not exist any engineering discipline upon which database design could be established. One result was that designers found it extremely difficult~and frequently impossible~ to explain why they had chosen a particular design. The only reason that appeared meaningful to me was the attainment of acceptable performance on the first application that was developed to run on the database. Of course, this often meant that the database design was inconsistent with attaining good performance on subsequently developed applications. Two major problems, and hence challenges, presented themselves. First, there was a complete absence of concern for the database as an object that would continue to exist and evolve independently of any collection of application programs that might exist at some instant in time. Second, there was no rational basis for database design because there were no carefully conceived concepts at a sufficiently high level of abstraction. Database design cannot be successfully pursued if the only concepts available are bits and bytes.

"Tables" (relations actually) should indeed have been "entirely abstract" but they never were, and the reasons have mainly to do with pressure to prove the concept/feasability of the idea (e.g. IBM staff who made their living off of IMS have long been Codd's fiercest opponents), causing people to take certain shortcuts and make certain not-so-warranted assumptions, as e.g. the assumption of 1-1 mapping to physical records in files. And it is rumoured that when IBM management finally got convinced of the feasability of the RM, Codd said "now go back and design a decent language", and management replied with "we have a language, we'll use that and build further upon it". And the rest is history ...

You do state some thing pertaining to the physical design level and to locking strategies that are a bit questionable these days. LOB attributes, e.g., won't ever be part of a "1-1 representation of the row that contains them". Locks will often be page-level locks, sometimes row-level locks, but rarely table-level locks.

I do believe that "proximal location" still continues to be a major technique for performance improvement (if the DBMSs would fully support it which indeed they mostly don't), but the gains are certainly much smaller than what they used to be with older hardware.

And "the DBMS should be free to choose the most appropriate on-disk representation" is a huge over-estimation of what a DBMS can do. Making that choice requires data to base such choices on. The reality is that even DBMS designers simply do not have a complete information model for what kind of data is involved in that process ...