exec dbms_random.seed ( 0 )
Statement processed.
create table t pctfree 0 as
select level c1, lpad ( 'x', 10, 'x' ) stuff from dual
connect by level <= 1000
Table created.
create table tchild1 pctfree 0 enable row movement as
select c1, c2, round ( dbms_random.value ( 0, 100 ) ) c3,
trunc ( sysdate, 'y' ) + ( rownum / 240 ) c4,
lpad ( 'x', 10, 'x' ) stuff
from t
cross join (
select mod ( level, 17 ) c2
from dual
connect by level <= 100
)
order by dbms_random.value
Table created.
create table tchild2 pctfree 0 enable row movement as
select c1, c2, round ( dbms_random.value ( 0, 100 ) ) c3,
trunc ( sysdate, 'y' ) + ( rownum / 240 ) c4,
lpad ( 'x', 10, 'x' ) stuff
from t
cross join (
select mod ( level, 17 ) c2
from dual
connect by level <= 100
)
order by dbms_random.value
Table created.
alter table tchild2 shrink space
Table altered.
alter table tchild1 shrink space
Table altered.
select count(*) from tchild1
COUNT(*) | 100000 |
---|
select count(*) from tchild2
COUNT(*) | 100000 |
---|
select count(*) from t
COUNT(*) | 1000 |
---|
select *
from t
where c1 <= 100
C1 | STUFF | 1 | xxxxxxxxxx | 2 | xxxxxxxxxx | 3 | xxxxxxxxxx | 4 | xxxxxxxxxx | 5 | xxxxxxxxxx | 6 | xxxxxxxxxx | 7 | xxxxxxxxxx | 8 | xxxxxxxxxx | 9 | xxxxxxxxxx | 10 | xxxxxxxxxx | 11 | xxxxxxxxxx | 12 | xxxxxxxxxx | 13 | xxxxxxxxxx | 14 | xxxxxxxxxx | 15 | xxxxxxxxxx | 16 | xxxxxxxxxx | 17 | xxxxxxxxxx | 18 | xxxxxxxxxx | 19 | xxxxxxxxxx | 20 | xxxxxxxxxx | 21 | xxxxxxxxxx | 22 | xxxxxxxxxx | 23 | xxxxxxxxxx | 24 | xxxxxxxxxx | 25 | xxxxxxxxxx | 26 | xxxxxxxxxx | 27 | xxxxxxxxxx | 28 | xxxxxxxxxx | 29 | xxxxxxxxxx | 30 | xxxxxxxxxx | 31 | xxxxxxxxxx | 32 | xxxxxxxxxx | 33 | xxxxxxxxxx | 34 | xxxxxxxxxx | 35 | xxxxxxxxxx | 36 | xxxxxxxxxx | 37 | xxxxxxxxxx | 38 | xxxxxxxxxx | 39 | xxxxxxxxxx | 40 | xxxxxxxxxx | 41 | xxxxxxxxxx | 42 | xxxxxxxxxx | 43 | xxxxxxxxxx | 44 | xxxxxxxxxx | 45 | xxxxxxxxxx | 46 | xxxxxxxxxx | 47 | xxxxxxxxxx | 48 | xxxxxxxxxx | 49 | xxxxxxxxxx | 50 | xxxxxxxxxx |
---|
begin
for rws in (
select /*+ gather_plan_statistics */c1, rownum,
( select count ( distinct c2 )
from tchild1 c
where t.c1 = c.c1 ) dist_c2,
( select avg ( c3 )
from tchild2 c
where t.c1 = c.c1 ) mean_c3,
( select max ( c4 )
from tchild1 c
where t.c1 = c.c1 ) max_c4,
( select min ( c4 )
from tchild2 c
where t.c1 = c.c1 ) min_c4
from t
where c1 <= 100
) loop
null;
end loop;
end;
Statement processed.
select *
from dbms_xplan.display_cursor ( '2wgmsabxdbhby', null, 'IOSTATS LAST')
PLAN_TABLE_OUTPUT | SQL_ID 2wgmsabxdbhby, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */C1, ROWNUM, ( SELECT COUNT ( | DISTINCT C2 ) FROM TCHILD1 C WHERE T.C1 = C.C1 ) DIST_C2, ( SELECT AVG | ( C3 ) FROM TCHILD2 C WHERE T.C1 = C.C1 ) MEAN_C3, ( SELECT MAX ( C4 ) | FROM TCHILD1 C WHERE T.C1 = C.C1 ) MAX_C4, ( SELECT MIN ( C4 ) FROM | TCHILD2 C WHERE T.C1 = C.C1 ) MIN_C4 FROM T WHERE C1 <= 100 | Plan hash value: 2227323319 | ---------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | ---------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.01 | 6 | | | 1 | SORT GROUP BY | | 100 | 1 | 100 |00:00:00.55 | 42600 | | |* 2 | TABLE ACCESS FULL| TCHILD1 | 100 | 100 | 10000 |00:00:00.53 | 42600 | | | 3 | SORT AGGREGATE | | 100 | 1 | 100 |00:00:00.58 | 42600 | | |* 4 | TABLE ACCESS FULL| TCHILD2 | 100 | 100 | 10000 |00:00:00.61 | 42600 | | | 5 | SORT AGGREGATE | | 100 | 1 | 100 |00:00:00.58 | 42600 | | |* 6 | TABLE ACCESS FULL| TCHILD1 | 100 | 100 | 10000 |00:00:00.55 | 42600 | | | 7 | SORT AGGREGATE | | 100 | 1 | 100 |00:00:00.58 | 42600 | | |* 8 | TABLE ACCESS FULL| TCHILD2 | 100 | 100 | 10000 |00:00:00.62 | 42600 | | | 9 | COUNT | | 1 | | 100 |00:00:00.01 | 6 | | |* 10 | TABLE ACCESS FULL| T | 1 | 100 | 100 |00:00:00.01 | 6 | | ---------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("C"."C1"=:B1) | 4 - filter("C"."C1"=:B1) | 6 - filter("C"."C1"=:B1) | 8 - filter("C"."C1"=:B1) | 10 - filter("C1"<=100) |
---|
select store_name, o.*
from co.stores s
cross apply (
select * from co.orders o
where s.store_id = o.store_id
order by o.order_datetime desc
fetch first 3 rows only
) o
order by store_name
fetch first 20 rows only
STORE_NAME | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | Bejing | 1912 | 30-MAR-19 11.05.48.922577 AM | 195 | COMPLETE | 21 | Bejing | 1908 | 29-MAR-19 06.46.08.118387 PM | 224 | COMPLETE | 21 | Bejing | 1911 | 30-MAR-19 09.18.13.207605 AM | 311 | COMPLETE | 21 | Bengaluru | 1831 | 14-MAR-19 08.32.36.013193 AM | 187 | COMPLETE | 13 | Bengaluru | 1791 | 06-MAR-19 01.45.58.672579 PM | 332 | COMPLETE | 13 | Bengaluru | 1793 | 06-MAR-19 11.17.49.990617 PM | 158 | COMPLETE | 13 | Berlin | 1817 | 10-MAR-19 09.54.25.125750 PM | 8 | COMPLETE | 8 | Berlin | 1623 | 03-FEB-19 11.37.12.453316 AM | 211 | COMPLETE | 8 | Berlin | 1795 | 07-MAR-19 05.43.17.990211 AM | 240 | COMPLETE | 8 | Bucharest | 1890 | 25-MAR-19 11.31.58.636926 PM | 7 | COMPLETE | 7 | Bucharest | 1665 | 11-FEB-19 12.40.18.478950 PM | 94 | COMPLETE | 7 | Bucharest | 1736 | 25-FEB-19 03.47.12.996505 AM | 239 | COMPLETE | 7 | Buenos Aires | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | Buenos Aires | 1928 | 04-APR-19 03.24.06.034344 PM | 367 | COMPLETE | 19 | Buenos Aires | 1930 | 04-APR-19 08.24.44.505645 PM | 338 | COMPLETE | 19 | Chicago | 1794 | 07-MAR-19 02.59.15.029147 AM | 237 | COMPLETE | 5 | Chicago | 1644 | 07-FEB-19 11.17.45.806853 AM | 179 | COMPLETE | 5 | Chicago | 1718 | 20-FEB-19 04.19.43.190304 PM | 179 | COMPLETE | 5 | Johannesburg | 1780 | 04-MAR-19 06.29.25.910248 AM | 272 | CANCELLED | 11 | Johannesburg | 1641 | 07-FEB-19 01.15.19.464639 AM | 388 | COMPLETE | 11 |
---|
select store_name, o.*
from co.stores s,
lateral (
select * from co.orders o
where s.store_id = o.store_id
order by o.order_datetime desc
fetch first 3 rows only
) o
order by store_name
fetch first 20 rows only
STORE_NAME | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | Bejing | 1912 | 30-MAR-19 11.05.48.922577 AM | 195 | COMPLETE | 21 | Bejing | 1908 | 29-MAR-19 06.46.08.118387 PM | 224 | COMPLETE | 21 | Bejing | 1911 | 30-MAR-19 09.18.13.207605 AM | 311 | COMPLETE | 21 | Bengaluru | 1831 | 14-MAR-19 08.32.36.013193 AM | 187 | COMPLETE | 13 | Bengaluru | 1791 | 06-MAR-19 01.45.58.672579 PM | 332 | COMPLETE | 13 | Bengaluru | 1793 | 06-MAR-19 11.17.49.990617 PM | 158 | COMPLETE | 13 | Berlin | 1817 | 10-MAR-19 09.54.25.125750 PM | 8 | COMPLETE | 8 | Berlin | 1623 | 03-FEB-19 11.37.12.453316 AM | 211 | COMPLETE | 8 | Berlin | 1795 | 07-MAR-19 05.43.17.990211 AM | 240 | COMPLETE | 8 | Bucharest | 1890 | 25-MAR-19 11.31.58.636926 PM | 7 | COMPLETE | 7 | Bucharest | 1665 | 11-FEB-19 12.40.18.478950 PM | 94 | COMPLETE | 7 | Bucharest | 1736 | 25-FEB-19 03.47.12.996505 AM | 239 | COMPLETE | 7 | Buenos Aires | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | Buenos Aires | 1928 | 04-APR-19 03.24.06.034344 PM | 367 | COMPLETE | 19 | Buenos Aires | 1930 | 04-APR-19 08.24.44.505645 PM | 338 | COMPLETE | 19 | Chicago | 1794 | 07-MAR-19 02.59.15.029147 AM | 237 | COMPLETE | 5 | Chicago | 1644 | 07-FEB-19 11.17.45.806853 AM | 179 | COMPLETE | 5 | Chicago | 1718 | 20-FEB-19 04.19.43.190304 PM | 179 | COMPLETE | 5 | Johannesburg | 1780 | 04-MAR-19 06.29.25.910248 AM | 272 | CANCELLED | 11 | Johannesburg | 1641 | 07-FEB-19 01.15.19.464639 AM | 388 | COMPLETE | 11 |
---|
select store_name, o.*
from co.stores s
cross join lateral (
select * from co.orders o
where s.store_id = o.store_id
order by o.order_datetime desc
fetch first 3 rows only
) o
order by store_name
fetch first 20 rows only
STORE_NAME | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | Bejing | 1912 | 30-MAR-19 11.05.48.922577 AM | 195 | COMPLETE | 21 | Bejing | 1908 | 29-MAR-19 06.46.08.118387 PM | 224 | COMPLETE | 21 | Bejing | 1911 | 30-MAR-19 09.18.13.207605 AM | 311 | COMPLETE | 21 | Bengaluru | 1831 | 14-MAR-19 08.32.36.013193 AM | 187 | COMPLETE | 13 | Bengaluru | 1791 | 06-MAR-19 01.45.58.672579 PM | 332 | COMPLETE | 13 | Bengaluru | 1793 | 06-MAR-19 11.17.49.990617 PM | 158 | COMPLETE | 13 | Berlin | 1817 | 10-MAR-19 09.54.25.125750 PM | 8 | COMPLETE | 8 | Berlin | 1623 | 03-FEB-19 11.37.12.453316 AM | 211 | COMPLETE | 8 | Berlin | 1795 | 07-MAR-19 05.43.17.990211 AM | 240 | COMPLETE | 8 | Bucharest | 1890 | 25-MAR-19 11.31.58.636926 PM | 7 | COMPLETE | 7 | Bucharest | 1665 | 11-FEB-19 12.40.18.478950 PM | 94 | COMPLETE | 7 | Bucharest | 1736 | 25-FEB-19 03.47.12.996505 AM | 239 | COMPLETE | 7 | Buenos Aires | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | Buenos Aires | 1928 | 04-APR-19 03.24.06.034344 PM | 367 | COMPLETE | 19 | Buenos Aires | 1930 | 04-APR-19 08.24.44.505645 PM | 338 | COMPLETE | 19 | Chicago | 1794 | 07-MAR-19 02.59.15.029147 AM | 237 | COMPLETE | 5 | Chicago | 1644 | 07-FEB-19 11.17.45.806853 AM | 179 | COMPLETE | 5 | Chicago | 1718 | 20-FEB-19 04.19.43.190304 PM | 179 | COMPLETE | 5 | Johannesburg | 1780 | 04-MAR-19 06.29.25.910248 AM | 272 | CANCELLED | 11 | Johannesburg | 1641 | 07-FEB-19 01.15.19.464639 AM | 388 | COMPLETE | 11 |
---|
select store_name, o.*
from co.stores s
cross apply (
select * from co.orders o
where s.store_id = o.store_id
order by o.order_datetime desc
fetch first 3 rows only
) o
where store_name like 'M%'
order by store_name
fetch first 20 rows only
STORE_NAME | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | Madrid | 1730 | 23-FEB-19 02.34.28.490023 AM | 10 | COMPLETE | 10 | Madrid | 1712 | 19-FEB-19 11.07.47.707259 PM | 213 | COMPLETE | 10 | Madrid | 1691 | 15-FEB-19 07.45.43.436385 AM | 358 | COMPLETE | 10 | Mexico City | 1931 | 05-APR-19 12.26.49.495590 AM | 136 | COMPLETE | 20 | Mexico City | 1922 | 02-APR-19 03.52.49.213390 PM | 339 | COMPLETE | 20 | Mexico City | 1929 | 04-APR-19 08.14.44.084942 PM | 165 | REFUNDED | 20 | Mumbai | 1896 | 27-MAR-19 10.05.21.921377 PM | 333 | COMPLETE | 14 | Mumbai | 1855 | 18-MAR-19 06.50.34.800957 AM | 391 | COMPLETE | 14 | Mumbai | 1811 | 09-MAR-19 05.31.53.231172 PM | 159 | COMPLETE | 14 |
---|
select store_name, o.*
from co.stores s
cross apply (
select * from co.orders o
where s.store_id = o.store_id
and o.order_datetime >= timestamp '2019-04-01 00:00:00'
order by o.order_datetime desc
fetch first 3 rows only
) o
order by store_name
fetch first 20 rows only
STORE_NAME | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | Buenos Aires | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | Buenos Aires | 1928 | 04-APR-19 03.24.06.034344 PM | 367 | COMPLETE | 19 | Buenos Aires | 1930 | 04-APR-19 08.24.44.505645 PM | 338 | COMPLETE | 19 | Mexico City | 1931 | 05-APR-19 12.26.49.495590 AM | 136 | COMPLETE | 20 | Mexico City | 1922 | 02-APR-19 03.52.49.213390 PM | 339 | COMPLETE | 20 | Mexico City | 1929 | 04-APR-19 08.14.44.084942 PM | 165 | REFUNDED | 20 | Online | 1923 | 03-APR-19 10.47.49.417719 AM | 354 | COMPLETE | 1 | S�o Paulo | 1926 | 03-APR-19 07.05.04.600442 PM | 366 | COMPLETE | 18 | Tel Aviv | 1950 | 12-APR-19 09.41.06.642641 PM | 348 | COMPLETE | 23 | Tel Aviv | 1948 | 11-APR-19 03.49.45.728115 AM | 319 | COMPLETE | 23 | Tel Aviv | 1949 | 11-APR-19 07.14.31.588169 PM | 348 | COMPLETE | 23 | Tokyo | 1945 | 10-APR-19 03.49.31.059138 PM | 341 | COMPLETE | 22 | Tokyo | 1938 | 07-APR-19 06.31.50.146426 PM | 370 | CANCELLED | 22 | Tokyo | 1937 | 07-APR-19 06.54.49.039864 AM | 312 | COMPLETE | 22 |
---|
select store_name, o.*
from co.stores s
outer apply (
select * from co.orders o
where s.store_id = o.store_id
and o.order_datetime >= timestamp '2019-04-01 00:00:00'
order by o.order_datetime desc
fetch first 3 rows only
) o
order by store_name
fetch first 20 rows only
STORE_NAME | ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | Bejing | - | - | - | - | - | Bengaluru | - | - | - | - | - | Berlin | - | - | - | - | - | Bucharest | - | - | - | - | - | Buenos Aires | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | Buenos Aires | 1928 | 04-APR-19 03.24.06.034344 PM | 367 | COMPLETE | 19 | Buenos Aires | 1930 | 04-APR-19 08.24.44.505645 PM | 338 | COMPLETE | 19 | Chicago | - | - | - | - | - | Johannesburg | - | - | - | - | - | Lagos | - | - | - | - | - | London | - | - | - | - | - | Madrid | - | - | - | - | - | Mexico City | 1931 | 05-APR-19 12.26.49.495590 AM | 136 | COMPLETE | 20 | Mexico City | 1922 | 02-APR-19 03.52.49.213390 PM | 339 | COMPLETE | 20 | Mexico City | 1929 | 04-APR-19 08.14.44.084942 PM | 165 | REFUNDED | 20 | Mumbai | - | - | - | - | - | New Dehli | - | - | - | - | - | New York City | - | - | - | - | - | Online | 1923 | 03-APR-19 10.47.49.417719 AM | 354 | COMPLETE | 1 | Perth | - | - | - | - | - |
---|
begin
for rws in (
select /*+ gather_plan_statistics */c1,
s1.*, s2.*
from t
cross apply (
select avg ( c3 ), min ( c4 )
from tchild2 c
where t.c1 = c.c1
) s1
cross apply (
select count ( distinct c2 ), max ( c4 )
from tchild1 c
where t.c1 = c.c1
) s2
where c1 <= 100
order by c1
) loop
null;
end loop;
end;
Statement processed.
select *
from dbms_xplan.display_cursor ( '1hvsah50dxbk9', null, 'IOSTATS LAST')
PLAN_TABLE_OUTPUT | SQL_ID 1hvsah50dxbk9, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */C1, S1.*, S2.* FROM T CROSS APPLY ( | SELECT AVG ( C3 ), MIN ( C4 ) FROM TCHILD2 C WHERE T.C1 = C.C1 ) S1 | CROSS APPLY ( SELECT COUNT ( DISTINCT C2 ), MAX ( C4 ) FROM TCHILD1 C | WHERE T.C1 = C.C1 ) S2 WHERE C1 <= 100 ORDER BY C1 | Plan hash value: 3581037868 | ---------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | ---------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.64 | 42929 | | | 1 | SORT ORDER BY | | 1 | 100 | 100 |00:00:00.64 | 42929 | | | 2 | NESTED LOOPS | | 1 | 100 | 100 |00:00:00.43 | 42929 | | |* 3 | HASH JOIN OUTER | | 1 | 100 | 100 |00:00:00.01 | 429 | | |* 4 | TABLE ACCESS FULL | T | 1 | 100 | 100 |00:00:00.01 | 4 | | | 5 | VIEW | VW_DCL_9B2A02E9 | 1 | 100 | 100 |00:00:00.01 | 425 | | | 6 | HASH GROUP BY | | 1 | 100 | 100 |00:00:00.01 | 425 | | |* 7 | TABLE ACCESS FULL| TCHILD2 | 1 | 10010 | 10000 |00:00:00.01 | 425 | | | 8 | VIEW | VW_LAT_9B2A02E9 | 100 | 1 | 100 |00:00:00.64 | 42500 | | | 9 | SORT GROUP BY | | 100 | 1 | 100 |00:00:00.64 | 42500 | | |* 10 | TABLE ACCESS FULL | TCHILD1 | 100 | 100 | 10000 |00:00:00.68 | 42500 | | ---------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("T"."C1"="ITEM_0") | 4 - filter("T"."C1"<=100) | 7 - filter("C"."C1"<=100) | 10 - filter("T"."C1"="C"."C1") |
---|
begin
for rws in (
select /*+ gather_plan_statistics */c1, min_c4, mean_c3,
count ( distinct c1.c2 ) cd, max ( c1.c4 ) mx
from (
select c1,
min ( c2.c4 ) min_c4, avg ( c2.c3 ) mean_c3
from t
left join tchild2 c2
using ( c1 )
where c1 <= 100
group by c1
) s
left join tchild1 c1
using ( c1 )
group by c1, min_c4, mean_c3
) loop
null;
end loop;
end;
Statement processed.
select *
from dbms_xplan.display_cursor ( '95wm0dfv7j6ar', null, 'IOSTATS LAST')
PLAN_TABLE_OUTPUT | SQL_ID 95wm0dfv7j6ar, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */C1, MIN_C4, MEAN_C3, COUNT ( | DISTINCT C1.C2 ) CD, MAX ( C1.C4 ) MX FROM ( SELECT C1, MIN ( C2.C4 ) | MIN_C4, AVG ( C2.C3 ) MEAN_C3 FROM T LEFT JOIN TCHILD2 C2 USING ( C1 ) | WHERE C1 <= 100 GROUP BY C1 ) S LEFT JOIN TCHILD1 C1 USING ( C1 ) GROUP | BY C1, MIN_C4, MEAN_C3 | Plan hash value: 598495560 | ------------------------------------------------------------------------------------------------ | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | ------------------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.40 | 854 | | | 1 | HASH GROUP BY | | 1 | 100 | 100 |00:00:00.40 | 854 | | | 2 | VIEW | VW_DAG_0 | 1 | 1203 | 1700 |00:00:00.40 | 854 | | | 3 | HASH GROUP BY | | 1 | 1203 | 1700 |00:00:00.40 | 854 | | | 4 | VIEW | VM_NWVW_1 | 1 | 10030 | 10000 |00:00:00.40 | 854 | | | 5 | HASH GROUP BY | | 1 | 10030 | 10000 |00:00:00.40 | 854 | | |* 6 | HASH JOIN OUTER | | 1 | 10030 | 1000K|00:00:00.16 | 854 | | |* 7 | HASH JOIN OUTER | | 1 | 1002 | 10000 |00:00:00.01 | 429 | | |* 8 | TABLE ACCESS FULL| T | 1 | 100 | 100 |00:00:00.01 | 4 | | |* 9 | TABLE ACCESS FULL| TCHILD1 | 1 | 10010 | 10000 |00:00:00.01 | 425 | | |* 10 | TABLE ACCESS FULL | TCHILD2 | 1 | 10010 | 10000 |00:00:00.01 | 425 | | ------------------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- | 6 - access("T"."C1"="C2"."C1") | 7 - access("T"."C1"="C1"."C1") | 8 - filter("T"."C1"<=100) | 9 - filter("C1"."C1"<=100) | 10 - filter("C2"."C1"<=100) |
---|
begin
for rws in (
select /*+ gather_plan_statistics */c1,
( select count ( distinct c2 )
from tchild1 c
where t.c1 = c.c1 ) dist_c2,
( select avg ( c3 )
from tchild2 c
where t.c1 = c.c1 ) mean_c3,
( select max ( c4 )
from tchild1 c
where t.c1 = c.c1 ) max_c4,
( select min ( c4 )
from tchild2 c
where t.c1 = c.c1 ) min_c4
from t
where c1 <= 100
) loop
null;
end loop;
end;
Statement processed.
select *
from dbms_xplan.display_cursor ( '9cb7yn0xw1dkx', null, 'IOSTATS LAST')
PLAN_TABLE_OUTPUT | SQL_ID 9cb7yn0xw1dkx, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */C1, ( SELECT COUNT ( DISTINCT C2 ) | FROM TCHILD1 C WHERE T.C1 = C.C1 ) DIST_C2, ( SELECT AVG ( C3 ) FROM | TCHILD2 C WHERE T.C1 = C.C1 ) MEAN_C3, ( SELECT MAX ( C4 ) FROM TCHILD1 | C WHERE T.C1 = C.C1 ) MAX_C4, ( SELECT MIN ( C4 ) FROM TCHILD2 C WHERE | T.C1 = C.C1 ) MIN_C4 FROM T WHERE C1 <= 100 | Plan hash value: 2804289939 | --------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | --------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.01 | 1279 | | | 1 | SORT GROUP BY | | 100 | 1 | 100 |00:00:00.44 | 42500 | | |* 2 | TABLE ACCESS FULL | TCHILD1 | 100 | 100 | 10000 |00:00:00.48 | 42500 | | |* 3 | HASH JOIN OUTER | | 1 | 100 | 100 |00:00:00.01 | 1279 | | |* 4 | HASH JOIN OUTER | | 1 | 100 | 100 |00:00:00.01 | 854 | | |* 5 | HASH JOIN OUTER | | 1 | 100 | 100 |00:00:00.01 | 429 | | |* 6 | TABLE ACCESS FULL | T | 1 | 100 | 100 |00:00:00.01 | 4 | | | 7 | VIEW | VW_SSQ_3 | 1 | 100 | 100 |00:00:00.01 | 425 | | | 8 | HASH GROUP BY | | 1 | 100 | 100 |00:00:00.01 | 425 | | |* 9 | TABLE ACCESS FULL| TCHILD2 | 1 | 10010 | 10000 |00:00:00.01 | 425 | | | 10 | VIEW | VW_SSQ_2 | 1 | 100 | 100 |00:00:00.01 | 425 | | | 11 | HASH GROUP BY | | 1 | 100 | 100 |00:00:00.01 | 425 | | |* 12 | TABLE ACCESS FULL | TCHILD1 | 1 | 10010 | 10000 |00:00:00.01 | 425 | | | 13 | VIEW | VW_SSQ_1 | 1 | 100 | 100 |00:00:00.01 | 425 | | | 14 | HASH GROUP BY | | 1 | 100 | 100 |00:00:00.01 | 425 | | |* 15 | TABLE ACCESS FULL | TCHILD2 | 1 | 10010 | 10000 |00:00:00.01 | 425 | | --------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("C"."C1"=:B1) | 3 - access("T"."C1"="ITEM_1") | 4 - access("T"."C1"="ITEM_2") | 5 - access("T"."C1"="ITEM_3") | 6 - filter("C1"<=100) | 9 - filter("C"."C1"<=100) | 12 - filter("C"."C1"<=100) | 15 - filter("C"."C1"<=100) |
---|
begin
for rws in (
select /*+ gather_plan_statistics */c1, c2, c3, c4,
count(*) over () rws,
(
select stuff from t
where t1.c1 = t.c1
)
from tchild1 t1
where t1.c1 <= 10
order by c1
) loop
null;
end loop;
end;
Statement processed.
select *
from dbms_xplan.display_cursor ( '9x13dr9yagud0', null, 'IOSTATS LAST')
PLAN_TABLE_OUTPUT | SQL_ID 9x13dr9yagud0, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */C1, C2, C3, C4, COUNT(*) OVER () | RWS, ( SELECT STUFF FROM T WHERE T1.C1 = T.C1 ) FROM TCHILD1 T1 WHERE | T1.C1 <= 10 ORDER BY C1 | Plan hash value: 1213069904 | ---------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | ---------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 426 | | |* 1 | TABLE ACCESS FULL | T | 10 | 1 | 10 |00:00:00.01 | 40 | | | 2 | WINDOW SORT | | 1 | 1001 | 1000 |00:00:00.01 | 426 | | |* 3 | TABLE ACCESS FULL| TCHILD1 | 1 | 1001 | 1000 |00:00:00.01 | 426 | | ---------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter("T"."C1"=:B1) | 3 - filter("T1"."C1"<=10) |
---|
create index i2 on tchild2 ( c2, c4 )
compress 1
Index created.
begin
for rws in (
select /*+ gather_plan_statistics */t1.c1, t1.c2, c3, c4,
count(*) over () rws,
case
when mod ( c1, 10 ) = 1 then (
select max ( c4 ) from tchild2 t2
where t1.c2 = t2.c2
)
end
from tchild1 t1
where t1.c4 <= date'2021-01-02'
order by t1.c1, t1.c2
) loop
null;
end loop;
end;
Statement processed.
select *
from dbms_xplan.display_cursor ( '6qjzyb51thsaw', null, 'IOSTATS LAST')
PLAN_TABLE_OUTPUT | SQL_ID 6qjzyb51thsaw, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */T1.C1, T1.C2, C3, C4, COUNT(*) OVER | () RWS, CASE WHEN MOD ( C1, 10 ) = 1 THEN ( SELECT MAX ( C4 ) FROM | TCHILD2 T2 WHERE T1.C2 = T2.C2 ) END FROM TCHILD1 T1 WHERE T1.C4 <= | DATE'2021-01-02' ORDER BY T1.C1, T1.C2 | Plan hash value: 3945560260 | -------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | -------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 240 |00:00:00.01 | 425 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | | | 2 | FIRST ROW | | 1 | 1 | 1 |00:00:00.01 | 2 | | |* 3 | INDEX RANGE SCAN (MIN/MAX)| I2 | 1 | 1 | 1 |00:00:00.01 | 2 | | | 4 | WINDOW SORT | | 1 | 240 | 240 |00:00:00.01 | 425 | | |* 5 | TABLE ACCESS FULL | TCHILD1 | 1 | 240 | 240 |00:00:00.01 | 425 | | -------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("T2"."C2"=:B1) | 5 - filter("T1"."C4"<=TO_DATE(' 2021-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) |
---|
begin
for rws in (
select /*+ gather_plan_statistics */t1.c1, t1.c2, t1.c3, t1.c4,
count(*) over () rws,
max ( t2.c4 )
from tchild1 t1
left join tchild2 t2
on t2.c2 = t1.c2
and mod ( t1.c1, 10 ) = 1
where t1.c4 <= date'2021-01-02'
group by t1.c1, t1.c2, t1.c3, t1.c4
order by t1.c1
) loop
null;
end loop;
end;
Statement processed.
select *
from dbms_xplan.display_cursor ( 'afr1h188fqznu', null, 'IOSTATS LAST')
PLAN_TABLE_OUTPUT | SQL_ID afr1h188fqznu, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */T1.C1, T1.C2, T1.C3, T1.C4, | COUNT(*) OVER () RWS, MAX ( T2.C4 ) FROM TCHILD1 T1 LEFT JOIN TCHILD2 | T2 ON T2.C2 = T1.C2 AND MOD ( T1.C1, 10 ) = 1 WHERE T1.C4 <= | DATE'2021-01-02' GROUP BY T1.C1, T1.C2, T1.C3, T1.C4 ORDER BY T1.C1 | Plan hash value: 579427724 | ------------------------------------------------------------------------------------------------------ | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | | ------------------------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 1 | | 240 |00:00:00.13 | 699 | 265 | | | 1 | WINDOW BUFFER | | 1 | 14118 | 240 |00:00:00.13 | 699 | 265 | | | 2 | SORT GROUP BY | | 1 | 14118 | 240 |00:00:00.13 | 699 | 265 | | |* 3 | HASH JOIN OUTER | | 1 | 14118 | 144K|00:00:04.59 | 699 | 265 | | |* 4 | TABLE ACCESS FULL | TCHILD1 | 1 | 240 | 240 |00:00:00.01 | 425 | 0 | | | 5 | INDEX FAST FULL SCAN| I2 | 1 | 100K| 100K|00:00:00.02 | 274 | 265 | | ------------------------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("T2"."C2"="T1"."C2" AND MOD("T1"."C1",10)=CASE WHEN ("T2"."C2" IS NOT NULL) | THEN 1 ELSE 1 END ) | 4 - filter("T1"."C4"<=TO_DATE(' 2021-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) | SQL_ID afr1h188fqznu, child number 1 | ------------------------------------- | SELECT /*+ gather_plan_statistics */T1.C1, T1.C2, T1.C3, T1.C4, | COUNT(*) OVER () RWS, MAX ( T2.C4 ) FROM TCHILD1 T1 LEFT JOIN TCHILD2 | T2 ON T2.C2 = T1.C2 AND MOD ( T1.C1, 10 ) = 1 WHERE T1.C4 <= | DATE'2021-01-02' GROUP BY T1.C1, T1.C2, T1.C3, T1.C4 ORDER BY T1.C1 | Plan hash value: 579427724 | ------------------------------------------------------------------------------------------------------ | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | | ------------------------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 1 | | 240 |00:00:00.08 | 699 | 265 | | | 1 | WINDOW BUFFER | | 1 | 14118 | 240 |00:00:00.08 | 699 | 265 | | | 2 | SORT GROUP BY | | 1 | 14118 | 240 |00:00:00.08 | 699 | 265 | | |* 3 | HASH JOIN OUTER | | 1 | 14118 | 144K|00:00:03.03 | 699 | 265 | | |* 4 | TABLE ACCESS FULL | TCHILD1 | 1 | 240 | 240 |00:00:00.01 | 425 | 0 | | | 5 | INDEX FAST FULL SCAN| I2 | 1 | 100K| 100K|00:00:00.01 | 274 | 265 | | ------------------------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- |
---|
create or replace function f ( p int )
return int as
val number;
begin
for i in 1 .. 250000 loop
val := ln ( exp ( i ) );
end loop;
return p;
end f;
Function created.
declare
start_time pls_integer;
begin
start_time := dbms_utility.get_time ();
for rws in (
with rws as (
select level x, 1 y from dual
connect by level <= 5
)
select /*+ gather_plan_statistics */* from rws
where 1 = f ( y )
) loop
null;
end loop;
dbms_output.put_line (
'Run time ' || to_char ( dbms_utility.get_time () - start_time, '9,990' )
);
start_time := dbms_utility.get_time ();
for rws in (
with rws as (
select level x, 1 y from dual
connect by level <= 5
)
select /*+ gather_plan_statistics */* from rws
where 1 = ( select f ( y ) from dual )
) loop
null;
end loop;
dbms_output.put_line (
'Run time ' || to_char ( dbms_utility.get_time () - start_time, '9,990' )
);
end;
Statement processed.
Run time 337
Run time 69
select *
from dbms_xplan.display_cursor ( 'ags52fggjq5pt', null, 'ROWSTATS LAST +PREDICATE')
PLAN_TABLE_OUTPUT | SQL_ID ags52fggjq5pt, child number 0 | ------------------------------------- | WITH RWS AS ( SELECT LEVEL X, 1 Y FROM DUAL CONNECT BY LEVEL <= 5 ) | SELECT /*+ gather_plan_statistics */* FROM RWS WHERE 1 = F ( Y ) | Plan hash value: 2403765415 | ------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | | ------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 5 | | |* 1 | VIEW | | 1 | 1 | 5 | | | 2 | CONNECT BY WITHOUT FILTERING| | 1 | | 5 | | | 3 | FAST DUAL | | 1 | 1 | 1 | | ------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter("F"("Y")=1) |
---|
select *
from dbms_xplan.display_cursor ( '6f7pnwp98qk32', null, 'ROWSTATS LAST +PREDICATE')
PLAN_TABLE_OUTPUT | SQL_ID 6f7pnwp98qk32, child number 0 | ------------------------------------- | WITH RWS AS ( SELECT LEVEL X, 1 Y FROM DUAL CONNECT BY LEVEL <= 5 ) | SELECT /*+ gather_plan_statistics */* FROM RWS WHERE 1 = ( SELECT F ( Y | ) FROM DUAL ) | Plan hash value: 2522534383 | -------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | | -------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 5 | | |* 1 | FILTER | | 1 | | 5 | | | 2 | VIEW | | 1 | 1 | 5 | | | 3 | CONNECT BY WITHOUT FILTERING| | 1 | | 5 | | | 4 | FAST DUAL | | 1 | 1 | 1 | | | 5 | FAST DUAL | | 1 | 1 | 1 | | -------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter(=1) |
---|