昨天介绍了 PostgreSQL 里的继承表，明天来持续探讨 ORACLE 数据库里有没有相似的货色。
首先，能够明确的说，ORACLE 数据库里没有继承表这种 OBJECT。
然而，和 PostgreSQL 的继承表相似，不同 Table 的数据以某种形式进行关联，共享存储空间的角度来讲，ORACLE 数据库也存在这样的 OBJECT。
那就是 CLUSTER。
首先，ORACLE 数据库对“CLUSTER”是这样定义的：
CREATE CLUSTER (oracle.com)
A cluster is a schema object that contains data from one or more tables.
上面咱们来看看 CLUSTER 是怎么利用的。
-- 做成 CLUSTER
SQL> conn test/test@localhost:1521/pdb
接続されました。SQL> CREATE CLUSTER object
(object VARCHAR2(23))
SIZE 512
STORAGE (initial 100K next 50K); 2 3 4
クラスタが作成されました。-- 为 CLUSTR 做一个索引
SQL> CREATE INDEX idx_object ON CLUSTER object;
索引が作成されました。-- 做成应用 CLUSTER 的第一个表
SQL> CREATE TABLE object_table
CLUSTER object (OBJECT_TYPE)
AS SELECT * FROM dba_objects WHERE OBJECT_TYPE = 'TABLE'; 2 3
表が作成されました。-- 做成应用 CLUSTER 的第二个表
SQL> CREATE TABLE object_index
CLUSTER object (OBJECT_TYPE)
AS SELECT * FROM dba_objects WHERE OBJECT_TYPE = 'INDEX'; 2 3
表が作成されました。-- 确认一下两个表的状态和定义
SQL> select count(*) from object_index;
COUNT(*)
----------
2898
SQL> select count(*) from object_table;
COUNT(*)
----------
2223
SQL> @obj
object_name に値を入力してください: object_index
旧 1: select owner,object_name,object_type from dba_objects where object_name like upper('&object_name')
新 1: select owner,object_name,object_type from dba_objects where object_name like upper('object_index')
OWNER OBJECT_NAME OBJECT_TYPE
-------------------- -------------------------------------------------- -----------------------
TEST OBJECT_INDEX TABLE
SQL> @obj
object_name に値を入力してください: object_table
旧 1: select owner,object_name,object_type from dba_objects where object_name like upper('&object_name')
新 1: select owner,object_name,object_type from dba_objects where object_name like upper('object_table')
OWNER OBJECT_NAME OBJECT_TYPE
-------------------- -------------------------------------------------- -----------------------
TEST OBJECT_TABLE TABLE
SQL> @ddl
object_type に値を入力してください: TABLE
object_name に値を入力してください: OBJECT_INDEX
owner に値を入力してください: TEST
旧 1: select dbms_metadata.get_ddl('&object_type','&object_name','&owner') from dual
新 1: select dbms_metadata.get_ddl('TABLE','OBJECT_INDEX','TEST') from dual
DBMS_METADATA.GET_DDL('TABLE','OBJECT_INDEX','TEST')
--------------------------------------------------------------------------------
CREATE TABLE "TEST"."OBJECT_INDEX"
("OWNER" VARCHAR2(128),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(128),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(23),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(128),
"SHARING" VARCHAR2(18),
"EDITIONABLE" VARCHAR2(1),
"ORACLE_MAINTAINED" VARCHAR2(1),
"APPLICATION" VARCHAR2(1),
"DEFAULT_COLLATION" VARCHAR2(100),
"DUPLICATED" VARCHAR2(1),
"SHARDED" VARCHAR2(1),
"CREATED_APPID" NUMBER,
"CREATED_VSNID" NUMBER,
"MODIFIED_APPID" NUMBER,
"MODIFIED_VSNID" NUMBER
) CLUSTER "TEST"."OBJECT" ("OBJECT_TYPE")
SQL> @ddl
object_type に値を入力してください: TABLE
object_name に値を入力してください: OBJECT_TABLE
owner に値を入力してください: TEST
旧 1: select dbms_metadata.get_ddl('&object_type','&object_name','&owner') from dual
新 1: select dbms_metadata.get_ddl('TABLE','OBJECT_TABLE','TEST') from dual
DBMS_METADATA.GET_DDL('TABLE','OBJECT_TABLE','TEST')
--------------------------------------------------------------------------------
CREATE TABLE "TEST"."OBJECT_TABLE"
("OWNER" VARCHAR2(128),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(128),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(23),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(128),
"SHARING" VARCHAR2(18),
"EDITIONABLE" VARCHAR2(1),
"ORACLE_MAINTAINED" VARCHAR2(1),
"APPLICATION" VARCHAR2(1),
"DEFAULT_COLLATION" VARCHAR2(100),
"DUPLICATED" VARCHAR2(1),
"SHARDED" VARCHAR2(1),
"CREATED_APPID" NUMBER,
"CREATED_VSNID" NUMBER,
"MODIFIED_APPID" NUMBER,
"MODIFIED_VSNID" NUMBER
) CLUSTER "TEST"."OBJECT" ("OBJECT_TYPE")
-- 确认一下两个表的存储状况
col OWNER for a10
col SEGMENT_NAME for a20
col SEGMENT_TYPE for a18
col SEGMENT_SUBTYPE for a10
col TABLESPACE_NAME for a10
col RETENTION for a7
set lin 200 pages 999
select OWNER,SEGMENT_NAME,SEGMENT_TYPE,SEGMENT_SUBTYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BLOCKS,EXTENTS,RETENTION
from dba_segments
where OWNER='TEST';
OWNER SEGMENT_NAME SEGMENT_TYPE SEGMENT_SU TABLESPACE HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS RETENTI
---------- -------------------- ------------------ ---------- ---------- ----------- ------------ ---------- ---------- -------
TEST OBJECT CLUSTER ASSM USERS 12 130 96 12
TEST IDX_OBJECT INDEX ASSM USERS 12 146 8 1
OK，曾经能够确认到应用 CLUSTER 的两张表的确共享了存储空间。
当然，ORACLE 数据库的 CLUSTER 和 PostgreSQL 继承表并不是雷同的货色，下面的例子只是从多张表共享物理存储空间的角度进行了剖析, 至于其余的角度，就见仁见智了。
2021/06/18 @ Dalian