Shulou(Shulou.com)06/01 Report--

Test data:

SQL > create table test1 as select * from dba_objects;Table created.SQL > create table test2 as select * from user_objects;Table created.SQL > create table test3 as select * from dba_objects;Table created.

Collect statistics:

BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname = > 'SCOTT', tabname = >' TEST1', estimate_percent = > 100, method_opt = > 'for columns owner size repeat', no_invalidate = > FALSE Degree = > 4, granularity = > 'ALL', cascade = > TRUE) END; / PL/SQL procedure successfully completed.SQL > with t as (select t1.* from test1 T1 for Test2 T2 where t1.object_id=t2.object_id) select * from tmeme Test3 T3 where t.object_id=t3.object_id 11 rows selected.Execution Plan---Plan hash value: 2878 150729 Murray- -| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |- -- | 0 | SELECT STATEMENT | | 86999 | 16m | | 1605 (1) | 00:00:20 | | * 1 | HASH JOIN | 86999 | 16m | | 1605 (1) | 00:00:20 | | 2 | TABLE ACCESS FULL | TEST2 | 13 | 65 | 3 (0) | 00:00:01 | * 3 | HASH JOIN | | 86999 | 16m | 9352K | 1602 (1) | 00:00:20 | 4 | TABLE ACCESS FULL | TEST1 | 86997 | 8325K | | 347 (1) | 00:00:05 | 5 | TABLE ACCESS FULL | TEST3 | 86999 | 8326K | | 347 (1) | 00:00:05 |- -Predicate Information (identified by operation id):- -1-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID") 3-access ("T1". "OBJECT_ID" = "T3". "OBJECT_ID") Statistics -- 0 recursive calls 0 db block gets 2491 consistent gets 2484 physical reads 0 redo size 3736 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11 rows processed plus materialize hint Force oracle to generate temporary table SQL > with t as (select / * + materialize * / t1.* from test1 T1 where t1.object_id=t2.object_id Test2 T2 where t1.object_id=t2.object_id) select * from tmeme Test3 T3 where t.object_id=t3.object_id 11 rows selected.Execution Plan---Plan hash value: 3288461629 Murray- -| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 582m | 165g | | 3963 (40) | 00:00:48 | 1 | TEMP TABLE TRANSFORMATION | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D661A_155646 | * 3 | HASH JOIN | | | 86997 | 8750K | | 351 (1) | 00:00:05 | | 4 | TABLE ACCESS FULL | TEST2 | 13 | 65 | | 3 (0) | 00:00:01 | | 5 | TABLE ACCESS FULL | TEST1 | 86997 | 8325K | | 347 (1) | 00:00:05 | * 6 | | HASH JOIN | | 582m | 165g | 9352K | 3613 (44) | 00:00:44 | | 7 | TABLE ACCESS FULL | TEST3 | 86999 | 8326K | | 347 (1) | 00:00:05 | 8 | VIEW | | 86997 | 17m | | 332 | (1) | 00:00:04 | | 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661A_155646 | 86997 | 8750K | | 332 (1) | 00:00:04 |- -Predicate Information (identified by operation id):-- 3-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID") 6-access ("T". "OBJECT_ID" = "T3". "OBJECT_ID") Statistics--- 55 recursive calls 8 db block gets 2525 consistent gets 2485 Physical reads 656 redo size 3736 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 11 rows processed

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.