So, something else I learned recently...
rajesh@10GR2> create table t
2 as
3 select *
4 from all_objects;
Table created.
Elapsed: 00:00:02.68
rajesh@10GR2>
rajesh@10GR2> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.92
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> with data as
2 (
3 select owner,count(*) as t_cnt
4 from t
5 group by owner )
6 select t.owner,d.t_cnt
7 from t, data d
8 where t.owner = d.owner;
56395 rows selected.
Elapsed: 00:00:01.43
Execution Plan
----------------------------------------------------------
Plan hash value: 3127217490
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56395 | 1982K| 361 (2)| 00:00:05 |
|* 1 | HASH JOIN | | 56395 | 1982K| 361 (2)| 00:00:05 |
| 2 | VIEW | | 26 | 780 | 182 (3)| 00:00:03 |
| 3 | HASH GROUP BY | | 26 | 156 | 182 (3)| 00:00:03 |
| 4 | TABLE ACCESS FULL| T | 56395 | 330K| 178 (1)| 00:00:03 |
| 5 | TABLE ACCESS FULL | T | 56395 | 330K| 178 (1)| 00:00:03 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."OWNER"="D"."OWNER")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1948 consistent gets
0 physical reads
0 redo size
670479 bytes sent via SQL*Net to client
4521 bytes received via SQL*Net from client
377 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
56395 rows processed.
The Recursive call is due to Hard parsing and will be removed due to repeated execution.
rajesh@10GR2> /
56395 rows selected.
Elapsed: 00:00:01.43
Execution Plan
----------------------------------------------------------
Plan hash value: 3127217490
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56395 | 1982K| 361 (2)| 00:00:05 |
|* 1 | HASH JOIN | | 56395 | 1982K| 361 (2)| 00:00:05 |
| 2 | VIEW | | 26 | 780 | 182 (3)| 00:00:03 |
| 3 | HASH GROUP BY | | 26 | 156 | 182 (3)| 00:00:03 |
| 4 | TABLE ACCESS FULL| T | 56395 | 330K| 178 (1)| 00:00:03 |
| 5 | TABLE ACCESS FULL | T | 56395 | 330K| 178 (1)| 00:00:03 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."OWNER"="D"."OWNER")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1948 consistent gets
0 physical reads
0 redo size
670479 bytes sent via SQL*Net to client
4521 bytes received via SQL*Net from client
377 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
56395 rows processed
Now, see the use of Oracle undocumented “materialize” hint in the “WITH clause”. The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the “WITH” clause. You may find a faster execution plan by using Global Temporary tables, depending on your release of Oracle
rajesh@10GR2> with data as
2 (
3 select /*+ materialize */ owner,count(*) as t_cnt
4 from t
5 group by owner )
6 select t.owner,d.t_cnt
7 from t, data d
8 where t.owner = d.owner;
56395 rows selected.
Elapsed: 00:00:01.50
Execution Plan
----------------------------------------------------------
Plan hash value: 3247137680
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56395 | 1982K| 363 (2)| 00:00:05 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | HASH GROUP BY | | 26 | 156 | 182 (3)| 00:00:03 |
| 4 | TABLE ACCESS FULL | T | 56395 | 330K| 178 (1)| 00:00:03 |
|* 5 | HASH JOIN | | 56395 | 1982K| 181 (2)| 00:00:03 |
| 6 | VIEW | | 26 | 780 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66C8_555A764 | 26 | 156 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | T | 56395 | 330K| 178 (1)| 00:00:03 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T"."OWNER"="D"."OWNER")
Statistics
----------------------------------------------------------
228 recursive calls
10 db block gets
2023 consistent gets
1 physical reads
1428 redo size
670479 bytes sent via SQL*Net to client
4521 bytes received via SQL*Net from client
377 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
56395 rows processed
rajesh@10GR2> /
56395 rows selected.
Elapsed: 00:00:01.73
Execution Plan
----------------------------------------------------------
Plan hash value: 3152285844
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56395 | 1982K| 363 (2)| 00:00:05 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | HASH GROUP BY | | 26 | 156 | 182 (3)| 00:00:03 |
| 4 | TABLE ACCESS FULL | T | 56395 | 330K| 178 (1)| 00:00:03 |
|* 5 | HASH JOIN | | 56395 | 1982K| 181 (2)| 00:00:03 |
| 6 | VIEW | | 26 | 780 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66C9_555A764 | 26 | 156 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | T | 56395 | 330K| 178 (1)| 00:00:03 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T"."OWNER"="D"."OWNER")
Statistics
----------------------------------------------------------
2 recursive calls
8 db block gets
1953 consistent gets
1 physical reads
648 redo size
670479 bytes sent via SQL*Net to client
4521 bytes received via SQL*Net from client
377 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
56395 rows processed
rajesh@10GR2>
No comments:
Post a Comment