Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Greg.

Asked: April 06, 2017 - 8:11 am UTC

Last updated: April 18, 2017 - 2:48 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 1000+ times

You Asked

Hello,

I have 2 tables T1 and T2:
  CREATE TABLE "T1" 
  ("DATE_M" DATE,    "ID" VARCHAR2(20), 
  "ADDR" VARCHAR2(17),  "VER" VARCHAR2(50), 
  "MODEL" VARCHAR2(10),   "ADD_I" VARCHAR2(10), 
  "SN" VARCHAR2(15),   "MODE" VARCHAR2(5), 
  "DATE_T" DATE, 
  "M_TYPE" VARCHAR2(50), 
  "M_PARAM" VARCHAR2(2500)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255 
 COMPRESS BASIC  NOLOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS" 
  PARTITION BY RANGE ("DATE_T") 
 (PARTITION "P_1"  VALUES LESS THAN (TO_DATE(' 2016-07-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE 
  PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255 
 COMPRESS BASIC NOLOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS" )

  CREATE TABLE T2
  ("LD_ID" DATE,     "DATE_M" DATE, 
  "ID" VARCHAR2(20),   "ADDR" VARCHAR2(17), 
  "VER" VARCHAR2(50),  "MODEL" VARCHAR2(10), 
  "ADD_I" VARCHAR2(10), "SN" VARCHAR2(15), "MODE" VARCHAR2(5), 
  "DATE_T" TIMESTAMP (6), 
  "M_TYPE" VARCHAR2(50),   
  "M_PARAM" CLOB
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255 
 COMPRESS BASIC  NOLOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS" 
 LOB ("M_PARAM") STORE AS SECUREFILE (
  ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE NOLOGGING  COMPRESS MEDIUM  KEEP_DUPLICATES 
  STORAGE(INITIAL 65536 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
  PARTITION BY RANGE ("DATE_T") 
 (PARTITION "P_1"  VALUES LESS THAN (TIMESTAMP' 2017-03-03 00:00:00') SEGMENT CREATION IMMEDIATE 
  PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255 
 COMPRESS BASIC NOLOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS" 
 LOB ("M_PARAM") STORE AS SECUREFILE (
  TABLESPACE "TS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE NOLOGGING  COMPRESS MEDIUM  KEEP_DUPLICATES 
  STORAGE(INITIAL 65536 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ) 


T2 is empty.
Then I copy the T1 into T2:
insert /*+ append */ into T2 (DATE_M,ID,ADDR,VER,MODEL,ADD_I,SN,MODE,DATE_T,M_TYPE,M_PARAM)
select * from T1 partition(P_1)


When I check the data occupation of these two tables (using user_segments - in case of T2 I also sum the LOB partition and index) the T2 is much bigger: T2 57.19MB (T2-57MB, LOB partition 0.13MB, LOB index 0.06MB) vs T1 18MB.

Why the T2 is much bigger than T1 even though the stored information is the same?
Is it possible to change definition of T2 or other database options to reduce size of T2?

Best regards,
Greg

Extra information:
The result of the statement
select trunc(length(M_PARAM)/50), count(*) from t1 group by trunc(length(M_PARAM)/50) order by 1


TRUNC(LENGTH(M_PARAM)/50) COUNT(*)
----------------------------- ----------
0 325983
1 3975
2 16755
3 61
4 352
5 492
6 1785
7 25645
8 47209
9 1602
10 2092
11 50
12 75
13 5
15 5
16 5

I forgot to mention that T1 has much more partitions but I did copy of the only one.

Best regards,
Greg

and Connor said...

Sorry - I can't reproduce your results. I'm putting everything into an empty tablespace called DEMO:


SQL> CREATE TABLE T1
  2    (DATE_M DATE,    ID VARCHAR2(20),
  3    ADDR VARCHAR2(17),  VER VARCHAR2(50),
  4    MODEL VARCHAR2(10),   ADD_I VARCHAR2(10),
  5    SN VARCHAR2(15),   XMODE VARCHAR2(5),
  6    DATE_T DATE,
  7    M_TYPE VARCHAR2(50),
  8    M_PARAM VARCHAR2(2500)
  9     ) SEGMENT CREATION IMMEDIATE
 10    PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255
 11   COMPRESS BASIC  NOLOGGING
 12    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 13    PCTINCREASE 0
 14    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 15    tablespace demo
 16    PARTITION BY RANGE (DATE_T)
 17   (PARTITION P_1  VALUES LESS THAN (TO_DATE(' 2016-07-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
 18    PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255
 19   COMPRESS BASIC NOLOGGING
 20    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 21    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 22    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 23    tablespace demo );

Table created.

SQL>
SQL> insert /*+ append */ into t1
  2  select date '2016-07-27'-rownum/100,rownum,
  3    'address','ver',
  4    'model','addi',
  5    'sn','mode',
  6    date '2016-07-27'-rownum/100,
  7    'type',
  8    rpad(rownum,1+mod(rownum,10)*100,'x')
  9  from dual
 10  connect by level <= 100000;

100000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> col partition_name format a30
SQL>
SQL> select
  2    partition_name,bytes
  3  from user_extents
  4  where tablespace_name = 'DEMO';

PARTITION_NAME                      BYTES
------------------------------ ----------
P_1                                 65536
P_1                                 65536
P_1                                 65536
P_1                                 65536
P_1                                 65536
P_1                                 65536
P_1                                 65536
P_1                                 65536
P_1                                 65536
P_1                                 65536
P_1                                 65536
P_1                                 65536
P_1                                 65536
P_1                                 65536
P_1                                 65536
P_1                                 65536
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576

68 rows selected.

SQL>
SQL> select bytes from user_segments
  2  where tablespace_name = 'DEMO';

     BYTES
----------
  55574528

1 row selected.


So about 55megs of data in there. Now I will

- create T2
- copy the data into it
- drop T1

so that the only thing left in DEMO is T2 segments.

SQL>
SQL>   CREATE TABLE T2
  2    (LD_ID DATE,     DATE_M DATE,
  3    ID VARCHAR2(20),   ADDR VARCHAR2(17),
  4    VER VARCHAR2(50),  MODEL VARCHAR2(10),
  5    ADD_I VARCHAR2(10), SN VARCHAR2(15), XMODE VARCHAR2(5),
  6    DATE_T TIMESTAMP (6),
  7    M_TYPE VARCHAR2(50),
  8    M_PARAM CLOB
  9     ) SEGMENT CREATION IMMEDIATE
 10    PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255
 11   COMPRESS BASIC  NOLOGGING
 12    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 13    PCTINCREASE 0
 14    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 15    tablespace demo
 16   LOB (M_PARAM) STORE AS SECUREFILE (
 17    ENABLE STORAGE IN ROW CHUNK 8192
 18    NOCACHE NOLOGGING  COMPRESS MEDIUM  KEEP_DUPLICATES
 19    STORAGE(INITIAL 65536 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
 20    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
 21    PARTITION BY RANGE (DATE_T)
 22   (PARTITION P_1  VALUES LESS THAN (TIMESTAMP' 2017-03-03 00:00:00') SEGMENT CREATION IMMEDIATE
 23    PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255
 24   COMPRESS BASIC NOLOGGING
 25    STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 26    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 27    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 28    tablespace demo
 29   LOB (M_PARAM) STORE AS SECUREFILE (
 30    tablespace demo ENABLE STORAGE IN ROW CHUNK 8192
 31    NOCACHE NOLOGGING  COMPRESS MEDIUM  KEEP_DUPLICATES
 32    STORAGE(INITIAL 65536 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
 33    PCTINCREASE 0
 34    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ) ;

Table created.

SQL>
SQL>
SQL> insert /*+ append */ into T2 (DATE_M,ID,ADDR,VER,MODEL,ADD_I,SN,XMODE,DATE_T,M_TYPE,M_PARAM)
  2  select * from T1 ;

100000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> drop table t1 purge;

Table dropped.

SQL>
SQL> select
  2    partition_name,bytes
  3  from user_extents
  4  where tablespace_name = 'DEMO';

PARTITION_NAME                      BYTES
------------------------------ ----------
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
P_1                               1048576
SYS_IL_P1018                        65536
SYS_LOB_P1017                      131072

12 rows selected.

SQL>
SQL> select bytes from user_segments
  2  where tablespace_name = 'DEMO';

     BYTES
----------
  10485760
     65536
    131072

3 rows selected.

SQL>
SQL>


So I'm then only seeing a tiny percentage of that. Now that's expected because my lobs are easily compressible, but I can't see who you'd grow *unless* your extent sizes are being chosen as larger by default.

So dig down into xxx_EXTENTS and check each extent size.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.