-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy path07_execution_plans4.txt
221 lines (175 loc) · 10.5 KB
/
07_execution_plans4.txt
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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
Queries based on Oracle demo tables. Some tables are not existing in our actual database.
Reading a table based on ROWID
------------------------------
SELECT e.employee_id, e.first_name, e.last_name
FROM nikovits.employees e WHERE rowid='AAATuXAACAAAALDAAA';
PLAN (access--filter)
-------------------------------------------------- -------------------------------------------------
SELECT STATEMENT + + --
TABLE ACCESS + BY USER ROWID + EMPLOYEES --
Index reading in desc order
-------------------------------
SELECT * FROM hr.employees e WHERE department_id < 50 ORDER BY department_id DESC;
PLAN (access--filter)
----------------------------------------------------- -----------------------------------------
SELECT STATEMENT + + --
TABLE ACCESS + BY INDEX ROWID + EMPLOYEES --
INDEX + RANGE SCAN DESCENDING + EMP_DEPARTMENT_IX "DEPARTMENT_ID"<50 -- "DEPARTMENT_ID"<50
Index skip scan (index columns: last_name, first_name)
---------------
SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name FROM hr.employees e
WHERE first_name = 'Steven';
PLAN (access--filter)
-------------------------------------------------- ----------------------------------------------
SELECT STATEMENT + + --
INDEX + SKIP SCAN + EMP_NAME_IX "FIRST_NAME"='Steven' -- "FIRST_NAME"='Steven'
Search within one index -> INLIST, union of several results -> CONCATENATION
----------------------------------------------------------------------------
SELECT /*+ USE_CONCAT */ * FROM hr.employees e
WHERE manager_id = 108 OR department_id = 110 OR department_id=90;
PLAN
--------------------------------------------------
SELECT STATEMENT + +
CONCATENATION + +
INLIST ITERATOR + +
TABLE ACCESS + BY INDEX ROWID + EMPLOYEES
INDEX + RANGE SCAN + EMP_DEPARTMENT_IX
TABLE ACCESS + BY INDEX ROWID + EMPLOYEES
INDEX + RANGE SCAN + EMP_MANAGER_IX
JOIN syntax is not important (JOIN or WHERE)
--------------------------------------------
SELECT c.cust_last_name, co.country_name
FROM sh.customers c NATURAL JOIN sh.countries co;
PLAN (access--filter)
-------------------------------------------------- -------------------------------------
SELECT STATEMENT + + --
HASH JOIN + + "C"."COUNTRY_ID"="CO"."COUNTRY_ID" --
TABLE ACCESS + FULL + COUNTRIES --
TABLE ACCESS + FULL + CUSTOMERS --
Not equijoin
------------
SELECT s.amount_sold, p.promo_name
FROM sh.sales s JOIN sh.promotions p ON (s.time_id BETWEEN p.promo_begin_date AND p.promo_end_date) ;
PLAN (access--filter)
----------------------------------------- -------------------------------------------------
SELECT STATEMENT + + --
MERGE JOIN + + --
SORT + JOIN + --
TABLE ACCESS + FULL + PROMOTIONS --
FILTER + + -- "S"."TIME_ID"<="P"."PROMO_END_DATE"
SORT + JOIN + "S"."TIME_ID">="P"."PROMO_BEGIN_DATE" -- "S"."TIME_ID">="P"."PROMO_BEGIN_DATE
PARTITION RANGE + ALL + --
TABLE ACCESS + FULL + SALES --
Outer join
----------
SELECT s.time_id, t.time_id
FROM sh.sales s RIGHT OUTER JOIN sh.times t ON (s.time_id = t.time_id);
PLAN (access--filter)
-------------------------------------------------- ---------------------------------
SELECT STATEMENT + + --
HASH JOIN + OUTER + "S"."TIME_ID"(+)="T"."TIME_ID" --
TABLE ACCESS + FULL + TIMES --
PARTITION RANGE + ALL + --
TABLE ACCESS + FULL + SALES --
If we have index ... it can use them
PLAN (access--filter)
-------------------------------------------------- ----------------------------------
SELECT STATEMENT + + --
HASH JOIN + OUTER + "S"."TIME_ID"(+)="T"."TIME_ID" --
INDEX + FAST FULL SCAN + TIME_PK --
PARTITION RANGE + ALL + --
BITMAP CONVERSION + TO ROWIDS + --
BITMAP INDEX + FAST FULL SCAN + SALES_TIME_BIX --
Right outer join
-----------------------
SELECT s.time_id, t.time_id
FROM sh.sales s LEFT OUTER JOIN sh.times t ON (s.time_id = t.time_id);
PLAN (access--filter)
-------------------------------------------------- ----------------------------------
SELECT STATEMENT + + --
HASH JOIN + RIGHT OUTER + "S"."TIME_ID"="T"."TIME_ID"(+) --
TABLE ACCESS + FULL + TIMES --
PARTITION RANGE + ALL + --
TABLE ACCESS + FULL + SALES --
Full outer join (lower branch searches Countries records without matching pair)
-----------------------------
SELECT c.cust_id, c.cust_last_name, co.country_name
FROM sh.customers c FULL OUTER JOIN sh.countries co ON (c.country_id = co.country_id);
PLAN (access--filter)
-------------------------------------------------- ----------------------------------------
SELECT STATEMENT + + --
VIEW + + --
UNION-ALL + + --
HASH JOIN + RIGHT OUTER + "C"."COUNTRY_ID"="CO"."COUNTRY_ID"(+) --
TABLE ACCESS + FULL + COUNTRIES --
TABLE ACCESS + FULL + CUSTOMERS --
HASH JOIN + ANTI + "C"."COUNTRY_ID"="CO"."COUNTRY_ID" --
TABLE ACCESS + FULL + COUNTRIES --
TABLE ACCESS + FULL + CUSTOMERS --
Not correlated subquery
----------------------
SELECT c.* FROM sh.customers c WHERE c.country_id IN
(SELECT co.country_id FROM sh.countries co WHERE co.country_subregion = 'Asia');
PLAN (access--filter)
-------------------------------------------------- -------------------------------------
SELECT STATEMENT + + --
HASH JOIN + + "C"."COUNTRY_ID"="CO"."COUNTRY_ID" --
TABLE ACCESS + FULL + COUNTRIES -- "CO"."COUNTRY_SUBREGION"='Asia'
TABLE ACCESS + FULL + CUSTOMERS --
Correlated subquery
-------------------
SELECT p1.* FROM sh.promotions p1 WHERE p1.promo_cost =
(SELECT MAX(p2.promo_cost) FROM sh.promotions p2 WHERE p1.promo_category = p2.promo_category);
PLAN (access--filter)
-------------------------------------------------- -------------------------------------------------
SELECT STATEMENT + + --
HASH JOIN + + "P1"."PROMO_COST"="VW_COL_1" AND "P1"."PROMO_CATEGORY"="PROMO_CATEGORY"
VIEW + + VW_SQ_1 --
HASH + GROUP BY + --
TABLE ACCESS + FULL + PROMOTIONS --
TABLE ACCESS + FULL + PROMOTIONS --
Antijoin (NOT IN) (subquery will be leading table)
-----------------
SELECT c.* FROM sh.customers c
WHERE c.cust_income_level = 'F: 110,000 - 129,999' AND c.country_id NOT IN
(SELECT co.country_id FROM sh.countries co WHERE co.country_subregion = 'Europe');
PLAN (access--filter)
-------------------------------------------------- -------------------------------------------------
SELECT STATEMENT + + --
HASH JOIN + RIGHT ANTI + "C"."COUNTRY_ID"="CO"."COUNTRY_ID" --
TABLE ACCESS + FULL + COUNTRIES -- "CO"."COUNTRY_SUBREGION"='Europe'
TABLE ACCESS + FULL + CUSTOMERS -- "C"."CUST_INCOME_LEVEL"='F: 110,000 - 129,999'
Semijoin (EXISTS) (NOT EXISTS -> Antijoin)
-----------------
SELECT e.employee_id, e.first_name, e.last_name, e.salary
FROM hr.employees e WHERE e.department_id = 80 AND e.job_id='SA_REP' AND EXISTS
(SELECT 1 FROM oe.orders o WHERE e.employee_id = o.sales_rep_id);
PLAN (access--filter)
-------------------------------------------------- -------------------------------------------------
SELECT STATEMENT + + --
NESTED LOOPS + SEMI + --
TABLE ACCESS + FULL + EMPLOYEES -- "E"."JOB_ID"='SA_REP' AND "E"."DEPARTMENT_ID"=80
INDEX + RANGE SCAN + ORD_SALES_REP_IX "E"."EMPLOYEE_ID"="O"."SALES_REP_ID" -- "O"."SALES_REP_
EXISTS not correlated (no join in plan)
--------------------
SELECT e.employee_id, e.first_name, e.last_name, e.salary
FROM hr.employees e WHERE e.department_id = 80 AND e.job_id='SA_REP' AND EXISTS
(SELECT 1 FROM oe.orders);
PLAN (access--filter)
-------------------------------------------------- -------------------------------------------------
SELECT STATEMENT + + --
FILTER + + -- EXISTS (SELECT /*+ */ 0 FROM "OE"."ORDERS" "ORDERS")
TABLE ACCESS + FULL + EMPLOYEES -- "E"."JOB_ID"='SA_REP' AND "E"."DEPARTMENT_ID"=80
INDEX + FULL SCAN + ORDER_PK --
Top N analysis
---------------
SELECT * FROM
(SELECT prod_id, prod_name, prod_list_price, prod_min_price FROM sh.products ORDER BY prod_list_price DESC)
WHERE ROWNUM <= 5;
PLAN (access--filter)
-------------------------------------------------- ---------------------------
SELECT STATEMENT + + --
COUNT + STOPKEY + -- ROWNUM<=5
VIEW + + --
SORT + ORDER BY STOPKEY + -- ROWNUM<=5
TABLE ACCESS + FULL + PRODUCTS --