Does an index work without a where clause? (part 1)

I was looking at some of the queries that the front end runs against one of the databases I support. The query currently does a full scan of one of the fact tables in order to generate a small set of static information - performance is acceptable at the moment but will gradually slow down as the fact table gets bigger.

Unfortunately amending the data model is not an option at the moment so I started thinking about other ways to do speed up the table. Could I use an index?

Tests are carried out on 11.2.0.3.

What is the performance like with a small table?

 CREATE TABLE small_tab
 AS
 SELECT *
 FROM  all_objects
 where rownum <= 70000;
 -- Gather statistics
SELECT DISTINCT object_type, object_type||' text'  
 FROM  small_tab;  
--------------------------------------------------------------------------------
 | Id | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
 --------------------------------------------------------------------------------
 |  0 | SELECT STATEMENT  |           |       |       |  307 (100) |          |
 |  1 | HASH UNIQUE       |           |  39   |  351  |  307  (2)  | 00:00:04 |
 |  2 |  TABLE ACCESS FULL| SMALL_TAB | 76220 |  669K |  304  (1)  | 00:00:04 |
 --------------------------------------------------------------------------------

What if I add a normal index?

 CREATE INDEX i_small_tab ON small_tab(object_type, object_type||' text');
 -- gather statistics
 SELECT DISTINCT object_type, object_type||' text'
 FROM  small_tab;
 --------------------------------------------------------------------------------
 | Id | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
 --------------------------------------------------------------------------------
 |  0 | SELECT STATEMENT  |           |       |       |  307 (100) |          |
 |  1 | HASH UNIQUE       |           |  39   |  897  |  307  (2)  | 00:00:04 |
 |  2 |  TABLE ACCESS FULL| SMALL_TAB | 76220 | 1711K |  304  (1)  | 00:00:04 |
 --------------------------------------------------------------------------------

Still a full scan of the table. Note that I am also unable to hint Oracle to use the index. I might take a closer look at this in a further post.

What about a bitmap index? Logically this should provide the best performance because of the way a bitmap operates internally. But is this the case?

 CREATE BITMAP INDEX i_small_tab_bmp ON small_tab(object_type, object_type||' text');  
 -- Gather statistics  
 SELECT DISTINCT object_type, object_type||' text'  
 FROM  small_tab;
 ------------------------------------------------------------------------------------------------
 | Id | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
 ------------------------------------------------------------------------------------------------
 |  0 | SELECT STATEMENT            |                 |       |       |   7 (100)  |          |
 |  1 | HASH UNIQUE                 |                 |  39   |  897  |   7 (29)   | 00:00:01 |
 |  2 |  BITMAP INDEX FAST FULL SCAN| I_SMALL_TAB_BMP | 76220 | 1711K |   5  (0)   | 00:00:01 |
 ------------------------------------------------------------------------------------------------

... and Oracle uses the bitmap index.  Response from the bitmap on my system is about 3 times as fast as well - approximately 0.01s rather than 0.03s when using a full scan.

My next post will summarize the same tests against a large table of approximately 2,000,000 rows.