Databases Reference
In-Depth Information
In the following example, you are querying against the
EMPLOYEES_BIG_TREE
table with a B-tree index
on the
GENDER
column. Note that there is a filter on the indexed
GENDER
column. The optimizer does use
the B-tree index and runs in 5.1 seconds. In the statistics for the query, the database performed 124,685
physical reads.
SQL> SELECT count(*) FROM employees_big_btree
2* WHERE gender = 'F';
COUNT(*)
----------
21233664
Elapsed: 00:00:05.10
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 20366 (3)| 00:04:05 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | INDEX FAST FULL SCAN| EMPLOYEES_I2 | 21M| 40M| 20366 (3)| 00:04:05 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("GENDER"='F')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
124904 consistent gets
124865 physical reads
0 redo size
529 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Now perform the exact same query on a table with the exact same number of rows, but instead the
table has a bitmap index on the
GENDER
column rather than a B-tree index. As you can see, the query uses
an index, runs in less than 1 second, and performed only 996 physical reads, rather than 124,685 physical
reads performed against a similar table with a B-tree index on the
GENDER
column.
SQL> SELECT count(*) FROM employees_big_bitmap
2* WHERE gender = 'F';
COUNT(*)
----------
21233664
Elapsed: 00:00:00.02
