- 博客(21)
- 收藏
- 关注
RSS订阅转载 递归查询(一)：connect by
语法： { CONNECT BY [ NOCYCLE ] condition [ START WITH condition ]| START WITH condition CONNECT BY [ NOCYCL...
2016-11-07 18:07:46
131
转载 1次connect by的优化
昨天一条sql造成了大量的CBC latch等待，CPU超过了90%，sql每次执行消耗了6441854逻辑读，耗时130s，只返回3行，基本全部消耗在CPU上，sql文本以及执行计划如下： sql文本 ...
2016-08-06 16:40:53
463
转载 connect by递归查询
对于递归查询，在11g之前只能用start with ... connnect by prior来实现，11g之后可用with as来实现，这里的讨论只基于connect by的形式。 在10.2.0...
2016-08-05 18:51:40
332
转载 Subquery Coalescing
1.定义: The purpose of subquery coalescing is to combine equivalent semi- and anti-join subqueries into a sin...
2016-07-21 11:45:38
137
转载 Join Elimination
1.定义 The purpose of join elimination is to remove redundant joins—in other words, to completely avoid execu...
2016-07-19 14:27:27
232
转载 Set Join Conversion
1.定义SJC - set join conversion The purpose of set to join conversion is to avoid sort operations in ...
2016-07-15 15:28:06
116
转载 OR Expansion
1.定义OR expansion is a transformation that can be used to optimize disjunctive queries (queries that contain...
2016-07-12 11:10:20
129
转载 Predicate Move-around
1.定义PM - predicate move-aroundA new type of optimization, called predicate move-around, is introduced. ...
2016-07-11 13:08:43
425
转载 filter push-down
FPD - filter push-down Filter pushdown is part from query transformations. Query transformation was b...
2016-07-09 02:49:01
352
转载 Join Predicate Push-Down
1.定义The join predicate pushdown (JPPD) transformation allows a view to be joined with index-based nested-lo...
2016-07-06 16:46:28
469
转载 Distinct Placement
1.定义 DP - distinct placementThe purpose of distinct placement is to eliminate duplicates as soon as pos...
2016-07-05 16:46:30
161
转载 Push Subquery
1.定义 By default, if Oracle cannot unnest a subquery it postpones executes of that subquery to the en...
2016-07-04 18:29:23
102
转载 b*tree index的那些事(四):index rebuild
创建测试表和索引 SQL> create table t1 as select * from dba_objects; Table created. ...
2016-07-03 15:28:19
92
转载 Order By Elimination
定义： OBYE - order by eliminationThe purpose of order-by elimination is to remove superfluous ORDER BY cl...
2016-06-27 21:53:47
127
转载 Subquery Unnesting
1.定义： The purpose of subquery unnesting is to inject semi- (IN, EXISTS), anti-join (NOT IN, NOT EXISTS),...
2016-06-23 14:25:05
115
转载 View Merging
1.定义： The purpose of view merging is to reduce the number of query blocks due to views and inline views ...
2016-06-20 18:58:31
170
转载 关于or展开的一个bug
描述 近日一个同事发来一个性能极差的sql的10053 trace，据同事说自动收集统计信息是打开的，每天表的数据量变化也不大，说明基本可以排除统计信息不准的情况，最后通过10053发...
2016-06-10 03:58:34
105
转载 b*tree index的那些事(三):唯一索引与非唯一索引结构区别
唯一索引和非唯一索引在结构上有一些区别，下面就来分析这些区别。 结构区别 root block和branch block结构基本一样,引用之前dump的非唯一索引分析。 ...
2016-06-05 00:14:01
282
转载 b*tree index的那些事(一):索引简介
B*tree index简介 结构图 从结构图可以看出，B*tree index由root block，branch...
2016-05-29 06:48:56
363
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人