Wednesday, September 03, 2014

A Simple Example with Indexes

I imagine there are many reasons why Oracle might (or might not) use an index. I guess there are also many reasons why you might (or might not) WANT Oracle to use an index.

I got the idea for this example from a book written by Mark Gurry and ran it on Oracle 11.2.
 
I dedicate it to Oliver, who thinks that database administrators spend all day creating indexes.
 
First I created a table:

SQL> create table t1
  2  as select * from dba_segments
  3  /
 
Table created.
 
SQL>
 
…then I made sure it contained enough data:
 
SQL> begin
  2  for a in 1..8 loop
  3  insert into t1 select * from t1;
  4  end loop;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
SQL>

I added an index:

SQL> create index i1 on t1(owner, extents)
  2  /
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats -
> (ownname=>'ORACLE',tabname=>'T1');
 
PL/SQL procedure successfully completed.
 
SQL>
 
…then I ran a query against the table:
 
SQL> set autotrace on
SQL> set timing on
SQL> select sum(bytes) from t1
  2  where owner = 'SYS'
  3  and extents = 1
  4  /
 
SUM(BYTES)
----------
3.0098E+10
 
Elapsed: 00:00:27.29
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3693069535
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    16 |  2438  (29)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |    16 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 10388 |   162K|  2438  (29)| 00:00:03 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("EXTENTS"=1 AND "OWNER"='SYS')
 
 
Statistics
----------------------------------------------------------
        365  recursive calls
          0  db block gets
      37109  consistent gets
      37046  physical reads
          0  redo size
        533  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL> set autotrace off
SQL> set timing off
SQL>
 
The query had to look at 23% of the rows in the table:
 
SQL> l
  1  select round
  2  ((select count(*) from t1
  3    where owner = 'SYS' and extents = 1)
  4  /
  5  (select count(*) from t1) * 100)
  6* as percentage from dual
SQL> /
 
PERCENTAGE
----------
        23
 
SQL>
 
…so it did a full table scan instead of using the index and the elapsed time was 27 seconds. I wanted to improve on this so I added the bytes column to the index:
 
SQL> drop index i1
  2  /
 
Index dropped.
 
SQL> create index i2 on t1(owner, extents, bytes)
  2  /
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats -
> (ownname=>'ORACLE',tabname=>'T1');
 
PL/SQL procedure successfully completed.
 
SQL>
 
I ran the query again. This time, Oracle could get all the information it needed from the index so the elapsed time went down to 3 seconds:
 
SQL> set autotrace on
SQL> set timing on
SQL> select sum(bytes) from t1
  2  where owner = 'SYS'
  3  and extents = 1
  4  /
 
SUM(BYTES)
----------
3.0098E+10
 
Elapsed: 00:00:03.34
 
Execution Plan
----------------------------------------------------------
Plan hash value: 494139663
 
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    16 |   581  (47)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |     1 |    16 |            |          |
|*  2 |   INDEX FAST FULL SCAN| I2   |   518K|  8099K|   581  (47)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("OWNER"='SYS' AND "EXTENTS"=1)
 
 
Statistics
----------------------------------------------------------
        432  recursive calls
          0  db block gets
       6843  consistent gets
        569  physical reads
          0  redo size
        533  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         13  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL> set autotrace off
SQL> set timing off
SQL>

No comments:

Post a Comment