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
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.