今天要对一张大表进行分表,大表的定义大致如下(省略了一些):
create table RUNLOG_DETAIL
(
DETAIL_ID NUMBER(10) not null,
RECORD_ID VARCHAR2(20),
FIELD_VALUE VARCHAR2(4000),
FIELD_VALUE_CLOB CLOB
);
create table RUNLOG_DETAIL_HIS
(
DETAIL_ID NUMBER(10) not null,
RECORD_ID VARCHAR2(20),
FIELD_VALUE VARCHAR2(4000),
FIELD_VALUE_CLOB CLOB
);
在进行下列操作的时候出现ORA-00932的错误
select * from RUNLOG_DETAIL
minus
select * from RUNLOG_DETAIL_HIS;
在oracle 的联机文档中查到minus的限制:
Restrictions on the Set Operators The set operators are subject to the following restrictions:
-
The set operators are not valid on columns of type
BLOB
,CLOB
,BFILE
,VARRAY
, or nested table.
想再深入的了解一下,执行下列语句同样报这个错
select distinct FIELD_VALUE_CLOB from RUNLOG_DETAIL;
揣测是distinct 的问题,union、minus、INTERSECT 用到了distinct, union all没有用到,实验下列语句没有错
select * from RUNLOG_DETAIL
union all
select * from RUNLOG_DETAIL_HIS;
当实验union、INTERSECT 则同样的报错,结论:不能进行distinct clob。
另:union、minus、INTERSECT 用到了distinct, union all没有用到实验:
SQL> create table test1(a varchar2(10));
Table created
SQL> create table test2(a varchar2(10));
Table created
SQL> insert into test1 values(1);
1 row inserted
SQL> insert into test1 values(1);
1 row inserted
SQL> insert into test1 values(2);
1 row inserted
SQL> insert into test1 values(2);
1 row inserted
SQL> insert into test2 values(1);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test1
2 minus
3 select * from test2;
A
----------
2
SQL> select * from test1
2 INTERSECT
3 select * from test2;
A
----------
1
SQL> select * from test1
2 union
3 select * from test2;
A
----------
1
2
SQL> select * from test1
2 union all
3 select * from test2;
A
----------
1
1
2
2
1