修改表结构的语句

最新推荐文章于 2024-07-04 10:01:20 发布

fanfjaiynu

最新推荐文章于 2024-07-04 10:01:20 发布

阅读量866

点赞数

分类专栏：数据库散装知识文章标签：数据库 oracle

本文链接：https://blog.csdn.net/fanfjaiyun/article/details/104531526

版权

数据库散装知识专栏收录该内容

2 篇文章 0 订阅

订阅专栏

alter table crm_ict_orderInfo rename column starttime to starttime_temp;
alter table crm_ict_orderInfo add starttime date;
update crm_ict_orderInfo set starttime =to_date(starttime_temp,'yyyy-mm-dd hh24:mi:ss')
alter table crm_ict_orderInfo drop (starttime_temp);

alter table crm_ict_orderInfo rename column endtime to endtime_temp;
alter table crm_ict_orderInfo add endtime date;
update crm_ict_orderInfo set endtime =to_date(endtime_temp,'yyyy-mm-dd hh24:mi:ss')
alter table crm_ict_orderInfo drop (endtime_temp);


select * from crm_ict_orderInfo order by starttime desc
select count(0) from crm_ict_orderInfo partition(region_00)

alter table crm_ict_orderInfo rename column starttime to starttime_temp;
alter table crm_ict_orderInfo add starttime date;
update crm_ict_orderInfo set starttime =to_date(starttime_temp,'yyyy-mm-dd hh24:mi:ss')
alter table crm_ict_orderInfo drop (starttime_temp);
alter table crm_ict_orderInfo rename column endtime to endtime_temp;
alter table crm_ict_orderInfo add endtime date;
update crm_ict_orderInfo set endtime =to_date(endtime_temp,'yyyy-mm-dd hh24:mi:ss')
alter table crm_ict_orderInfo drop (endtime_temp);

-删除分区数据-

ALTER TABLE crm_ict_orderInfo TRUNCATE PARTITION region_18

-循环给一个分区加入几十万数据-

declare 
 x number;
begin
  x := 0;
  while x < 10000 loop
  x := x+1;
  insert into CRM_ICT_ORDERINFO (order_id, user_id, region, region_name, phonenumber, productname, productnumber2, commoditycode, commodityname, type, starttime, endtime)
values ('200006136811076196', '18091103', '00', '镇江地区', '13501180285', '产品变更', '主体产品变更', '2400000280', 'LTE服务', 'T', to_date('01-10-2018 16:46:28', 'dd-mm-yyyy hh24:mi:ss'), to_date('02-08-2018 16:46:39', 'dd-mm-yyyy hh24:mi:ss'));

commit;
end loop;
end;

给表添加索引

create index crm_ict_orderInfo_index on 
crm_ict_orderinfo(order_id,phonenumber) 
local (partition region_00,partition region_11,
	   partition region_12,partition region_13,
	   partition region_14,partition region_15,
	   partition region_16,partition region_17,
	   partition region_18,partition region_19,
	   partition region_20,partition region_21,
	   partition region_22,partition region_23);