[Oracle Database 12c New Feature] Aggregate Data Across Many PDBs by CONTAINERS Clause

作者：张乐奕

2014 年 12 月发布

在最新版本的 Oracle Database 12.1.0.2 中，新特性提供了 PDB Containers 子句，用以从 CDB$ROOT 层面直接聚合查询多个 PDB 中同一张表的数据。在新特性文档中该段如下描述：

Screen Shot 2014-10-16 at 8.54.26 AM

 

但是实现起来并非看上去如此简单。

现有测试环境如下：当前 CDB 中有 2 个 PDB，分别是 PDB1 和 PDB2；每个 PDB 中都有一个相同名字的 Local User，为 KAMUS；每个 KAMUS 用户下都有一个 TT 表，表结构相同，数据不同。

  • 首先按照想象，在 CDB$ROOT 中直接使用 SYS 用户查询，会报 ORA-00942 错误。
SQL> SHOW USER
USER IS "SYS"
SQL> SHOW con_name
 
CON_NAME
------------------------------
CDB$ROOT
 
SQL> SELECT COUNT(*) FROM CONTAINERS(KAMUS.TT) WHERE CON_ID IN (3);
SELECT COUNT(*) FROM CONTAINERS(KAMUS.TT) WHERE CON_ID IN (3)
                                      *
ERROR at line 1:
ORA-00942: TABLE OR VIEW does NOT exist
  • 这要求我们首先创建一个 Common User。并赋予其足够的权限。赋予 select any table 权限是为了方便测试，在真实环境中你可能需要更精细地规划权限。
SQL> CREATE USER C##KAMUS IDENTIFIED BY oracle DEFAULT tablespace users;
 
USER created.
 
SQL> GRANT dba TO C##KAMUS CONTAINER=ALL;
 
GRANT succeeded.
 
SQL> GRANT SELECT any TABLE TO C##KAMUS CONTAINER=ALL;
 
GRANT succeeded.
  • 其次要求用 Common User 分别连接所有需要聚合查询的 PDB，在其中创建一个与表名字相同的视图。
sqlplus "C##KAMUS/oracle@db-cluster-scan:1521/pdb1"
CREATE OR REPLACE VIEW tt AS SELECT * FROM kamus.tt;
ALTER SESSION SET container=pdb2;
CREATE OR REPLACE VIEW tt AS SELECT * FROM kamus.tt;
  • 然后还需要在 Common User 中创建一个相同名字的空表，否则查询仍然会报 ORA-00942 错误。
SQL> SHOW USER
USER IS "C##KAMUS"
SQL> SHOW con_name
 
CON_NAME
------------------------------
CDB$ROOT
SQL> CREATE TABLE TT (dummy CHAR(1));
 
TABLE created.
 
SQL> SELECT COUNT(*) FROM CONTAINERS(TT);
 
  COUNT(*)
----------
    117362
  • 只需要创建一个名字相同的表，已经可以聚合查询 count(*)了。但是如果在 SQL 语句中涉及到特定列仍会有问题。从报错中透露的 P000 进程，可知 Oracle 在实现此过程中使用了并行查询，不同的并行子进程在不同的 PDB 中查询相关表，最后在 CDB 级别中的汇总显示。
SQL> SELECT OBJECT_NAME FROM CONTAINERS(TT) WHERE rownum<11;
SELECT OBJECT_NAME FROM CONTAINERS(TT) WHERE rownum<11
*
ERROR at line 1:
ORA-12801: error signaled IN parallel query server P000, instance dbserver1.vbox.com:cdb12c_1 (1)
ORA-00904: "OBJECT_NAME": invalid identifier
 
 
SQL> SELECT COUNT(*) FROM CONTAINERS(TT) WHERE OBJECT_NAME='XX';
SELECT COUNT(*) FROM CONTAINERS(TT) WHERE OBJECT_NAME='XX'
*
ERROR at line 1:
ORA-12801: error signaled IN parallel query server P000, instance dbserver1.vbox.com:cdb12c_1 (1)
ORA-00904: "OBJECT_NAME": invalid identifier
  • 因此可以将所有期望聚合查询的列都加入到 C##KAMUS 用户的TT表中，此处增加了 OBJECT_NAME 字段，可以看到特意在测试中增加了 number 类型的 OBJECT_NAME 字段，而 PDB 中的 OBJECT_NAME 字段均为 varchar2 类型，因此可见只需列名称相同即可，无需类型相同。
SQL> ALTER TABLE TT ADD OBJECT_NAME NUMBER(10);
 
TABLE altered.
 
SQL> SELECT OBJECT_NAME FROM CONTAINERS(TT) WHERE rownum<11;
 
OBJECT_NAME
------------------------------------
ICOL$
I_CDEF3
TS$
CDEF$
I_FILE2
I_OBJ5
I_OBJ1
I_OBJ4
I_USER2
I_COL2
 
10 ROWS selected.
 
SQL> SELECT COUNT(*) FROM CONTAINERS(TT) WHERE OBJECT_NAME LIKE 'ICOL%';
 
  COUNT(*)
----------
        12
  • 从以上已经看出，如果更简单，那么在 C##KAMUS 中创建一个与 PDB 中 KAMUS.TT 表完全相同结构的空表即可。这里用 impdp 来实现。
impdp C##KAMUS/oracle@db-cluster-scan:1521/cdb12c DIRECTORY=dpump DUMPFILE=expdat.dmp EXCLUDE=TABLE_DATA 
TABLES=KAMUS.TT REMAP_SCHEMA=KAMUS:C##KAMUS
 
SQL> SHOW con_name
 
CON_NAME
------------------------------
CDB$ROOT
SQL> SHOW USER
USER IS "C##KAMUS"
SQL> SELECT TABLE_NAME FROM tabs;
 
TABLE_NAME
------------------------------
TT
 
SQL> SELECT COUNT(*) FROM TT;
 
  COUNT(*)
----------
         0
 
SQL>
SQL> SELECT COUNT(*) FROM CONTAINERS(TT);
 
  COUNT(*)
----------
    117386
 
SQL> SELECT COUNT(*) FROM CONTAINERS(TT) WHERE CON_ID IN (3);
 
  COUNT(*)
----------
     58693
  • 查看执行计划，在执行计划中已经完全没有显示最终表的名称，而是出现 X$CDBVW$ 这样的 FIXED TABLE 名称，在 CDB 中的执行计划将很难判断真实的执行路径。
SQL> SET autot ON
SQL> SELECT COUNT(*) FROM CONTAINERS(TT);
 
 
  COUNT(*)
----------
    117386
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 3954817379
 
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | ROWS  | Cost (%CPU)| TIME     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     1 |     1 (100)| 00:00:01 |       |       |        |      |            |
|   1 |  SORT AGGREGATE           |          |     1 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR          |          |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000 |     1 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |     1 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX PARTITION LIST ALL|          | 58693 |     1 (100)| 00:00:01 |     1 |   254 |  Q1,00 | PCWC |            |
|   6 |       FIXED TABLE FULL    | X$CDBVW$ | 58693 |     1 (100)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------
 
 
Statistics
----------------------------------------------------------
     117574  recursive calls
          0  db block gets
      58796  consistent gets
          0  physical reads
        124  redo SIZE
        544  bytes sent via SQL*Net TO client
        551  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
         13  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

结论：操作起来稍显复杂，功能正常。

鼓励发表数据库选件 (DBO) 相关的内容或在 Oracle 技术网上发表文章。参见在 Oracle 技术网上发表技术文章。