autotrace table with/without an index : AUTOTRACE « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL PLUS Session Environment
3. AUTOTRACE

SQL> create table website
  2  ( hostname       varchar2(10),
  3    upTime             date,
  4    load           number,
  5    other_stats    char(65),
  6    constraint website_pk primary key(hostname,upTime)
  7  )
  8  /

Table created.

SQL>
SQL>
SQL> create table indexedwebsite
  2  ( hostname       varchar2(10),
  3    upTime             date,
  4    load           number,
  5    other_stats    char(65),
  6    constraint indexedwebsite_pk primary key(hostname,upTime)
  7  )
  8  organization index
  9  /

Table created.

SQL>
SQL>
SQL> declare
  2      l_load number;
  3  begin
  4      for l_HOURS in 1 .. 100
  5      loop
  6          for l_HOSTS in 1 .. 100
  7          loop
  8              l_load := dbms_random.random;
  9              insert into website(hostname,upTime,load,other_stats)values('hostnm' || l_hosts, sysdate-(100-l_hours)/24,l_load, 'x' );
 10              insert into indexedwebsite(hostname,upTime,load,other_stats)values('hostnm' || l_hosts, sysdate-(100-l_hours)/24,l_load, 'x' );
 11          end loop;
 12          commit;
 13      end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> analyze table website compute statistics;

Table analyzed.

SQL>
SQL>
SQL> analyze table indexedwebsite compute statistics;

Table analyzed.

SQL>
SQL>
SQL> set autotrace on
SQL> select avg(load)
  2    from website
  3   where hostname = 'hostnm50'
  4     and upTime >= sysdate-100/24
  5  /

 AVG(LOAD)
----------
45908976.3

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 757115644

--------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    29 |    21 |
|   1 |  SORT AGGREGATE    |         |     1 |    29 |       |
|*  2 |   TABLE ACCESS FULL| WEBSITE |   100 |  2900 |    21 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("HOSTNAME"='hostnm50' AND
              "UPTIME">=SYSDATE@!-4.16666666666666666666666666666666666667)

Note
-----
   - cpu costing is off (consider enabling it)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        136  consistent gets
          0  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select avg(load)
  2    from indexedwebsite
  3   where hostname = 'hostnm50'
  4     and upTime >= sysdate-100/24
  5  /

 AVG(LOAD)
----------
45908976.3

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2378983545

-----------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     1 |    29 |     4 |
|   1 |  SORT AGGREGATE   |                   |     1 |    29 |       |
|*  2 |   INDEX RANGE SCAN| INDEXEDWEBSITE_PK |   100 |  2900 |     4 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HOSTNAME"='hostnm50' AND
              "UPTIME">=SYSDATE@!-4.16666666666666666666666666666666666667)

Note
-----
   - cpu costing is off (consider enabling it)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> drop table website;

Table dropped.

SQL> drop table indexedwebsite;

Table dropped.