IM_COL_TAB 1 ID NO INMEMORY
IM_COL_TAB 2 COL1 NO INMEMORY
IM_COL_TAB 3 COL2 NO INMEMORY
IM_COL_TAB 4 COL3 FOR CAPACITY HIGH
IM_COL_TAB 5 COL4 NO INMEMORY
5 rows selected.
SQL>
6. 管理物化视图
CREATE MATERIALIZED VIEW和ALTER MATERIALIZED VIEW命令和 CREATE TABLE and ALTER TABLE命令类似。
CONN test/test@pdb1
CREATE TABLE t1 AS
SELECT * FROM all_objects;
CREATE MATERIALIZED VIEW t1_mv INMEMORY
ASSELECT * FROM t1;
SELECT table_name,
inmemory,
inmemory_priority,
inmemory_distribute,
inmemory_compression,
inmemory_duplicate
FROM user_tables
WHERE table_name = 'T1_MV';
TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRIINMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- ----------------------- ----------------- -------------
T1_MV ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE
1 row selected.
SQL>
ALTER MATERIALIZED VIEW t1_mv
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH PRIORITY HIGH;
SELECT table_name,
inmemory,
inmemory_priority,
inmemory_distribute,
inmemory_compression,
inmemory_duplicate
FROM user_tables
WHERE table_name = 'T1_MV';
TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRIINMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- ----------------------- ----------------- -------------
T1_MV ENABLED HIGH AUTO FOR CAPACITY HIGHNO DUPLICATE
1 row selected.
SQL>
ALTER MATERIALIZED VIEW t1_mv NO INMEMORY;
SELECT table_name,
inmemory,
inmemory_priority,
inmemory_distribute,
inmemory_compression,
inmemory_duplicate
FROM user_tables
WHERE table_name = 'T1_MV';
TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRIINMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- ----------------------- ----------------- -------------
T1_MV DISABLED
1 row selected.
SQL>
7. 管理表空间
设置表空间的默认内存列存储参数意味着该表空间内的所有表和物化视图将使用那些设置，显示覆盖情况除外。
下列将展示如何在表空间创建期间设置内存列存储参数。关键字DEFAULT是内存列存储子句的一部分。默认内存列存储设置可以查询DBA_TABLESPACES视图查看。
CONN sys@pdb1 AS SYSDBA
CREATE TABLESPACE new_ts
DATAFILE '/u01/app/oracle/oradata/CDB1/datafile/pdb1/pdb1_new_ts.dbf'SIZE 10M
DEFAULT INMEMORY;
SELECT tablespace_name,
def_inmemory,
def_inmemory_priority,
def_inmemory_distribute,
def_inmemory_compression,
def_inmemory_duplicate
FROM dba_tablespaces
ORDER BY tablespace_name;
TABLESPACE_NAME DEF_INME DEF_INMEDEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
------------------------------ ---------------- --------------- ----------------- -------------