Чего не сделаешь от скуки...
alter session set statistics_level = all;
Session altered.
Elapsed: 00:00:00.00
create table tst as
select trunc((rownum+2)/3) a, trunc(dbms_random.value(1, 3+1)) b, trunc((rownum+2)/3)+mod((rownum+2),3) c
from dual
connect by level <= 1000000;
Table created.
Elapsed: 00:00:07.93
CREATE INDEX tst1 ON tst(b, c, a);
Index created.
Elapsed: 00:00:03.17
--CREATE INDEX tst2 ON tst(a, b, c);
--CREATE INDEX tst3 ON tst(a, c);
begin
dbms_stats.gather_table_stats(user,
'TST',
estimate_percent => null,
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
cascade => true);
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:12.84
select sum(a + c + tst2) result
from (select a,
c,
(select min(c)
from tst t2
where t2.a = tst.a
and t2.c > tst.c
and t2.b = 2
and exists (select c
from tst t3
where t3.b = 1
and t3.a = t2.a
and t3.c < t2.c)) tst2
from tst
where rownum < 1000);
RESULT
----------
64432
Elapsed: 00:01:09.53
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cxhf9vhdvxdnc, child number 0
-------------------------------------
select sum(a + c + tst2) result from (select a, c,
(select min(c) from tst t2 where t2.a =
tst.a and t2.c > tst.c and t2.b = 2
and exists (select c from tst t3
where t3.b = 1 and t3.a = t2.a
and t3.c < t2.c)) tst2 from tst
where rownum < 1000)
Plan hash value: 2135431802
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 999 | 1 | 999 |00:01:09.49 | 1115K|
| 2 | NESTED LOOPS | | 999 | 1 | 174 |00:01:09.48 | 1115K|
|* 3 | INDEX RANGE SCAN | TST1 | 999 | 1 | 344 |00:01:09.46 | 1114K|
|* 4 | INDEX RANGE SCAN | TST1 | 344 | 1 | 174 |00:00:00.02 | 693 |
| 5 | SORT AGGREGATE | | 1 | 1 | 1 |00:01:09.50 | 1115K|
| 6 | VIEW | | 1 | 999 | 999 |00:01:09.50 | 1115K|
|* 7 | COUNT STOPKEY | | 1 | | 999 |00:00:00.01 | 6 |
| 8 | TABLE ACCESS FULL| TST | 1 | 1000K| 999 |00:00:00.01 | 6 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."B"=2 AND "T2"."C">:B1 AND "T2"."A"=:B2 AND "T2"."C" IS NOT
NULL)
filter("T2"."A"=:B1)
4 - access("T3"."B"=1 AND "T3"."A"=:B1 AND "T3"."C"<"T2"."C")
filter(("T3"."A"=:B1 AND "T3"."A"="T2"."A"))
7 - filter(ROWNUM<1000)
35 rows selected.
Elapsed: 00:00:00.27
select sum(a + c + tst2) result
from (select tst.a, tst.c, min(t2.c) tst2
from (select a, c from tst where rownum < 1000) tst
left join tst t2
on t2.a = tst.a
and t2.c > tst.c
and t2.b = 2
and exists (select null
from tst t3
where t3.b = 1
and t3.a = t2.a
and t3.c < t2.c)
group by tst.a, tst.c);
RESULT
----------
64432
Elapsed: 00:01:29.04
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0zxg9x9jp8b4z, child number 0
-------------------------------------
select sum(a + c + tst2) result from (select tst.a, tst.c, min(t2.c) tst2 from (select a, c from
tst where rownum < 1000) tst left join tst t2 on t2.a = tst.a and t2.c >
tst.c and t2.b = 2 and exists (select null from tst t3
where t3.b = 1 and t3.a = t2.a and t3.c < t2.c) group by
tst.a, tst.c)
Plan hash value: 2041982616
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:01:29.03 | 1551K| | | |
| 2 | VIEW | | 1 | 7992 | 999 |00:01:29.03 | 1551K| | | |
| 3 | HASH GROUP BY | | 1 | 7992 | 999 |00:01:29.03 | 1551K| 904K| 904K| 1292K (0)|
| 4 | NESTED LOOPS OUTER | | 1 | 7992 | 1009 |00:01:29.02 | 1551K| | | |
| 5 | VIEW | | 1 | 999 | 999 |00:00:00.01 | 6 | | | |
|* 6 | COUNT STOPKEY | | 1 | | 999 |00:00:00.01 | 6 | | | |
| 7 | TABLE ACCESS FULL| TST | 1 | 1000K| 999 |00:00:00.01 | 6 | | | |
| 8 | VIEW | | 999 | 8 | 138 |00:01:29.01 | 1551K| | | |
|* 9 | HASH JOIN SEMI | | 999 | 8 | 138 |00:01:29.01 | 1551K| 1023K| 1023K| 338K (0)|
|* 10 | INDEX RANGE SCAN | TST1 | 999 | 167 | 344 |00:01:04.32 | 1115K| | | |
|* 11 | TABLE ACCESS FULL| TST | 303 | 333K| 58M|00:00:00.01 | 435K| | | |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(ROWNUM<1000)
9 - access("T3"."A"="T2"."A")
filter("T3"."C"<"T2"."C")
10 - access("T2"."B"=2 AND "T2"."C">"TST"."C" AND "T2"."A"="TST"."A" AND "T2"."C" IS NOT NULL)
filter("T2"."A"="TST"."A")
11 - filter("T3"."B"=1)
36 rows selected.
Elapsed: 00:00:00.27
select sum(a + c + tst2) result
from (select tst.a,
tst.c,
case
when count(t3.a) > 0 then
min(t2.c)
else
null
end tst2
from (select a, c from tst where rownum < 1000) tst
left join tst t2
on t2.a = tst.a
and t2.c > tst.c
and t2.b = 2
left join tst t3
on t3.b = 1
and t3.a = t2.a
and t3.c < t2.c
group by tst.a, tst.c);
RESULT
----------
64432
Elapsed: 00:00:00.32
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gv5h9x8s1hpa6, child number 0
-------------------------------------
select sum(a + c + tst2) result from (select tst.a, tst.c, case
when count(t3.a) > 0 then min(t2.c) else null
end tst2 from (select a, c from tst where rownum < 1000) tst left join tst t2
on t2.a = tst.a and t2.c > tst.c and t2.b = 2 left join tst t3
on t3.b = 1 and t3.a = t2.a and t3.c < t2.c group by tst.a, tst.c)
Plan hash value: 2010606776
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.30 | 4978 | | | |
| 2 | VIEW | | 1 | 999 | 999 |00:00:00.30 | 4978 | | | |
| 3 | HASH GROUP BY | | 1 | 999 | 999 |00:00:00.30 | 4978 | 869K| 869K| 1215K (0)|
|* 4 | HASH JOIN OUTER | | 1 | 999 | 1076 |00:00:00.30 | 4978 | 1114K| 1114K| 1261K (0)|
|* 5 | HASH JOIN OUTER | | 1 | 999 | 1040 |00:00:00.15 | 2492 | 1269K| 1269K| 1292K (0)|
| 6 | VIEW | | 1 | 999 | 999 |00:00:00.01 | 6 | | | |
|* 7 | COUNT STOPKEY | | 1 | | 999 |00:00:00.01 | 6 | | | |
| 8 | TABLE ACCESS FULL| TST | 1 | 1000K| 999 |00:00:00.01 | 6 | | | |
|* 9 | TABLE ACCESS FULL | TST | 1 | 333K| 333K|00:00:00.01 | 2486 | | | |
|* 10 | TABLE ACCESS FULL | TST | 1 | 333K| 333K|00:00:00.01 | 2486 | | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T3"."A"="T2"."A")
filter("T3"."C"<"T2"."C")
5 - access("T2"."A"="TST"."A")
filter("T2"."C">"TST"."C")
7 - filter(ROWNUM<1000)
9 - filter("T2"."B"=2)
10 - filter("T3"."B"=1)
36 rows selected.
Elapsed: 00:00:00.12