Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

In-memory on Partition Exchange loads

Rajeshwaran JeyabalMar 27 2015 — edited Mar 27 2015

I am reading about the in-memory technical paper @ OTN & practicing the partition exchange loads mentioned at the page no# 14

Can you help me to understand why after exchange partition the Inmemory column store is not getting updated with appropriate segment types?

rajesh@PDB1> create table t1

  2 partition by list(id)

  3  ( partition p1 values (1) )

  4  as

  5 select a.*, 1 id

  6  from all_objects a;

Table created.

rajesh@PDB1> alter table t1 inmemory;

Table altered.

rajesh@PDB1> select count(*) from t1;

  COUNT(*)

----------

     89338

1 row selected.

rajesh@PDB1>

rajesh@PDB1> select segment_name,partition_name,segment_type,

  2 inmemory_size,bytes_not_populated,populate_status

  3  from v$im_segments ;

SEGMENT_NA PARTITION_ SEGMENT_TYPE    INMEMORY_SIZE BYTES_NOT_POPULATED POPULATE_

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

T1         P1         TABLE PARTITION       4325376                   0 COMPLETED

1 row selected.

rajesh@PDB1> create table t2

  2 INMEMORY

  3  as

  4 select a.*,2 id

  5  from all_objects a;

Table created.

rajesh@PDB1> select segment_name,partition_name,segment_type,

  2 inmemory_size,bytes_not_populated,populate_status

  3  from v$im_segments ;

SEGMENT_NA PARTITION_ SEGMENT_TYPE    INMEMORY_SIZE BYTES_NOT_POPULATED POPULATE_

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

T1         P1         TABLE PARTITION       4325376                   0 COMPLETED

1 row selected.

rajesh@PDB1> select count(*) from t2;

  COUNT(*)

----------

     89339

1 row selected.

rajesh@PDB1> select segment_name,partition_name,segment_type,

  2 inmemory_size,bytes_not_populated,populate_status

  3  from v$im_segments ;

SEGMENT_NA PARTITION_ SEGMENT_TYPE    INMEMORY_SIZE BYTES_NOT_POPULATED POPULATE_

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

T2                    TABLE                 4325376                   0 COMPLETED

T1         P1         TABLE PARTITION       4325376                   0 COMPLETED

2 rows selected.

rajesh@PDB1> alter table t1

  2  add partition p2 values(2);

Table altered.

rajesh@PDB1> alter table t1

  2 exchange partition p2

  3  with table t2 ;

Table altered.

rajesh@PDB1> select count(*) from t1;

  COUNT(*)

----------

    178677

1 row selected.

rajesh@PDB1> select count(*) from t1 partition (p2);

  COUNT(*)

----------

     89339

1 row selected.

rajesh@PDB1> select count(*) from t2;

  COUNT(*)

----------

         0

1 row selected.

rajesh@PDB1> exec dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

rajesh@PDB1> select segment_name,partition_name,segment_type,

  2 inmemory_size,bytes_not_populated,populate_status

  3  from v$im_segments ;

SEGMENT_NA PARTITION_ SEGMENT_TYPE    INMEMORY_SIZE BYTES_NOT_POPULATED POPULATE_

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

T2                    TABLE                 4325376                   0 COMPLETED

T1         P1         TABLE PARTITION       4325376                   0 COMPLETED

2 rows selected.

rajesh@PDB1>

rajesh@PDB1> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'STORAGE',FALSE);

PL/SQL procedure successfully completed.

rajesh@PDB1> select dbms_metadata.get_ddl('TABLE','T1') from dual;

DBMS_METADATA.GET_DDL('TABLE','T1')

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

  CREATE TABLE "RAJESH"."T1"

   ( "OWNER" VARCHAR2(128) NOT NULL ENABLE,

        "OBJECT_NAME" VARCHAR2(128) NOT NULL ENABLE,

        "SUBOBJECT_NAME" VARCHAR2(128),

        "OBJECT_ID" NUMBER NOT NULL ENABLE,

        "DATA_OBJECT_ID" NUMBER,

        "OBJECT_TYPE" VARCHAR2(23),

        "CREATED" DATE NOT NULL ENABLE,

        "LAST_DDL_TIME" DATE NOT NULL ENABLE,

        "TIMESTAMP" VARCHAR2(19),

        "STATUS" VARCHAR2(7),

        "TEMPORARY" VARCHAR2(1),

        "GENERATED" VARCHAR2(1),

        "SECONDARY" VARCHAR2(1),

        "NAMESPACE" NUMBER NOT NULL ENABLE,

        "EDITION_NAME" VARCHAR2(128),

        "SHARING" VARCHAR2(13),

        "EDITIONABLE" VARCHAR2(1),

        "ORACLE_MAINTAINED" VARCHAR2(1),

        "ID" NUMBER

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  TABLESPACE "DATA_12C"

  INMEMORY PRIORITY NONE MEMCOMPRESS FOR QUERY LOW

  DISTRIBUTE AUTO NO DUPLICATE

  PARTITION BY LIST ("ID")

(PARTITION "P1"  VALUES (1)

INMEMORY PRIORITY NONE MEMCOMPRESS FOR QUERY LOW

  DISTRIBUTE AUTO NO DUPLICATE SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

  TABLESPACE "DATA_12C" ,

PARTITION "P2"  VALUES (2)

INMEMORY PRIORITY NONE MEMCOMPRESS FOR QUERY LOW

  DISTRIBUTE AUTO NO DUPLICATE SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

  TABLESPACE "DATA_12C" )

1 row selected.

rajesh@PDB1>

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 24 2015
Added on Mar 27 2015
0 comments
325 views