【新特性】04.创建和填充外部表到In-Memory
Oracle Database 18c支持将外部表中的数据填充到内存列存储(IM列存储)中。这个功能充许用户将外部文件中的数据加载到IM列存储中。只是这个填充过程必须通过执行DBMS_INMEMORY.POPULATE来手动完成。
在Exadata上运行的Oracle Database 19c环境中，对外部表使用IM列存储进行了增强，如果启用了内存的外部表功能，就会自动将外部数据填充到IM列存储中。在实际的项目中利用Oracle Database In-Memory的功能极大加速对外部数据的查询和数据聚合，这个功能可以很好的帮助在某些数据仓库或报表项目中需要将存储在外部表的历史冷数据纳入统计分析，从而帮助应用加速运行。
一、环境准备
本次测试需要CDB和一个PDB，一个测试文件放在/opt/XieBro/下，内用以逗号分割
[root@T1 opt]# mkdir XieBro [root@T1 opt]# cd XieBro [root@T1 opt]# cat db2021.txt 1,ZhangSan 2,LiSi 3,WangWu [root@T1 opt]# chown -R oracle.dba XieBro/
二、配置In-Memory Column Store Size
要使用内存列格式存储数据，我们首先要分配一部分内存区域用来存储列格式数据，我们只需要配置一个参数INMEMORY_SIZE既可启用列格式内存。
如果配置失败可以参考文章
[oracle@T1 oracle]$ sqlplus sys/oracle as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 20 09:15:14 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> alter system set inmemory_size=200m scope=spfile; System altered.
重启pdb使参数生效
三、创建数据库目录对象
SQL> create directory extfiles as '/opt/XieBro/'; Directory created.
四、创建In-Memory外部表
在pdb里面创建一个用户，此用户拥有In-Memory外部表对象，同时将数据库目录对象授权给此用户。
SQL> create user hypt identified by hypt default tablespace users quota unlimited on users; User created. SQL> grant read,write on directory extfiles to hypt; Grant succeeded. SQL> grant create session,create table to hypt; Grant succeeded.
创建内存外部表，使用INMEMORY MEMCOMPRESS FOR CAPACITY HIGH子句
SQL> create table hypt.inmem_ext_tab (id number,name varchar2(20)) 2 organization external 3 (type oracle_loader default directory extfiles 4 access parameters (fields terminated by ',') 5 location ('db2021.txt')) 6 inmemory memcompress for capacity high; Table created.
检查此外部表的In-Memory是否开启，如下所示，可以看到此外部表已经启用In-Memory了
SQL> COL TABLE_NAME FOR A20 SQL> COL INMEMORY FOR A20 SQL> COL INMEMORY_COMPRESSION FOR A30 SQL> SELECT TABLE_NAME,INMEMORY,INMEMORY_COMPRESSION FROM DBA_EXTERNAL_TABLES WHERE OWNER='HYPT'; TABLE_NAME INMEMORY INMEMORY_COMPRESSION -------------------- -------------------- ------------------------------ INMEM_EXT_TAB ENABLED FOR CAPACITY HIGH
五、查询In-Memory外部表
内存中外部表的查询必须先将QUERY_REWRITE_INTEGRITY参数设置为stale_tolerated
SQL> alter session set query_rewrite_integrity=stale_tolerated; Session altered. SQL> select * from hypt.inmem_ext_tab; ID NAME ---------- -------------------- 1 ZhangSan 2 LiSi 3 WangWu
检查一下此外部表数据是否被加载到In-Memory内存中，如下图所示可以看到此外部表已成功加载到In-Memory内，从而验证了19c可以自动装载外部表的数据到In-Memory中，无需手动装载。
SQL> SELECT SEGMENT_NAME,POPULATE_STATUS FROM V$IM_SEGMENTS; SEGMENT_NAME POPULATE_STATUS -------------- ----------------- INMEM_EXT_TAB COMPLETED
六、确认查询In-Memory外部表是否使用In-Memory数据访问
SQL> explain plan for select /*+ PARALLEL(2)*/ * from hypt.inmem_ext_tab; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 4280284238 ------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | TQ |IN-OUT| PQ Distrib| ------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 102K| 2492K| 189 (0)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 102K| 2492K| 189 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 102K| 2492K| 189 (0)| 00:00:01 | Q1,00 | PCWC | | | 4 | EXTERNAL TABLE ACCESS INMEMORY FULL| INMEM_EXT_TAB | 102K| 2492K| 189 (0)| 00:00:01 | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ Note ----- - Degree of Parallelism is 2 because of hint 15 rows selected.
七、总结
上面的例子验证了Oracle database 19c In-Memory在外部表中的增强，可以自动装载外部表的数据到In-Memory，中无需手动装载，从而加快了数仓、报表用到外部表数据的应用，简单、直接、自动化。
