sql优化.ppt

1、Sql优化的一些知识,预备知识,如何通过ROWID访问表 18位ROWID代码如下：AAAlyFACDAAFnrbAA3 对象编号+文件编号+块编号+行编号 AAAlyF ACD AAFnrb AA3 ROWID编码方式：ROWID是64位编码18个字符组成6+3+6+3组合而成 编码关系：A-Z 0-25 a-z 26-51 0-9 52-61 +/ 62-63,SELECT t.rowid,t.* from dm.dm_a_user_m_info t WHERE t.gather_mon=201112 AND t.svc_id应用案例：,SELECT DBMS_R

2、OWID.ROWID_OBJECT(AAAlyFACDAAFnrbAA3) OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(AAAlyFACDAAFnrbAA3) FILE_ID, DBMS_ROWID.ROWID_BLOCK_NUMBER(AAAlyFACDAAFnrbAA3) BLOCK_ID, DBMS_ROWID.ROWID_ROW_NUMBER(AAAlyFACDAAFnrbAA3) NUMS FROM DUAL; 通过oracle 自带的包将rowid转换为10进制数值。 通过十进制编号反查找相应的信息： 对象信息： SELECT * from d

3、ba_objects t where t.OBJECT_ID=154757; 文件信息：SELECT * from dba_data_files t WHERE t.FILE_ID=131;,通过rowid删除重复数据,DELETE FROM SRC.D_PRO_MOBILE WHERE ROWID IN (SELECT ROWID ROW_ID FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY PRO_ID ORDER BY T.PRO_DESC) ORD FROM SRC.D_PRO_MOBILE T) T WHERE T.ORD 1),通过

4、分析函数将重复数据区分出来，并且用ord来区分出来,SELECT T.*, ROW_NUMBER() OVER(PARTITION BY PRO_ID ORDER BY T.PRO_DESC) ORD FROM SRC.D_PRO_MOBILE T,找到重复数据的rowid并从表中删除,系统视图表,SELECT * from Dba_Tables ; 可以查询某个数据库下的所有表,SELECT * FROM ALL_SOURCE t;查询所有过程文本内容,问题：假如要查询user_info 这个表的绘制过程，则可做如下搜索： SELECT * FROM ALL_SOURCE t WHERE t

5、.TEXT LIKE %user_info%,由上述sql查找到有关的过程，在进入到相应的过程中查找想要查询的信息。,SELECT T.TABLE_OWNER,T.TABLE_NAME,T.PARTITION_NAME,T.NUM_ROWS,T.SAMPLE_SIZE,T.LAST_ANALYZED from DBA_TAB_PARTITIONS T WHERE T.TABLE_OWNER=UI AND T.TABLE_NAME=UI_USER_EXT_INFO,查询分区数据信息,DBA权限视图数据来源于统计数据的收集。 如何收集统计数据，系统晚上10点-第二天早上6点，周六日全天自动收集。,

6、BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME = MID, -用户 TABNAME = MID_YD_V_USE_BILLS_DAILY_NEW, -表名 PARTNAME = PART | V_ACCT_MONTH, -分区名 DEGREE = 8 -多线程 ); END; 其他可以使用默认设置。,DML语句(insert, update,delete等) DDL语句 (create ,drop ,alter,truncate 等) DCL语句(commit,rollback等)控制语句 Truncate 操作和delete的操作区别以及drop闪回

7、技术的应用 闪回操作：flashback table t1 to before drop;,所有的Oracle表都有一个容纳数据的上限（很象一个水库历史最高的水位），我们把这个上限称为“high water mark”或HWM。这个HWM是一个标记(专门有一个数据块用来记录高水标记等)，用来说明已经有多少数据块分配给这个表.,笛卡尔乘积,笛卡尔乘积是一个表的每一行以此与另一个表的所有行匹配。 例如：集合A=a,b B=1,2, A和B做笛卡尔积的结果为： A*B=(a,1),(a,2),(b,1),(b,2) 现有表A,B如下: A*B=,当两个表关联但不写关联字段时，会出现笛卡尔积的情况。

8、笛卡尔积很耗费资源，所以要尽可能避免。,Sql查询内部原理,查询在处理过程中分为四个大的阶段,这一阶段主要是进行语法分析，将原查询转换为数据库内部格式以便于机器处理，不符合语法规范的报错返回，为sql优化过程铺平道路。,将查询转换为内部格式阶段,数据库优化器将执行一系列“保证能够优化”的优化过程，是不会去考虑实际数据的值和数据库的存取路径；优化器将查询的内部表示转换为等价的规范格式。比如说将“A=B替换为B=A”，消除语句表面上的差异，以便能够找到一种在某些方面比原查询更为高效的表示方法。,将内部格式转换为规范格式,优化器考虑的是索引、物理存取路径、数据值的分布、数据的物理聚集存储等问题。基本

9、的策略就是将查询表达式看成一系列的“低层操作”，对于每一个可能的低层操作，都有一组可用的低层过程，而每一个低层过程都会有一个相关的代价计算公式（磁盘i/o代价，cpu利用率）。,为执行选择低层过程,这一阶段就是构造一组查询计划，选择一个最优，也就是代价最小的查询计划。因为每个计划都绑定一系列的低层过程，每一个过程对应查询中的一个低层操作。优化器此时将所有的过程进行计算，选择一个代价最低的执行。,生成并选择最低代价的查询计划,Sql查询内部原理,第一阶段,第二阶段,第三阶段,第四阶段,三种优化器,基于选择的优化器,基于代价的 优化器（CBO）,基于规则的 优化器(RBO),Oracle基于规则优

10、化器是通过一组简单的启发式规则和打分原则来确定语句的执行过程和访问方式的。RBO是不关心被访问对象的实际数据分布情况，索引效率等，仅凭想象去决定该如何访问数据库。,优化器尝试找到最低成本的访问数据的方法，为了最大的吞吐量或最快的初始响应时间。计算使用不同的执行计划的成本，并选择成本最低的一个。 计算的依据主要是依赖统计信息的收集，所以比较过分的依赖于正确的统计信息。 在计算表的连接顺序是过多的耗费CPU，最好使用+ordered 提示符固定连接顺序,根据表是否被分析过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器。,基于规则的优化器成本计算,各种操作的优先级别已经确

11、定，造成的负面结果是最终的访问结果可能并不是最优的执行路径。,三种关联规则,1：排序-合并连接 内部链接过程： 如果两个行源都已经预先排序，则这种链接方式效率比较高。,SELECT /*+use_merge(A,B)*/ * FROM (SELECT T.* FROM SRC.FU_T_USER_D_G T WHERE T.GATHER_MON = 201201 AND T.GATHER_DAY = 31 AND GENT_TAG = 30) A, (SELECT * FROM SRC.FU_T_USER_3G_D_G T WHERE T.GATHER_MON = 201201 AND T.G

12、ATHER_DAY = 31) B WHERE A.USER_ID = B.USER_ID,嵌套循环连接：,主表(驱动表)和被探查表的选择：row source1中的每一行去匹配row source2 中的每一行，所以主表往往选择较小的表，减少整体的I/O操作。 优点：可以先返回已经连接的行，而不必等待所有连接操作处理完成后返回数据，这可以实现快速的响应时间,驱动表和被探查表,行源，驱动表和被探查表的概念,SELECT * FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY SVC_ID ORDER BY USER_STA) ORD FROM SR

13、C.FU_T_USER_D_G T WHERE T.GATHER_MON = 201201 AND T.GATHER_DAY = 31) T, SRC.D_USER_DNNR A, SRC.D_EVAL_CHANNEL B WHERE T.USER_DNNR = A.USER_DNNR(+) AND T.BLG_CHANNEL = B.EVAL_CHANNEL(+) AND ORD = 1,驱动表又称为外部表，指在做表的关联时的外部表。 上例中row source1 为驱动表， row source2 为被驱动表 驱动表一般选取较小的表,驱动表未什么选择小表,假设行源1有20条记录，行源2有1

14、0w记录，I/O操作次数总和为200 0020,假设行源1有10w条记录，行源2有20条记录 I/O操作次数总和为210 0000,CBO优化器会根据行源所占物理数据块的大小以及其他统计信息来决定最后的执行计划。,哈希连接,较小的表用来构建哈希表，另外一个表作为匹配表，一般用于等值关联。 例如：,SELECT * FROM (SELECT T.* FROM SRC.FU_T_USER_D_G T WHERE T.GATHER_MON = 201201 AND T.GATHER_DAY = 31 AND GENT_TAG = 30) A, (SELECT * FROM SRC.FU_T_USER

15、_3G_D_G T WHERE T.GATHER_MON = 201201 AND T.GATHER_DAY = 31) B WHERE A.USER_ID = B.USER_ID,哈希表的构建以及哈希连接的原理,哈希算法的演示： 定义hash算法为n MOD 10，通过这种算法，可以将所有进入的数据均匀放在10个hash bucket里面，hash bucket编号从0到9.比如，我们把1到100都通过这个hash函数均匀放到这10个hash bucket里，当查找32在哪里时，只要将32 MOD 10等于2，这样就知道可以到2号hash bucket里去找。 哈希算法的优缺点： 需要适当的

16、哈希算法 以牺牲内存资源来换取速度，牺牲空间换取时间。,三种关联方式的I/O次数比较： 问题：在用户表中找到3G每个用户的归属渠道，假设用户表只有200w数据。渠道码表20条记录,SELECT * FROM (SELECT * FROM SRC.FU_T_USER_D_G T WHERE T.GATHER_MON = 201201 AND T.GATHER_DAY = 31 AND t.gent_tag=20) A, SRC.d_User_Dnnr B WHERE A.user_dnnr = B.User_Dnnr(+),执行计划1：笛卡尔积联结 1：读取两个表，读取操作2000000+20=

17、2000020 2：创建笛卡尔积：2000000*20=40000000写 3：读取笛卡尔积并与选择条件比较2000000*20=40000000读 合计I/O成本：2000020+40000000+40000000=82000020,执行计划2：合并排序联结 1：读取两个表2000000+20=2000020 2：分别按照关联字段排序 3：进行关联操作,执行计划3：循环嵌套联结 1：读取码表，以码表为驱动表，没读取一条记录，和用户表匹配一次 合计I/O操作：20+20*2000000=40000020,执行计划4：哈希联结 1：对码表构建哈希表，读20+20次写 2：将用户表做为驱动表进行读

18、取并匹配 2000000次读取2000000次计算 2000000次读取码表 合计I/O=40000040 次读写,分区表的构建,种类： 范围分区（通常按照月、日分区）、间隔分区、散列分区、列表分区、应用分区、基于虚拟列的分区、系统分区、组合分区等 意义：将一个大表从逻辑上划分成较小的快 分区表的维护： 增加分区 alter table tablename add partition partition_name 合并分区alter table tablename merge partition partition_name1, partition_name2 into partition p

19、artition_name3; 删除分区 alter table tablename drop partition pattition_name 拆分分区 ALTER TABLE dable_name SPLIT PARTITION P_201103 AT (201103) INTO (PARTITION P_201102,PARTITION P_201103),索引的使用以及区别,索引就是建立起相应字段与rowid的对应关系。 索引建立：CREATE INDEX index_name ON tablename (字段名称) 索引的种类： B树索引(默认类型) 位图索引 HASH索引 反转键(

20、reverse key)索引 基于函数的索引 分区索引(本地和全局索引) ：相对于分区表而言，本地索引和全局索引最大的区别在于删除某个分区数据后，全局索引会变的不可用，但本地索引不会出现这种情况 位图连接索引等 索引的建立注意事项：对于经常更改的表不适合建立索引，索引要定时维护，对于有索引的表，插入数据更加耗时。,常用的hint提示符,Hint提示符的应用： /*+ordered*/ 按照sql的顺序依次关联表 /*+first_rows*/最快响应时间为目标 /*+all_rows*/最大吞吐量为目标 /*+CHOOSE*/ 根据统计信息来判断基于开销还是基于规则 /*+index( tab

21、le_name index_name)*/ 多线程用法 /*+parallel(table_name,number)*/ Hint提示符的位置 一般位于select ，update，delete,等操作的后面并注释掉 多个Hint操作符用空格隔开,IN和EXISTS语句的区别,NOT Exists 用法 SELECT /*+First_Rows*/ * FROM SRC.FU_T_USER_D_G T WHERE T.GATHER_MON = 201201 AND T.GATHER_DAY = 31 AND T.GENT_TAG = 30 AND NOT EXISTS (SELECT SVCN

22、UM SVC_ID - 字段可以随意选取 FROM SRC.FO_B_USERPRODUCT_D T1 WHERE T1.GATHER_MON = 201201 AND T1.GATHER_DAY = 31 AND T1.PRODUCTID IN (SELECT PRO_ID FROM SRC.D_PRO_TV) AND T1.SVCNUM = T.SVC_ID) 使用Exists必须要关联,其他实现方法：,SELECT /*+First_Rows*/ * FROM SRC.FU_T_USER_D_G T, (SELECT SVCNUM SVC_ID FROM SRC.FO_B_USERPRODUCT_D T1 WHERE T1.GATHER_MON = 201201 AND T1.GATHER_DAY = 31 AND T1.PRODUCTID IN (SELEC