Plan d’exécution d’une
Selection d’une colonne clé avec bitmap

SQL> explain plan for 
     SELECT last_name FROM test
       WHERE last_name='Austin';
Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
|Id| Operation                   |Name  |Rows |Bytes|Cost(%CPU)| Time     |
---------------------------------------------------------------------------
| 0| SELECT STATEMENT            |      |17172| 117K|   7   (0)| 00:00:01 |
| 1|  BITMAP CONVERSION TO ROWIDS|      |17172| 117K|   7   (0)| 00:00:01 |
|*2|   BITMAP INDEX SINGLE VALUE |BMAP_X|     |     |          |          |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("LAST_NAME"='Austin')

Plan d’exécution d’une
Selection d’une colonne NON clé avec bitmap

SQL> explain plan for
     SELECT first_name 
     FROM test WHERE last_name='Austin';
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
|Id| Operation                    |Name  |Rows |Bytes|Cost(%CPU)|Time    |
-------------------------------------------------------------------------
| 0  SELECT STATEMENT             |      |17172| 218K| 2493 (1) |00:00:30|
| 1|  TABLE ACCESS BY INDEX ROWID |TEST  |17172| 218K| 2493 (1) |00:00:30|
| 2|   BITMAP CONVERSION TO ROWIDS|      |     |     |          |        |
|*3|    BITMAP INDEX SINGLE VALUE |BMAP_X|     |     |          |        |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("LAST_NAME"='Austin')

Conclusion:
Pas d’acces table sur S1
Un cout très inférieur pour S1

Temps d’exécution MOYEN
Selection clé avec index bitmap

SQL> SELECT last_name from test
     where last_name='Austin';
...
DAUSTIN 590.423.4569 25/06/1997 IT_PROG 4800 103 60
105 David                Austin
DAUSTIN 590.423.4569 25/06/1997 IT_PROG 4800 103 60
105 David                Austin
16384 rows selected.
Elapsed: 00:00:00.91

Temps d’exécution MOYEN
Selection NON clé avec index bitmap

SQL> SELECT last_name from test
     where last_name='Austin';
...
DAUSTIN 590.423.4569 25/06/1997 IT_PROG 4800 103 60
105 David                Austin
DAUSTIN 590.423.4569 25/06/1997 IT_PROG 4800 103 60
105 David                Austin
16384 rows selected.
Elapsed: 00:00:00.80

Conclusion: pas de différence notable des temps d’exécution !!
Hypothèse: pourrait-on soupçonner le Cache des données ?

Vérification

SQL> CONNECT SYSTEM/passwd
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

SQL> CONNECT HR1/HR1
SQL> SELECT last_name from test
     where last_name='Austin';
...
16384 rows selected.
Elapsed: 00:00:01.10

SQL> CONNECT SYSTEM/passwd
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

SQL> CONNECT HR1/HR1
SQL> SELECT last_name from test
     where last_name='Austin';
...
16384 rows selected.
Elapsed: 00:00:10.50

exemple de temps de réponse de COUNT(*) avec bitmap

SQL>   select count(*) from test
      where last_name='Austin'
COUNT(*)
--------
   16384
Elapsed: 00:00:00.00