Oracle常用基本语句_表处理部分

表处理部分

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