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