社区
Oracle
帖子详情
oracle 动态执行的sql语句中, insert into 中用多个union all 连接出来的语句效率高, 还是把union all分成多个insert into 效率高呢, 为什么?
wscft
2009-01-14 06:02:41
如题
...全文
2047
5
打赏
收藏
oracle 动态执行的sql语句中, insert into 中用多个union all 连接出来的语句效率高, 还是把union all分成多个insert into 效率高呢, 为什么?
如题
复制链接
扫一扫
分享
转发到动态
举报
写回复
配置赞助广告
用AI写文章
5 条
回复
切换为时间正序
请发表友善的回复…
发表回复
打赏红包
viscount
2009-01-15
打赏
举报
回复
个人看法 分开比较好
如果 union all 的数据量很大的话,
可能会出现04030的错。
wscft
2009-01-14
打赏
举报
回复
用的是oracle 9i,是这样的,从同一个表中根据不同的条件关系,把八个SELECT出来的结果集UNION ALL 后用INSERT 语句插入一个表中后commit提交,
与分成八个INSERT INTO 语句后并分别提交时,执行的效率如何?
与SELECT 直接出来的结果集的数据量的大小是否有关系?
对于不同的数据库系统有什么区别呢,没有找到令人信服的说法,谁有相关的该方面的技术文档,给个链接看看, 谢谢
wind__dance
2009-01-14
打赏
举报
回复
区别应该不大,你可能考虑事务对插入的影响吧?一次提交的话和一个事务区别不大,因为它不一直写重做日志
dawugui
2009-01-14
打赏
举报
回复
如果都是最后才提交,需要的时间基本一样.
如果没insert就提交一次,则是合在一起的时间少.
Andy__Huang
2009-01-14
打赏
举报
回复
一样的,因为insert into 语句数量一样多
当然,如果你一次性提交和每insert一句就提交一次,效果是不一样的
一次性提交效果更好
SQL培训第一期
1 SQL基础 1.1 基本概念 结构化查询语言(Structured Query Language)简称SQL,是一种关系数据库查询语言,用于存取数据以及查询、更新和管理关系数据库系统。 1.2
语句
结构 1.2.1 数据查询语言(DQL) 对数据库进行的信息查询,select。 1.2.2 数据操作语言(DML) 用于操作关系型数据库对象内部的数据,
insert
、update、delete。 1.2.3 数据定义语言(DDL) 用来建立及定义数据表、字段以及索引等数据库结构,create、alter、drop 。 1.2.4 数据控制语言(DCL) 用于控制对数据库里数据的访问,通常用于创建与用户访问相关的对象以及控制用户的权限,grant、revoke(撤销)。 1.2.5 事务控制命令(TPL) 用于管理数据库事务,commit、rollback、savepoint(在一组事务里创建标记点以用于回退)。 1.3 表的构成 1.3.1 字段 字段是表里的一列,用于保持每条记录的特定信息 1.3.2 记录 记录,也被成为一行数据,是表里的每一行 1.4 完整性的约束条件 1.4.1 实体完整性 关系模型的实体完整性在create table
中
用primary key约束实现,primary key约束用于定义主键,它保证主键的唯一性和非空性。 1.4.2 参照完整性 关系模型的参照完整性可以通过在create table
中
用foreign key (<外键>) references <被参照表名> (<与外键对应的主键名>)进行约束定义。 1.4.3 用户定义完整心 在create table
语句
中
可以根据应用要求,定义属性以及元组上的约束。 常见的用户定义的完整性约束有: not null或null约束。 unique约束:唯一性约束。 default约束:默认值约束。 check约束:检查约束,check约束通过约束条件表达式设置列值应该满足的条件。 1.5 范式 1.5.1 第一范式 1.5.1.1 规范 无重复的列,确保每列保持原子性,即数据库表
中
的所有字段值都是不可分解的原子值。 1.5.1.2 举例 姓名 年龄 联系电话 地址 省 市 详细地址 1.5.2 第二范式 1.5.2.1 规范 属性完全依赖于主键,确保表
中
每列都与主键相关。 1.5.2.2 举例 订单表 订单Id 商品Id 总金额 商品名称 001 1 10 苹果 001 2 10 橘子 联合主键订单Id、商品Id => 商品表 商品Id 商品名称 单价 订单表 订单Id 总金额 1.5.3 第三范式 1.5.3.1 规范 属性不依赖于其它非主属性,确保数据表
中
的每一列数据都和主键直接相关,而不能间接相关,即要求一个数据库表
中
不包含已在其它表
中
已包含的非主关键字信息。 1.5.3.2 举例 党员表 党员Id 党员姓名 组织Code 符合3NF 党员表 党员Id 党员姓名 组织名称 不符合3NF 组织表 组织Code 组织名称 1.6 外
连接
1.6.1 准备 create table student_A( uuid varchar2(32), name varchar2(100)); create table student_B( uuid varchar2(32), name varchar2(100));
insert
into student_A values('1','小黄');
insert
into student_A values('2','小黑');
insert
into student_A values('3','小红');
insert
into student_B values('1','大黄');
insert
into student_B values('2','大黑');
insert
into student_B values('4','大红');
insert
into student_B values('4','大紫'); 1.6.2 左
连接
(left join) 1.6.2.1 说明 查询指定的左表的所有行,而不仅仅是联接列所匹配的行;如果左表的某行在右表
中
没有匹配行,则在相关联的结果集行
中
右表的所有选择列表列均为空值。 1.6.2.2 语法 select A.*,B.* from student_A A left join student_B B on A.Uuid = B.Uuid; 1.6.2.3 结果 1.6.2.4 (+)表示 select A.*,B.* from student_A A,student_B B where A.Uuid = B.Uuid(+) 1.6.3 右
连接
(right join) 1.6.3.1 说明 查询指定的右表的所有行,而不仅仅是联接列所匹配的行;如果右表的某行在左表
中
没有匹配行,则在相关联的结果集行
中
左表的所有选择列表列均为空值。 1.6.3.2 语法 select A.*,B.* from student_A A right join student_B B on A.Uuid = B.Uuid; 1.6.3.3 结果 1.6.3.4 (+)表示 select A.*,B.* from student_A A,student_B B where A.Uuid(+) = B.Uuid 1.6.4 全外
连接
(full outer join) 1.6.4.1 说明 完整外部联接返回左表和右表
中
的所有行;当某行在另一个表
中
没有匹配行时,则另一个表的选择列表列包含空值;如果表之间有匹配行,则整个结果集行包含基表的数据值。 1.6.4.2 语法 select A.*, B.* from student_A A full outer join student_B B on A.Uuid = B.Uuid; 1.6.4.3 结果 1.6.4.4 全外
连接
不支持(+)写法 1.6.5 (+) + 表示补充,即哪个表有加号,这个表就是匹配表。 1.7 运算符 1.7.1 比较 =、>,<,>=,<=,!=,<>, 1.7.2 确定范围 between and 、not between and 1.7.3 确定集合 in、not in 1.7.4 字符匹配 like(“%”匹配任何长度,“_”匹配一个字符) 1.7.5 转义字符 1.7.5.1 常规转义字符 “\”转义字符,“\%”则表示单纯的字符“%” 1.7.5.2 escape escape关键字经常用于使某些特殊字符,如通配符:'%','_'转义为它们原来的字符的意义,被定义的转义字符通常使用'\',但是也可以使用其他的符号。 select * from Student_a where name like '%$%%' escape '$'; 1.7.6 空值 is null、is not null 1.7.7 集合查询
union
(并)、intersect(交)、minus(差) 1.7.8 多重条件 and、or、not 1.7.9 对查询结果分组
1.7.10 分组筛选条件 [having <条件表达式>] 1.7.11 字符串拼接 select 'A' || 'B' from dual; // || 拼接 1.8 函数 1.8.1 聚集函数 count、sum、avg、max、min 1.8.2 case when 1.8.2.1 语法 select t.uuid, t.score, case when t.score > 90 then '优秀' when t.score > 60 then '及格' else '不及格' end from exam_user_exam t 1.8.3 decode 1.8.3.1 语法 select decode(x,1,'x is 1', 2 , 'x is 2','others') from dual 1.8.3.2 说明 当x等于1时,则返回‘x is 1’,当x等于2时,则返回‘x is 2’,否则,返回‘others’。 1.8.4 nulls first(nulls last)排序 1.8.4.1 语法 select * from dy_info t order by t.degree nulls first 1.8.4.2 说明 控制null显示行位置 1.8.5 Nvl 1.8.5.1 语法 select nvl(t.sap,'空') from dy_info t; 1.8.5.2 说明 如果sap号为空,则返回‘空’,否则返回sap号。 1.8.6 递归查询 1.8.6.1 语法 select t.* from g_organ t start with t.organcode = '080' connect by prior t.parentcode = t.organcode; //递归查询父节点 select t.* from g_organ t start with t.organcode = '080' connect by t.parentcode = prior t.organcode; //递归查询子节点 1.8.7
union
和
union
all 1.8.7.1 语法 select * from dual
union
all select * from dual 1.8.7.2 说明
union
会对查询数据进行去重并排序,
union
all只是简单的将两个结果合并。 1.8.8 wm_concat 1.8.8.1 语法 select wm_concat(t.role_name) from g_role t where t.role_name like '%书记%' 1.8.8.2 说明 拼接字符串,结果为:‘党工委副书记,总支副书记,党支部书记,党委书记,党委副书记,党总支书记,党工委书记,党总支副书记,党支部副书记’ 1.8.9 相似度 1.8.9.1 语法 select utl_match.edit_distance_similarity('aaaaa','bbaaaa') from dual; 1.8.10 去格式 1.8.10.1
oracle
正则表达式:去除<>>格式 select REGEXP_REPLACE(title,'<[^>]*>','') title from exam_question 1.8.11 rank() over (partition by …) 1.8.11.1 语法 select organcode,score,ranknum from ( select t.organcode, t.score, rank() over (partition by t.organcode order by t.score desc) ranknum from exam_user_exam t) where ranknum < 4 1.8.11.2 说明 获取每个组织,考试成绩前三名。 1.9 存储过程 1.9.1 定义 存储过程是一组为了完成特定功能的
SQL
语句
集,经编译后存储在数据库
中
。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来
执行
它。 1.9.2 准备 create table t_user ( username varchar2(20), password varchar2(20) ); create table t_user_temp ( username varchar2(20), password varchar2(20) );
insert
into t_user(username,password) values('小王','1111');
insert
into t_user(username,password) values('小李','1111'); 1.9.3 创建存储过程 create or replace procedure adduser as begin
insert
into t_user_temp(username,password) select username,password from t_user t where t.username = '小李'; end adduser; 1.9.4
执行
begin adduser; end; 1.9.5 验证 select * from t_user; select * from t_user_temp 2 SQL深入 2.1 常用 2.1.1 表空间 2.1.1.1 创建表空间 create tablespace TS_DJY datafile 'd:/software/
oracle
/tablespace/ts_djy.dat' size 1024M autoextend on next 100M maxsize 2048M; 2.1.1.2 指定用户表空间 alter user cssdj default tablespace TS_DJY; 2.1.1.3 指定表的表空间 create table t_student( uuid varchar2(32) )tablespace TS_DJY; 2.1.2 自增sequence 2.1.2.1 创建 create sequence seq_student_uuid minvalue 1 maxvalue 999999999999999 start with 1 increment by 1 nocache; 2.1.2.2 使用 select seq_student_uuid.nextval from dual; //获取下一个 select seq_student_uuid.currval from dual; //获取当前 2.1.3 批量插入 2.1.3.1 查询结果批量插入
insert
into tablea(cola,colb) select cola,colb from tableb; 2.1.3.2 创建相同的表结构并插入数据(备份表数据) create tab_new as select * from tab_old; 2.1.3.3 创建相同的表结构不插入数据 create tab_new as select * from tab_old where rownum=0; 2.1.4 伪列伪表 2.1.4.1 伪列 伪列不是表的真实列,但是你可以象使用真实列一样使用伪列,常用伪列:rowid行的绝对物理编号,每一行是唯一的。rownum序号,返回查询结果的每行序号。系统时间sysdate,返回系统当前时间。 2.1.4.2 伪表 select * from dual; 2.1.5 系统表 2.1.5.1 user_tables 当前用户表信息 2.1.5.2 user_tab_columns 当前用户表所有列信息,搜索列所在的表: select * from user_tab_columns t where t.column_name like '%ORGANCODE%'; 2.1.5.3 user_tablespaces 当前用户表空间 2.1.5.4 dba_users 数据库所有用户 2.1.5.5 dba_tables 数据库所有表 2.1.5.6 dba_tablespaces 数据库所有表空间 2.1.6 锁表解锁 2.1.6.1 查看锁表信息 select l.OBJECT_ID,s.SID,s.SERIAL#,s.USERNAME, s.MACHINE from V$locked_Object l,V$session s where l.SESSION_ID = s.SID; 2.1.6.2 解锁 alter system kill session 'sid,serial#'; 2.1.7 备份与恢复 2.1.7.1 导出 导出用户: exp cssdj/cssdj@zr owner=(cssdj,cssdj_zsy) file=d:/cssdj.dmp log=d:/cssdj.log 导出用户表: exp cssdj/cssdj@zr tables=(g_dict,g_dict_item) file=d:/cssdj.dmp log=d:/cssdl.log 2.1.7.2 导入 imp cssdj/cssdj@zr fromuser=cssdj_zsy touser=cssdj file=d:/cssdj.cmp log=d:/cssdj.log 2.1.7.3 数据泵 2.1.8 Dblink 2.1.8.1 创建 create database link orcllink connect to cssdj identified by cssdj using '(DESCRIPTION = (ADDRESS_LIST= (ADDRESS=(PROTOCOL = TCP)(HOST = 219.239.110.65)(PORT = 1521))) (CONNECT_DATA=(SID = orcl)))'; 2.1.8.2 删除 drop database link orcllink; 2.1.8.3 使用 select * from dy_info@orcllink; 2.2
Oracle
与Mysql差异 2.2.1 Group by 2.2.1.1
Oracle
select后面出现的列,如果没有使用集合函数,必须出现在group by
中
。 select sno,sname,sum(grade) from student group by sno,sname; //合法写法 select sno,sname,sum(grade) from student group by sno; //非法写法 select sno,min(sname),sum(grade) from student group by sno; //建议用这种写法,
效率高
些 2.2.1.2 Mysql select sno,sname,sum(grade) from student group by sno,sname; //合法写法 select sno,sname,sum(grade) from student group by sno; //合法写法 2.2.2 分页 2.2.2.1
Oracle
使用rownum来表明分页位置,而且rownum只能小于某值,不能大于某值,故而rownum和where联用才能完成数据范围的控制 2.2.2.2 Mysql mysql的分页可以用limit startNum,pageNum 2.3 了解 2.3.1 事务 2.3.1.1 Rollback start transaction; --开始事务
insert
into g_dict values('test','测试','1',''); --
执行
数据操作语言(DML) select * from g_dict where code = 'test'; --可以查看是否
执行
正确 rollback; --错误
执行
rollback操作 commit; --正确
执行
commit操作 2.3.1.2 Savepoint start transaction; --开始事务
insert
into g_dict values('t1','测试','1',''); --
执行
数据操作语言(DML) savepoint pointA;
insert
into g_dict values('t2','测试','1',''); --
执行
数据操作语言(DML) select * from g_dict where code = 'test33'; --可以查看是否
执行
正确 rollback to savepoint pointA; 2.3.2 利用
执行
计划评估
SQL
语句
的性能 2.3.2.1 工具 在PL/SQL Developer
中
写好一段SQL代码后,按F5,PL/SQL Developer会自动打开
执行
计划窗口,显示该SQL的
执行
计划。 2.3.2.2 查看总COST,获得资源耗费的总体印象 一般而言,
执行
计划第一行所对应的COST(即成本耗费)值,反应了运行这段SQL的总体估计成本,单看这个总成本没有实际意义,但可以拿它与相同逻辑不同
执行
计划的SQL的总体COST进行比较,通常COST低的
执行
计划要好一些。 2.3.2.3 了解
执行
计划的
执行
步骤 按照从左至右,从上至下的方法,了解
执行
计划的
执行
步骤;
执行
计划按照层次逐步缩进,从左至右看,缩进最多的那一步,最先
执行
,如果缩进量相同,则按照从上而下的方法判断
执行
顺序,可粗略认为上面的步骤优先
执行
。每一个
执行
步骤都有对应的COST,可从单步COST的高低,以及单步的估计结果集(对应ROWS/基数),来分析表的访问方式,
连接
顺序以及
连接
方式是否合理。 2.3.2.4 分析表的访问方式 表的访问方式主要是两种:全表扫描(TABLE ACCESS FULL)和索引扫描(INDEX SCAN),如果表上存在选择性很好的索引,却走了全表扫描,而且是大表的全表扫描,就说明表的访问方式可能存在问题;若大表上没有合适的索引而走了全表扫描,就需要分析能否建立索引,或者是否能选择更合适的表
连接
方式和
连接
顺序以提高效率。 2.3.2.5 分析表的
连接
方式和
连接
顺序 表的
连接
顺序:就是以哪张表作为驱动表来
连接
其他表的先后访问顺序。 表的
连接
方式:简单来讲,就是两个表获得满足条件的数据时的
连接
过程。主要有三种表
连接
方式,嵌套循环(NESTED LOOPS)、哈希
连接
(HASH JOIN)和排序-合并
连接
(SORT MERGE JOIN)。我们常见得是嵌套循环和哈希
连接
。 嵌套循环:最适用也是最简单的
连接
方式。类似于用两层循环处理两个游标,外层游标称作驱动表,
Oracle
检索驱动表的数据,一条一条的代入内层游标,查找满足WHERE条件的所有数据,因此内层游标表
中
可用索引的选择性越好,嵌套循环
连接
的性能就越高。 哈希
连接
:先将驱动表的数据按照条件字段以散列的方式放入内存,然后在内存
中
匹配满足条件的行。哈希
连接
需要有合适的内存,而且必须在CBO优化模式下,
连接
两表的WHERE条件有等号的情况下才可以使用。哈希
连接
在表的数据量较大,表
中
没有合适的索引可用时比嵌套循环的效率要高。 2.3.3 优化器
Oracle
优化器分为基于规则的优化器(RBO)和基于代价的优化器(CBO)。 2.3.3.1 规则的优化器(RBO) RBO的优化方式,依赖于一套严格的语法规则,只要按照规则写出的
语句
,不管数据表和索引的内容是否发生变化,不会影响PL/
SQL
语句
的"
执行
计划"。 2.3.3.2 基于代价的优化器(CBO) CBO计算各种可能"
执行
计划"的"代价",即cost,从
中
选用cost最低的方案,作为实际运行方案。各"
执行
计划"的cost的计算根据,依赖于数据表
中
数据的统计分布,
ORACLE
数据库本身对该统计分布是不清楚的,须要分析表和相关的索引,才能搜集到CBO所需的数据。 2.3.4 表分析analysis 2.3.4.1 说明 analyze table,一般可以指定分析表、所有字段、所有索引字段、所有索引,若不指定则全部都分析。 2.3.4.2 表分析 analyze table dy_info compute statistics; 2.3.4.3 删除分析数据 analyze table dy_info delete statistics; 2.3.5
oracle
添加强制索引 如果使用的是CBO的话,可能SQL不
执行
索引,则可以添加强制索引
执行
索引。 2.3.5.1 语法 /*+index(tablename indexname)*/ 2.3.5.2 举例 select /*+index(t INDEX_SAP)*/* from dy_info t where t.sap = 'T6000890'
分享高性能批量插入和批量删除
sql
语句
写法
从it专家网看到一篇好文章,粘过来用下 我们这里讨论的只是普通
sql
语句
如何写更高效,不考虑特殊的用文件
中
转等导入方式,毕竟在代码
中
调用
sql
语句
或存储过程才更方便。 批量删除很简单,大家可能都用过: DELETE FROM TestTable WHEREID IN (1, 3, 54, 68) --sql2005下运行通过 当用户在界面上不连续的选择多项进行删除时,该语
Oracle
数据库的配置和
SQL
语句
的优化 /*+ rule */ &
INSERT
/*+append*/INTO
Oracle
数据库的配置和
SQL
语句
的优化 。
INSERT
/*+append*/INTO t_servicexx(serviceid,clientid,prod_id,serviceno,addrid, connectno,fgsid,gl_serviceid,up_serviceid,servlev, ...
ORACLE
ROWNUM用法、select into与
insert
into区别、merge into的使用、递归查询
ROWNUM用法
ORACLE
中
ROWNUM用法总结! 对于
Oracle
的 rownum 问题,很多资料都说不支持>,>=,=,between...and,只能用以上符号(<、<=、!=),并非说用>,& gt;=,=,between..and 时会提示SQL语法错误,而是经常是查不出一条记录来,还会出现似乎是莫名其妙的结果来,其实您只要理解好了这...
oracle
sql以及性能调优
目 录 1.选用适合的
ORACLE
优化器 2 2.访问Table的方式 3 3.共享
SQL
语句
3 4.选择最有效率的表名顺序(只在基于规则的优化器
中
有效) 5 5.WHERE子句
中
的
连接
顺序. 6 6.SELECT子句
中
避免使用 ‘ * ‘ 7 7.减少访问数据库的次数 7 8.使用DECODE函数来减少处理时间 8 9.整合简单,无关联的数据库访问 9 10.删除重复记录 10 11.用TRUNCATE替代DELETE 10 12.尽量多使用COMMIT 11 13.计算记录条数 11 14.
Oracle
17,086
社区成员
55,238
社区内容
发帖
与我相关
我的任务
Oracle
Oracle开发相关技术讨论
复制链接
扫一扫
分享
社区描述
Oracle开发相关技术讨论
社区管理员
加入社区
获取链接或二维码
近7日
近30日
至今
加载中
查看更多榜单
社区公告
暂无公告
试试用AI创作助手写篇文章吧
+ 用AI写文章