1.建立一个表空间，并指定INMEMORY字句
SQL> CREATE TABLESPACE tbs1 datafile 'tbs1' SIZE 40M? DEFAULT INMEMORY;

2.在新建立的表空间上建立一张表，且表不指定INMEMORY
SQL> create table bmw.tt tablespace tbs1 as select * from dba_objects ;
Table created.

3.可以看到新建立的表也都开启了INMEMORY，这是因为我们在表空间级别开启了INMEMORY。
SQL> l
? 1* select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from dba_tables where table_name='TT'
SQL> /

TABLE_NAME? ? ? ? ? INMEMORY_PRIORITY? ? ? ? INMEMORY_DISTRIBUTE? ? ? ? ? ? ? ? ? ? ? ? ? INMEMORY_COMPRESSION
-------------------- ------------------------ --------------------------------------------- ------------------------------
TT? ? ? ? ? ? ? ? ? NONE? ? ? ? ? ? ? ? ? ? AUTO? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? FOR QUERY LOW

SQL> l
? 1? SELECT * FROM V$INMEMORY_AREA
? 2*
SQL> /
POOL? ? ? ALLOC_BYTES USED_BYTES POPULATE_S? ? CON_ID
---------- ----------- ---------- ---------- ----------
1MB POOL? ? 837812224? ? 4194304 DONE? ? ? ? ? ? ? ? 3
64KB POOL? ? 201326592? ? 131072 DONE? ? ? ? ? ? ? ? 3

当表没有查询时，我们可以看到并未分配内存

4.查询表
SQL> set autot trace
SQL> SELECT * FROM bmw.tt;
90935 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
-----------------------------------------------------------------------------------
| Id? | Operation? ? ? ? ? ? ? ? ? | Name | Rows? | Bytes | Cost (%CPU)| Time? ? |
-----------------------------------------------------------------------------------
|? 0 | SELECT STATEMENT? ? ? ? ? |? ? ? | 90935 |? ? 9M|? ? 32? (16)| 00:00:01 |
|? 1 |? TABLE ACCESS INMEMORY FULL| TT? | 90935 |? ? 9M|? ? 32? (16)| 00:00:01 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
? ? ? ? ? 6? recursive calls
? ? ? ? ? 0? db block gets
? ? ? 7485? consistent gets
? ? ? 1527? physical reads
? ? ? ? ? 0? redo size
? ? 4945264? bytes sent via SQL*Net to client
? ? ? 67234? bytes received via SQL*Net from client
? ? ? 6064? SQL*Net roundtrips to/from client
? ? ? ? ? 0? sorts (memory)
? ? ? ? ? 0? sorts (disk)
? ? ? 90935? rows processed

以上虽然使用了TABLE ACCESS INMEMORY FULL，但由于表未在内存中，还是产生了大量的 consistent gets

5.再查询测试一下，可以看到consistent gets降到了3
SQL> /
90935 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
-----------------------------------------------------------------------------------
| Id? | Operation? ? ? ? ? ? ? ? ? | Name | Rows? | Bytes | Cost (%CPU)| Time? ? |
-----------------------------------------------------------------------------------
|? 0 | SELECT STATEMENT? ? ? ? ? |? ? ? | 90935 |? ? 9M|? ? 32? (16)| 00:00:01 |
|? 1 |? TABLE ACCESS INMEMORY FULL| TT? | 90935 |? ? 9M|? ? 32? (16)| 00:00:01 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
? ? ? ? ? 0? recursive calls
? ? ? ? ? 0? db block gets
? ? ? ? ? 3? consistent gets
? ? ? ? ? 0? physical reads
? ? ? ? ? 0? redo size
? ? 4945264? bytes sent via SQL*Net to client
? ? ? 67234? bytes received via SQL*Net from client
? ? ? 6064? SQL*Net roundtrips to/from client
? ? ? ? ? 0? sorts (memory)
? ? ? ? ? 0? sorts (disk)
? ? ? 90935? rows processed
SQL> set autot off
SQL> SELECT * FROM V$INMEMORY_AREA;
POOL? ? ? ALLOC_BYTES USED_BYTES POPULATE_S? ? CON_ID
---------- ----------- ---------- ---------- ----------
1MB POOL? ? 837812224? ? 8388608 DONE? ? ? ? ? ? ? ? 3
64KB POOL? ? 201326592? ? 262144 DONE? ? ? ? ? ? ? ? 3