with time series data, this would not be true:
I mean how many times do you have a large master table that every record in a dimension joins to the same master? the rest of the master records have no representation in the dimension?
I see now you were setting up a super exaggerated example. Your real world case would be "a single very fat dimension record is joined to thousands of fact records and we don't need the fat dimension record in processing the fact records - but since we join they get thrown in to the sort anyway and blow out temp"
Here is a potential 'technique' for such a thing, scalar subqueries. We can process tdetail_ref_many and then pick up the master row after processing and sorting it. Here is a comparision of the estimated temp space for "just a join" and "scalar subquery"
ops$tkyte%ORA11GR2> create or replace type myScalarType as object
2 (
3 id number ,
4 t1 varchar2(4000),
5 t2 varchar2(4000),
6 t3 varchar2(4000),
7 t4 varchar2(4000),
8 t5 varchar2(4000),
9 t6 varchar2(4000),
10 t7 varchar2(4000),
11 t8 varchar2(4000),
12 t9 varchar2(4000)
13 );
14 /
Type created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from tmaster m ,tdetail_ref_one d where m.id = d.masterid order by d.t;
Execution Plan
----------------------------------------------------------
Plan hash value: 3990881030
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90000 | 3095M| | 678K (1)|
| 1 | SORT ORDER BY | | 90000 | 3095M| 703M| 678K (1)|
| 2 | MERGE JOIN | | 90000 | 3095M| | 4324 (1)|
| 3 | TABLE ACCESS BY INDEX ROWID| TMASTER | 3000 | 103M| | 3008 (1)|
| 4 | INDEX FULL SCAN | SYS_C0047219 | 3000 | | | 7 (0)|
|* 5 | SORT JOIN | | 90000 | 4394K| 10M| 1316 (1)|
| 6 | TABLE ACCESS FULL | TDETAIL_REF_ONE | 90000 | 4394K| | 205 (1)|
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("M"."ID"="D"."MASTERID")
filter("M"."ID"="D"."MASTERID")
ops$tkyte%ORA11GR2> select d.*, (select myScalarType( id, t1, t2, t3, t4, t5, t6, t7, t8, t9) from tmaster where tmaster.id = d.id)
2 from tdetail_ref_many d
3 order by d.t
4 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2022710602
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90000 | 4482K| | 1350 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID| TMASTER | 1 | 36013 | | 2 (0)|
|* 2 | INDEX UNIQUE SCAN | SYS_C0047219 | 1 | | | 1 (0)|
| 3 | SORT ORDER BY | | 90000 | 4482K| 5680K| 1350 (1)|
| 4 | TABLE ACCESS FULL | TDETAIL_REF_MANY | 90000 | 4482K| | 205 (1)|
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TMASTER"."ID"=:B1)
ops$tkyte%ORA11GR2> set autotrace off
of course, if you needed some attributes from tmaster to process tdetail-ref-many by - you would/could do this:
ops$tkyte%ORA11GR2> select d.*, (select myScalarType( id, t1, t2, t3, t4, t5, t6, t7, t8, t9) from tmaster where tmaster.id = d.id)
2 from tdetail_ref_many d, tmaster m
3 where d.id = m.id
4 and m.t3 = 'abc...'
5 order by d.t
6 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1760991612
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4056 | 3214 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID | TMASTER | 1 | 36013 | 2 (0)|
|* 2 | INDEX UNIQUE SCAN | SYS_C0047219 | 1 | | 1 (0)|
| 3 | SORT ORDER BY | | 1 | 4056 | 3214 (1)|
| 4 | NESTED LOOPS | | | | |
| 5 | NESTED LOOPS | | 1 | 4056 | 3213 (1)|
| 6 | TABLE ACCESS FULL | TDETAIL_REF_MANY | 90000 | 4482K| 205 (1)|
|* 7 | INDEX UNIQUE SCAN | SYS_C0047219 | 1 | | 0 (0)|
|* 8 | TABLE ACCESS BY INDEX ROWID| TMASTER | 1 | 4005 | 1 (0)|
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TMASTER"."ID"=:B1)
7 - access("D"."ID"="M"."ID")
8 - filter("M"."T3"='abc...')
Of course: If Tom Kyte doesn't know about it then we can safely assume it doesn't exist, at least not in the current version :-) that is FALSE :) I learn something new about Oracle just about every single day.