12C新特性：IN-MEMORY实战（7）如何将数据填充到inmemory中

将数据填充到inmemory中有以下几种办法

1、使用count语句填充

这个之前的实验中有提及，保险起见需要加上full hint保证全表查询。实验如下：

SQL>  ALTER table  test1 no inmemory;

Table altered.

SQL>  ALTER table  test1 INMEMORY;

Table altered.

SQL>  select INST_ID,SEGMENT_NAME,INMEMORY_SIZE/1024/1024,BYTES/1024/1024,BYTES_NOT_POPULATED/1024/1024,INMEMORY_DISTRIBUTE,INMEMORY_DUPLICATE,POPULATE_STATUS from gv$im_segments;

no rows selected

SQL> SELECT /*+ FULL(test1) NO_PARALLEL(test1) */ COUNT(*) FROM test1;

  COUNT(*)
----------
     72573

SQL> select INST_ID,SEGMENT_NAME,INMEMORY_SIZE/1024/1024,BYTES/1024/1024,BYTES_NOT_POPULATED/1024/1024,INMEMORY_DISTRIBUTE,INMEMORY_DUPLICATE,POPULATE_STATUS from gv$im_segments;

   INST_ID SEGME INMEMORY_SIZE/1024/1024 BYTES/1024/1024 BYTES_NOT_POPULATED/1024/1024 INMEMORY_D INMEMORY_DUPLICATE   POPULATE_S
---------- ----- ----------------------- --------------- ----------------------------- ---------- -------------------- ----------
         1 TEST1                    4.25       11.015625                             0 AUTO       NO DUPLICATE         COMPLETED

2、PRIORITY HIGH进行填充

SQL>  ALTER table  test1 no inmemory;

Table altered.

SQL>  ALTER table  test1 INMEMORY;

Table altered.

SQL>  select INST_ID,SEGMENT_NAME,INMEMORY_SIZE/1024/1024,BYTES/1024/1024,BYTES_NOT_POPULATED/1024/1024,INMEMORY_DISTRIBUTE,INMEMORY_DUPLICATE,POPULATE_STATUS from gv$im_segments;

no rows selected

SQL> ALTER table  test1 no inmemory;

Table altered.

SQL> ALTER table  test1 INMEMORY PRIORITY HIGH;

Table altered.

SQL> select INST_ID,SEGMENT_NAME,INMEMORY_SIZE/1024/1024,BYTES/1024/1024,BYTES_NOT_POPULATED/1024/1024,INMEMORY_DISTRIBUTE,INMEMORY_DUPLICATE,POPULATE_STATUS from gv$im_segments;

   INST_ID SEGME INMEMORY_SIZE/1024/1024 BYTES/1024/1024 BYTES_NOT_POPULATED/1024/1024 INMEMORY_D INMEMORY_DUPLICATE   POPULATE_S
---------- ----- ----------------------- --------------- ----------------------------- ---------- -------------------- ----------
         1 TEST1                    4.25       11.015625                             0 AUTO       NO DUPLICATE         COMPLETED

3、使用DBMS_INMEMORY.POPULATE填充

SQL> ALTER table  test1 no inmemory;

Table altered.

SQL> ALTER table  test1 INMEMORY;

Table altered.

SQL> select INST_ID,SEGMENT_NAME,INMEMORY_SIZE/1024/1024,BYTES/1024/1024,BYTES_NOT_POPULATED/1024/1024,INMEMORY_DISTRIBUTE,INMEMORY_DUPLICATE,POPULATE_STATUS from gv$im_segments;

no rows selected

SQL> BEGIN
  2    DBMS_INMEMORY.POPULATE( schema_name => 'TEST', table_name => 'TEST1');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> select INST_ID,SEGMENT_NAME,INMEMORY_SIZE/1024/1024,BYTES/1024/1024,BYTES_NOT_POPULATED/1024/1024,INMEMORY_DISTRIBUTE,INMEMORY_DUPLICATE,POPULATE_STATUS from gv$im_segments;

   INST_ID SEGME INMEMORY_SIZE/1024/1024 BYTES/1024/1024 BYTES_NOT_POPULATED/1024/1024 INMEMORY_D INMEMORY_DUPLICATE   POPULATE_S
---------- ----- ----------------------- --------------- ----------------------------- ---------- -------------------- ----------
         1 TEST1                    4.25       11.015625                             0 AUTO       NO DUPLICATE         COMPLETED