2 Replies Latest reply on Mar 26, 2013 4:25 PM by user5716448

    Range interavl partitioned table not being created in compressed format

    user5716448
      Hi,

      Using oracle 11.2.03. and trying to create a table with compress


      Normal table fine but trying syntax below but tabel properties show no compression

      Tried compress at top of sql as per belwo and at bottom

      Aldso tried compress and compress for oltp - same bahviour not getting compressed.

      nOW HSOING AT TABLE LEVEL OR ON TABLE PORPETIES BUT WHEN check user_tab_partitions shows as compressed for oltp

      Thanks
      create table RETAILER_TRANSACTION_comP_POR parallel
      COMPRESS FOR OLTP
      
      partition by range (PARTITIONING_DATE)
      INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
      subpartition by hash (PLANT_ISSUE_ID) 
      subPARTITIONS 4
      (
        partition PART_200912 values less than (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_200912
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201001 values less than (TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201001
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201002 values less than (TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201002
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201003 values less than (TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201003
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201004 values less than (TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201004
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201005 values less than (TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201005
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201006 values less than (TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201006
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201007 values less than (TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace RTRN_PART_201007
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201008 values less than (TO_DATE(' 2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201008
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201009 values less than (TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201009
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201010 values less than (TO_DATE(' 2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201010
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201011 values less than (TO_DATE(' 2010-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201011
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201012 values less than (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201012
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201101 values less than (TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201101
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201102 values less than (TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201102
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201103 values less than (TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201103
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201104 values less than (TO_DATE(' 2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201104
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201105 values less than (TO_DATE(' 2011-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201105
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201106 values less than (TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201106
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201107 values less than (TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201107
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201108 values less than (TO_DATE(' 2011-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201108
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201109 values less than (TO_DATE(' 2011-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201109
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201110 values less than (TO_DATE(' 2011-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201110
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201111 values less than (TO_DATE(' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201111
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201112 values less than (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
         tablespace RTRN_PART_201112
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201201 values less than (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201201
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201202 values less than (TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
           tablespace RTRN_PART_201202
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201203 values less than (TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201203
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201204 values less than (TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201204
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201205 values less than (TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
           tablespace RTRN_PART_201205
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201206 values less than (TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201206
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201207 values less than (TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
         tablespace RTRN_PART_201207
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201208 values less than (TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201208
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201209 values less than (TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201209
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201210 values less than (TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201210
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201211 values less than (TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201211
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201212 values less than (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
            tablespace RTRN_PART_201212
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201301 values less than (TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
            tablespace RTRN_PART_201301
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201302 values less than (TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201302
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201303 values less than (TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201303
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201304 values less than (TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
         tablespace RTRN_PART_201304
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201305 values less than (TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201305
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201306 values less than (TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201306
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201307 values less than (TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201307
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201308 values less than (TO_DATE(' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201308
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201309 values less than (TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201309
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201310 values less than (TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201310
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201311 values less than (TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201311
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          ),
        partition PART_201312 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          tablespace RTRN_PART_201312
          pctfree 1
          initrans 1
          maxtrans 255
          storage
          (
            initial 8M
            next 1M
            minextents 1
            maxextents unlimited
          )
      )
      
      --COMPRESS --FOR OLTP
      as select *
      from retailer_transaction RT
      WHERE RT.DWH_NUM = 1441336376
      Edited by: user5716448 on 26-Mar-2013 07:16
        • 1. Re: Range interavl partitioned table not being created in compressed format
          rp0428
          >
          Normal table fine but trying syntax below but tabel properties show no compression
          >
          What properties? You didn't show any query or result that shows if the table is compressed or not.
          >
          nOW HSOING AT TABLE LEVEL OR ON TABLE PORPETIES BUT WHEN check user_tab_partitions shows as compressed for oltp
          >
          I have no idea what any of that means.

          Again - you didn't post anything showing if the table is compressed or not. And without the DDL for the source table no one can try to reproduce your results.

          Thanks for using code tags and posting some of the DDL needed but you should edit your post and REMOVE all of the extra partition definitions since all they do is bloat the listing.

          This works for me in vanilla 11.2.0.1.0
          create table EMP_PART parallel
          COMPRESS FOR OLTP
          partition by range (hiredate)
          INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
          subpartition by hash (deptno) 
          subPARTITIONS 4
          (
            partition all_data values less than (to_date('01/01/2013', 'mm/dd/yyyy')
          ))
          as select * from emp
          
          select table_name, compression, compress_for, composite, partition_name
          from user_tab_partitions
          
          TABLE_NAME     COMPRESSION     COMPRESS_FOR     COMPOSITE     PARTITION_NAME
          EMP_PART     ENABLED     OLTP     YES     ALL_DATA
          • 2. Re: Range interavl partitioned table not being created in compressed format
            user5716448
            Hi,


            Thanks for update.

            Yes - looks ok when look at user_tab_partitions.

            Was klooking in user_tables and not seeing any compression set.

            Thanks