【Oracle Database 12c New Feature】Aggregate Data Across Many PDBs by CONTAINERS Clause

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

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

现有测试环境如下：当前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

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

Channel [K]

【Oracle Database 12c New Feature】Aggregate Data Across Many PDBs by CONTAINERS Clause

Related

Leave a Reply Cancel reply