使用唯一约束启用更简单的联接

这是一个概念性问题,因为我正在考虑两种方法来设计频繁联接的一组表的数据库。

这里是案例A:

/*SCHEMA*/
CREATE TABLE SCHEMA (
SCHEMA_ID    INTEGER,
CONSTRAINT sch_pk PRIMARY KEY (SCHEMA_ID)
);

/*OBJECTS*/
CREATE TABLE OBJECT (
OBJECT_ID    INTEGER,
SCHEMA_ID    INTEGER NOT NULL,
CONSTRAINT obj_pk PRIMARY KEY (OBJECT_ID),
CONSTRAINT obj_sch_uniq UNIQUE (OBJECT_ID,SCHEMA_ID),
CONSTRAINT obj_fk FOREIGN KEY (SCHEMA_ID) REFERENCES SCHEMA(SCHEMA_ID)
);

/*COLUMNS*/
CREATE TABLE COL (
COL_ID      INTEGER,
OBJECT_ID   INTEGER,
CONSTRAINT col_pk PRIMARY KEY (COL_ID),
CONSTRAINT col_obj_uniq UNIQUE (COL_ID,OBJECT_ID),
CONSTRAINT col_fk FOREIGN KEY (OBJECT_ID) REFERENCES OBJECT(OBJECT_ID)
);

这里是案例B:

/*SCHEMA*/
CREATE TABLE SCHEMA (
schema_id               INTEGER,
CONSTRAINT schema_pk PRIMARY KEY (schema_ID),
);

/*OBJECTS*/
CREATE TABLE OBJECT (
object_id               INTEGER,
schema_id               INTEGER,
CONSTRAINT object_pk PRIMARY KEY (object_id,schema_id),
CONSTRAINT object_schema_fk FOREIGN KEY (schema_id) REFERENCES SCHEMA (schema_id)
);

/*COLUMNS*/
CREATE TABLE COL (
column_id               INTEGER,
object_id               INTEGER,
schema_id               INTEGER,
CONSTRAINT column_pk PRIMARY KEY (column_id,object_id,schema_id),
CONSTRAINT column_object_fk FOREIGN KEY (object_id,schema_id) REFERENCES OBJECT (object_id,schema_id)
);

对这些表集运行的常见查询如下: 案例A

SELECT *
FROM METADATA_CONTROL.COL
INNER JOIN METADATA_CONTROL.OBJECT ON METADATA_CONTROL.COL.OBJECT_ID = METADATA_CONTROL.OBJECT.OBJECT_ID
WHERE OBJECT.SCHEMA_ID = 101;

案例B

SELECT *
FROM METADATA_CONTROL.COL
WHERE SCHEMA_ID = 101;

可以看出,案例A需要连接,案例B不需要。我的问题是:

-这两个表结构执行相同的业务需求是否正确? -如果有,我如何确定要实施哪种情况?

我在理解不同关系表结构的性能增益/惩罚方面很薄弱。这是在Oracle 12c上。

我非常感谢在这种情况下提供的任何指导,以及一些额外的资源或规则,这些资源或规则是关于查询性能以及它们如何与约束和联接相关的。

谢谢您!