Mega Code Archive

Analyze and autotrace single column key and multi-column key

SQL> SQL> create table t 2 as 3 select * from all_objects; Table created. SQL> SQL> create index t_idx1 on t(object_id); Index created. SQL> SQL> create index t_idx2 on t(owner,object_type); Index created. SQL> SQL> analyze table t 2 compute statistics 3 for table 4 for all indexes 5 for all indexed columns; Table analyzed. SQL> set autotrace traceonly explain SQL> select object_id, owner, object_type 2 from t 3 where object_id between 100 and 2000 4 and owner = 'SYS' 5 / Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 ---------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 938 | 66598 | 25 | |* 1 | TABLE ACCESS FULL| T | 938 | 66598 | 25 | ---------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000 AND "OWNER"='SYS') Note ----- - cpu costing is off (consider enabling it) SQL> SQL> set autotrace traceonly SQL> select object_id, owner, object_type 2 from t 3 where object_id between 100 and 2000 4 and owner = 'SYS' 5 / 1250 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 ---------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 938 | 66598 | 25 | |* 1 | TABLE ACCESS FULL| T | 938 | 66598 | 25 | ---------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000 AND "OWNER"='SYS') Note ----- - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 246 consistent gets 0 physical reads 0 redo size 25173 bytes sent via SQL*Net to client 1293 bytes received via SQL*Net from client 85 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1250 rows processed SQL> SQL> select /*+ index( t t_idx1 ) */ object_id, owner, object_type 2 from t 3 where object_id between 100 and 2000 4 and owner = 'SYS' 5 / 1250 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4055207394 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 938 | 66598 | 32 | |* 1 | TABLE ACCESS BY INDEX ROWID| T | 938 | 66598 | 32 | |* 2 | INDEX RANGE SCAN | T_IDX1 | 1810 | | 5 | ---------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER"='SYS') 2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=2000) Note ----- - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 195 consistent gets 0 physical reads 0 redo size 25173 bytes sent via SQL*Net to client 1293 bytes received via SQL*Net from client 85 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1250 rows processed SQL> SQL> select /*+ index( t t_idx2 ) */ object_id, owner, object_type 2 from t 3 where object_id between 100 and 2000 4 and owner = 'SYS' 5 / 1250 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3371054274 ---------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 938 | 66598 | 522 | |* 1 | TABLE ACCESS BY INDEX ROWID| T | 938 | 66598 | 522 | |* 2 | INDEX RANGE SCAN | T_IDX2 | 6520 | | 23 | ---------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=2000) 2 - access("OWNER"='SYS') Note ----- - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 670 consistent gets 0 physical reads 0 redo size 22392 bytes sent via SQL*Net to client 1293 bytes received via SQL*Net from client 85 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1250 rows processed SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped.