Databases Reference
In-Depth Information
Here are the corresponding execution plan and statistics:
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 103K| 4435K| 133 (3)| 00:00:02 |
| 1 | TABLE ACCESS FULL| CUST | 103K| 4435K| 133 (3)| 00:00:02 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7206 consistent gets
0 physical reads
0 redo size
6259463 bytes sent via SQL*Net to client
76215 bytes received via SQL*Net from client
6883 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
103217 rows processed
This output shows that several thousand block reads were required (
consistent gets
). Oracle
searched every row in the table to bring back the results required to satisfy the query. In this situation, all
used blocks of the table must be read, and there is no way for Oracle to use the index to speed up the
retrieval of the data.
Note
For the examples in this section, your results may vary slightly depending on how many rows you initially
insert into the table. We inserted a little over 100,000 rows to seed the table.
Prepping for B-tree Indexes
Before creating a B-tree index, it's prudent to make a few architectural decisions that will impact
maintainability and availability. The following are manageability features that you should consider
before building indexes:
•
Estimate the size of the index before creating it.
•
Consider designating a tablespace just for indexes (separate from tables). This
allows you to more easily manage indexes separately from tables for tasks such as
backup and recovery.
•
Allow objects to inherit storage parameters from their tablespace.
•
Define naming standards to be used when creating indexes.
