SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'t_inc2', method_opt=>'for all columns size 254', cascade => true); PL/SQL procedure successfully completed. SQL> select * from t_inc2 a, t_inc2 b where a.x = 5 and a.x = b.x; 15625 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1207328753 -------------------------------------------------------------------------------- ------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | -------------------------------------------------------------------------------- ------------ | 0 | SELECT STATEMENT | | 1 | 208 | 7 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T_INC2 | 1 | 104 | 3 (0) | 00:00:01 | | 2 | NESTED LOOPS | | 1 | 208 | 7 (0) | 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| T_INC2 | 1 | 104 | 4 (0) | 00:00:01 | |* 4 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 3 (0) | 00:00:01 | |* 5 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 2 (0) | 00:00:01 | -------------------------------------------------------------------------------- ------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."X"=5) 5 - access("B"."X"=5) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 17295 consistent gets 127 physical reads 0 redo size 211124 bytes sent via SQL*Net to client 11832 bytes received via SQL*Net from client 1043 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 15625 rows processed SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'t_inc2', method_opt=>'for all columns size 254', cascade => true); PL/SQL procedure successfully completed. SQL> select * from t_inc2 a, t_inc2 b where a.x = 5 and a.x = b.x; 15625 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1207328753 -------------------------------------------------------------------------------- ------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | -------------------------------------------------------------------------------- ------------ | 0 | SELECT STATEMENT | | 1 | 208 | 7 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T_INC2 | 1 | 104 | 3 (0) | 00:00:01 | | 2 | NESTED LOOPS | | 1 | 208 | 7 (0) | 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| T_INC2 | 1 | 104 | 4 (0) | 00:00:01 | |* 4 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 3 (0) | 00:00:01 | |* 5 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 2 (0) | 00:00:01 | -------------------------------------------------------------------------------- ------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."X"=5) 5 - access("B"."X"=5) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 17295 consistent gets 127 physical reads 0 redo size 211124 bytes sent via SQL*Net to client 11832 bytes received via SQL*Net from client 1043 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 15625 rows processed SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'t_inc2', method_opt=>'for all columns size 254', cascade => true); PL/SQL procedure successfully completed. SQL> select * from t_inc2 a, t_inc2 b where a.x = 5 and a.x = b.x; 15625 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1207328753 -------------------------------------------------------------------------------- ------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | -------------------------------------------------------------------------------- ------------ | 0 | SELECT STATEMENT | | 1 | 208 | 7 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T_INC2 | 1 | 104 | 3 (0) | 00:00:01 | | 2 | NESTED LOOPS | | 1 | 208 | 7 (0) | 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| T_INC2 | 1 | 104 | 4 (0) | 00:00:01 | |* 4 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 3 (0) | 00:00:01 | |* 5 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 2 (0) | 00:00:01 | -------------------------------------------------------------------------------- ------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."X"=5) 5 - access("B"."X"=5) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 17295 consistent gets 127 physical reads 0 redo size 211124 bytes sent via SQL*Net to client 11832 bytes received via SQL*Net from client 1043 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 15625 rows processed SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'t_inc2', method_opt=>'for all columns size 254', cascade => true); PL/SQL procedure successfully completed. SQL> select * from t_inc2 a, t_inc2 b where a.x = 5 and a.x = b.x; 15625 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3858377297 -------------------------------------------------------------------------------- ----------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ----------- | 0 | SELECT STATEMENT | | 73489 | 14M| 212 (1)| 00:00:03 | |* 1 | HASH JOIN | | 73489 | 14M| 212 (1)| 00:00:03 | | 2 | TABLE ACCESS BY INDEX ROWID| T_INC2 | 271 | 28184 | 105 (0)| 00:00:02 | |* 3 | INDEX RANGE SCAN | T_INC_IDX2 | 271 | | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| T_INC2 | 271 | 28184 | 105 (0)| 00:00:02 | |* 5 | INDEX RANGE SCAN | T_INC_IDX2 | 271 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- ----------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."X"="B"."X") 3 - access("A"."X"=5) 5 - access("B"."X"=5) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 295 consistent gets 127 physical reads 0 redo size 211124 bytes sent via SQL*Net to client 11832 bytes received via SQL*Net from client 1043 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 15625 rows processed SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'t_inc2', method_opt=>'for all columns size 254', cascade => true); PL/SQL procedure successfully completed. SQL> select * from t_inc2 a, t_inc2 b where a.x = 5 and a.x = b.x; 15625 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1207328753 -------------------------------------------------------------------------------- ------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | -------------------------------------------------------------------------------- ------------ | 0 | SELECT STATEMENT | | 1 | 208 | 7 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T_INC2 | 1 | 104 | 3 (0) | 00:00:01 | | 2 | NESTED LOOPS | | 1 | 208 | 7 (0) | 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| T_INC2 | 1 | 104 | 4 (0) | 00:00:01 | |* 4 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 3 (0) | 00:00:01 | |* 5 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 2 (0) | 00:00:01 | -------------------------------------------------------------------------------- ------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."X"=5) 5 - access("B"."X"=5) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 17295 consistent gets 127 physical reads 0 redo size 211124 bytes sent via SQL*Net to client 11832 bytes received via SQL*Net from client 1043 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 15625 rows processed SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'t_inc2', method_opt=>'for all columns size 254', cascade => true); PL/SQL procedure successfully completed. SQL> select * from t_inc2 a, t_inc2 b where a.x = 5 and a.x = b.x; 15625 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1207328753 -------------------------------------------------------------------------------- ------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | -------------------------------------------------------------------------------- ------------ | 0 | SELECT STATEMENT | | 1 | 208 | 7 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T_INC2 | 1 | 104 | 3 (0) | 00:00:01 | | 2 | NESTED LOOPS | | 1 | 208 | 7 (0) | 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| T_INC2 | 1 | 104 | 4 (0) | 00:00:01 | |* 4 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 3 (0) | 00:00:01 | |* 5 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 2 (0) | 00:00:01 | -------------------------------------------------------------------------------- ------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."X"=5) 5 - access("B"."X"=5) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 17295 consistent gets 127 physical reads 0 redo size 211124 bytes sent via SQL*Net to client 11832 bytes received via SQL*Net from client 1043 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 15625 rows processed SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'t_inc2', method_opt=>'for all columns size 254', cascade => true); PL/SQL procedure successfully completed. SQL> select * from t_inc2 a, t_inc2 b where a.x = 5 and a.x = b.x; 15625 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1207328753 -------------------------------------------------------------------------------- ------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | -------------------------------------------------------------------------------- ------------ | 0 | SELECT STATEMENT | | 1 | 208 | 7 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T_INC2 | 1 | 104 | 3 (0) | 00:00:01 | | 2 | NESTED LOOPS | | 1 | 208 | 7 (0) | 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| T_INC2 | 1 | 104 | 4 (0) | 00:00:01 | |* 4 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 3 (0) | 00:00:01 | |* 5 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 2 (0) | 00:00:01 | -------------------------------------------------------------------------------- ------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."X"=5) 5 - access("B"."X"=5) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 17295 consistent gets 127 physical reads 0 redo size 211124 bytes sent via SQL*Net to client 11832 bytes received via SQL*Net from client 1043 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 15625 rows processed SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'t_inc2', method_opt=>'for all columns size 254', cascade => true); PL/SQL procedure successfully completed. SQL> select * from t_inc2 a, t_inc2 b where a.x = 5 and a.x = b.x; 15625 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3858377297 -------------------------------------------------------------------------------- ----------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ----------- | 0 | SELECT STATEMENT | | 99713 | 19M| 244 (1)| 00:00:03 | |* 1 | HASH JOIN | | 99713 | 19M| 244 (1)| 00:00:03 | | 2 | TABLE ACCESS BY INDEX ROWID| T_INC2 | 316 | 32864 | 121 (0)| 00:00:02 | |* 3 | INDEX RANGE SCAN | T_INC_IDX2 | 316 | | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| T_INC2 | 316 | 32864 | 121 (0)| 00:00:02 | |* 5 | INDEX RANGE SCAN | T_INC_IDX2 | 316 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- ----------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."X"="B"."X") 3 - access("A"."X"=5) 5 - access("B"."X"=5) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 295 consistent gets 127 physical reads 0 redo size 211124 bytes sent via SQL*Net to client 11832 bytes received via SQL*Net from client 1043 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 15625 rows processed SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'t_inc2', method_opt=>'for all columns size 254', cascade => true); PL/SQL procedure successfully completed. SQL> select * from t_inc2 a, t_inc2 b where a.x = 5 and a.x = b.x; 15625 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1207328753 -------------------------------------------------------------------------------- ------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | -------------------------------------------------------------------------------- ------------ | 0 | SELECT STATEMENT | | 1 | 208 | 7 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T_INC2 | 1 | 104 | 3 (0) | 00:00:01 | | 2 | NESTED LOOPS | | 1 | 208 | 7 (0) | 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| T_INC2 | 1 | 104 | 4 (0) | 00:00:01 | |* 4 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 3 (0) | 00:00:01 | |* 5 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 2 (0) | 00:00:01 | -------------------------------------------------------------------------------- ------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."X"=5) 5 - access("B"."X"=5) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 17295 consistent gets 127 physical reads 0 redo size 211124 bytes sent via SQL*Net to client 11832 bytes received via SQL*Net from client 1043 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 15625 rows processed SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'t_inc2', method_opt=>'for all columns size 254', cascade => true); PL/SQL procedure successfully completed. SQL> select * from t_inc2 a, t_inc2 b where a.x = 5 and a.x = b.x; 15625 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1207328753 -------------------------------------------------------------------------------- ------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | -------------------------------------------------------------------------------- ------------ | 0 | SELECT STATEMENT | | 1 | 208 | 7 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T_INC2 | 1 | 104 | 3 (0) | 00:00:01 | | 2 | NESTED LOOPS | | 1 | 208 | 7 (0) | 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| T_INC2 | 1 | 104 | 4 (0) | 00:00:01 | |* 4 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 3 (0) | 00:00:01 | |* 5 | INDEX RANGE SCAN | T_INC_IDX2 | 1 | | 2 (0) | 00:00:01 | -------------------------------------------------------------------------------- ------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."X"=5) 5 - access("B"."X"=5) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 17295 consistent gets 127 physical reads 0 redo size 211124 bytes sent via SQL*Net to client 11832 bytes received via SQL*Net from client 1043 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 15625 rows processed SQL> spool off