中国电信sql培训材料

中国电信安徽公司 企业信息化部
Sql执行计划2/2
oracle中explain plan的指标含义
OPERATION - 执行的操作。如，表扫描还是表更行。 OPTIONS - 操作行为。如 FULL 全部扫表，还是通过索引 。 OBJECT_NAME - 这个步骤应用的表明或者是索引名。 COST - 优化器分配给这个步骤的代价。 NULL表示是 基于rule的优化器。 CPU_COST - 优化器分配给这个步骤的CUP代价。 NULL表 示是基于rule的优化器 IO_COST -优化器分配给这个步骤的输入输出代价。 NULL表示是基于rule的优化器 CARDINALITY - 估量这个步骤产生的行数。 ACCESS_PREDICATES - 这个步骤扫描数据时用到的条件谓词。 FILTER_PREDICATES - 从这个步骤返回数据之前，过滤数据用到的条 件谓词。
a、insert ，为缺省方式，要求在插入之前test表必须是空的 b、append ，追加记录
c、replace ，删除旧记录，替换成新记录
中国电信安徽公司 企业信息化部
4
SQL LOAD的使用—输入文件
3、输入文件－－>另存为：test.txt 保存类型 制表符 “|”分隔或者”,”分隔； 4、在dos下执行导入命令： 示例：
load data Append INTO TABLE test FIELDS TERMINATED BY "," (column1,column2........columnn) ------控制文件标识 infile ------要输入的数据文件名 append into ------向表中追加记录 ------字段终止于‘,’ ------定义列对应顺序其中
中国电信安徽公司 企业信息化部
多表关联查询案例
select a.* , case when a.inst_type = 'T03' then b.prd_id when a.inst_type = 'T05' then c.prd_id when a.inst_type = 'T06' then d.ofr_id when a.inst_type = 'T07' then e.ofr_id when a.inst_type = 'T15' then h.group_user_type_id when a.inst_type = 'T19' then j.group_id when a.inst_type = 'T20' then k.ofr_id else null end prd_ofr_id, case when a.inst_type = 'T03' then b.prd_inst_id when a.inst_type = 'T05' then c.main_prd_inst_id when a.inst_type = 'T06' then (select max(b.ofr_detail_inst_ref_id) from crm.TB_PRD_OFR_DETAIL_INST_551@ods_133_Dw b where a.inst_id = b.ofr_inst_id ) when a.inst_type = 'T07' then e.ofr_detail_inst_id when a.inst_type = 'T15' then h.prd_inst_id when a.inst_type = 'T19' then (select max(b.ofr_detail_inst_ref_id) from crm.TB_PRD_OFR_DETAIL_INST_551@ods_133_Dw b where b.ofr_inst_id = j.ofr_inst_id ) when a.inst_type = 'T20' then k.main_prd_inst_id else null end prd_inst_id from mct_16_cust_dzl_ck_551 a left join CRM_CSP.TB_PRD_PRD_INST_551@ods_133_Dw b on a.inst_id = b.prd_inst_id left join CRM_CSP.TB_PRD_SUB_PRD_INST_551@ods_133_Dw c on a.inst_id = c.prd_inst_id left join CRM_CSP.TB_PRD_OFR_INST_551@ods_133_Dw d on a.inst_id = d.ofr_inst_id left join CRM_CSP.TB_PRD_OFR_DETAIL_INST_551@ods_133_Dw e on a.inst_id = e.ofr_detail_inst_id left join CRM_CSP.TB_PRD_GROUP_USER_GROUP_551@ods_133_Dw h on a.inst_id = h.group_user_group_id left join CRM_CSP.TB_PRD_OFR_STRA_INST_551@ods_133_Dw j on a.inst_id = j.strategy_inst_id left join CRM_CSP.TB_PRD_VAS_PRD_INST_551@ods_133_Dw k on a.inst_id = k.prd_inst_id where a.latn_id = 551 ;
(低效) SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEP TNO FROM DEPT WHERE LOC = ‘MELB')
中国电信安徽公司 企业信息化部
Sql执行计划1/2
执行统计语句之前，先用PL/SQL DEVELOPER工具的“执行计划”查 看一下； “工具”菜单栏下 Explain plan 控件 ，或选择查询语句，点击”F5”执行。 小提示：对于单个表超过100万记录 的表关联查询，一定先要用执行计划 查看一下。
sqlldr userid=qx_xugang/XXXXXX@edw control=D:\input.ctl data= D:\ test.txt bad=D:\xg.bad log=D:\xg.log rows=2000
中国电信安徽公司 企业信息化部
5
SQL LOAD的使用—小技巧
5、导入日期字段：
install_date date 'YYYY-MM-DD HH24:MI:SS',
6、完整示例演示：
中国电信安徽公司 企业信息化部
6
目 录
1 1
SQL*Loader 使用简介
2
SQL 优化实例讲解
3
常用的ORACLE函数讲解
中国电信安徽公司 企业信息化部
7
WHERE条件
(1)WHERE 子句中的连接顺序．： ORACLE 采用自下而上的顺序解析WHERE 子句,根据 这个原理,表之间的连接必须写在其他WHERE 条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE 子 句的末尾.
ORACEL SQL 培训材料
中国电信安徽公司 企业信息化部 二○一一年一月
用戶至上 用心服务 Customer First Service Foremost
目 录
1 1
SQL*Loader 使用简介
2
SQL 优化实例讲解
3
常用的ORACLE函数讲解
中国电信安徽公司 企业信息化部
2
SQL LOAD的定义
中国电信安徽公司 企业信息化部
索引是好还是坏1/2
所谓索引的好坏是指： 1，索引不是越多越好。特别是大量从来或者几乎不用的索引， 对系统只有损害。OLTP系统每表超过5个索引即会降低性能，而且 在一个sql 中， Oracle 从不能使用超过 5个索引。 2，很多时候，单列索引不如复合索引有效率。
中国电信安徽公司 企业信息化部
EXISTS和NOT EXISTS 2/2
例子： (高效）SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')
中国电信安徽公司 企业信息化部
删除重复记录
(2)删除重复记录： 最高效的删除重复记录方法( 因为使用了ROWID)例子：
DELETE FROM xg_cdma_tcfx_201011_t3 a WHERE a.ROWID > (SELECT MIN(b.ROWID) FROM xg_cdma_tcfx_201011_t3 b WHERE b.latn_id = a.latn_id and b.prd_inst_id = a.prd_inst_id ); commit;
中国电信安徽公司 企业信息化部
3
SQL LOAD的使பைடு நூலகம்—控制文件
1、控制文件是用一种语言写的文本文件，这个文本文件 能被SQL*LOADER识别。SQL*LOADER根据控制文件 可以找到需要加载的数据。并且分析和解释这些数据。 2、创建SQL*LOAD输入数据所需要的文件，用记事本 编辑控制文件，名称为：input.ctl 内容如下：
3，用于多表连结的字段，加上索引会很有作用。
那么，在什么情况下单列索引不如复合索引有效率呢？有一种 情况是显而易见的，那就是，当sql 语句所查询的列，全部都出现 在复合索引中时，此时由于 Oracle 只需要查询索引块即可获得所有 数据，当然比使用多个单列索引要快得多。（此时，这种优化方式 被称为 Index only access path）
--小提示： 关联的字段最好已建立索引。
中国电信安徽公司 企业信息化部
EXISTS和NOT EXISTS 1/2
(3） 用EXISTS 替代IN、用NOT EXISTS 替代NOT IN： 在许多基于基础表的查询中,为了满足一个条件,往往需 要对另一个表进行联接.在这种情况 下, 使用EXISTS(或 NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN 子句将执行 一个内部的排序和合并. 无论在哪种情况下 ,NOT IN 都是最低效的(因为它对子查询中的 表执行了一 个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成 外连接(Outer Join s)或NOT EXISTS.
中国电信安徽公司 企业信息化部
索引是好还是坏2/2
使用索引的技巧 l 对于小表来说使用索引对于性能不会有任何提高 l 当你的索引列中有极多的不同的数据和空值时索引会使性能有极大 的提高 l 当查询要返回的数据很少时索引可以优化你的查询比较好的情况是 少于全部数据的25% 如果你要返回的数据很多时索引会加大系统开 销 l 索引可以提高数据的返回速度但是它使得数据的更新操作变慢在对 记录和索引进行更新时请不要忘记这一点如果要进行大量的更新操 作在你执行更新操作时请不要忘记先删除索引当执行完更新操作后 只需要简单的恢复索引即可对于一次特定的操作系统可以保存删除 的索引18 个小时在这个时间内数据更新完后你可以恢复它 l 对字段的索引已经对两个表进行了归并操作这一技术可以极大地提 高归并的速度 l 不要创建对经常需要更新或修改的字段创建索引更新索引的开销会 降低你所期望获得的性能
Oracle 的SQL*LOADER可以将外部数据加载到数据库表 中。SQL*LOADER的基本特点：
1）能装入不同数据类型文件及多个数据文件的数据 2）可装入固定格式，自由定界以及可度长格式的数据 3）可以装入二进制，压缩十进制数据 4）一次可对多个表装入数据 5）连接多个物理记录装到一个记录中 6）对一单记录分解再装入到表中 7）可以用 数对制定列生成唯一的KEY 8）可对磁盘或 磁带数据文件装入制表中 9）提供装入错误报告 10）可以将文件中的整型字符串，自动转成压缩十进制并装入列表中。
--统计10月办理，11月生效的用户11月份收入 create table xg_cdma_tcfx_cust_201011_t tablespace report as select a.* from xg_cdma_tcfx_cust_t2 a where exists (select 1 from xg_cdma_tcfx_cust_t b where a.prd_inst_id=b.prd_inst_id and a.latn_id=b.latn_id and b.dev_num=1);