Yesterday we introduced inheritance tables in PostgreSQL, and today we’ll continue our discussion of whether there is something similar in the Oracle database.

First, it is clear that Oracle databases do not inherit objects such as tables. However, similar to PostgreSQL’s inherited tables, data from different tables are related in some way. In terms of shared storage space, Oracle databases also have such objects.

That’s CLUSTER.

First, Oracle Database defines “CLUSTER” as follows:

CREATE CLUSTER (oracle.com)
A cluster is a schema object that contains data from one or more tables.

Let’s look at how Cluster is used.

2. Get the CLUSTER SQL> conn test/test@localhost:1521/ PDB and get the promise. SQL> CREATE CLUSTER object (object VARCHAR2(23)) SIZE 512 STORAGE (initial 100K next 50K); 2, 3, 4 クラスタが SQL> CREATE INDEX idx_object ON CLUSTER object; The index が is printed. SQL> CREATE TABLE object_table CLUSTER object (OBJECT_TYPE) AS SELECT * FROM dba_objects WHERE object is clustered OBJECT_TYPE = 'TABLE'; 2 3 Make the table が into squares. SQL> CREATE TABLE object_index CLUSTER object (OBJECT_TYPE) AS SELECT * FROM dba_objects WHERE object_index CLUSTER object (OBJECT_TYPE OBJECT_TYPE = 'INDEX'; 2 3 Make the table が into squares. SQL> select count(*) from object_index; COUNT(*) ---------- 2898 SQL> select count(*) from object_table; COUNT (*) -- -- -- -- -- -- -- -- -- -- 2223 SQL > @ obj object_name に numerical を し into force て く だ さ い : object_index old 1: Select owner,object t_name,object t_type from dba_objects where object t_name like upper('&object t_name') select owner,object t_name,object t_type from dba_objects where object t_name like upper('&object t_name') select owner,object_name,object_type from dba_objects where object_name like upper('object_index') OWNER OBJECT_NAME OBJECT_TYPE -------------------- -------------------------------------------------- ----------------------- TEST Object t_index TABLE SQL >@obj object t_nameに and Select owner,object t_name,object t_type from dba_objects where object t_name like upper('&object t_name') select owner,object t_name,object t_type from dba_objects where object t_name like upper('&object t_name') 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 >@ddl object_typeに and value of TABLE object_name value and value of object index owner value and value of: TEST 1: select dbms_metadata.get_ddl('&object_type','&object_name','&owner') from dual 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に, and the value of OBJECT_TABLE owner is: TEST old 1: Select dbms_metadata.get_ddl('&object_type','&object_name','&owner') from dual 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, you can confirm that the two tables that use CLUSTER do share storage space.

Of course, the Oracle database CLUSTER and PostgreSQL inherited tables are not the same thing. The above example is just from the perspective of multiple tables sharing physical storage space. The rest is a matter of opinion.

2021/06/18 @ Dalian