Database Reference
In-Depth Information
For example, say we have a 10,000,000-row table (see the “Setting Up Your Environment” section at the beginning
of this topic for details on creating
BIG_TABLE
) with a primary key index on a number column:
EODA@ORA12CR1> select index_name, blevel, num_rows
2 from user_indexes
3 where table_name = 'BIG_TABLE';
INDEX_NAME BLEVEL NUM_ROWS
-------------------- ---------- ---------
BIG_TABLE_PK 2 9848991
The
BLEVEL
is 2, meaning the
HEIGHT
is 3, and it will take two I/Os to find a leaf (resulting in a third I/O). So we
would expect three I/Os to retrieve any given key value from this index:
EODA@ORA12CR1> set autotrace on
EODA@ORA12CR1> select id from big_table where id = 42;
Execution Plan
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01|
|* 1 | INDEX UNIQUE SCAN | BIG_TABLE_PK | 1 | 6 | 2 (0)| 00:00:01|
---------------------------------------------------------------------------------
Statistics
-----------------------------------------------------------
... 3 consistent gets
1 rows processed
EODA@ORA12CR1> select id from big_table where id = 12345;
Statistics
-----------------------------------------------------------
... 3 consistent gets
... 1 rows processed
EODA@ORA12CR1> select id from big_table where id = 1234567;
Statistics
-----------------------------------------------------------
... 3 consistent gets
... 1 rows processed
The B*Tree is an excellent general-purpose indexing mechanism that works well for large and small tables, and
experiences little, if any, degradation in retrieval performance as the size of the underlying table grows.
