《最新sql优化经典讲解ppt模版课件》由会员分享，可在线阅读，更多相关《最新sql优化经典讲解ppt模版课件（82页珍藏版）》请在金锄头文库上搜索。
1、SQL优化基础,索引,索引的类型B*树索引 反向码索引 降序索引 位图索引 基于函数的索引,B*树索引,基于二叉树的,由分支块和叶子块组成. 包括每个被索引列的值和行所对应的ROWID Create index indexname on tabename(columnname),反向索引,B*索引的一个分支 反转索引码中每列的字节 便于并发 Create index indexname on tablename(column) reverse,ORACLE的内部表示,SQL select dump(1,16) from dual2 union all select dump(2,16) fro
2、m dual3 union all select dump(3,16) from dual4 /DUMP(1,16) - Typ=2 Len=2: c1,2 Typ=2 Len=2: c1,3 Typ=2 Len=2: c1,4已用时间: 00: 00: 00.00,反向的情况,SQL select dump(reverse(1),16) from dual2 union all select dump(reverse(2),16) from dual3 union all select dump(reverse(3),16) from dual;DUMP(REVERSE(1),1 - Typ
3、=2 Len=2: 2,c1 Typ=2 Len=2: 3,c1 Typ=2 Len=2: 4,c1已用时间: 00: 00: 00.00 SQL,降序索引,索引中的储存方式由升序变成降序 SQL select * from test t where id between 1 and 100 order by id desc,name asc; 已选择100行。 已用时间: 00: 00: 00.01 Execution Plan -0 SELECT STATEMENT Optimizer=CHOOSE1 0 SORT (ORDER BY)2 1 TABLE ACCESS (BY INDEX
4、ROWID) OF TEST3 2 INDEX (RANGE SCAN) OF PK_ID (UNIQUE),SQL create index i_desc on test(id desc,name asc); 索引已创建。 已用时间: 00: 00: 00.01 Execution Plan -0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF TEST2 1 INDEX (RANGE SCAN) OF PK_ID (UNIQUE),位图索引,用于低cardinality列(即列的唯一值除以行数为一个
5、很小的值,接近零) 如”性别” 不适用于并行 只能在CBO下 Create bitmap index indexname on tablename(column),SQL select * from testbitmap where sex=男; 已选择5000行。Execution Plan -0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (FULL) OF TESTBITMAP,位图索引的特点,比B*tree索引更节省空间 建立速度快 不能走RULE 可存储NULL值,SQL create index bit_inx_sex on
6、testbitmap(sex); 索引已创建。 SQL select * from testbitmap where sex=男;已选择5000行。 Execution Plan -0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF TESTBITMAP2 1 INDEX (RANGE SCAN) OF BIT_INX_SEX (NON-UNIQUE),函数索引,必须拥有QUERY_REWRITE权限 必须使用基于成本的优化器 必须设置以下两个参数:query_rewrite_enabled=trueq
7、uery_rewrite_integrity=trustedCreate index indexname on tabename(fun(column),SQL create index l_fun on test(upper(name); 分析表略去 SQL select * from test where upper(name)=1TEST;ID NAME - -1 1test已用时间: 00: 00: 00.04 Execution Plan -0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=11)1 0 TABLE AC
8、CESS (BY INDEX ROWID) OF TEST (Cost=2 Card=1 Bytes=11)2 1 INDEX (RANGE SCAN) OF L_FUN (NON-UNIQUE) (Cost=1 Card=1),各种索引使用场合,B*tree多用于oltp系统.cardinlity高的情况下反向索引OPS环境下降序索引带排序操作,位图索引适用于低cardinality列.适合集中读取,不适合插入和修改,原因在于任何需要更新同一个位图索引条目的修改都将锁定整个位图，严重抑制了并发性,未用到索引的原因,类型不匹配 对列使用了函数，而索引只是基于列的。 使用索引实际会降低速度。 很
9、长时间没有分析表了，表的增长较快，这样CBO会作出错误的判断。 结果集返回的比例过大 Query_rewrite_enabled未设为true而导致函数索引不可用,索引的管理,DROP过多的索引 监控索引alter index monitoring usage;注:9205以下有BUG. 重建索引alter index IDX rebuild online 迁移索引alter index rebuild tablespace ,常见的一些问题,使用NULL条件查询方法UPDATE为N/A,然后查:如 id=N/A Like %a%:使用firstrows提示,Like %DURE 如:wher
10、e object_type like %DUREcreate index inxreserve on user_test(reverse(object_type) 反向索引创建后改写为: where reverse(object_type) like ERUD%,理解SQL工具,执行计划,ORACLE_HOME/rdbms/admin/utlxplan.sql Create public synonym plan_table for sys.plan_table;,查看执行计划的几种方式,1.explain plan for select * .select * from table(dbms
11、_xplan.display); 2.Explain plan set statement_id=idinto plan_table for sql statement 3.sqlplus: set autotrace on; 4.sql_trace and tkprof,Trace文件解释,Count Cpu Elapsed Disk Query Current Rows,阅读执行计划,首先读取缩进最深的语句 如果两上语句的缩进程度相同,则上面的先,Oracle优化器,Oracle优化器的类型,Cost Based Optimizer(简称CBO) 基于统计信息的优化Rule Based O
12、ptimizer(简称RBO) 基于数据字典的优化，在以后的版本中将不支持,RBO,根据数据字典 查询有无可用的索引，如果有则使用，否则不使用 不同的访问方法有预定好的优先级，选择优先级高的执行方法,RBO中访问数据,Rowid - Cluster Join(SR) - Hash Cluster Key (SR)- UK或PK(SR) - Cluster Join - Hash Cluster Key - Index Cluster Key - Composite Index - Single Column Index - Bound Range Index Scan - Unbound Ra
13、nge Index Scan - Sort Merge Join - Max or Min on Indexed Column - Order on Indexed Column - Full Table Scan,RBO的特点,总是使用索引 总是使用驱动表 只有在不可避免的避免的情况才使用全表扫描 任何索引都可以(但并非很好),例子,SQL select count(*) from user_test;COUNT(*) -30904 已用时间: 00: 00: 00.01 SQL create index id_owner on user_test(owner);索引已创建。已用时间: 00
14、: 00: 00.09,SQL select * from user_test where owner=SYS; 已选择13931行。 已用时间: 00: 00: 00.07 Execution Plan -0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF USER_TEST2 1 INDEX (RANGE SCAN) OF ID_OWNER (NON-UNIQUE) Statistics -0 recursive calls0 db block gets2060 consistent gets29 physical reads0 redo size995554 bytes sent via SQL*Net to client10711 bytes received via SQL*Net from client930 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk),
