表处理部分
Alter update
查看表的各列:DBA_TAB_COLUMNS
--------------------alter篇---------------------- ALTER TABLE 表名 ADD 列名 数据类型 (添加单列) ALTER TABLE 表名 ADD ( 列名1 数据类型1,列名2 数据类型2) (添加多列) 删除单列:ALTER TABLE 表名 DROP COLUMN 列名 删除多列:ALTER TABLE 表名 DROP (列名1,列名2) alter table DT_FLOOR modify unit_id NUMBER(15); 修改字段类型 修改单列数据类型:ALTER TABLE 表名 MODIFY 列名 数据类型 同时修改多列数据类型:ALTER TABLE 表名 MODIFY ( 列名1 数据类型1,列名2 数据类型2) alter table表名rename column列名to 新列名 修改表的一个字段的名称
ALTER TABLE ower.tablename RENAME TO tablename1 alter table ower.tablename add primary key (ID)
注意:当表中含有大量数据时在新增字段再附带默认值default就会花时较长,可修改为新增字段后再修改字段的默认值,此修改只能在后期插入数据才能生效。 lter table manpower.s_salary add (add_sh number(7,2) default 0);需要时间21分钟。 分2步执行,效果显著提高: alter table manpower.s_salary add (add_sh number(7,2));需要时间0.25秒。 alter table manpower.s_salary modify (add_sh number(7,2) default 0); 需要时间0.25秒。 ----------------------update--------------------------------------- update a set id=(select id from b where a.title=b.title) update /*+bypass_ujvc */ (select a.id,b.id as id1 from a inner join b on a.title=b.title) set id=id1 第二条比第一条高效 对于 /*+ BYPASS_UJVC*/ 的用途:在使用implict update table 时,发现其中一个表一定要有唯一约束,否则会报错!但是oracle可以使用hints:/*+ BYPASS_UJVC*/ 屏蔽掉队唯一性的检查。这个应该对性能影响不大。
drop index mcconf_index; alter index mcconf_index rebuild; create index mcconf_index on mc$ma_warn_config (NAME);
|
更新表的统计信息:
--分析表 execute dbms_stats.gather_table_stats(ownname => 'owner',tabname => 'table_name' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true); 可以查看表 DBA_TABLES来查看表是否与被分析过,如: SELECT TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES
--分析索引 exec dbms_stats.gather_index_stats(ownname => 'xxxxx',indname => 'IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ;
--如果发现执行计划走错,删除表的统计信息 SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_list') ;
|
创主键:
alter table clz_find_nonser_0205 add constraint ser_ctraint primary key (serviceid) |
建dblink:
create database link link名称 connect to 对方数据库用户名 identified by 对方数据库用户密码 using '对方数据库ip:端口/实例名';
--临时重复条数的查询方法
select 'a',level from dual connect by level<=20
自增字段:
drop table oracle_mysql_tablechange; create table oracle_mysql_tablechange( id int, 过程 varchar2(300), 原表名 varchar2(100), mysql表名 varchar2(100), MySQL使用格式 varchar2(300), 有无合并全地市 varchar2(6), 处理人 varchar2(30) );
CREATE SEQUENCE insert_mysql_seq INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 MAXVALUE 99999999 -- 最大值
-- 在表添加触发器 create or replace trigger t_insert_mysql_seq -- 触发器名称 before insert on oracle_mysql_tablechange --oracle_mysql_tablechange 表名 for each row declare nextid number; begin if :new.id is null or :new.id=0 then --id是表上的自增字段 select insert_mysql_seq.nextval -- insert_mysql_seq创建的序列 into nextid from sys.dual; :new.id:=nextid; end if; end t_insert_mysql_seq;
select * from oracle_mysql_tablechange
insert into oracle_mysql_tablechange (过程,原表名 ,mysql表名,MySQL使用格式,有无合并全地市,处理人) values('p_week_crm_gis','tmp_crm_ser_pro_xx_00','tb_crm_ser_pro_00_xx','如广州:tb_crm_ser_pro_00_gz','无','clz'); |
修改字段:
改一个已存在的表的不允许为Null的列改为可为Null: ALTER TABLE TABLENAME MODIFY COLUMNNAME COLUMNTYPE NULL; alter table clz_test_1120 rename column regionid to id 修改表的一个字段的名称 ALTER TABLE tmp_build_id_bak modify build_id number; 修改字段类型 |
排序:
ROWNUM 行数 |
改表名:
ALTER TABLE teat2 RENAME TO teat1 修改表名 |
批量删除表:
Begin for t in (select table_name from user_tables where table_name like 'CLZ_%' and last_analyzed<to_date('20130225','yyyymmdd') and table_name not in ('CLZ_WORKSHEET_LOGTYPE','CLZ_YF' ,’ clz_ad_0928’)) loop execute immediate 'drop table ' || t.table_name; end loop; end; |
索引:
加硬性索引:SELECT /*+ index(hr IDX_HIS_WORKSHEET_ROUTELOG_1) */* FROM RMGZ.HIS_WORKSHEET_ROUTELOG HR where HR.OPERATETIME >= TO_DATE('20121101', 'yyyymmdd') AND HR.OPERATETIME < TO_DATE('20121201', 'yyyymmdd') |
With as:
作用相当于建张临时表使用,数据量太大时不建议使用。 Eg: With table_t as ( Select 1 as a from dual Union all Select 2 as a from dual ); Select avg(a) from table_t |