-
编辑 删除
递归查询(一)：connect by
语法： { CONNECT BY [ NOCYCLE ] condition [ START WITH condition ]| START WITH condition CONNECT BY [ NOCYCLE ] condition}START WITH:specifies the root row(s) of the hierarchy.CONNECT BY:specifies the relationship between parent rows and child rows of the hierarchy. 1.In a hierarchi...
-
编辑 删除
1次connect by的优化
昨天一条sql造成了大量的CBC latch等待，CPU超过了90%，sql每次执行消耗了6441854逻辑读，耗时130s，只返回3行，基本全部消耗在CPU上，sql文本以及执行计划如下：sql文本SELECT /*+qb_name(a)*/ t.idFROM venustech.venus_acc_organizationview t start WITH t.id IN (? ? ? ? SELECT /*+qb_...
-
编辑 删除
connect by递归查询
对于递归查询，在11g之前只能用start with ... connnect by prior来实现，11g之后可用with as来实现，这里的讨论只基于connect by的形式。 ----------------------------------- ---------- ------------------------------------------------------------ _optimizer_connect_by_combine_sw T...
-
编辑 删除
Subquery Coalescing
1.定义: The purpose of subquery coalescing is to combine equivalent semi- and anti-join subqueries into a single query block. The main reason for applying this heuristic-based query transformation, which is available as of version 11.2, is to reduce the number of table accesses, and thus to redu...
-
编辑 删除
Join Elimination
1.定义 The purpose of join elimination is to remove redundant joins—in other words, to completely avoid executing a join even if a SQL statement explicitly calls for it. The key information used by the query optimizer to decide whether it’s sensible to implement this query transformation is th...
-
编辑 删除
Set Join Conversion
1.定义SJC - set join conversionThe purpose of set to join conversion is to avoid sort operations in compound queries involving INTERSECT and MINUS. This query transformation also postpones the elimination of duplicates to the end of processing for such queries.A compound query based on th...
-
编辑 删除
OR Expansion
1.定义OR expansion is a transformation that can be used to optimize disjunctive queries (queries that contain OR clauses). The basic idea in OR expansion is to transform a query containing disjunctions into the form of a UNION ALL query of two or more branches. This is done by splitting the disj...
-
编辑 删除
Predicate Move-around
1.定义PM - predicate move-aroundA new type of optimization, called predicate move-around, is introduced. It is shown how this optimization considerably improves the efficiency of evaluating SQL queries that have query graphs with a large number of query blocks (which is a typical situation whe...
-
编辑 删除
filter push-down
FPD - filter push-downFilter pushdown is part from query transformations. Query transformation was based on heuristic rules in 9i. From 10G R1 however by default query transformations are cost based since"_optimizer_cost_based_transformation" defaults to linear. ( there are / were bugs rela...
-
编辑 删除
Join Predicate Push-Down
1.定义The join predicate pushdown (JPPD) transformation allows a view to be joined with index-based nested-loop join method, which may provide a more optimal alternative. In the join predicate pushdown transformation, the view remains a separate query block, but it contains the join predicate, w...
-
编辑 删除
Distinct Placement
1.定义DP - distinct placement当对连接查询进行distinct操作时，CBO可能优先将一个表先进行一次distinct操作转化成一个内嵌视图后，再与后续的表关联。即执行计划里会出现两次distinct，这种转换常用于对某个表先执行distinct能过滤大量数据的情况。是否转化取决于COST。 2.参数(默认为true) KSPPINM &n...
-
编辑 删除
Push Subquery
1.定义 By default, if Oracle cannot unnest a subquery it postpones executes of that subquery to the end of the execution plan. Historically the push_subq hint told Oracle to run any outstanding subqueries at the earliest possible moment. But in 10g this changes (for the be...
-
编辑 删除
b*tree index的那些事(四):index rebuild
创建测试表和索引 SQL> create table t1 as select * from dba_objects; Table created. SQL> create index i1 on t1(object_id); Index created. SQL> select object_id,object_name from dba_objects where object_name in('T1','I1'); OBJECT_ID OBJECT_NAM --...
-
编辑 删除
一次DBA_FREE_SPACE的优化
sql文本：SELECT TABLESPACE_NAME, FILE_ID, COUNT (*) EXTENT_COUNT, MIN (BYTES) BY...
-
编辑 删除
Order By Elimination
定义：OBYE - order by eliminationCBO在进行转换前先将sql中一些没有必要的排序操作去掉，防止一些不必要的排序操作。 参数：(默认为true) KSPPINM KSPPST KSPPDESC--------...
-
编辑 删除
Subquery Unnesting
1.定义： 简单来说就是当sql出现嵌套子查询时，CBO会尝试将子查询展开，使子查询的表能与外部查询的表相关联。 2.分类： SU - subquery unnesting 1)semi-join 当子查询前的where条件：exists/in/anyCBO会这样转换：SU: Transform an ANY subquery to semi-join or distinct 2)anti-join ...
-
编辑 删除
View Merging
1.定义： 简单来说就是CBO将视图展开到外部查询中去，并且将视图中的where条件也合并到外部查询的where条件中。 Recursively process any views referenced by the current view Either completely merge the view into the referencing query block OR simply move the definiti...
-
编辑 删除
关于or展开的一个bug
描述近日一个同事发来一个性能极差的sql的10053 trace，据同事说自动收集统计信息是打开的，每天表的数据量变化也不大，说明基本可以排除统计信息不准的情况，最后通过10053发现了一些奇怪的现象，请容我慢慢道来。 问题分析 sql文本 select * ...
-
编辑 删除
b*tree index的那些事(三):唯一索引与非唯一索引结构区别
唯一索引和非唯一索引在结构上有一些区别，下面就来分析这些区别。 结构区别 root block和branch block结构基本一样,引用之前dump的非唯一索引分析。 非唯一索引的branch block Branch block dump =================header address 14018650799571...
-
编辑 删除
b*tree index的那些事(二):索引结构分析
B*tree index结构分析 SQL>?drop?table?lxy purge;Table?dropped.SQL>?create?table?lxy?(a?char(2000));Table&n...
