Chapter 4 specifies the attribute of the inmemory column on the no inmemory table for im enabled filling objects: examples (Part IV of im-4.4)

In-Memory The manual : Preface （IM- Preface ）

Chapter one Oracle Database In-Memory Relevant concepts (IM-1.1)

Chapter one Oracle Database In-Memory Relevant concepts （ To continue ）(IM-1.2)

Chapter two Oracle Database In-Memory Architecture ( On ) (IM-2.1)

Chapter two In-Memory Architecture (IM-2.2)

Chapter two IM Architecture ：CPU framework ：SIMD Vector processing （IM-2.3）

The third chapter Enable and adjust IM The size of the column store （IM-3.1）

Chapter four by In-Memory Enable fill objects （IM-4.1 The first part ）

Chapter four by In-Memory Enable fill objects （IM-4.2 The second part ）

Chapter four by IM Enable and disable columns for fill objects （IM-4.3 The third part ） This article is IM Chapter 4 of the series ： stay NO INMEMORY The table specifies INMEMORY Column properties ： Example （IM-4.4 The first part ）

stay NO INMEMORY The table specifies INMEMORY Column properties ： Example

from OracleDatabase 12c The first 2 edition （12.2） Start , Can be specified as INMEMORY Specifies the column level of the object INMEMORY Clause .

In previous releases , Rank INMEMORY Clause only in INMEMORY Valid when specified on a table or partition . This restriction means that when a table or partition is associated with INMEMORY Before clause Association , This column cannot be associated with INMEMORY Clause is associated with .

from OracleDatabase 12c Release 2（12.2） Start , If... Is specified at the column level INMEMORY Clause , The database will record the properties of the specified column . If the table is NO INMEMORY（ The default value ）, Specify the table or partition as INMEMORY Before , Column level properties do not affect how the table is queried . If you mark the table itself as NO INMEMORY, The database will delete any existing column level attributes .

In this example , Your goal is to ensure that the columns in the partitioned table c3 Never fill up to IM Column storage . You can perform the following steps ：

1. Create a partition table t as follows ：

CREATE TABLE t (c1 NUMBER, c2 NUMBER, c3 NUMBER) 

  NO INMEMORY -- this clause specifies the table itself as NO INMEMORY

  PARTITION BY LIST (c1) 

    ( PARTITION p1 VALUES (0), 

      PARTITION p2 VALUES (1), 

      PARTITION p3 VALUES (2) );

surface t yes NO INMEMORY. The table consists of columns c1 List partition on , And has three partitions ：p1,p2 and p3.

2. Compression of columns in query tables （ Include sample output ）：

COL TABLE_NAME FORMAT a20

COL COLUMN_NAME FORMAT a20

SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION

FROM   V$IM_COLUMN_LEVEL

WHERE  TABLE_NAME = 'T'

ORDER BY COLUMN_NAME;

no rows selected

As shown in the output , Column level is not set INMEMORY attribute .

3. Make sure that the columns c3 Never filled , Please put NO INMEMORY Property is applied to the column c3：

ALTER TABLE t NO INMEMORY (c3);

4. Compression of columns in query tables （ Include sample output ）：

SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION

FROM   V$IM_COLUMN_LEVEL

WHERE  TABLE_NAME = 'T'

ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION

-------------------- -------------------- --------------------

T                    C1                   DEFAULT

T                    C2                   DEFAULT

T                    C3                   NO INMEMORY

The database has recorded c3 Of NO INMEMORY attribute . Other columns use default compression .

5. Partition p3 Designated as INMEMORY：

ALTER TABLE t 

  MODIFY PARTITION p3 

    INMEMORY PRIORITY CRITICAL;

Because column c3 Previously designated as NO INMEMORY, So zoning p3 The initial population of will not include columns c3.

6. Specify the entire table as INMEMORY：

ALTER TABLE t INMEMORY;

7. Compression of columns in query tables （ Include sample output ）：

SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION

FROM   V$IM_COLUMN_LEVEL

WHERE  TABLE_NAME = 'T'

ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION

-------------------- -------------------- --------------------------

T                    C1                   DEFAULT

T                    C2                   DEFAULT

T                    C3                   NO INMEMORY

The database retains Columns c3 Of NO INMEMORY Set up . Other columns use default compression .

1. Pair column c1 and c2 Apply different compression levels ：

ALTER TABLE t 

  INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (c1) 

  INMEMORY MEMCOMPRESS FOR CAPACITY LOW (c2);

9. Compression of columns in query tables （ Include sample output ）：

SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION

FROM   V$IM_COLUMN_LEVEL

WHERE  TABLE_NAME = 'T'

ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION

-------------------- -------------------- --------------------------

T                    C1                   FOR CAPACITY HIGH

T                    C2                   FOR CAPACITY LOW

T                    C3                   NO INMEMORY

Each column now has a different compression level .

10. Specify the entire table as NO INMEMORY：

ALTER TABLE t NO INMEMORY;

11. Compression of columns in query tables （ Include sample output ）：

SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION

FROM   V$IM_COLUMN_LEVEL

WHERE  TABLE_NAME = 'T'

ORDER BY COLUMN_NAME;

no rows selected

Because the whole table is specified as NO INMEMORY, So the database deletes all column levels INMEMORY attribute .

Shandong Oracle User group (Shandong Oracle User Group), abbreviation ：SDOUG, Is a full of vitality 、 Young non-profit organizations , It aims to provide an exchange platform for technology lovers in Jinan and surrounding areas .SDOUG Organize offline technology sharing activities from time to time , Promote local and surrounding IT Technological development 、 Help technology enthusiasts improve themselves . Share technology 、 Share happiness ,SDOUG On the road .