Transformation之NUll-aware Anti-join（NAAJ）【九】
只用在anti join not in not exists 這種 deptno在emp這張表上有空值的情況，或者可以為空的情況
這樣查不出來。因為emp表
ANTI NA這種join會考慮到空值的這種情況。這種transform在資料倉儲的系統中有極其大的作用
點選( 此處)摺疊或開啟
-
*
*
* 2015
-02
-28 07
:39
:41
.562
-
*
*
* SESSION ID
:
(59
.3553
) 2015
-02
-28 07
:39
:41
.562
-
*
*
* CLIENT ID
:
(
) 2015
-02
-28 07
:39
:41
.562
-
*
*
* SERVICE
NAME
:
(SYS$USERS
) 2015
-02
-28 07
:39
:41
.562
-
*
*
* MODULE
NAME
:
(SQL
*Plus
) 2015
-02
-28 07
:39
:41
.562
-
*
*
* ACTION
NAME
:
(
) 2015
-02
-28 07
:39
:41
.562
-
-
Registered qb
: SEL$1 0x2ed166d0
(PARSER
)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
QUERY BLOCK SIGNATURE
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
signature
(
)
: qb_name
=SEL$1 nbfros
=1 flg
=0
-
fro
(0
)
: flg
=4 objn
=75333 hint_alias
=
\
"DEPT\"
@
\
"SEL$1\"
-
-
Registered qb
: SEL$2 0x2ed114a0
(PARSER
)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
QUERY BLOCK SIGNATURE
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
signature
(
)
: qb_name
=SEL$2 nbfros
=1 flg
=0
-
fro
(0
)
: flg
=4 objn
=75335 hint_alias
=
\
"EMP\"
@
\
"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
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Predicate Move
-Around
(PM
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
OPTIMIZER INFORMATION
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
-
-
-
-
- Current SQL Statement
for
this session
(sql_id
=296c8ug3jwdcz
)
-
-
-
-
-
-
select
* from dept where deptno
not
in
(select deptno from emp
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
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
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Compilation Environment Dump
-
Bug Fix Control Environment
-
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
PARAMETERS WITH DEFAULT VALUES
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
PARAMETERS
IN OPT_PARAM HINT
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Column Usage Monitoring is ON
: tracking level
= 1
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
-
Considering Query Transformations on query block SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Query transformations
(QT
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
JF
: Checking validity
of join factorization
for query block SEL$2
(
#0
)
-
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
(
#0
)
-
TE
: Bypassed
: No partitioned table
in query block
.
-
CBQT
: Validity checks passed
for 296c8ug3jwdcz
.
-
CSE
: Considering common
sub
-expression elimination
in query block SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Common Subexpression elimination
(CSE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: Considering common
sub
-expression elimination
in query block SEL$2
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Common Subexpression elimination
(CSE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: CSE
not performed on query block SEL$2
(
#0
)
.
-
CSE
: CSE
not performed on query block SEL$1
(
#0
)
.
-
OBYE
: Considering Order
-by Elimination from view SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Order
-by elimination
(OBYE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
OBYE
: OBYE bypassed
: no order by to eliminate
.
-
OJE
: Begin
: find best directive
for query block SEL$1
(
#0
)
-
OJE
: End
: finding best directive
for query block SEL$1
(
#0
)
-
OJE
: Begin
: find best directive
for query block SEL$2
(
#0
)
-
OJE
: End
: finding best directive
for query block SEL$2
(
#0
)
-
query block SEL$1
(
#0
) unchanged
-
Considering Query Transformations on query block SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Query transformations
(QT
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: Considering common
sub
-expression elimination
in query block SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Common Subexpression elimination
(CSE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: Considering common
sub
-expression elimination
in query block SEL$2
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Common Subexpression elimination
(CSE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: CSE
not performed on query block SEL$2
(
#0
)
.
-
CSE
: CSE
not performed on query block SEL$1
(
#0
)
.
-
query block SEL$1
(
#0
) unchanged
-
apadrv
-start sqlid
=2637289499962258847
-
:
-
call
(
in
-use
=1760
, alloc
=16344
)
, compile
(
in
-use
=61552
, alloc
=62688
)
, execution
(
in
-use
=3456
, alloc
=4032
)
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Peeked values
of the binds
in SQL statement
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
-
CBQT
: Considering cost
-based transformation on query block SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
COST
-BASED QUERY TRANSFORMATIONS
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
FPD
: Considering simple filter
push
(pre rewrite
)
in query block SEL$2
(
#0
)
-
FPD
: Current where clause predicates
?
?
-
-
FPD
: Considering simple filter
push
(pre rewrite
)
in query block SEL$1
(
#0
)
-
FPD
: Current where clause predicates
DEPT
.
DEPTNO
<
>ALL
(SELECT
EMP
.
DEPTNO FROM
EMP
EMP
)
-
-
OBYE
: Considering Order
-by Elimination from view SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Order
-by elimination
(OBYE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
OBYE
: OBYE bypassed
: no order by to eliminate
.
-
Considering Query Transformations on query block SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Query transformations
(QT
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: Considering common
sub
-expression elimination
in query block SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Common Subexpression elimination
(CSE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: Considering common
sub
-expression elimination
in query block SEL$2
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Common Subexpression elimination
(CSE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: CSE
not performed on query block SEL$2
(
#0
)
.
-
CSE
: CSE
not performed on query block SEL$1
(
#0
)
.
-
kkqctdrvTD
-start on query block SEL$1
(
#0
)
-
kkqctdrvTD
-start
:
:
-
call
(
in
-use
=1760
, alloc
=16344
)
, compile
(
in
-use
=104000
, alloc
=108064
)
, execution
(
in
-use
=3456
, alloc
=4032
)
-
-
Registered qb
: SEL$1 0x2ed90f98
(COPY SEL$1
)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
QUERY BLOCK SIGNATURE
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
signature
(
)
: NULL
-
Registered qb
: SEL$2 0x2ed91878
(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
: Transform ALL subquery to a null
-aware antijoin
.
-
SU
: Checking validity
of unnesting subquery SEL$2
(
#3
)
-
SU
: Validity checks failed
.
-
Registered qb
: SEL$5DA710D3 0x2ed90f98
(SUBQUERY UNNEST SEL$1
; SEL$2
)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
QUERY BLOCK SIGNATURE
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
signature
(
)
: qb_name
=SEL$5DA710D3 nbfros
=2 flg
=0
-
fro
(0
)
: flg
=0 objn
=75333 hint_alias
=
"DEPT"
@
"SEL$1"
-
fro
(1
)
: flg
=0 objn
=75335 hint_alias
=
"EMP"
@
"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
)
-
OJE
: Begin
: find best directive
for query block SEL$2
(
#3
)
-
OJE
: End
: finding best directive
for query block SEL$2
(
#3
)
-
kkqctdrvTD
-cleanup
: transform
(
in
-use
=5224
, alloc
=8392
)
:
-
call
(
in
-use
=2864
, alloc
=16344
)
, compile
(
in
-use
=126952
, alloc
=131752
)
, execution
(
in
-use
=3456
, alloc
=4032
)
-
-
kkqctdrvTD
-end
:
-
call
(
in
-use
=2864
, alloc
=16344
)
, compile
(
in
-use
=118736
, alloc
=131752
)
, execution
(
in
-use
=3456
, alloc
=4032
)
-
-
Subquery removal
for query block SEL$2
(
#2
)
-
RSW
:
Not valid
for subquery removal SEL$2
(
#2
)
-
Subquery unchanged
.
-
SU
: Transform ALL subquery to a null
-aware antijoin
.
-
SJC
: Considering set
-join conversion
in query block SEL$5DA710D3
(
#1
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Set
-Join Conversion
(SJC
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
SJC
: Considering set
-join conversion
in query block SEL$2
(
#4
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Set
-Join Conversion
(SJC
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
SJC
:
not performed
-
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
)
-
OJE
: Begin
: find best directive
for query block SEL$2
(
#4
)
-
OJE
: End
: finding best directive
for query block SEL$2
(
#4
)
-
JE
: Considering Join Elimination on query block SEL$5DA710D3
(
#1
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Join Elimination
(
JE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
SQL
:
*
*
*
*
*
*
* UNPARSED QUERY IS
*
*
*
*
*
*
*
-
SELECT
DEPT
.
DEPTNO
DEPTNO
,
DEPT
.
DNAME
DNAME
,
DEPT
.
LOC
LOC FROM
EMP
EMP
,
DEPT
DEPT WHERE
DEPT
.
DEPTNO
=
EMP
.
DEPTNO
AND
NOT EXISTS
(SELECT 0 FROM
EMP
EMP WHERE
EMP
.
DEPTNO IS NULL
)
-
JE
: cfro
: EMP objn
:75333 col#
:8 dfro
:DEPT dcol#
:1
-
JE
: cfro
: EMP objn
:75333 col#
:8 dfro
:DEPT dcol#
:1
-
JE
: cfro
: EMP objn
:75333 col#
:8 dfro
:DEPT dcol#
:1
-
JE
: cfro
: EMP objn
:75333 col#
:8 dfro
:DEPT dcol#
:1
-
SQL
:
*
*
*
*
*
*
* UNPARSED QUERY IS
*
*
*
*
*
*
*
-
SELECT
DEPT
.
DEPTNO
DEPTNO
,
DEPT
.
DNAME
DNAME
,
DEPT
.
LOC
LOC FROM
EMP
EMP
,
DEPT
DEPT WHERE
DEPT
.
DEPTNO
=
EMP
.
DEPTNO
AND
NOT EXISTS
(SELECT 0 FROM
EMP
EMP WHERE
EMP
.
DEPTNO IS NULL
)
-
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
=4440
, alloc
=16344
)
, compile
(
in
-use
=130192
, alloc
=136000
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-cleanup
: transform
(
in
-use
=0
, alloc
=0
)
:
-
call
(
in
-use
=4440
, alloc
=16344
)
, compile
(
in
-use
=130936
, alloc
=136000
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-end
:
-
call
(
in
-use
=4440
, alloc
=16344
)
, compile
(
in
-use
=131336
, alloc
=136000
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-start on query block SEL$5DA710D3
(
#1
)
-
kkqctdrvTD
-start
:
:
-
call
(
in
-use
=4440
, alloc
=16344
)
, compile
(
in
-use
=131336
, alloc
=136000
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-cleanup
: transform
(
in
-use
=0
, alloc
=0
)
:
-
call
(
in
-use
=4440
, alloc
=16344
)
, compile
(
in
-use
=132024
, alloc
=136000
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-end
:
-
call
(
in
-use
=4440
, alloc
=16344
)
, compile
(
in
-use
=132424
, alloc
=136000
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-start on query block SEL$5DA710D3
(
#1
)
-
kkqctdrvTD
-start
:
:
-
call
(
in
-use
=4440
, alloc
=16344
)
, compile
(
in
-use
=132424
, alloc
=136000
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
TE
: Checking validity
of table expansion
for query block SEL$2
(
#2
)
-
TE
: Bypassed
: No partitioned table
in query block
.
-
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
=0
, alloc
=0
)
:
-
call
(
in
-use
=4440
, alloc
=16344
)
, compile
(
in
-use
=133112
, alloc
=136000
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-end
:
-
call
(
in
-use
=4440
, alloc
=16344
)
, compile
(
in
-use
=133512
, alloc
=136000
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
TE
: Checking validity
of table expansion
for query block SEL$2
(
#2
)
-
TE
: Bypassed
: No partitioned table
in query block
.
-
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 \
"DEPT\"
.
\
"DEPTNO\" \
"DEPTNO\"
,
\
"DEPT\"
.
\
"DNAME\" \
"DNAME\"
,
\
"DEPT\"
.
\
"LOC\" \
"LOC\" FROM \
"SCOTT\"
.
\
"EMP\" \
"EMP\"
,
\
"SCOTT\"
.
\
"DEPT\" \
"DEPT\" WHERE \
"DEPT\"
.
\
"DEPTNO\"
=
\
"EMP\"
.
\
"DEPTNO\"
AND
NOT EXISTS
(SELECT 0 FROM \
"SCOTT\"
.
\
"EMP\" \
"EMP\" WHERE \
"EMP\"
.
\
"DEPTNO\" IS NULL
)
-
ST
:
not valid since star transformation parameter is FALSE
-
ST
:
not valid since star transformation parameter is FALSE
-
kkqctdrvTD
-start on query block SEL$5DA710D3
(
#1
)
-
kkqctdrvTD
-start
:
:
-
call
(
in
-use
=4584
, alloc
=16344
)
, compile
(
in
-use
=133512
, alloc
=136000
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
JF
: Checking validity
of join factorization
for query block SEL$2
(
#2
)
-
JF
: Bypassed
:
not a
UNION
or
UNION
-ALL query block
.
-
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
=0
, alloc
=0
)
:
-
call
(
in
-use
=4584
, alloc
=16344
)
, compile
(
in
-use
=134224
, alloc
=136000
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-end
:
-
call
(
in
-use
=4584
, alloc
=16344
)
, compile
(
in
-use
=134624
, alloc
=136000
)
, execution
(
in
-use
=3624
, 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
=4584
, alloc
=16344
)
, compile
(
in
-use
=134624
, alloc
=136000
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-cleanup
: transform
(
in
-use
=0
, alloc
=0
)
:
-
call
(
in
-use
=4584
, alloc
=16344
)
, compile
(
in
-use
=135336
, alloc
=136000
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-end
:
-
call
(
in
-use
=4584
, alloc
=16344
)
, compile
(
in
-use
=135752
, alloc
=136000
)
, execution
(
in
-use
=3624
, 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
)
-
\
"DEPT\"
.
\
"DEPTNO\"
=
\
"EMP\"
.
\
"DEPTNO\"
AND
NOT EXISTS
(SELECT 0 FROM \
"EMP\" \
"EMP\"
)
-
FPD
: Considering simple filter
push
in query block SEL$2
(
#2
)
-
\
"EMP\"
.
\
"DEPTNO\" IS NULL
-
try to generate transitive predicate from check constraints
for query block SEL$2
(
#2
)
-
finally
: \
"EMP\"
.
\
"DEPTNO\" IS NULL
-
-
try to generate transitive predicate from check constraints
for query block SEL$5DA710D3
(
#1
)
-
finally
:
DEPT
.
DEPTNO
=
EMP
.
DEPTNO
AND
NOT EXISTS
(SELECT 0 FROM
EMP
EMP
)
-
-
Final query after transformations
:
*
*
*
*
*
*
* UNPARSED QUERY IS
*
*
*
*
*
*
*
-
SELECT
DEPT
.
DEPTNO
DEPTNO
,
DEPT
.
DNAME
DNAME
,
DEPT
.
LOC
LOC
FROM
EMP
EMP
,
DEPT
DEPT
WHERE
DEPT
.
DEPTNO
=
EMP
.
DEPTNO
AND
NOT
EXISTS
(
SELECT 0 FROM
SCOTT
.
EMP
EMP
WHERE
EMP
.
DEPTNO
IS NULL
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
First K Rows
: Setup begin
-
kkoqbc
: optimizing query block SEL$2
(
#2
)
-
-
:
-
call
(
in
-use
=4920
, alloc
=16344
)
, compile
(
in
-use
=141688
, alloc
=144288
)
, execution
(
in
-use
=3704
, alloc
=4032
)
-
-
kkoqbc
-subheap
(create
addr
=0x2b692ed1faa0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
QUERY BLOCK TEXT
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
select deptno from emp
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
QUERY BLOCK SIGNATURE
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
signature
(optimizer
)
: qb_name
=SEL$2 nbfros
=1 flg
=0
-
fro
(0
)
: flg
=0 objn
=75335 hint_alias
=
\
"EMP\"
@
\
"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
-
Index Stats
:
:
-
Index
: PK_EMP Col#
: 1
-
LVLS
: 0 #LB
: 1 #DK
: 14 LB/K
: 1
.00 DB/K
: 1
.00 CLUF
: 1
.00
-
Access path analysis
for EMP
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
SINGLE TABLE ACCESS PATH
-
Single Table Cardinality Estimation
for EMP
[EMP
]
-
Column
(
#8
)
: DEPTNO
(
-
AvgLen
: 3 NDV
: 3 Nulls
: 0 Density
: 0
.333333 Min
: 10 Max
: 30
-
Table
: EMP Alias
: EMP
-
Card
: Original
: 14
.000000 Rounded
: 1 Computed
: 0
.00 Non Adjusted
: 0
.00
-
Access Path
: TableScan
-
Cost
: 3
.00 Resp
: 3
.00 Degree
: 0
-
Cost_io
: 3
.00 Cost_cpu
: 39947
-
Resp_io
: 3
.00 Resp_cpu
: 39947
-
Best
:
: AccessPath
: TableScan
-
Cost
: 3
.00 Degree
: 1 Resp
: 3
.00 Card
: 0
.00 Bytes
: 0
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
-
-
OPTIMIZER STATISTICS
AND COMPUTATIONS
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
GENERAL PLANS
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Considering cardinality
-based initial join order
.
-
Permutations
for Starting Table
:0
-
Join order
[1
]
: EMP
[EMP
]
#0
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Best so far
: Table#
: 0 cost
: 3
.0019 card
: 0
.0000 bytes
: 3
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Number
of join permutations tried
: 1
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Enumerating distribution method
(advanced
)
-
-
Or
-Expansion validity checks failed on query block SEL$2
(
#2
) because NO_EXPAND hint
-
Transfer Optimizer annotations
for query block SEL$2
(
#2
)
-
Final cost
for query block SEL$2
(
#2
)
- All Rows Plan
:
-
Best join order
: 1
-
Cost
: 3
.0019 Degree
: 1 Card
: 1
.0000 Bytes
: 3
-
Resc
: 3
.0019 Resc_io
: 3
.0000 Resc_cpu
: 39947
-
Resp
: 3
.0019 Resp_io
: 3
.0000 Resc_cpu
: 39947
-
kkoqbc
-subheap
(delete
addr
=0x2b692ed1faa0
,
in
-use
=13504
, alloc
=16408
)
-
kkoqbc
-end
:
-
:
-
call
(
in
-use
=13504
, alloc
=32816
)
, compile
(
in
-use
=142192
, alloc
=144288
)
, execution
(
in
-use
=3704
, alloc
=4032
)
-
-
kkoqbc
: finish optimizing query block SEL$2
(
#2
)
-
kkoqbc
: optimizing query block SEL$5DA710D3
(
#1
)
-
-
:
-
call
(
in
-use
=11264
, alloc
=32816
)
, compile
(
in
-use
=148104
, alloc
=148432
)
, execution
(
in
-use
=3864
, alloc
=4032
)
-
-
kkoqbc
-subheap
(create
addr
=0x2b692ed1faa0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
QUERY BLOCK TEXT
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
select
* from dept where deptno
not
in
(select deptno from emp
)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
QUERY BLOCK SIGNATURE
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
signature
(optimizer
)
: qb_name
=SEL$5DA710D3 nbfros
=2 flg
=0
-
fro
(0
)
: flg
=0 objn
=75333 hint_alias
=
\
"DEPT\"
@
\
"SEL$1\"
-
fro
(1
)
: flg
=0 objn
=75335 hint_alias
=
\
"EMP\"
@
\
"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
: DEPT Alias
: DEPT
-
#Rows
: 4 #Blks
: 5 AvgRowLen
: 20
.00 ChainCnt
: 0
.00
-
Column
(
#1
)
: DEPTNO
(
-
AvgLen
: 3 NDV
: 4 Nulls
: 0 Density
: 0
.250000 Min
: 10 Max
: 40
-
Index Stats
:
:
-
Index
: PK_DEPT Col#
: 1
-
LVLS
: 0 #LB
: 1 #DK
: 4 LB/K
: 1
.00 DB/K
: 1
.00 CLUF
: 1
.00
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Table Stats
:
:
-
Table
: EMP Alias
: EMP
-
#Rows
: 14 #Blks
: 5 AvgRowLen
: 38
.00 ChainCnt
: 0
.00
-
Column
(
#8
)
: DEPTNO
(
-
AvgLen
: 3 NDV
: 3 Nulls
: 0 Density
: 0
.333333 Min
: 10 Max
: 30
-
Index Stats
:
:
-
Index
: PK_EMP Col#
: 1
-
LVLS
: 0 #LB
: 1 #DK
: 14 LB/K
: 1
.00 DB/K
: 1
.00 CLUF
: 1
.00
-
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
-
-
Access path analysis
for DEPT
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
SINGLE TABLE ACCESS PATH
-
Single Table Cardinality Estimation
for DEPT
[DEPT
]
-
Table
: DEPT Alias
: DEPT
-
Card
: Original
: 4
.000000 Rounded
: 4 Computed
: 4
.00 Non Adjusted
: 4
.00
-
Access Path
: TableScan
-
Cost
: 3
.00 Resp
: 3
.00 Degree
: 0
-
Cost_io
: 3
.00 Cost_cpu
: 36367
-
Resp_io
: 3
.00 Resp_cpu
: 36367
-
Best
:
: AccessPath
: TableScan
-
Cost
: 3
.00 Degree
: 1 Resp
: 3
.00 Card
: 4
.00 Bytes
: 0
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
-
-
OPTIMIZER STATISTICS
AND COMPUTATIONS
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
GENERAL PLANS
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Considering cardinality
-based initial join order
.
-
Permutations
for Starting Table
:0
-
Join order
[1
]
: DEPT
[DEPT
]
#0 EMP
[EMP
]
#1
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Now joining
: EMP
[EMP
]
#1
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
NL Join
-
Outer table
: Card
: 4
.00 Cost
: 3
.00 Resp
: 3
.00 Degree
: 1 Bytes
: 20
-
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
: 10
.00 Cost_cpu
: 195036
-
Resp_io
: 10
.00 Resp_cpu
: 195036
-
-
Best NL cost
: 10
.01
-
resc
: 10
.01 resc_io
: 10
.00 resc_cpu
: 195036
-
resp
: 10
.01 resp_io
: 10
.00 resc_cpu
: 195036
-
>
>
> adjusting AJ/SJ sel based on min/max ranges
: jsel
=min
(0
.750000
, 0
.666667
)
-
Anti Join Card
: 1
.333333
= outer
(4
.000000
)
*
(1
- sel
(0
.666667
)
)
-
Join Card
- Rounded
: 1 Computed
: 1
.33
-
Outer table
: DEPT Alias
: DEPT
-
resc
: 3
.00 card 4
.00 bytes
: 20 deg
: 1 resp
: 3
.00
-
Inner table
: EMP Alias
: EMP
-
resc
: 3
.00 card
: 14
.00 bytes
: 3 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
: 32 Total Rows
: 4
-
Initial runs
: 1 Merge passes
: 0 IO Cost / pass
: 0
-
Total IO sort cost
: 0 Total CPU sort cost
: 21021466
-
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
: 14 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
: 8
.00 Resp
: 8
.00
[multiMatchCost
=0
.00
]
-
SM Join
-
SM cost
: 8
.00
-
resc
: 8
.00 resc_io
: 6
.00 resc_cpu
: 42121007
-
resp
: 8
.00 resp_io
: 6
.00 resp_cpu
: 42121007
-
SM Join
(with index on outer
)
-
Access Path
: index
(FullScan
)
-
Index
: PK_DEPT
-
resc_io
: 2
.00 resc_cpu
: 15803
-
ix_sel
: 1
.000000 ix_sel_with_filters
: 1
.000000
-
Cost
: 2
.00 Resp
: 2
.00 Degree
: 1
-
Outer table
: DEPT Alias
: DEPT
-
resc
: 2
.00 card 4
.00 bytes
: 20 deg
: 1 resp
: 2
.00
-
Inner table
: EMP Alias
: EMP
-
resc
: 3
.00 card
: 14
.00 bytes
: 3 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
: 14 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
: 6
.00 Resp
: 6
.00
[multiMatchCost
=0
.00
]
-
Outer table
: DEPT Alias
: DEPT
-
resc
: 3
.00 card 4
.00 bytes
: 20 deg
: 1 resp
: 3
.00
-
Inner table
: EMP Alias
: EMP
-
resc
: 3
.00 card
: 14
.00 bytes
: 3 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
=0
.00
]
-
HA Join
-
HA cost
: 6
.50
-
resc
: 6
.50 resc_io
: 6
.00 resc_cpu
: 10588587
-
resp
: 6
.50 resp_io
: 6
.00 resp_cpu
: 10588587
-
Best
:
: JoinMethod
: SortMergeNullAwareAnti
-
Cost
: 6
.00 Degree
: 1 Resp
: 6
.00 Card
: 1
.33 Bytes
: 23
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Best so far
: Table#
: 0 cost
: 2
.0008 card
: 4
.0000 bytes
: 80
-
Table#
: 1 cost
: 6
.0028 card
: 1
.3333 bytes
: 23
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Number
of join permutations tried
: 1
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Consider using bloom filter between DEPT
[DEPT
]
and EMP
[EMP
] with
?
?
-
kkoBloomFilter
: join ndv
:0 reduction
:1
.000000
(limit
:0
.500000
) rejected because no single
-tables predicates
-
Enumerating distribution method
(advanced
)
-
-
-
- Distribution method
for
-
join between DEPT
[DEPT
]
(serial
)
and EMP
[EMP
]
(serial
)
; jm
= 10
; right side access path
= TableScan
-
-
-
-
- serial Sort
-Merge
-Join
-
> NONE
-
-
Final adjusted join cardinality
: 1
, sq
. fil
. factor
: 20
.000000
-
(newjo
-save
)
[0 1
]
-
Or
-Expansion validity checks failed on query block SEL$5DA710D3
(
#1
) because NO_EXPAND hint
-
Transfer Optimizer annotations
for query block SEL$5DA710D3
(
#1
)
-
Final cost
for query block SEL$5DA710D3
(
#1
)
- All Rows Plan
:
-
Best join order
: 1
-
Cost
: 6
.0028 Degree
: 1 Card
: 1
.0000 Bytes
: 23
-
Resc
: 6
.0028 Resc_io
: 5
.0000 Resc_cpu
: 21078977
-
Resp
: 6
.0028 Resp_io
: 5
.0000 Resc_cpu
: 21078977
-
kkoqbc
-subheap
(delete
addr
=0x2b692ed1faa0
,
in
-use
=24856
, alloc
=32840
)
-
kkoqbc
-end
:
-
:
-
call
(
in
-use
=25128
, alloc
=82128
)
, compile
(
in
-use
=149096
, alloc
=152576
)
, execution
(
in
-use
=3864
, alloc
=4032
)
-
-
kkoqbc
: finish optimizing query block SEL$5DA710D3
(
#1
)
-
First K Rows
: Setup end
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
kkoqbc
: optimizing query block SEL$2
(
#2
)
-
-
:
-
call
(
in
-use
=22920
, alloc
=82128
)
, compile
(
in
-use
=149224
, alloc
=152576
)
, execution
(
in
-use
=3864
, alloc
=4032
)
-
-
kkoqbc
-subheap
(create
addr
=0x2b692ed1faa0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
QUERY BLOCK TEXT
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
select deptno from emp
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
QUERY BLOCK SIGNATURE
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
signature
(optimizer
)
: qb_name
=SEL$2 nbfros
=1 flg
=0
-
fro
(0
)
: flg
=0 objn
=75335 hint_alias
=
\
"EMP\"
@
\
"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
(
#8
)
: DEPTNO
(
-
AvgLen
: 3 NDV
: 3 Nulls
: 0 Density
: 0
.333333 Min
: 10 Max
: 30
-
Index Stats
:
:
-
Index
: PK_EMP Col#
: 1
-
LVLS
: 0 #LB
: 1 #DK
: 14 LB/K
: 1
.00 DB/K
: 1
.00 CLUF
: 1
.00
-
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
: 1 Computed
: 0
.00 Non Adjusted
: 0
.00
-
Access Path
: TableScan
-
Cost
: 3
.00 Resp
: 3
.00 Degree
: 0
-
Cost_io
: 3
.00 Cost_cpu
: 39947
-
Resp_io
: 3
.00 Resp_cpu
: 39947
-
Best
:
: AccessPath
: TableScan
-
Cost
: 3
.00 Degree
: 1 Resp
: 3
.00 Card
: 0
.00 Bytes
: 0
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
-
-
OPTIMIZER STATISTICS
AND COMPUTATIONS
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
GENERAL PLANS
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Considering cardinality
-based initial join order
.
-
Permutations
for Starting Table
:0
-
Join order
[1
]
: EMP
[EMP
]
#0
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Best so far
: Table#
: 0 cost
: 3
.0019 card
: 0
.0000 bytes
: 3
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
(newjo
-stop
-1
) k
:0
, spcnt
:0
, perm
:1
, maxperm
:2000
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Number
of join permutations tried
: 1
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Enumerating distribution method
(advanced
)
-
-
Trying
or
-Expansion on query block SEL$2
(
#2
)
-
Transfer Optimizer annotations
for query block SEL$2
(
#2
)
-
id
=0 frofand predicate
=
\
"EMP\"
.
\
"DEPTNO\" IS NULL
-
Final cost
for query block SEL$2
(
#2
)
- All Rows Plan
:
-
Best join order
: 1
-
Cost
: 3
.0019 Degree
: 1 Card
: 1
.0000 Bytes
: 3
-
Resc
: 3
.0019 Resc_io
: 3
.0000 Resc_cpu
: 39947
-
Resp
: 3
.0019 Resp_io
: 3
.0000 Resc_cpu
: 39947
-
kkoqbc
-subheap
(delete
addr
=0x2b692ed1faa0
,
in
-use
=13504
, alloc
=16408
)
-
kkoqbc
-end
:
-
:
-
call
(
in
-use
=29752
, alloc
=82128
)
, compile
(
in
-use
=149552
, alloc
=152576
)
, execution
(
in
-use
=3864
, alloc
=4032
)
-
-
kkoqbc
: finish optimizing query block SEL$2
(
#2
)
-
kkoqbc
: optimizing query block SEL$5DA710D3
(
#1
)
-
-
:
-
call
(
in
-use
=29752
, alloc
=82128
)
, compile
(
in
-use
=149552
, alloc
=152576
)
, execution
(
in
-use
=3864
, alloc
=4032
)
-
-
kkoqbc
-subheap
(create
addr
=0x2b692ed1faa0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
QUERY BLOCK TEXT
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
select
* from dept where deptno
not
in
(select deptno from emp
)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
QUERY BLOCK SIGNATURE
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
signature
(optimizer
)
: qb_name
=SEL$5DA710D3 nbfros
=2 flg
=0
-
fro
(0
)
: flg
=0 objn
=75333 hint_alias
=
\
"DEPT\"
@
\
"SEL$1\"
-
fro
(1
)
: flg
=0 objn
=75335 hint_alias
=
\
"EMP\"
@
\
"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
: DEPT Alias
: DEPT
-
#Rows
: 4 #Blks
: 5 AvgRowLen
: 20
.00 ChainCnt
: 0
.00
-
Column
(
#3
)
: LOC
(
-
AvgLen
: 8 NDV
: 4 Nulls
: 0 Density
: 0
.250000
-
Column
(
#2
)
: DNAME
(
-
AvgLen
: 10 NDV
: 4 Nulls
: 0 Density
: 0
.250000
-
Column
(
#1
)
: DEPTNO
(
-
AvgLen
: 3 NDV
: 4 Nulls
: 0 Density
: 0
.250000 Min
: 10 Max
: 40
-
Index Stats
:
:
-
Index
: PK_DEPT Col#
: 1
-
LVLS
: 0 #LB
: 1 #DK
: 4 LB/K
: 1
.00 DB/K
: 1
.00 CLUF
: 1
.00
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Table Stats
:
:
-
Table
: EMP Alias
: EMP
-
#Rows
: 14 #Blks
: 5 AvgRowLen
: 38
.00 ChainCnt
: 0
.00
-
Column
(
#8
)
: DEPTNO
(
-
AvgLen
: 3 NDV
: 3 Nulls
: 0 Density
: 0
.333333 Min
: 10 Max
: 30
-
Index Stats
:
:
-
Index
: PK_EMP Col#
: 1
-
LVLS
: 0 #LB
: 1 #DK
: 14 LB/K
: 1
.00 DB/K
: 1
.00 CLUF
: 1
.00
-
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
-
-
Access path analysis
for DEPT
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
SINGLE TABLE ACCESS PATH
-
Single Table Cardinality Estimation
for DEPT
[DEPT
]
-
Table
: DEPT Alias
: DEPT
-
Card
: Original
: 4
.000000 Rounded
: 4 Computed
: 4
.00 Non Adjusted
: 4
.00
-
Access Path
: TableScan
-
Cost
: 3
.00 Resp
: 3
.00 Degree
: 0
-
Cost_io
: 3
.00 Cost_cpu
: 36367
-
Resp_io
: 3
.00 Resp_cpu
: 36367
-
Best
:
: AccessPath
: TableScan
-
Cost
: 3
.00 Degree
: 1 Resp
: 3
.00 Card
: 4
.00 Bytes
: 0
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
-
-
OPTIMIZER STATISTICS
AND COMPUTATIONS
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
GENERAL PLANS
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Considering cardinality
-based initial join order
.
-
Permutations
for Starting Table
:0
-
Join order
[1
]
: DEPT
[DEPT
]
#0 EMP
[EMP
]
#1
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Now joining
: EMP
[EMP
]
#1
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
NL Join
-
Outer table
: Card
: 4
.00 Cost
: 3
.00 Resp
: 3
.00 Degree
: 1 Bytes
: 20
-
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
: 10
.00 Cost_cpu
: 195036
-
Resp_io
: 10
.00 Resp_cpu
: 195036
-
-
Best NL cost
: 13
.01
-
resc
: 10
.01 resc_io
: 10
.00 resc_cpu
: 195036
-
resp
: 10
.01 resp_io
: 10
.00 resc_cpu
: 195036
-
>
>
> adjusting AJ/SJ sel based on min/max ranges
: jsel
=min
(0
.750000
, 0
.666667
)
-
Anti Join Card
: 1
.333333
= outer
(4
.000000
)
*
(1
- sel
(0
.666667
)
)
-
Join Card
- Rounded
: 1 Computed
: 1
.33
-
Outer table
: DEPT Alias
: DEPT
-
resc
: 3
.00 card 4
.00 bytes
: 20 deg
: 1 resp
: 3
.00
-
Inner table
: EMP Alias
: EMP
-
resc
: 3
.00 card
: 14
.00 bytes
: 3 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
: 32 Total Rows
: 4
-
Initial runs
: 1 Merge passes
: 0 IO Cost / pass
: 0
-
Total IO sort cost
: 0 Total CPU sort cost
: 21021466
-
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
: 14 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
: 8
.00 Resp
: 8
.00
[multiMatchCost
=0
.00
]
-
SM Join
-
SM cost
: 8
.00
-
resc
: 8
.00 resc_io
: 6
.00 resc_cpu
: 42121007
-
resp
: 8
.00 resp_io
: 6
.00 resp_cpu
: 42121007
-
SM Join
(with index on outer
)
-
Access Path
: index
(FullScan
)
-
Index
: PK_DEPT
-
resc_io
: 2
.00 resc_cpu
: 15803
-
ix_sel
: 1
.000000 ix_sel_with_filters
: 1
.000000
-
Cost
: 2
.00 Resp
: 2
.00 Degree
: 1
-
Outer table
: DEPT Alias
: DEPT
-
resc
: 2
.00 card 4
.00 bytes
: 20 deg
: 1 resp
: 2
.00
-
Inner table
: EMP Alias
: EMP
-
resc
: 3
.00 card
: 14
.00 bytes
: 3 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
: 14 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
: 6
.00 Resp
: 6
.00
[multiMatchCost
=0
.00
]
-
Outer table
: DEPT Alias
: DEPT
-
resc
: 3
.00 card 4
.00 bytes
: 20 deg
: 1 resp
: 3
.00
-
Inner table
: EMP Alias
: EMP
-
resc
: 3
.00 card
: 14
.00 bytes
: 3 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
=0
.00
]
-
HA Join
-
HA cost
: 6
.50
-
resc
: 6
.50 resc_io
: 6
.00 resc_cpu
: 10588587
-
resp
: 6
.50 resp_io
: 6
.00 resp_cpu
: 10588587
-
Best
:
: JoinMethod
: SortMergeNullAwareAnti
-
Cost
: 6
.00 Degree
: 1 Resp
: 6
.00 Card
: 1
.33 Bytes
: 23
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Best so far
: Table#
: 0 cost
: 2
.0008 card
: 4
.0000 bytes
: 80
-
Table#
: 1 cost
: 6
.0028 card
: 1
.3333 bytes
: 23
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
(newjo
-stop
-1
) k
:0
, spcnt
:0
, perm
:1
, maxperm
:2000
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Number
of join permutations tried
: 1
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Consider using bloom filter between DEPT
[DEPT
]
and EMP
[EMP
] with
?
?
-
kkoBloomFilter
: join ndv
:0 reduction
:1
.000000
(limit
:0
.500000
) rejected because no single
-tables predicates
-
Enumerating distribution method
(advanced
)
-
-
-
- Distribution method
for
-
join between DEPT
[DEPT
]
(serial
)
and EMP
[EMP
]
(serial
)
; jm
= 10
; right side access path
= TableScan
-
-
-
-
- serial Sort
-Merge
-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
=
\
"DEPT\"
.
\
"DEPTNO\"
=
\
"EMP\"
.
\
"DEPTNO\"
-
id
=0 frosand
(sort
-merge/hash
) predicate
=
\
"DEPT\"
.
\
"DEPTNO\"
=
\
"EMP\"
.
\
"DEPTNO\"
-
Final cost
for query block SEL$5DA710D3
(
#1
)
- All Rows Plan
:
-
Best join order
: 1
-
Cost
: 6
.0028 Degree
: 1 Card
: 1
.0000 Bytes
: 23
-
Resc
: 6
.0028 Resc_io
: 5
.0000 Resc_cpu
: 21078977
-
Resp
: 6
.0028 Resp_io
: 5
.0000 Resc_cpu
: 21078977
-
kkoqbc
-subheap
(delete
addr
=0x2b692ed1faa0
,
in
-use
=24856
, alloc
=32840
)
-
kkoqbc
-end
:
-
:
-
call
(
in
-use
=38760
, alloc
=98600
)
, compile
(
in
-use
=151272
, alloc
=152576
)
, execution
(
in
-use
=3864
, alloc
=4032
)
-
-
kkoqbc
: finish optimizing query block SEL$5DA710D3
(
#1
)
-
apadrv
-end
-
:
-
call
(
in
-use
=38760
, alloc
=98600
)
, compile
(
in
-use
=152256
, alloc
=152576
)
, execution
(
in
-use
=3864
, alloc
=4032
)
-
-
-
Starting SQL statement dump
-
-
user_id
=83 user_name
=SCOTT module
=SQL
*Plus action
=
-
sql_id
=296c8ug3jwdcz plan_hash_value
=
-2064285032 problem_type
=3
-
-
-
-
-
- Current SQL Statement
for
this session
(sql_id
=296c8ug3jwdcz
)
-
-
-
-
-
-
select
* from dept where deptno
not
in
(select deptno from emp
)
-
sql_text_length
=64
-
sql
=select
* from dept where deptno
not
in
(select deptno from emp
)
-
-
-
-
-
- Explain Plan Dump
-
-
-
-
-
-
-
-
-
-
- Plan Table
-
-
-
-
-
-
-
=
=
=
=
=
=
=
=
=
=
=
=
-
Plan Table
-
=
=
=
=
=
=
=
=
=
=
=
=
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
| Id
| Operation
|
Name
| Rows
| Bytes
| Cost
| Time
|
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
| 0
| SELECT STATEMENT
|
|
|
| 6
|
|
-
| 1
| MERGE JOIN ANTI NA
|
| 1
| 23
| 6
| 00
:00
:01
|
-
| 2
| SORT JOIN
|
| 4
| 80
| 2
| 00
:00
:01
|
-
| 3
| TABLE ACCESS BY INDEX ROWID
| DEPT
| 4
| 80
| 2
| 00
:00
:01
|
-
| 4
| INDEX FULL SCAN
| PK_DEPT
| 4
|
| 1
| 00
:00
:01
|
-
| 5
| SORT UNIQUE
|
| 14
| 42
| 4
| 00
:00
:01
|
-
| 6
| TABLE ACCESS FULL
| EMP
| 14
| 42
| 3
| 00
:00
:01
|
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
Predicate Information
:
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
5
- access
(
"DEPTNO"
=
"DEPTNO"
)
-
5
- filter
(
"DEPTNO"
=
"DEPTNO"
)
-
-
Content
of other_xml column
-
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
-
db_version
: 11
.2
.0
.3
-
parse_schema
: SCOTT
-
plan_hash
: 2230682264
-
plan_hash_2
: 644022833
-
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"
)
-
INDEX
(
@
"SEL$5DA710D3"
"DEPT"
@
"SEL$1"
(
"DEPT"
.
"DEPTNO"
)
)
-
FULL
(
@
"SEL$5DA710D3"
"EMP"
@
"SEL$2"
)
-
LEADING
(
@
"SEL$5DA710D3"
"DEPT"
@
"SEL$1"
"EMP"
@
"SEL$2"
)
-
USE_MERGE
(
@
"SEL$5DA710D3"
"EMP"
@
"SEL$2"
)
-
END_OUTLINE_DATA
- * /
ANTI NA 是中join的型別，他是拿語法寫不出來的，就是內部的一個演算法。以前oracle沒有這種join，10g都沒有，是11.1.0.6加進去的。
deptno列設定為not null
我們看一下：（not null 執行計劃不會考慮是NA這種情況，就直接用的anti）
點選( 此處)摺疊或開啟
-
*
*
* 2015
-02
-28 09
:47
:15
.951
-
Registered qb
: SEL$1 0x772b66d0
(PARSER
)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
QUERY BLOCK SIGNATURE
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
signature
(
)
: qb_name
=SEL$1 nbfros
=1 flg
=0
-
fro
(0
)
: flg
=4 objn
=75333 hint_alias
=
\
"DEPT\"
@
\
"SEL$1\"
-
-
Registered qb
: SEL$2 0x772b1490
(PARSER
)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
QUERY BLOCK SIGNATURE
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
signature
(
)
: qb_name
=SEL$2 nbfros
=1 flg
=0
-
fro
(0
)
: flg
=4 objn
=75335 hint_alias
=
\
"EMP\"
@
\
"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
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Predicate Move
-Around
(PM
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
OPTIMIZER INFORMATION
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
-
-
-
-
- Current SQL Statement
for
this session
(sql_id
=0jfbpts9pskp8
)
-
-
-
-
-
-
select /
* sample 9
*
/
* from dept where deptno
not
in
(select deptno from emp
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
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
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Compilation Environment Dump
-
Bug Fix Control Environment
-
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
PARAMETERS WITH DEFAULT VALUES
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Compilation Environment Dump
-
optimizer_mode_hinted
= false
-
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
PARAMETERS
IN OPT_PARAM HINT
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Column Usage Monitoring is ON
: tracking level
= 1
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
-
Considering Query Transformations on query block SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Query transformations
(QT
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
JF
: Checking validity
of join factorization
for query block SEL$2
(
#0
)
-
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
(
#0
)
-
TE
: Bypassed
: No partitioned table
in query block
.
-
CBQT
: Validity checks passed
for 0jfbpts9pskp8
.
-
CSE
: Considering common
sub
-expression elimination
in query block SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Common Subexpression elimination
(CSE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: Considering common
sub
-expression elimination
in query block SEL$2
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Common Subexpression elimination
(CSE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: CSE
not performed on query block SEL$2
(
#0
)
.
-
CSE
: CSE
not performed on query block SEL$1
(
#0
)
.
-
OBYE
: Considering Order
-by Elimination from view SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Order
-by elimination
(OBYE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
OBYE
: OBYE bypassed
: no order by to eliminate
.
-
OJE
: Begin
: find best directive
for query block SEL$1
(
#0
)
-
OJE
: End
: finding best directive
for query block SEL$1
(
#0
)
-
OJE
: Begin
: find best directive
for query block SEL$2
(
#0
)
-
OJE
: End
: finding best directive
for query block SEL$2
(
#0
)
-
query block SEL$1
(
#0
) unchanged
-
Considering Query Transformations on query block SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Query transformations
(QT
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: Considering common
sub
-expression elimination
in query block SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Common Subexpression elimination
(CSE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: Considering common
sub
-expression elimination
in query block SEL$2
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Common Subexpression elimination
(CSE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: CSE
not performed on query block SEL$2
(
#0
)
.
-
CSE
: CSE
not performed on query block SEL$1
(
#0
)
.
-
query block SEL$1
(
#0
) unchanged
-
apadrv
-start sqlid
=628663150943423144
-
:
-
call
(
in
-use
=1760
, alloc
=16344
)
, compile
(
in
-use
=61568
, alloc
=62200
)
, execution
(
in
-use
=3456
, alloc
=4032
)
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Peeked values
of the binds
in SQL statement
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
-
CBQT
: Considering cost
-based transformation on query block SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
COST
-BASED QUERY TRANSFORMATIONS
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
FPD
: Considering simple filter
push
(pre rewrite
)
in query block SEL$2
(
#0
)
-
FPD
: Current where clause predicates
?
?
-
-
FPD
: Considering simple filter
push
(pre rewrite
)
in query block SEL$1
(
#0
)
-
FPD
: Current where clause predicates \
"DEPT\"
.
\
"DEPTNO\"
<
>ALL
(SELECT \
"EMP\"
.
\
"DEPTNO\" FROM \
"EMP\" \
"EMP\"
)
-
-
OBYE
: Considering Order
-by Elimination from view SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Order
-by elimination
(OBYE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
OBYE
: OBYE bypassed
: no order by to eliminate
.
-
Considering Query Transformations on query block SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Query transformations
(QT
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: Considering common
sub
-expression elimination
in query block SEL$1
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Common Subexpression elimination
(CSE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: Considering common
sub
-expression elimination
in query block SEL$2
(
#0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Common Subexpression elimination
(CSE
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
CSE
: CSE
not performed on query block SEL$2
(
#0
)
.
-
CSE
: CSE
not performed on query block SEL$1
(
#0
)
.
-
kkqctdrvTD
-start on query block SEL$1
(
#0
)
-
kkqctdrvTD
-start
:
:
-
call
(
in
-use
=1760
, alloc
=16344
)
, compile
(
in
-use
=104008
, alloc
=107576
)
, execution
(
in
-use
=3456
, alloc
=4032
)
-
-
Registered qb
: SEL$1 0x77351f98
(COPY SEL$1
)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
QUERY BLOCK SIGNATURE
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
signature
(
)
: NULL
-
Registered qb
: SEL$2 0x77352878
(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
: Transform ALL subquery to a regular antijoin
.
-
Registered qb
: SEL$5DA710D3 0x77351f98
(SUBQUERY UNNEST SEL$1
; SEL$2
)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
QUERY BLOCK SIGNATURE
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
signature
(
)
: qb_name
=SEL$5DA710D3 nbfros
=2 flg
=0
-
fro
(0
)
: flg
=0 objn
=75333 hint_alias
=
\
"DEPT\"
@
\
"SEL$1\"
-
fro
(1
)
: flg
=0 objn
=75335 hint_alias
=
\
"EMP\"
@
\
"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
=4528
, alloc
=8392
)
:
-
call
(
in
-use
=2456
, alloc
=16344
)
, compile
(
in
-use
=121344
, alloc
=131264
)
, execution
(
in
-use
=3456
, alloc
=4032
)
-
-
kkqctdrvTD
-end
:
-
call
(
in
-use
=2456
, alloc
=16344
)
, compile
(
in
-use
=113128
, alloc
=131264
)
, execution
(
in
-use
=3456
, alloc
=4032
)
-
-
Subquery removal
for query block SEL$2
(
#2
)
-
RSW
:
Not valid
for subquery removal SEL$2
(
#2
)
-
Subquery unchanged
.
-
SU
: Transform ALL subquery to a regular antijoin
.
-
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 \
"DEPT\"
.
\
"DEPTNO\" \
"DEPTNO\"
,
\
"DEPT\"
.
\
"DNAME\" \
"DNAME\"
,
\
"DEPT\"
.
\
"LOC\" \
"LOC\" FROM \
"SCOTT\"
.
\
"EMP\" \
"EMP\"
,
\
"SCOTT\"
.
\
"DEPT\" \
"DEPT\" WHERE \
"DEPT\"
.
\
"DEPTNO\"
=
\
"EMP\"
.
\
"DEPTNO\"
-
JE
: cfro
: EMP objn
:75333 col#
:8 dfro
:DEPT dcol#
:1
-
JE
: cfro
: EMP objn
:75333 col#
:8 dfro
:DEPT dcol#
:1
-
JE
: cfro
: EMP objn
:75333 col#
:8 dfro
:DEPT dcol#
:1
-
JE
: cfro
: EMP objn
:75333 col#
:8 dfro
:DEPT dcol#
:1
-
JE
: cfro
: DEPT objn
:75335 col#
:1 dfro
:EMP dcol#
:8
-
SQL
:
*
*
*
*
*
*
* UNPARSED QUERY IS
*
*
*
*
*
*
*
-
SELECT
DEPT
.
DEPTNO
DEPTNO
,
DEPT
.
DNAME
DNAME
,
DEPT
.
LOC
LOC FROM
EMP
EMP
,
SCOTT
.
DEPT
DEPT WHERE
DEPT
.
DEPTNO
=
EMP
.
DEPTNO
-
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
=3432
, alloc
=16344
)
, compile
(
in
-use
=115208
, alloc
=131264
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-cleanup
: transform
(
in
-use
=0
, alloc
=0
)
:
-
call
(
in
-use
=3432
, alloc
=16344
)
, compile
(
in
-use
=115800
, alloc
=131264
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-end
:
-
call
(
in
-use
=3432
, alloc
=16344
)
, compile
(
in
-use
=116080
, alloc
=131264
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-start on query block SEL$5DA710D3
(
#1
)
-
kkqctdrvTD
-start
:
:
-
call
(
in
-use
=3432
, alloc
=16344
)
, compile
(
in
-use
=116080
, alloc
=131264
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-cleanup
: transform
(
in
-use
=0
, alloc
=0
)
:
-
call
(
in
-use
=3432
, alloc
=16344
)
, compile
(
in
-use
=116648
, alloc
=131264
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-end
:
-
call
(
in
-use
=3432
, alloc
=16344
)
, compile
(
in
-use
=116928
, alloc
=131264
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-start on query block SEL$5DA710D3
(
#1
)
-
kkqctdrvTD
-start
:
:
-
call
(
in
-use
=3432
, alloc
=16344
)
, compile
(
in
-use
=116928
, alloc
=131264
)
, execution
(
in
-use
=3624
, 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
=0
, alloc
=0
)
:
-
call
(
in
-use
=3432
, alloc
=16344
)
, compile
(
in
-use
=117520
, alloc
=131264
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-end
:
-
call
(
in
-use
=3432
, alloc
=16344
)
, compile
(
in
-use
=117800
, alloc
=131264
)
, execution
(
in
-use
=3624
, 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 \
"DEPT\"
.
\
"DEPTNO\" \
"DEPTNO\"
,
\
"DEPT\"
.
\
"DNAME\" \
"DNAME\"
,
\
"DEPT\"
.
\
"LOC\" \
"LOC\" FROM \
"SCOTT\"
.
\
"EMP\" \
"EMP\"
,
\
"SCOTT\"
.
\
"DEPT\" \
"DEPT\" WHERE \
"DEPT\"
.
\
"DEPTNO\"
=
\
"EMP\"
.
\
"DEPTNO\"
-
ST
:
not valid since star transformation parameter is FALSE
-
kkqctdrvTD
-start on query block SEL$5DA710D3
(
#1
)
-
kkqctdrvTD
-start
:
:
-
call
(
in
-use
=3480
, alloc
=16344
)
, compile
(
in
-use
=117800
, alloc
=131264
)
, execution
(
in
-use
=3624
, 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
=0
, alloc
=0
)
:
-
call
(
in
-use
=3480
, alloc
=16344
)
, compile
(
in
-use
=118368
, alloc
=131264
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-end
:
-
call
(
in
-use
=3480
, alloc
=16344
)
, compile
(
in
-use
=118648
, alloc
=131264
)
, execution
(
in
-use
=3624
, 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
=3480
, alloc
=16344
)
, compile
(
in
-use
=118648
, alloc
=131264
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-cleanup
: transform
(
in
-use
=0
, alloc
=0
)
:
-
call
(
in
-use
=3480
, alloc
=16344
)
, compile
(
in
-use
=119216
, alloc
=131264
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkqctdrvTD
-end
:
-
call
(
in
-use
=3480
, alloc
=16344
)
, compile
(
in
-use
=119496
, alloc
=131264
)
, execution
(
in
-use
=3624
, 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
)
-
\
"DEPT\"
.
\
"DEPTNO\"
=
\
"EMP\"
.
\
"DEPTNO\"
-
try to generate transitive predicate from check constraints
for query block SEL$5DA710D3
(
#1
)
-
finally
:
DEPT
.
DEPTNO
=
EMP
.
DEPTNO
-
-
Final query after transformations
:
*
*
*
*
*
*
* UNPARSED QUERY IS
*
*
*
*
*
*
*
-
SELECT
DEPT
.
DEPTNO
DEPTNO
,
DEPT
.
DNAME
DNAME
,
DEPT
.
LOC
LOC FROM
EMP
EMP
,
DEPT
DEPT WHERE
DEPT
.
DEPTNO
=
EMP
.
DEPTNO
-
kkoqbc
: optimizing query block SEL$5DA710D3
(
#1
)
-
-
:
-
call
(
in
-use
=3720
, alloc
=16344
)
, compile
(
in
-use
=120800
, alloc
=131264
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkoqbc
-subheap
(create
addr
=0x2af4772bfaa0
)
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
QUERY BLOCK TEXT
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
select /
* sample 9
*
/
* from dept where deptno
not
in
(select deptno from emp
)
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
QUERY BLOCK SIGNATURE
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
signature
(optimizer
)
: qb_name
=SEL$5DA710D3 nbfros
=2 flg
=0
-
fro
(0
)
: flg
=0 objn
=75333 hint_alias
=
\
"DEPT\"
@
\
"SEL$1\"
-
fro
(1
)
: flg
=0 objn
=75335 hint_alias
=
\
"EMP\"
@
\
"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
: DEPT Alias
: DEPT
-
#Rows
: 4 #Blks
: 5 AvgRowLen
: 20
.00 ChainCnt
: 0
.00
-
Column
(
#1
)
: DEPTNO
(
-
AvgLen
: 3 NDV
: 4 Nulls
: 0 Density
: 0
.250000 Min
: 10 Max
: 40
-
Index Stats
:
:
-
Index
: PK_DEPT Col#
: 1
-
LVLS
: 0 #LB
: 1 #DK
: 4 LB/K
: 1
.00 DB/K
: 1
.00 CLUF
: 1
.00
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Table Stats
:
:
-
Table
: EMP Alias
: EMP
-
#Rows
: 14 #Blks
: 5 AvgRowLen
: 38
.00 ChainCnt
: 0
.00
-
Column
(
#8
)
: DEPTNO
(
-
AvgLen
: 3 NDV
: 3 Nulls
: 0 Density
: 0
.333333 Min
: 10 Max
: 30
-
Index Stats
:
:
-
Index
: PK_EMP Col#
: 1
-
LVLS
: 0 #LB
: 1 #DK
: 14 LB/K
: 1
.00 DB/K
: 1
.00 CLUF
: 1
.00
-
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
-
-
Access path analysis
for DEPT
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
SINGLE TABLE ACCESS PATH
-
Single Table Cardinality Estimation
for DEPT
[DEPT
]
-
Table
: DEPT Alias
: DEPT
-
Card
: Original
: 4
.000000 Rounded
: 4 Computed
: 4
.00 Non Adjusted
: 4
.00
-
Access Path
: TableScan
-
Cost
: 3
.00 Resp
: 3
.00 Degree
: 0
-
Cost_io
: 3
.00 Cost_cpu
: 36367
-
Resp_io
: 3
.00 Resp_cpu
: 36367
-
Best
:
: AccessPath
: TableScan
-
Cost
: 3
.00 Degree
: 1 Resp
: 3
.00 Card
: 4
.00 Bytes
: 0
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
-
-
OPTIMIZER STATISTICS
AND COMPUTATIONS
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
GENERAL PLANS
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Considering cardinality
-based initial join order
.
-
Permutations
for Starting Table
:0
-
Join order
[1
]
: DEPT
[DEPT
]
#0 EMP
[EMP
]
#1
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Now joining
: EMP
[EMP
]
#1
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
NL Join
-
Outer table
: Card
: 4
.00 Cost
: 3
.00 Resp
: 3
.00 Degree
: 1 Bytes
: 20
-
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
: 10
.00 Cost_cpu
: 195036
-
Resp_io
: 10
.00 Resp_cpu
: 195036
-
-
Best NL cost
: 10
.01
-
resc
: 10
.01 resc_io
: 10
.00 resc_cpu
: 195036
-
resp
: 10
.01 resp_io
: 10
.00 resc_cpu
: 195036
-
>
>
> adjusting AJ/SJ sel based on min/max ranges
: jsel
=min
(0
.750000
, 0
.666667
)
-
Anti Join Card
: 1
.333333
= outer
(4
.000000
)
*
(1
- sel
(0
.666667
)
)
-
Join Card
- Rounded
: 1 Computed
: 1
.33
-
Outer table
: DEPT Alias
: DEPT
-
resc
: 3
.00 card 4
.00 bytes
: 20 deg
: 1 resp
: 3
.00
-
Inner table
: EMP Alias
: EMP
-
resc
: 3
.00 card
: 14
.00 bytes
: 3 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
: 32 Total Rows
: 4
-
Initial runs
: 1 Merge passes
: 0 IO Cost / pass
: 0
-
Total IO sort cost
: 0 Total CPU sort cost
: 21021466
-
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
: 14 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
: 8
.00 Resp
: 8
.00
[multiMatchCost
=0
.00
]
-
SM Join
-
SM cost
: 8
.00
-
resc
: 8
.00 resc_io
: 6
.00 resc_cpu
: 42121007
-
resp
: 8
.00 resp_io
: 6
.00 resp_cpu
: 42121007
-
SM Join
(with index on outer
)
-
Access Path
: index
(FullScan
)
-
Index
: PK_DEPT
-
resc_io
: 2
.00 resc_cpu
: 15803
-
ix_sel
: 1
.000000 ix_sel_with_filters
: 1
.000000
-
Cost
: 2
.00 Resp
: 2
.00 Degree
: 1
-
Outer table
: DEPT Alias
: DEPT
-
resc
: 2
.00 card 4
.00 bytes
: 20 deg
: 1 resp
: 2
.00
-
Inner table
: EMP Alias
: EMP
-
resc
: 3
.00 card
: 14
.00 bytes
: 3 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
: 14 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
: 6
.00 Resp
: 6
.00
[multiMatchCost
=0
.00
]
-
Outer table
: DEPT Alias
: DEPT
-
resc
: 3
.00 card 4
.00 bytes
: 20 deg
: 1 resp
: 3
.00
-
Inner table
: EMP Alias
: EMP
-
resc
: 3
.00 card
: 14
.00 bytes
: 3 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
=0
.00
]
-
HA Join
-
HA cost
: 6
.50
-
resc
: 6
.50 resc_io
: 6
.00 resc_cpu
: 10588587
-
resp
: 6
.50 resp_io
: 6
.00 resp_cpu
: 10588587
-
Best
:
: JoinMethod
: SortMergeAnti
-
Cost
: 6
.00 Degree
: 1 Resp
: 6
.00 Card
: 1
.33 Bytes
: 23
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Best so far
: Table#
: 0 cost
: 2
.0008 card
: 4
.0000 bytes
: 80
-
Table#
: 1 cost
: 6
.0028 card
: 1
.3333 bytes
: 23
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
(newjo
-stop
-1
) k
:0
, spcnt
:0
, perm
:1
, maxperm
:2000
-
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Number
of join permutations tried
: 1
-
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
-
Consider using bloom filter between DEPT
[DEPT
]
and EMP
[EMP
] with
?
?
-
kkoBloomFilter
: join ndv
:0 reduction
:1
.000000
(limit
:0
.500000
) rejected because no single
-tables predicates
-
Enumerating distribution method
(advanced
)
-
-
-
- Distribution method
for
-
join between DEPT
[DEPT
]
(serial
)
and EMP
[EMP
]
(serial
)
; jm
= 9
; right side access path
= TableScan
-
-
-
-
- serial Sort
-Merge
-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
=
\
"DEPT\"
.
\
"DEPTNO\"
=
\
"EMP\"
.
\
"DEPTNO\"
-
id
=0 frosand
(sort
-merge/hash
) predicate
=
\
"DEPT\"
.
\
"DEPTNO\"
=
\
"EMP\"
.
\
"DEPTNO\"
-
Final cost
for query block SEL$5DA710D3
(
#1
)
- All Rows Plan
:
-
Best join order
: 1
-
Cost
: 6
.0028 Degree
: 1 Card
: 1
.0000 Bytes
: 23
-
Resc
: 6
.0028 Resc_io
: 5
.0000 Resc_cpu
: 21078977
-
Resp
: 6
.0028 Resp_io
: 5
.0000 Resc_cpu
: 21078977
-
kkoqbc
-subheap
(delete
addr
=0x2af4772bfaa0
,
in
-use
=24056
, alloc
=32840
)
-
kkoqbc
-end
:
-
:
-
call
(
in
-use
=17592
, alloc
=65656
)
, compile
(
in
-use
=123016
, alloc
=131264
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
kkoqbc
: finish optimizing query block SEL$5DA710D3
(
#1
)
-
apadrv
-end
-
:
-
call
(
in
-use
=17592
, alloc
=65656
)
, compile
(
in
-use
=124000
, alloc
=131264
)
, execution
(
in
-use
=3624
, alloc
=4032
)
-
-
-
Starting SQL statement dump
-
-
user_id
=83 user_name
=SCOTT module
=SQL
*Plus action
=
-
sql_id
=0jfbpts9pskp8 plan_hash_value
=1353548327 problem_type
=3
-
-
-
-
-
- Current SQL Statement
for
this session
(sql_id
=0jfbpts9pskp8
)
-
-
-
-
-
-
select /
* sample 9
*
/
* from dept where deptno
not
in
(select deptno from emp
)
-
sql_text_length
=79
-
sql
=select /
* sample 9
*
/
* from dept where deptno
not
in
(select deptno from emp
)
-
-
-
-
-
- Explain Plan Dump
-
-
-
-
-
-
-
-
-
-
- Plan Table
-
-
-
-
-
-
-
=
=
=
=
=
=
=
=
=
=
=
=
-
Plan Table
-
=
=
=
=
=
=
=
=
=
=
=
=
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
| Id
| Operation
|
Name
| Rows
| Bytes
| Cost
| Time
|
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
| 0
| SELECT STATEMENT
|
|
|
| 6
|
|
-
| 1
| MERGE JOIN ANTI
|
| 1
| 23
| 6
| 00
:00
:01
|
-
| 2
| TABLE ACCESS BY INDEX ROWID
| DEPT
| 4
| 80
| 2
| 00
:00
:01
|
-
| 3
| INDEX FULL SCAN
| PK_DEPT
| 4
|
| 1
| 00
:00
:01
|
-
| 4
| SORT UNIQUE
|
| 14
| 42
| 4
| 00
:00
:01
|
-
| 5
| TABLE ACCESS FULL
| EMP
| 14
| 42
| 3
| 00
:00
:01
|
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
Predicate Information
:
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
4
- access
(
"DEPTNO"
=
"DEPTNO"
)
-
4
- filter
(
"DEPTNO"
=
"DEPTNO"
)
-
-
Content
of other_xml column
-
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
=
-
db_version
: 11
.2
.0
.3
-
parse_schema
: SCOTT
-
plan_hash
: 1353548327
-
plan_hash_2
: 2151567395
-
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\"
)
-
INDEX
(
@
\
"SEL$5DA710D3\" \
"DEPT\"
@
\
"SEL$1\"
(
\
"DEPT\"
.
\
"DEPTNO\"
)
)
-
FULL
(
@
\
"SEL$5DA710D3\" \
"EMP\"
@
\
"SEL$2\"
)
-
LEADING
(
@
"SEL$5DA710D3"
"DEPT"
@
"SEL$1"
"EMP"
@
"SEL$2"
)
-
USE_MERGE
(
@
"SEL$5DA710D3"
"EMP"
@
"SEL$2"
)
-
END_OUTLINE_DATA
- * /
在業務建模的時候如果這個列可以為空，那麼就讓他為空，業務邏輯不允許他為空，那麼就不讓他為空，如果可以給他個預設值的話、儘量給一個預設值，別讓他為空。可能就是最佳化器行為導致一些結果可能有寫麻煩，比如第一個例子那就查不出結果來。執行計劃也就可能和你想象的完全不同，還有很多，比如你不等的時候，null值既不等於也不等於，null是不能比較的一個值，建模的時候null值一定要考慮清楚
下面我們將 這個引數關閉
點選( 此處)摺疊或開啟
- alter session set "_optimizer_null_aware_antijoin" = false
先關的最佳化器引數
parameters：
_optimizer_null_aware_antijoin
_unnest_subquery
_optimizer_unnest_all_subqueries
比如null_ware anti-join這個功能關閉的話，一定要用
_optimizer_null_aware_antijoin這個引數，一定不要用下面的那兩個，要是用那兩個引數
可能帶來災難性的問題，除非你說你的應用寫的無比的好，沒有join 沒有view 不用jppd 不用view merge，那好那你可以關。bug解決後立馬把這個引數再開啟。
有些系統從低版本升級到高版本，有些客戶講高版本的一些引數關閉，來保證高板本和低版本一致，這樣絕得會沒問題，但是這樣是不對的。
Hints:這些hint會影響這個行為，但是不是為了這個行為而設定的。
No_query_transformation
unnest,no_unnest
push_subq
no_push_subq
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-1444080/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Transformation之JE(連線消除)【八】ORM
- Transformation之simple view merge 【三】ORMView
- Transformation之CVM (complex view merge)【四】ORMView
- Transformation之FPD（filter push-down）的transform【一】ORMFilter
- 【sql調優之執行計劃】temp table transformationSQLORM
- BAdI: InfoSpoke with TransformationORM
- Star Transformation（星形轉換）ORM
- 線段樹 transformation——hdu 4578ORM
- 藍芽之九-AT命令藍芽
- Transformation之Non-correlated（無關聯子查詢）=any的transform【五】ORM
- Transformation之Subquery Un-nesting（子查詢的非巢狀）SU【六】ORM巢狀
- MySQL鎖系列（九）之longtransactionMySql
- Transformation HDU - 4578線段樹綜合操作ORM
- Statspack之九-其它重要指令碼指令碼
- Anti-Join中允許關聯column為空值( null),造成hint失效Null
- 數字轉型Digital Transformation簡介GitORM
- flink：StreamExecutionEnvironment、DataStream和Transformation與StreamOperatorASTORM
- 【Spark Java API】Transformation(8)—fullOuterJoin、leftOuterJoin、rightOuterJoinSparkJavaAPIORM
- Spark 系列（九）—— Spark SQL 之 Structured APISparkSQLStructAPI
- 計算機網路之九：VLAN計算機網路
- Python物件導向之九：反射Python物件反射
- CCNA學習之“獨孤九劍”(轉)
- rust學習九.1、集合之向量Rust
- rust學習九.2、集合之字元Rust字元
- SVG座標系統和transformation徹底理解SVGORM
- Codeforces 486C Palindrome Transformation(貪心)ORM
- Java設計模式之（九）——門面模式Java設計模式
- Akka系列（九）：Akka分散式之Akka Remote分散式REM
- Akka 系列（九）：Akka 分散式之 Akka Remote分散式REM
- Git詳解之九：Git內部原理Git
- Kubernetes安裝之九：配置node節點之kubelet
- 大資料教程：Transformation和Action運算元演示大資料ORM
- Oracle優化器：星型轉換（Star Query Transformation ）Oracle優化ORM
- 從一條問題SQL優化看SQL TransformationSQL優化ORM
- 使用 WebSphere Message Broker 的 WebSphere Transformation Extender 外掛WebORM
- ZT：簡單介紹join,outer-join,semi-join,anti-join的區別
- robot framework學習筆記之九-雜記Framework筆記
- ReactNative學習筆記九之TabNavigatorReact筆記