dbms_stats.set_table_stats( user, 'myTable1', numrows => 10000000, numblks => 1000000 )
SQL>
SQL>
SQL> create table myTable1
2 as
3 select *
4 from all_users
5 where 1=0;
SQL>
SQL> create index myTable1_username_idx on myTable1(username);
SQL>
SQL> create table myTable2
2 as
3 select *
4 from all_objects
5 created;
SQL>
SQL> exec dbms_stats.set_table_stats( user, 'myTable1', numrows => 10000000, numblks => 1000000 );
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_stats.set_table_stats( user, 'myTable2', numrows => 10000, numblks => 1000 );
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace traceonly explain
SQL> select myTable1.username, sum(myTable2.object_id)
2 from myTable1, myTable2
3 where myTable1.username = myTable2.owner (+)
4 group by myTable1.username
5 /
SQL>
SQL> set autotrace off
SQL>
SQL> drop table myTable1;
SQL> drop table myTable2;
Table dropped.
Related examples in the same category