Join
Row source
Inner join
Outer join
Left outer-join:L.c=R.c(+)
Semi-join :一个逻辑概念,但是写不出semi-join没有一个表达方式,通常exist、in这两种语法会变成semi-join ,所谓半连接就是用一个结果集过滤另一个结果集,但是这种过滤的方式使用join的方式过滤。一般情况如果找到一个合适的结果就不往下找了,这就是semi-join
Anti-join :not exists、not in
Nested loop NL
Sort merge joiin -SM 笛卡尔属于这种
Hash join Hj
子查询
Sbuquery 在query/DML内部的查询 in (select.. .. )
Correlated 相关子查询 列>... ...
Non-correlated 无关子查询 你完全不能把这两个表之间做什么关系
Scalar view 标量 子查询 select子句里面带的子查询 通常性能不好
In-line view
无关联子查询transformaton
=any 符合这个条件的就可以。有的时候这种无关联的成本有时候比较高,因为得先吧把any中的全部查出来,再做比较,其实oracle会给你做成这种:
执行计划是semi-join
点击( 此处 )折叠或打开
-
set line 200
-
set pages 200
-
drop table emp1 ;
-
create table emp1 as select * from emp ;
-
alter session set events '10053 trace name context forever ,level 1';
-
select / * + ^ ^ ^ ^ * / * from emp where hiredate = any ( select hiredate from emp1 where deptno = 20 ) ;
-
alter session set events '10053 trace name context off';
-
-
/ *
-
SELECT EMP . *
-
FROM SCOTT . EMP1 EMP1 , SCOTT . EMP EMP
-
WHERE EMP . HIREDATE = EMP1 . HIREDATE AND EMP1 . DEPTNO = 20
-
* /
点击( 此处 )折叠或打开
-
Registered qb : SEL$1 0x873b6678 ( PARSER )
-
- - - - - - - - - - - - - - - - - - - - -
-
QUERY BLOCK SIGNATURE
-
- - - - - - - - - - - - - - - - - - - - -
-
signature ( ) : qb_name = SEL$1 nbfros = 1 flg =
-
fro ( ) : flg = 4 objn = 75335 hint_alias = "EMP" @ "SEL$1"
-
-
Registered qb : SEL$2 0x873b14a0 ( PARSER )
-
- - - - - - - - - - - - - - - - - - - - -
-
QUERY BLOCK SIGNATURE
-
- - - - - - - - - - - - - - - - - - - - -
-
signature ( ) : qb_name = SEL$2 nbfros = 1 flg =
-
fro ( ) : flg = 4 objn = 78917 hint_alias = "EMP1" @ "SEL$2"
-
-
SPM : statement not found in SMB
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
Automatic degree of parallelism ( ADOP )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
Automatic degree of parallelism is disabled : Parameter .
-
-
PM : Considering predicate move - around in query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
Predicate Move - Around ( PM )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
OPTIMIZER INFORMATION
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
- - - - - Current SQL Statement for this session ( sql_id = 0rbab21pk95av ) - - - - -
-
select / * + ^ ^ ^ ^ * / * from emp where hiredate = any ( select hiredate from emp1 where deptno = 20 )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Legend
-
The following abbreviations are used by optimizer trace .
-
CBQT - cost - based query transformation
-
JPPD - join predicate push - down
-
OJPPD - old - style ( non - cost - based ) JPPD
-
FPD - filter push - down
-
PM - predicate move - around
-
CVM - complex view merging
-
SPJ - select - project - join
-
SJC - set join conversion
-
SU - subquery unnesting
-
OBYE - order by elimination
-
OST - old style star transformation
-
ST - new ( cbqt ) star transformation
-
CNT - count ( col ) to count ( * ) transformation
-
JE - Join Elimination
-
JF - join factorization
-
SLP - select list pruning
-
DP - distinct placement
-
qb - query block
-
LB - leaf blocks
-
DK - distinct keys
-
LB/K - average number of leaf blocks per key
-
DB/K - average number of data blocks per key
-
CLUF - clustering factor
-
NDV - number of distinct values
-
Resp - response cost
-
Card - cardinality
-
Resc - resource cost
-
NL - nested loops ( join )
-
SM - sort merge ( join )
-
HA - hash ( join )
-
CPUSPEED - CPU Speed
-
IOTFRSPEED - I/O transfer speed
-
IOSEEKTIM - I/O seek time
-
SREADTIM - average single block read time
-
MREADTIM - average multiblock read time
-
MBRC - average multiblock read count
-
MAXTHR - maximum I/O system throughput
-
SLAVETHR - average slave I/O throughput
-
dmeth - distribution method
-
1 : no partitioning required
-
2 : value partitioned
-
4 : right is random ( round - robin )
-
128 : left is random ( round - robin )
-
8 : broadcast right and partition left
-
16 : broadcast left and partition right
-
32 : partition left using partitioning of right
-
64 : partition right using partitioning of left
-
256 : run the join in serial
-
0 : invalid distribution method
-
sel - selectivity
-
ptn - partition
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
PARAMETERS USED BY THE OPTIMIZER
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
PARAMETERS WITH ALTERED VALUES
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
-
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
PARAMETERS IN OPT_PARAM HINT
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Column Usage Monitoring is ON : tracking level = 1
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
-
Considering Query Transformations on query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
Query transformations ( QT )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
JF : Checking validity of join factorization for query block SEL$2 ( # )
-
JF : Bypassed : not a UNION or UNION - ALL query block .
-
ST : not valid since star transformation parameter is FALSE
-
TE : Checking validity of table expansion for query block SEL$2 ( # )
-
TE : Bypassed : No partitioned table in query block .
-
CBQT : Validity checks passed for 0rbab21pk95av .
-
CSE : Considering common sub - expression elimination in query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Common Subexpression elimination ( CSE )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : Considering common sub - expression elimination in query block SEL$2 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Common Subexpression elimination ( CSE )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : CSE not performed on query block SEL$2 ( # ) .
-
CSE : CSE not performed on query block SEL$1 ( # ) .
-
OBYE : Considering Order - by Elimination from view SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Order - by elimination ( OBYE )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * *
-
OBYE : OBYE bypassed : no order by to eliminate .
-
OJE : Begin : find best directive for query block SEL$1 ( # )
-
OJE : End : finding best directive for query block SEL$1 ( # )
-
OJE : Begin : find best directive for query block SEL$2 ( # )
-
OJE : End : finding best directive for query block SEL$2 ( # )
-
query block SEL$1 ( # ) unchanged
-
Considering Query Transformations on query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
Query transformations ( QT )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : Considering common sub - expression elimination in query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Common Subexpression elimination ( CSE )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : Considering common sub - expression elimination in query block SEL$2 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Common Subexpression elimination ( CSE )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : CSE not performed on query block SEL$2 ( # ) .
-
CSE : CSE not performed on query block SEL$1 ( # ) .
-
query block SEL$1 ( # ) unchanged
-
apadrv - start sqlid = 841411239277270363
-
:
-
call ( in - use = 1832 , alloc = 16344 ) , compile ( in - use = 64568 , alloc = 66920 ) , execution ( in - use = 3456 , alloc = 4032 )
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Peeked values of the binds in SQL statement
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
-
CBQT : Considering cost - based transformation on query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
COST - BASED QUERY TRANSFORMATIONS
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
FPD : Considering simple filter push ( pre rewrite ) in query block SEL$2 ( # )
-
FPD : Current where clause predicates "EMP1" . "DEPTNO" = 20
-
-
FPD : Considering simple filter push ( pre rewrite ) in query block SEL$1 ( # )
-
FPD : Current where clause predicates "EMP" . "HIREDATE" = ANY ( SELECT "EMP1" . "HIREDATE" FROM "EMP1" "EMP1" )
-
-
OBYE : Considering Order - by Elimination from view SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Order - by elimination ( OBYE )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * *
-
OBYE : OBYE bypassed : no order by to eliminate .
-
Considering Query Transformations on query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
Query transformations ( QT )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : Considering common sub - expression elimination in query block SEL$1 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Common Subexpression elimination ( CSE )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : Considering common sub - expression elimination in query block SEL$2 ( # )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Common Subexpression elimination ( CSE )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
CSE : CSE not performed on query block SEL$2 ( # ) .
-
CSE : CSE not performed on query block SEL$1 ( # ) .
-
kkqctdrvTD - start on query block SEL$1 ( # )
-
kkqctdrvTD - start : :
-
call ( in - use = 1832 , alloc = 16344 ) , compile ( in - use = 107016 , alloc = 110912 ) , execution ( in - use = 3456 , alloc = 4032 )
-
-
Registered qb : SEL$1 0x8741cf18 ( COPY SEL$1 )
-
- - - - - - - - - - - - - - - - - - - - -
-
QUERY BLOCK SIGNATURE
-
- - - - - - - - - - - - - - - - - - - - -
-
signature ( ) : NULL
-
Registered qb : SEL$2 0x8741db40 ( COPY SEL$2 )
-
- - - - - - - - - - - - - - - - - - - - -
-
QUERY BLOCK SIGNATURE
-
- - - - - - - - - - - - - - - - - - - - -
-
signature ( ) : NULL
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Cost - Based Subquery Unnesting
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
SU : Unnesting query blocks in query block SEL$1 ( # 1 ) that are valid to unnest .
-
Subquery removal for query block SEL$2 ( # 2 )
-
RSW : Not valid for subquery removal SEL$2 ( # 2 )
-
Subquery unchanged .
-
Subquery Unnesting on query block SEL$1 ( # 1 ) SU : Performing unnesting that does not require costing .
-
SU : Considering subquery unnest on query block SEL$1 ( # 1 ) .
-
SU : Checking validity of unnesting subquery SEL$2 ( # 2 )
-
SU : Passed validity checks .
-
SU : Transforming ANY subquery to a join .
-
Registered qb : SEL$5DA710D3 0x8741cf18 ( SUBQUERY UNNEST SEL$1 ; SEL$2 )
-
- - - - - - - - - - - - - - - - - - - - -
-
QUERY BLOCK SIGNATURE
-
- - - - - - - - - - - - - - - - - - - - -
-
signature ( ) : qb_name = SEL$5DA710D3 nbfros = 2 flg =
-
fro ( ) : flg = 0 objn = 75335 hint_alias = \ "EMP\" @ \ "SEL$1\"
-
fro ( 1 ) : flg = 0 objn = 78917 hint_alias = \ "EMP1\" @ \ "SEL$2\"
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Cost - Based Complex View Merging
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
CVM : Finding query blocks in query block SEL$5DA710D3 ( # 1 ) that are valid to merge .
-
OJE : Begin : find best directive for query block SEL$5DA710D3 ( # 1 )
-
OJE : End : finding best directive for query block SEL$5DA710D3 ( # 1 )
-
kkqctdrvTD - cleanup : transform ( in - use = 5064 , alloc = 8392 ) :
-
call ( in - use = 2512 , alloc = 16344 ) , compile ( in - use = 125432 , alloc = 135984 ) , execution ( in - use = 3496 , alloc = 4032 )
-
-
kkqctdrvTD - end :
-
call ( in - use = 2512 , alloc = 16344 ) , compile ( in - use = 117456 , alloc = 135984 ) , execution ( in - use = 3496 , alloc = 4032 )
-
-
Subquery removal for query block SEL$2 ( # 2 )
-
RSW : Not valid for subquery removal SEL$2 ( # 2 )
-
Subquery unchanged .
-
SU : Transforming ANY subquery to a join .
-
SJC : Considering set - join conversion in query block SEL$5DA710D3 ( # 1 )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Set - Join Conversion ( SJC )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
SJC : not performed
-
OJE : Begin : find best directive for query block SEL$5DA710D3 ( # 1 )
-
OJE : End : finding best directive for query block SEL$5DA710D3 ( # 1 )
-
JE : Considering Join Elimination on query block SEL$5DA710D3 ( # 1 )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
Join Elimination ( JE )
-
* * * * * * * * * * * * * * * * * * * * * * * * *
-
SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
-
SELECT EMP . EMPNO EMPNO , EMP . ENAME ENAME , EMP . JOB JOB , EMP . MGR MGR , EMP . HIREDATE HIREDATE , EMP . SAL SAL , EMP . COMM COMM , EMP . DEPTNO DEPTNO FROM SCOTT . EMP1 EMP1 , SCOTT . EMP EMP WHERE EMP . HIREDATE = EMP1 . HIREDATE AND EMP1 . DEPTNO = 20
-
JE : cfro : EMP1 objn : 75335 col# : 5 dfro : EMP dcol# : 5
-
JE : cfro : EMP1 objn : 75335 col# : 5 dfro : EMP dcol# : 5
-
JE : cfro : EMP objn : 78917 col# : 5 dfro : EMP1 dcol# : 5
-
SQL : * * * * * * * UNPARSED QUERY IS * * * * * * *
-
SELECT EMP . EMPNO EMPNO , EMP . ENAME ENAME , EMP . JOB JOB , EMP . MGR MGR , EMP . HIREDATE HIREDATE , EMP . SAL SAL , EMP . COMM COMM , EMP . DEPTNO DEPTNO FROM SCOTT . EMP1 EMP1 , SCOTT . EMP EMP WHERE EMP . HIREDATE = EMP1 . HIREDATE AND EMP1 . DEPTNO = 20
-
Query block SEL$5DA710D3 ( # 1 ) unchanged
-
PM : Considering predicate move - around in query block SEL$5DA710D3 ( # 1 )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
Predicate Move - Around ( PM )
-
* * * * * * * * * * * * * * * * * * * * * * * * * *
-
PM : PM bypassed : Outer query contains no views .
-
PM : PM bypassed : Outer query contains no views .
-
kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )
-
kkqctdrvTD - start : :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 119176 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 120008 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
kkqctdrvTD - end :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 120528 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )
-
kkqctdrvTD - start : :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 120528 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 121344 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
kkqctdrvTD - end :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 121864 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )
-
kkqctdrvTD - start : :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 121864 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
TE : Checking validity of table expansion for query block SEL$5DA710D3 ( # 1 )
-
TE : Bypassed : No partitioned table in query block .
-
kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 122672 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
kkqctdrvTD - end :
-
call ( in - use = 3568 , alloc = 16344 ) , compile ( in - use = 123192 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
TE : Checking validity of table expansion for query block SEL$5DA710D3 ( # 1 )
-
TE : Bypassed : No partitioned table in query block .
-
ST : Query in kkqstardrv : * * * * * * * UNPARSED QUERY IS * * * * * * *
-
SELECT EMP . EMPNO EMPNO , EMP . ENAME ENAME , EMP . JOB JOB , EMP . MGR MGR , EMP . HIREDATE HIREDATE , EMP . SAL SAL , EMP . COMM COMM , EMP . DEPTNO DEPTNO FROM SCOTT . EMP1 EMP1 , SCOTT . EMP EMP WHERE EMP . HIREDATE = EMP1 . HIREDATE AND EMP1 . DEPTNO = 20
-
ST : not valid since star transformation parameter is FALSE
-
kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )
-
kkqctdrvTD - start : :
-
call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 123192 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
JF : Checking validity of join factorization for query block SEL$5DA710D3 ( # 1 )
-
JF : Bypassed : not a UNION or UNION - ALL query block .
-
kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :
-
call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 124000 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
kkqctdrvTD - end :
-
call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 124520 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
JPPD : Considering Cost - based predicate pushdown from query block SEL$5DA710D3 ( # 1 )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Cost - based predicate pushdown ( JPPD )
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
kkqctdrvTD - start on query block SEL$5DA710D3 ( # 1 )
-
kkqctdrvTD - start : :
-
call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 124520 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
kkqctdrvTD - cleanup : transform ( in - use = , alloc = ) :
-
call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 125328 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
kkqctdrvTD - end :
-
call ( in - use = 3664 , alloc = 16344 ) , compile ( in - use = 125872 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
JPPD : Applying transformation directives
-
query block SEL$1 transformed to SEL$5DA710D3 ( # 1 )
-
FPD : Considering simple filter push in query block SEL$5DA710D3 ( # 1 )
-
EMP . HIREDATE = EMP1 . HIREDATE AND EMP1 . DEPTNO = 20
-
try to generate transitive predicate from check constraints for query block SEL$5DA710D3 ( # 1 )
-
finally : EMP . HIREDATE = EMP1 . HIREDATE AND EMP1 . DEPTNO = 20 finally关键字,复杂的子查询会有多个finally,因为是针对query block优化的
-
-
Final query after transformations : * * * * * * * UNPARSED QUERY IS * * * * * * *
-
SELECT EMP . EMPNO EMPNO , EMP . ENAME ENAME , EMP . JOB JOB , EMP . MGR MGR , EMP . HIREDATE HIREDATE , EMP . SAL SAL , EMP . COMM COMM , EMP . DEPTNO DEPTNO FROM SCOTT . EMP1 EMP1 , SCOTT . EMP EMP WHERE EMP . HIREDATE = EMP1 . HIREDATE AND EMP1 . DEPTNO = 20
-
kkoqbc : optimizing query block SEL$5DA710D3 ( # 1 )
-
-
:
-
call ( in - use = 3952 , alloc = 16344 ) , compile ( in - use = 127176 , alloc = 135984 ) , execution ( in - use = 3752 , alloc = 4032 )
-
-
kkoqbc - subheap ( create addr = 0x2b02873bfaa0 )
-
* * * * * * * * * * * * * * * *
-
QUERY BLOCK TEXT
-
* * * * * * * * * * * * * * * *
-
select / * + ^ ^ ^ ^ * / * from emp where hiredate = any ( select hiredate from emp1 where deptno = 20 )
-
- - - - - - - - - - - - - - - - - - - - -
-
QUERY BLOCK SIGNATURE
-
- - - - - - - - - - - - - - - - - - - - -
-
signature ( optimizer ) : qb_name = SEL$5DA710D3 nbfros = 2 flg =
-
fro ( ) : flg = 0 objn = 75335 hint_alias = "EMP" @ "SEL$1"
-
fro ( 1 ) : flg = 0 objn = 78917 hint_alias = "EMP1" @ "SEL$2"
-
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
SYSTEM STATISTICS INFORMATION
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
Using NOWORKLOAD Stats
-
CPUSPEEDNW : 1752 millions instructions/sec ( default is 100 )
-
IOTFRSPEED : 4096 bytes per millisecond ( default is 4096 )
-
IOSEEKTIM : 10 milliseconds ( default is 10 )
-
MBRC : NO VALUE blocks ( default is 8 )
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
BASE STATISTICAL INFORMATION
-
* * * * * * * * * * * * * * * * * * * * * * *
-
Table Stats : :
-
Table : EMP Alias : EMP
-
#Rows : 14 #Blks : 5 AvgRowLen : 38 . 00 ChainCnt : 0 . 00
-
Column ( # 5 ) : HIREDATE (
-
AvgLen : 8 NDV : 13 Nulls : 0 Density : 0 . 076923 Min : 2444591 Max : 2446939
-
Index Stats : :
-
Index : PK_EMP Col# : 1
-
LVLS : 0 #LB : 1 #DK : 14 LB/K : 1 . 00 DB/K : 1 . 00 CLUF : 1 . 00
-
* * * * * * * * * * * * * * * * * * * * * * *
-
Table Stats : :
-
Table : EMP1 Alias : EMP1 ( NOT ANALYZED )
-
#Rows : 327 #Blks : 4 AvgRowLen : 100 . 00 ChainCnt : 0 . 00
-
Column ( # 5 ) : HIREDATE ( NO STATISTICS ( using defaults )
-
AvgLen : 9 NDV : 10 Nulls : 0 Density : 0 . 097859
-
Access path analysis for EMP1
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
SINGLE TABLE ACCESS PATH
-
Single Table Cardinality Estimation for EMP1 [ EMP1 ]
-
-
* * * 2015 - 02 - 27 01 : 51 : 40 . 125
-
* * Performing dynamic sampling initial checks . * *
-
Column ( # 8 ) : DEPTNO ( NO STATISTICS ( using defaults )
-
AvgLen : 13 NDV : 10 Nulls : 0 Density : 0 . 097859
-
* * Dynamic sampling initial checks returning TRUE ( level = 2 ) .
-
* * Dynamic sampling updated table stats . : blocks = 4
-
-
* * * 2015 - 02 - 27 01 : 51 : 40 . 126
-
* * Generated dynamic sampling query :
-
query text :
-
SELECT / * OPT_DYN_SAMP * / / * + ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL ( SAMPLESUB ) opt_param ( 'parallel_execution_enabled' , 'false' ) NO_PARALLEL_INDEX ( SAMPLESUB ) NO_SQL_TUNE * / NVL ( SUM ( C1 ) , ) , NVL ( SUM ( C2 ) , ) , COUNT ( DISTINCT C3 ) , NVL ( SUM ( CASE WHEN C3 IS NULL THEN 1 ELSE 0 END ) , ) FROM ( SELECT / * + IGNORE_WHERE_CLAUSE NO_PARALLEL ( "EMP1" ) FULL ( "EMP1" ) NO_PARALLEL_INDEX ( "EMP1" ) * / 1 AS C1 , CASE WHEN EMP1 . DEPTNO = 20 THEN 1 ELSE 0 END AS C2 , "EMP1" . "HIREDATE" AS C3 FROM "SCOTT" . "EMP1" "EMP1" ) SAMPLESUB
-
-
* * * 2015 - 02 - 27 01 : 51 : 40 . 127
-
* * Executed dynamic sampling query :
-
level : 2
-
sample pct . : 100 . 000000
-
actual sample size : 14
-
filtered sample card . : 5
-
orig . card . : 327
-
block cnt . table stat . : 4
-
block cnt . for sampling : 4
-
max . sample block cnt . : 64
-
sample block cnt . : 4
-
ndv C3 : 13
-
scaled : 13 . 00
-
nulls C4 : 0
-
scaled : 0 . 00
-
min . sel . est . : 0 . 01000000
-
* * Dynamic sampling col . stats . :
-
Column ( # 5 ) : HIREDATE ( Part# : 0
-
AvgLen : 7 NDV : 13 Nulls : 0 Density : 0 . 076923
-
* * Using dynamic sampling NULLs estimates .
-
* * Using dynamic sampling NDV estimates .
-
Scaled NDVs using cardinality = 14 .
-
* * Using dynamic sampling card . : 14
-
* * Dynamic sampling updated table card .
-
* * Using single table dynamic sel . est . : 0 . 35714286
-
Table : EMP1 Alias : EMP1
-
Card : Original : 14 . 000000 Rounded : 5 Computed : 5 . 00 Non Adjusted : 5 . 00
-
Access Path : TableScan
-
Cost : 3 . 00 Resp : 3 . 00 Degree : 0
-
Cost_io : 3 . 00 Cost_cpu : 33246
-
Resp_io : 3 . 00 Resp_cpu : 33246
-
Best : : AccessPath : TableScan
-
Cost : 3 . 00 Degree : 1 Resp : 3 . 00 Card : 5 . 00 Bytes : 0
-
-
Access path analysis for EMP
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
SINGLE TABLE ACCESS PATH
-
Single Table Cardinality Estimation for EMP [ EMP ]
-
Table : EMP Alias : EMP
-
Card : Original : 14 . 000000 Rounded : 14 Computed : 14 . 00 Non Adjusted : 14 . 00
-
Access Path : TableScan
-
Cost : 3 . 00 Resp : 3 . 00 Degree : 0
-
Cost_io : 3 . 00 Cost_cpu : 39667
-
Resp_io : 3 . 00 Resp_cpu : 39667
-
Best : : AccessPath : TableScan
-
Cost : 3 . 00 Degree : 1 Resp : 3 . 00 Card : 14 . 00 Bytes : 0
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
-
-
OPTIMIZER STATISTICS AND COMPUTATIONS
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
GENERAL PLANS
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Considering cardinality - based initial join order .
-
Permutations for Starting Table :
-
Join order [ 1 ] : EMP [ EMP ] # 0 EMP1 [ EMP1 ] # 1
-
-
* * * * * * * * * * * * * * *
-
Now joining : EMP1 [ EMP1 ] # 1
-
* * * * * * * * * * * * * * *
-
NL Join
-
Outer table : Card : 14 . 00 Cost : 3 . 00 Resp : 3 . 00 Degree : 1 Bytes : 38
-
Access path analysis for EMP1
-
Inner table : EMP1 Alias : EMP1
-
Access Path : TableScan
-
NL Join : Cost : 20 . 02 Resp : 20 . 02 Degree : 1
-
Cost_io : 20 . 00 Cost_cpu : 505108
-
Resp_io : 20 . 00 Resp_cpu : 505108
-
-
Best NL cost : 20 . 02
-
resc : 20 . 02 resc_io : 20 . 00 resc_cpu : 505108 resc_cpu指的是cpu cost resc_io是IOcost,cost很好关心
-
resp : 20 . 02 resp_io : 20 . 00 resc_cpu : 505108
-
Semi Join Card : 5 . 384615 = outer ( 14 . 000000 ) * sel ( . 384615 )
-
Join Card - Rounded : 5 Computed : 5 . 38
-
Outer table : EMP Alias : EMP
-
resc : 3 . 00 card 14 . 00 bytes : 38 deg : 1 resp : 3 . 00
-
Inner table : EMP1 Alias : EMP1
-
resc : 3 . 00 card : 5 . 00 bytes : 22 deg : 1 resp : 3 . 00
-
using dmeth : 2 #groups : 1
-
SORT ressource Sort statistics
-
Sort width : 334 Area size : 292864 Max Area size : 58720256
-
Degree : 1
-
Blocks to Sort : 1 Row size : 52 Total Rows : 14
-
Initial runs : 1 Merge passes : 0 IO Cost / pass : 0
-
Total IO sort cost : 0 Total CPU sort cost : 21023507
-
Total Temp space used : 0
-
SORT ressource Sort statistics
-
Sort width : 334 Area size : 292864 Max Area size : 58720256
-
Degree : 1
-
Blocks to Sort : 1 Row size : 35 Total Rows : 5
-
Initial runs : 1 Merge passes : 0 IO Cost / pass : 0
-
Total IO sort cost : 0 Total CPU sort cost : 21021629
-
Total Temp space used : 0
-
SM join : Resc : 8 . 00 Resp : 8 . 00 [ multiMatchCost = . 00 ]
-
SM Join
-
SM cost : 8 . 00
-
resc : 8 . 00 resc_io : 6 . 00 resc_cpu : 42118049
-
resp : 8 . 00 resp_io : 6 . 00 resp_cpu : 42118049
-
Outer table : EMP Alias : EMP
-
resc : 3 . 00 card 14 . 00 bytes : 38 deg : 1 resp : 3 . 00
-
Inner table : EMP1 Alias : EMP1
-
resc : 3 . 00 card : 5 . 00 bytes : 22 deg : 1 resp : 3 . 00
-
using dmeth : 2 #groups : 1
-
Cost per ptn : 0 . 50 #ptns : 1
-
hash_area : 124 ( max = 14336 ) buildfrag : 1 probefrag : 1 ppasses : 1
-
Hash join : Resc : 6 . 50 Resp : 6 . 50 [ multiMatchCost = . 00 ]
-
HA Join
-
HA cost : 6 . 50
-
resc : 6 . 50 resc_io : 6 . 00 resc_cpu : 10586066
-
resp : 6 . 50 resp_io : 6 . 00 resp_cpu : 10586066
-
Best : : JoinMethod : HashSemi best
-
Cost : 6 . 50 Degree : 1 Resp : 6 . 50 Card : 5 . 38 Bytes : 60
-
* * * * * * * * * * * * * * * * * * * * * * *
-
Best so far : Table# : 0 cost : 3 . 0019 card : 14 . 0000 bytes : 532
-
Table# : 1 cost : 6 . 5036 card : 5 . 3846 bytes : 300
-
* * * * * * * * * * * * * * * * * * * * * * *
-
Join order [ 2 ] : EMP1 [ EMP1 ] # 1 EMP [ EMP ] #
-
SORT ressource Sort statistics
-
Sort width : 334 Area size : 292864 Max Area size : 58720256
-
Degree : 1
-
Blocks to Sort : 1 Row size : 35 Total Rows : 5
-
Initial runs : 1 Merge passes : 0 IO Cost / pass : 0
-
Total IO sort cost : 0 Total CPU sort cost : 21021629
-
Total Temp space used : 0
-
-
* * * * * * * * * * * * * * *
-
Now joining : EMP [ EMP ] #
-
* * * * * * * * * * * * * * *
-
NL Join
-
Outer table : Card : 5 . 00 Cost : 4 . 00 Resp : 4 . 00 Degree : 1 Bytes : 22
-
Access path analysis for EMP
-
Inner table : EMP Alias : EMP
-
Access Path : TableScan
-
NL Join : Cost : 10 . 01 Resp : 10 . 01 Degree : 1
-
Cost_io : 9 . 00 Cost_cpu : 21173876
-
Resp_io : 9 . 00 Resp_cpu : 21173876
-
-
Best NL cost : 10 . 01
-
resc : 10 . 01 resc_io : 9 . 00 resc_cpu : 21173876
-
resp : 10 . 01 resp_io : 9 . 00 resc_cpu : 21173876
-
Join Card : 5 . 384615 = outer ( 5 . 000000 ) * inner ( 14 . 000000 ) * sel ( . 076923 )
-
Join Card - Rounded : 5 Computed : 5 . 38
-
Outer table : EMP1 Alias : EMP1
-
resc : 4 . 00 card 5 . 00 bytes : 22 deg : 1 resp : 4 . 00
-
Inner table : EMP Alias : EMP
-
resc : 3 . 00 card : 14 . 00 bytes : 38 deg : 1 resp : 3 . 00
-
using dmeth : 2 #groups : 1
-
SORT ressource Sort statistics
-
Sort width : 334 Area size : 292864 Max Area size : 58720256
-
Degree : 1
-
Blocks to Sort : 1 Row size : 35 Total Rows : 3
-
Initial runs : 1 Merge passes : 0 IO Cost / pass : 0
-
Total IO sort cost : 0 Total CPU sort cost : 21021320
-
Total Temp space used : 0
-
SORT ressource Sort statistics
-
Sort width : 334 Area size : 292864 Max Area size : 58720256
-
Degree : 1
-
Blocks to Sort : 1 Row size : 52 Total Rows : 14
-
Initial runs : 1 Merge passes : 0 IO Cost / pass : 0
-
Total IO sort cost : 0 Total CPU sort cost : 21023507
-
Total Temp space used : 0
-
SM join : Resc : 9 . 00 Resp : 9 . 00 [ multiMatchCost = . 00 ]
-
SM Join
-
SM cost : 9 . 00
-
resc : 9 . 00 resc_io : 6 . 00 resc_cpu : 63139368
-
resp : 9 . 00 resp_io : 6 . 00 resp_cpu : 63139368
-
Outer table : EMP1 Alias : EMP1
-
resc : 4 . 00 card 5 . 00 bytes : 22 deg : 1 resp : 4 . 00
-
Inner table : EMP Alias : EMP
-
resc : 3 . 00 card : 14 . 00 bytes : 38 deg : 1 resp : 3 . 00
-
using dmeth : 2 #groups : 1
-
Cost per ptn : 0 . 50 #ptns : 1
-
hash_area : 124 ( max = 14336 ) buildfrag : 1 probefrag : 1 ppasses : 1
-
Hash join : Resc : 7 . 50 Resp : 7 . 50 [ multiMatchCost = . 00 ]
-
HA Join
-
HA cost : 7 . 50
-
resc : 7 . 50 resc_io : 6 . 00 resc_cpu : 31606944
-
resp : 7 . 50 resp_io : 6 . 00 resp_cpu : 31606944
-
Join order aborted : cost > best plan cost
-
* * * * * * * * * * * * * * * * * * * * * * *
-
( newjo - stop - 1 ) k : , spcnt : , perm : 2 , maxperm : 2000
-
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Number of join permutations tried : 2
-
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-
Consider using bloom filter between EMP [ EMP ] and EMP1 [ EMP1 ] with ? ?
-
kkoBloomFilter : join ( lcdn : 14 rcdn : 5 jcdn : 5 limit : 35 )
-
Computing bloom ndv for creator : EMP [ EMP ] ccdn : 14 . 0 and user : EMP1 [ EMP1 ] ucdn : 5 .
-
kkopqComputeBloomNdv : predicate ( bndv : 13 ndv : 13 ) and ( bndv : 13 ndv : 5 )
-
kkopqComputeBloomNdv : pred cnt : 2 ndv : 14 reduction : 3
-
kkoBloomFilter : join ndv : 0 reduction : 2 . 800000 ( limit : . 500000 ) rejected because distinct value ratio
-
Enumerating distribution method ( advanced )
-
- - - Distribution method for
-
join between EMP [ EMP ] ( serial ) and EMP1 [ EMP1 ] ( serial ) ; jm = 2 ; right side access path = TableScan
-
- - - - serial Hash - Join - > NONE
-
-
( newjo - save ) [ 0 1 ]
-
Trying or - Expansion on query block SEL$5DA710D3 ( # 1 )
-
Transfer Optimizer annotations for query block SEL$5DA710D3 ( # 1 )
-
id = 0 frofkksm [ i ] ( sort - merge/hash ) predicate = EMP . HIREDATE = EMP1 . HIREDATE
-
id = 0 frosand ( sort - merge/hash ) predicate = EMP . HIREDATE = EMP1 . HIREDATE
-
id = 0 frofand predicate = EMP1 . DEPTNO = 20
-
Final cost for query block SEL$5DA710D3 ( # 1 ) - All Rows Plan :
-
Best join order : 1
-
Cost : 6 . 5036 Degree : 1 Card : 5 . 0000 Bytes : 300
-
Resc : 6 . 5036 Resc_io : 6 . 0000 Resc_cpu : 10586066
-
Resp : 6 . 5036 Resp_io : 6 . 0000 Resc_cpu : 10586066
-
kkoqbc - subheap ( delete addr = 0x2b02873bfaa0 , in - use = 26480 , alloc = 32840 )
-
kkoqbc - end :
-
:
-
call ( in - use = 27832 , alloc = 82024 ) , compile ( in - use = 130344 , alloc = 135984 ) , execution ( in - use = 4384 , alloc = 8088 )
-
-
kkoqbc : finish optimizing query block SEL$5DA710D3 ( # 1 )
-
apadrv - end
-
:
-
call ( in - use = 27832 , alloc = 82024 ) , compile ( in - use = 131424 , alloc = 135984 ) , execution ( in - use = 4384 , alloc = 8088 )
-
-
-
Starting SQL statement dump
-
-
user_id = 83 user_name = SCOTT module = SQL * Plus action =
-
sql_id = 0rbab21pk95av plan_hash_value = 1992738195 problem_type = 3
-
- - - - - Current SQL Statement for this session ( sql_id = 0rbab21pk95av ) - - - - -
-
select / * + ^ ^ ^ ^ * / * from emp where hiredate = any ( select hiredate from emp1 where deptno = 20 )
-
sql_text_length = 94
-
sql = select / * + ^ ^ ^ ^ * / * from emp where hiredate = any ( select hiredate from emp1 where deptno = 20 )
-
- - - - - Explain Plan Dump - - - - -
-
- - - - - Plan Table - - - - -
-
-
= = = = = = = = = = = =
-
Plan Table
-
= = = = = = = = = = = =
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
-
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
-
| 0 | SELECT STATEMENT | | | | 7 | |
-
| 1 | HASH JOIN SEMI | | 5 | 300 | 7 | 00 : 00 : 01 |
-
| 2 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 | 00 : 00 : 01 |
-
| 3 | TABLE ACCESS FULL | EMP1 | 5 | 110 | 3 | 00 : 00 : 01 |
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
-
Predicate Information :
-
- - - - - - - - - - - - - - - - - - - - - -
-
1 - access ( "HIREDATE" = "HIREDATE" )
-
3 - filter ( "DEPTNO\" = 20 )
-
-
Content of other_xml column
-
= = = = = = = = = = = = = = = = = = = = = = = = = = =
-
db_version : 11 . 2 . . 3
-
parse_schema : SCOTT
-
dynamic_sampling : 2
-
plan_hash : 1992738195
-
plan_hash_2 : 2144276133
-
Outline Data :
-
/ * +
-
BEGIN_OUTLINE_DATA
-
IGNORE_OPTIM_EMBEDDED_HINTS
-
OPTIMIZER_FEATURES_ENABLE ( '11.2.0.3' )
-
DB_VERSION ( '11.2.0.3' )
-
ALL_ROWS
-
OUTLINE_LEAF ( @ "SEL$5DA710D3" )
-
UNNEST ( @ "SEL$2" )
-
OUTLINE ( @ "SEL$1" )
-
OUTLINE ( @ "SEL$2" )
-
FULL ( @ "SEL$5DA710D3" \ "EMP\" @ \ "SEL$1" )
-
FULL ( @ "SEL$5DA710D3" "EMP1" @ "SEL$2" )
-
LEADING ( @ "SEL$5DA710D3" "EMP" @ "SEL$1" "EMP1" @ "SEL$2" )
-
USE_HASH ( @ "SEL$5DA710D3" "EMP1" @ "SEL$2" )
-
END_OUTLINE_DATA
-
* /
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29990276/viewspace-1442948/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29990276/viewspace-1442948/