oracle exchange特性做sql优化测试

2653885

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

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



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



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

SQL>sqlplus / nolog
SQL> 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
----------
1
SQL> ALTER TABLE ml_test MODIFY ID NUMBER(5);
Table altered
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 中的列类型或大小不匹配
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 created
SQL>  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 created
SQL>
SQL> ALTER TABLE ml_test MODIFY ID2 NUMBER(5)
2  ;
Table altered
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 中的列类型或大小不匹配


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

SQL>  ALTER TABLE t_part MODIFY ID2 NUMBER(5);
Table altered
SQL> 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_part
Name 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;