Skip to content

Commit dbc770a

Browse files
nigelbaylissnigelbayliss
committed
add fk
1 parent adce454 commit dbc770a

File tree

5 files changed

+129
-124
lines changed

5 files changed

+129
-124
lines changed

‎optimizer/autonomous/hints/plan.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,4 @@
1+
set trims on
12
set linesize 200
23
set tab off
34
set pagesize 1000

‎optimizer/autonomous/hints/q1.lst

Lines changed: 39 additions & 38 deletions
Original file line numberDiff line numberDiff line change
@@ -9,51 +9,52 @@ SUM(T1.NUM) SUM(T2.NUM)
99

1010
SQL>
1111
SQL> @plan
12+
SQL> set trims on
1213
SQL> set linesize 200
1314
SQL> set tab off
1415
SQL> set pagesize 1000
1516
SQL> column plan_table_output format a180
16-
SQL>
17+
SQL>
1718
SQL> SELECT *
1819
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL'));
1920

20-
PLAN_TABLE_OUTPUT
21+
PLAN_TABLE_OUTPUT
2122
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
22-
SQL_ID 60t3bw2rtn35d, child number 6
23-
-------------------------------------
24-
select sum(t1.num), sum(t2.num) from table1 t1 join table2 t2 on
25-
(t1.id = t2.id)
26-
27-
Plan hash value: 339338377
28-
29-
---------------------------------------------------------------------------------------------------
30-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
31-
---------------------------------------------------------------------------------------------------
32-
| 0 | SELECT STATEMENT | | | | 4 (100)| |
33-
| 1 | RESULT CACHE | 86m6ud7jmfq443pumuj63z1bmd | | | | |
34-
| 2 | SORT AGGREGATE | | 1 | 52 | | |
35-
|* 3 | HASH JOIN | | 1 | 52 | 4 (0)| 00:00:01 |
36-
| 4 | TABLE ACCESS FULL| TABLE2 | 1 | 26 | 2 (0)| 00:00:01 |
37-
| 5 | TABLE ACCESS FULL| TABLE1 | 1000 | 26000 | 2 (0)| 00:00:01 |
38-
---------------------------------------------------------------------------------------------------
39-
40-
Predicate Information (identified by operation id):
41-
---------------------------------------------------
42-
43-
3 - access("T1"."ID"="T2"."ID")
44-
45-
Result Cache Information (identified by operation id):
46-
------------------------------------------------------
47-
48-
1 -
49-
50-
Note
51-
-----
52-
- dynamic statistics used: dynamic sampling (level=2)
53-
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
54-
55-
56-
33 rows selected.
23+
SQL_ID 60t3bw2rtn35d, child number 6
24+
-------------------------------------
25+
select sum(t1.num), sum(t2.num) from table1 t1 join table2 t2 on
26+
(t1.id = t2.id)
5727

58-
SQL>
28+
Plan hash value: 339338377
29+
30+
---------------------------------------------------------------------------------------------------
31+
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
32+
---------------------------------------------------------------------------------------------------
33+
| 0 | SELECT STATEMENT | | | | 4 (100)| |
34+
| 1 | RESULT CACHE | db11srrdf8ar4d06x4b1j674pp | | | | |
35+
| 2 | SORT AGGREGATE | | 1 | 52 | | |
36+
|* 3 | HASH JOIN | | 1 | 52 | 4 (0)| 00:00:01 |
37+
| 4 | TABLE ACCESS FULL| TABLE2 | 1 | 26 | 2 (0)| 00:00:01 |
38+
| 5 | TABLE ACCESS FULL| TABLE1 | 1000 | 26000 | 2 (0)| 00:00:01 |
39+
---------------------------------------------------------------------------------------------------
40+
41+
Predicate Information (identified by operation id):
42+
---------------------------------------------------
43+
44+
3 - access("T1"."ID"="T2"."ID")
45+
46+
Result Cache Information (identified by operation id):
47+
------------------------------------------------------
48+
49+
1 -
50+
51+
Note
52+
-----
53+
- dynamic statistics used: dynamic sampling (level=2)
54+
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
55+
56+
57+
33 rows selected.
58+
59+
SQL>
5960
SQL> spool off

‎optimizer/autonomous/hints/q2.lst

Lines changed: 41 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -8,53 +8,54 @@ SUM(T1.NUM) SUM(T2.NUM)
88
----------- -----------
99
1 10
1010

11-
SQL>
11+
SQL>
1212
SQL> @plan
13+
SQL> set trims on
1314
SQL> set linesize 200
1415
SQL> set tab off
1516
SQL> set pagesize 1000
1617
SQL> column plan_table_output format a180
17-
SQL>
18+
SQL>
1819
SQL> SELECT *
1920
2 FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL'));
2021

21-
PLAN_TABLE_OUTPUT
22+
PLAN_TABLE_OUTPUT
2223
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
23-
SQL_ID 1c899cktncv5m, child number 0
24-
-------------------------------------
25-
select /*+ LEADING(t1 t2) USE_NL(t2) */ sum(t1.num), sum(t2.num)
26-
from table1 t1 join table2 t2 on (t1.id = t2.id)
27-
28-
Plan hash value: 339338377
29-
30-
---------------------------------------------------------------------------------------------------
31-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
32-
---------------------------------------------------------------------------------------------------
33-
| 0 | SELECT STATEMENT | | | | 4 (100)| |
34-
| 1 | RESULT CACHE | 86m6ud7jmfq443pumuj63z1bmd | | | | |
35-
| 2 | SORT AGGREGATE | | 1 | 52 | | |
36-
|* 3 | HASH JOIN | | 1 | 52 | 4 (0)| 00:00:01 |
37-
| 4 | TABLE ACCESS FULL| TABLE2 | 1 | 26 | 2 (0)| 00:00:01 |
38-
| 5 | TABLE ACCESS FULL| TABLE1 | 1000 | 26000 | 2 (0)| 00:00:01 |
39-
---------------------------------------------------------------------------------------------------
40-
41-
Predicate Information (identified by operation id):
42-
---------------------------------------------------
43-
44-
3 - access("T1"."ID"="T2"."ID")
45-
46-
Result Cache Information (identified by operation id):
47-
------------------------------------------------------
48-
49-
1 -
50-
51-
Note
52-
-----
53-
- dynamic statistics used: dynamic sampling (level=2)
54-
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
55-
56-
57-
33 rows selected.
58-
59-
SQL>
24+
SQL_ID 1c899cktncv5m, child number 0
25+
-------------------------------------
26+
select /*+ LEADING(t1 t2) USE_NL(t2) */ sum(t1.num), sum(t2.num)
27+
from table1 t1 join table2 t2 on (t1.id = t2.id)
28+
29+
Plan hash value: 339338377
30+
31+
---------------------------------------------------------------------------------------------------
32+
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
33+
---------------------------------------------------------------------------------------------------
34+
| 0 | SELECT STATEMENT | | | | 4 (100)| |
35+
| 1 | RESULT CACHE | db11srrdf8ar4d06x4b1j674pp | | | | |
36+
| 2 | SORT AGGREGATE | | 1 | 52 | | |
37+
|* 3 | HASH JOIN | | 1 | 52 | 4 (0)| 00:00:01 |
38+
| 4 | TABLE ACCESS FULL| TABLE2 | 1 | 26 | 2 (0)| 00:00:01 |
39+
| 5 | TABLE ACCESS FULL| TABLE1 | 1000 | 26000 | 2 (0)| 00:00:01 |
40+
---------------------------------------------------------------------------------------------------
41+
42+
Predicate Information (identified by operation id):
43+
---------------------------------------------------
44+
45+
3 - access("T1"."ID"="T2"."ID")
46+
47+
Result Cache Information (identified by operation id):
48+
------------------------------------------------------
49+
50+
1 -
51+
52+
Note
53+
-----
54+
- dynamic statistics used: dynamic sampling (level=2)
55+
- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation
56+
57+
58+
33 rows selected.
59+
60+
SQL>
6061
SQL> spool off

0 commit comments

Comments
 (0)