analyze and autotrace single column key and multi-column key : Analyze « SQL Plus « Oracle PL / SQL

1. Oracle PL / SQL
2. SQL Plus
3. Analyze

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.

   
    
  

Related examples in the same category