Oracle 将数据加载到CLOB列

你好，

我有2个表T1和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为空。
然后我将T1复制到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)
复制

当我检查这两个表的数据占用时 (使用user_segments-在T2的情况下，我也将LOB分区和索引求和)，T2要大得多: T2 57.19mb (T2-57MB，LOB分区0.13mb，LOB索引0.06mb) vs T1 18mb。

为什么T2比T1大得多，即使存储的信息是相同的？
是否可以更改T2的定义或其他数据库选项以减小T2的大小？

致以最诚挚的问候，
格雷格

额外信息:
声明的结果

select trunc(length(M_PARAM)/50), count(*) from t1 group by trunc(length(M_PARAM)/50) order by 1
复制

TRUNC (长度 (M_PARAM)/50) 计数 (*)
-----------------------------
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

我忘了提到T1有更多的分区，但我确实复制了唯一的分区。

致以最诚挚的问候，
格雷格

抱歉-我不能复制你的结果。我把一切都放在一个名为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.
复制

大约有55兆数据。现在我会的

-创建T2
-将数据复制到其中
-下降T1

所以演示中唯一剩下的就是T2段。

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>
复制

所以我只看到了其中的一小部分。现在这是可以预料的，因为我的lob很容易压缩，但是我看不到您会增长的人 * 除非 * 默认情况下将您的范围大小选择为较大。

因此，深入研究xxx_EXTENTS并检查每个区域的大小。