Storage Indexes - Part IV

It is often stated that in Exadata, you don’t need a conventional database indexes any more as everything runs fast, that indexes are simply waste of time and storage. So simply drop all database indexes and things will run so fast, well that is not quite true, there are many many cases where database indexes are still critical for optimal performance and scalability in an Exadata environment.

 

The first example is a scenario when one want either the minimum or maximum of a column value. As database index entries are always ordered, finding minimum or maximum of a column value is efficient by traversing down to either the first/last leaf block with in the index structure. Therefore an execution of INDEX FULL SCAN MIN/MAX access path by which the CBO can decide to access an index in this manner.

 

c##rajesh@PDB1> select * from table( dbms_xplan.display_cursor(format=>'allstats last') );

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------

SQL_ID  2hg8tc5vjaj9k, child number 0

-------------------------------------

select min( object_id ) from t

 

Plan hash value: 2683064407

 

-------------------------------------------------------------------------------------------------------

| Id  | Operation                  | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |       |      1 |        |      1 |00:00:00.01 |       3 |      3 |

|   1 |  SORT AGGREGATE            |       |      1 |      1 |      1 |00:00:00.01 |       3 |      3 |

|   2 |   INDEX FULL SCAN (MIN/MAX)| T_IDX |      1 |      1 |      1 |00:00:00.01 |       3 |      3 |

-------------------------------------------------------------------------------------------------------

 

 

14 rows selected.

 

With only a few consistent gets, we have managed to get the minimum value of a column extremely quickly and efficiently

 

However if we drop this index and re-run the same query in exadata many times,

 

c##rajesh@PDB1> alter index T_IDX invisible;

 

Index altered.

 

c##rajesh@PDB1> select s.name,m.value

  2  from v$statname s ,

  3      v$mystat m

  4  where s.statistic# = m.statistic#

  5  and s.name in ('cell physical IO bytes saved by storage index',

  6  'cell physical IO interconnect bytes returned by smart scan' );

 

NAME                                                                    VALUE

------------------------------------------------------------ ----------------

cell physical IO bytes saved by storage index                               0

cell physical IO interconnect bytes returned by smart scan                  0

 

c##rajesh@PDB1> set serveroutput off

c##rajesh@PDB1> alter session set statistics_level=all;

 

Session altered.

 

c##rajesh@PDB1> select min( object_id ) from t;

 

MIN(OBJECT_ID)

--------------

             2

 

c##rajesh@PDB1> select * from table( dbms_xplan.display_cursor(format=>'allstats last') );

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------

SQL_ID  2hg8tc5vjaj9k, child number 0

-------------------------------------

select min( object_id ) from t

 

Plan hash value: 2966233522

 

------------------------------------------------------------------------------------------------------

| Id  | Operation                  | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |      |      1 |        |      1 |00:00:01.84 |    2108K|   2108K|

|   1 |  SORT AGGREGATE            |      |      1 |      1 |      1 |00:00:01.84 |    2108K|   2108K|

|   2 |   TABLE ACCESS STORAGE FULL| T    |      1 |    100M|    100M|00:00:00.92 |    2108K|   2108K|

------------------------------------------------------------------------------------------------------

 

 

14 rows selected.

 

c##rajesh@PDB1> select s.name,m.value

  2  from v$statname s ,

  3      v$mystat m

  4  where s.statistic# = m.statistic#

  5  and s.name in ('cell physical IO bytes saved by storage index',

  6  'cell physical IO interconnect bytes returned by smart scan' );

 

NAME                                                                    VALUE

------------------------------------------------------------ ----------------

cell physical IO bytes saved by storage index                               0

cell physical IO interconnect bytes returned by smart scan          516301536

 

c##rajesh@PDB1>

 

We noticed that query takes considerably longer and that the storage indexes have been unable to be of any help with not a single byte saved.

 

This is because all type of aggregations are performed at database layer and not in the storage cells.  Even though storage index could be seen as perhaps being a value there, they are totally ignored by Oracle when retrieving either the minimum/maximum of a column value.