关于postgresql:ORACLE-和PostgreSQL继承表类似的实现方式

昨天介绍了 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