oracle exchange特性做sql优化测试

来源：互联网发布：风驰网络加速器官网编辑：程序博客网时间：2024/04/29 04:31

简单介绍：

exchange ，oracle特性之一，用与分区表与非分区表的交换，利用oracle exchange特性提取分区表一部分数据做sql优化测试

表结构有一个对象编号 object_id ，而物理存储segment有一个代表编号 data_object_id ，若是分区表，则一个 object_id 有多个 data_object_id

交换分区的时候，只是把不同 object_id 对应的 data_object_id交换了一下，而segment本身并没有改变

和物理存储位置无关，但必须是分区表和非分区表之间才可以交换

SQL>sqlplus / nologSQL> conn mengl/mengl已连接SQL> create table ml_test (id number(3));表已创建。SQL> insert into ml_test values (1);已创建 1 行。SQL> create table t_part(id number(3)) partition by range(id)  2  (partition t1 values less than (10),  3  partition t2 values less than (20));表已创建。SQL> alter table t_part exchange partition t1 with table ml_test;表已更改。SQL> select * from ml_test;未选定行 SQL> select * from t_part partition (t1);        ID----------         1SQL> ALTER TABLE ml_test MODIFY ID NUMBER(5); Table alteredSQL> alter table t_part exchange partition t1 with table ml_test; alter table t_part exchange partition t1 with table ml_test ORA-14097: ALTER TABLE EXCHANGE PARTITION 中的列类型或大小不匹配 SQL> ALTER TABLE t_part MODIFY ID NUMBER(5);   ALTER TABLE t_part MODIFY ID NUMBER(5) ORA-14060: 不能更改表分区列的数据类型或长度

注：如果是分区列，那结构就不能被改变了。

下面是非分区列的测试，当类型不一致，是不能交换的

SQL> create table ml_test (id number(3),id2 NUMBER(3)); Table createdSQL>  create table t_part(id number(3),id2 number(3)) partition by range(id)  2     (partition t1 values less than (10),  3     partition t2 values less than (20)); Table createdSQL> SQL> ALTER TABLE ml_test MODIFY ID2 NUMBER(5)  2  ; Table alteredSQL> alter table t_part exchange partition t1 with table ml_test; alter table t_part exchange partition t1 with table ml_test ORA-14097: ALTER TABLE EXCHANGE PARTITION 中的列类型或大小不匹配

当结果改为一致，就可以交换了

SQL>  ALTER TABLE t_part MODIFY ID2 NUMBER(5);  Table alteredSQL> alter table t_part exchange partition t1 with table ml_test; Table altered SQL> alter table ml_test drop column id2; Table altered

1.列的顺序不一致，但是结构的顺序一致，是可以交换的

SQL> desc t_part
Name Type Nullable Default Comments
----- ------------ -------- ------- --------
ID NUMBER(3) Y
NAME VARCHAR2(10) Y
ID2 NUMBER(5) Y
LIANG NUMBER(2) Y
MENG NUMBER(2) Y

SQL> desc ml_test
Name Type Nullable Default Comments
----- ------------ -------- ------- --------
ID NUMBER(3) Y
NAME VARCHAR2(10) Y
ID2 NUMBER(5) Y
MENG NUMBER(2) Y
LIANG NUMBER(2) Y

SQL> alter table t_part exchange partition t1 with table ml_test;
Table altered
--这里成功交换，是因为都是number(2),exchange 特性认结构顺序，而不是列名顺序。

2.下面不能交换，是因为结构顺序不一致。

SQL> desc t_part
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER(3) Y
ID2 NUMBER(5) Y
NAME VARCHAR2(10) Y

SQL> desc ml_test
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER(3) Y
NAME VARCHAR2(10) Y
ID2 NUMBER(5) Y

SQL> alter table t_part exchange partition t1 with table ml_test;

alter table t_part exchange partition t1 with table ml_test

ORA-14097: ALTER TABLE EXCHANGE PARTITION 中的列类型或大小不匹配

3.以下更能证明这是结构一致的交换，而非字段的交换

SQL> select * from ml_test1;

ID ID2 MENG LIANG
---- ---- ---- -----
1 2 me li

SQL> select * from t_part1;

ID ID2 LIANG MENG
---- ---- ----- ----

SQL> alter table t_part1 exchange partition t1 with table ml_test1;

Table altered

SQL> select * from t_part1;

ID ID2 LIANG MENG
---- ---- ----- ----
1 2 me li

unused 测试

SQL> desc ml_test;Name Type      Nullable Default Comments ---- --------- -------- ------- -------- ID   NUMBER(3) Y                          SQL> desc t_partName Type      Nullable Default Comments ---- --------- -------- ------- -------- ID   NUMBER(3) Y   SQL> alter table t_part exchange partition t1 with table ml_test;  alter table t_part exchange partition t1 with table ml_test ORA-14097: ALTER TABLE EXCHANGE PARTITION 中的列类型或大小不匹配

对于以上问题是，为什么结构一样，交换还是报错呢？

那是因为ml_test 是drop column，而t_part 是 ALTER TABLE t_part DROP UNUSED name1;

设置为UNUSED，其实只是不可见，并没有彻底删除，此时没有释放存储空间，如果是compress 表，那么此时不参与压缩与解压，io操作。

怎么处理呢？请看下面步骤：

SQL> ALTER TABLE t_part DROP UNUSED COLUMNS; Table altered Executed in 0.062 seconds SQL> alter table t_part exchange partition t1 with table ml_test;  Table altered Executed in 0.032 seconds SQL>

ALTER TABLE ml_xx SET UNUSED COLUMN GlobalFlag;
ALTER TABLE ml_xx DROP UNUSED COLUMNS;

现网实战：

--刷新试图CALL   dbms_mview.refresh('user_Partitions', 'C');--删除分区BEGIN  FOR cur IN (    select     table_name, partition_name    from user_Partitions    WHERE           partition_name <> 'Pxxxxxxxx'          AND table_name='CS_xxxxxxx_ML'          ORDER BY table_name, partition_name  ) LOOP          EXECUTE IMMEDIATE 'ALTER TABLE xxxx.' || cur.table_name || ' DROP PARTITION ' || cur.partition_name;  END LOOP;END;--创建分区DECLARE v_sql VARCHAR2(1000);v_date1 VARCHAR2(20);v_date2  VARCHAR2(20);v_date DATE;v_count NUMBER(3);BEGIN   v_count:=1;  v_date1:=to_char(SYSDATE,'yyyymmddhh24');  v_date2:=to_char(SYSDATE,'yyyy-mm-dd hh24');   WHILE v_count<=23 LOOP     v_sql:='';     v_date:=TRUNC(to_date('2011-07-23 00:00:00','yyyy-mm-dd hh24:mi:ss'),'hh24')+v_count/24;     v_date1:=to_char(v_date,'yyyymmddhh24');       v_date2:=to_char(v_date,'yyyy-mm-dd hh24');     v_sql:='ALTER TABLE CS_XXXX_ML ADD PARTITION  P' ||v_date1 ||' VALUES LESS THAN (to_date('''||v_date2||':00:00'',''yyyy-mm-dd hh24:mi:ss''))   TABLESPACE tablespace_namexxxx;';     dbms_output.put_line(v_sql); -- dbms_output.put_line(to_char(v_date,'yyyymmdd hh24:mi:ss'));        -- EXECUTE IMMEDIATE v_sql;             v_count:=v_count+1;   END LOOP;END;

---交换空间

DECLARE v_sql1 VARCHAR2(1000);v_sql2 VARCHAR2(1000);v_date1 VARCHAR2(20);v_date2  VARCHAR2(20);v_date DATE;v_count NUMBER(3);BEGIN   v_count:=1;  v_date1:=to_char(SYSDATE,'yyyymmddhh24');  v_date2:=to_char(SYSDATE,'yyyy-mm-dd hh24');   WHILE v_count<=23 LOOP     v_sql1:='';     v_sql2:='';     v_date:=TRUNC(to_date('2011-07-23 00:00:00','yyyy-mm-dd hh24:mi:ss'),'hh24')+v_count/24;     v_date1:=to_char(v_date,'yyyymmddhh24');       v_date2:=to_char(v_date,'yyyy-mm-dd hh24');     v_sql1:='alter table CS_XXXX exchange partition P' ||v_date1 ||' with table CS_XXXX_tmp INCLUDING INDEXES  without VALIDATION UPDATE INDEXES ;';     dbms_output.put_line(v_sql1);      v_sql2:='alter table CS_XXXX_ml exchange partition P' ||v_date1 ||' with table CS_XXXX_tmp INCLUDING INDEXES  without VALIDATION UPDATE INDEXES ;';      dbms_output.put_line(v_sql2); -- dbms_output.put_line(to_char(v_date,'yyyymmdd hh24:mi:ss'));        -- EXECUTE IMMEDIATE v_sql;             v_count:=v_count+1;   END LOOP;END;