autotrace table with/without an index : autotrace « SQL Plus « Oracle PL / SQL






autotrace table with/without an index

  
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.

   
    
  








Related examples in the same category

1.autotrace command
2.Autotrace on and off
3.Autotrace lower text function
4.Autotrace running total
5.Autotrace a query on a huge table
6.Autotrace a large table
7.Autotrace a query with group clause
8.Execution Plan
9.autotrace a nested query
10.set autotrace on explain for every single statement
11.set autotrace traceonly explain for bitmap index
12.set autotrace traceonly explain, and condition
13.set autotrace traceonly statistics
14.set autotrace traceonly statistics for 'select * from tableName'
15.set autotrace traceonly
16.AUTOTRACE exists (subquery)
17.AUTOTRACE table joining
18.AUTOTRACE table joining and aggregate function
19.autotrace ansi full outer join
20.autotrace count(*)
21.autotrace ctxsys.context index
22.autotrace merge command