/
su.sql
100 lines (84 loc) · 2.71 KB
/
su.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
conn tc/tc@localhost/pdb
set echo on lin 130 pages 100
drop table departments;
drop table employees;
create table departments
as
select rownum department_id,
10 manager_count,
100 employee_count
from dual
connect by level<=10;
create table employees
as
select rownum employee_id,
department_id,
lpad('x', 100, 'x') name
from departments,
lateral(
select level
from dual
connect by level<=employee_count
)(+);
set lin 85
alter session set events 'trace[sql_optimizer.*]';
explain plan for
select *
from departments d
where manager_count > employee_count
+ (select --+ unnest
count(*)
from employees e
where e.department_id = d.department_id);
alter session set events 'trace[sql_optimizer.*] off';
select value from v$diag_info where name='Default Trace File';
select * from dbms_xplan.display();
explain plan for
select *
from departments d
where manager_count > (select --+ unnest
count(*)
from employees e
where e.department_id = d.department_id);
select * from dbms_xplan.display();
set lin 130
select --+ gather_plan_statistics
*
from departments d
where manager_count > employee_count
+ (select --+ unnest
count(*)
from employees e
where e.department_id = d.department_id);
select * from dbms_xplan.display_cursor(format=> 'allstats last');
insert into departments values (100, 1, 0);
select --+ gather_plan_statistics
*
from departments d
where manager_count > employee_count
+ (select --+ unnest
count(*)
from employees e
where e.department_id = d.department_id);
select * from dbms_xplan.display_cursor(format=> 'allstats last');
select banner_full from v$version;
explain plan for
select *
from departments d
where employee_count > (select --+ unnest
count(*)
from employees e
where e.department_id = d.department_id);
select * from dbms_xplan.display();
explain plan for
with d as (
select --+ no_merge
d.*, manager_count - employee_count employee_diff
from departments d)
select *
from d
where employee_diff > (select --+ unnest
count(*)
from employees e
where e.department_id = d.department_id);
select * from dbms_xplan.display();