Database Reference
In-Depth Information
Note that the
TABLE ACCESS BY USER ROWID
operation is exclusively used when the rowid is directly passed as a
parameter or literal. In the next section, you'll see that when a rowid is extracted from an index, the
TABLE ACCESS BY
INDEX ROWID
operation is used instead. The effectiveness of these table accesses is the same; the two operations are
used only to distinguish the source of the rowid.
When a SQL statement specifies several rowids through an
IN
condition, an additional
INLIST ITERATOR
operation appears in the execution plan. The following query is an example. Note that operation 1 (the parent) simply
indicates that operation 2 (the child) is processed several times. According to the value of the
Starts
column of
operation 2, it's processed twice. In other words, the
emp
table is accessed twice through a rowid:
SELECT * FROM emp WHERE rowid IN ('AAADGZAAEAAAAAoAAH', 'AAADGZAAEAAAAAoAAI')
-----------------------------------------------------
| Id | Operation | Name | Starts |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | INLIST ITERATOR | | 1 |
| 2 | TABLE ACCESS BY USER ROWID| EMP | 2 |
-----------------------------------------------------
In summary, whenever specific rows are accessed at least two times, you should consider getting the rowid
during the first access and then take advantage of it for subsequent accesses.
Index Access
Index accesses are by far the most often used access paths for SQL statements with strong selectivity. To take
advantage of them, you have to to apply at least one of the restrictions present in the
WHERE
clause or a join condition
through an index. To do that, it's essential to not only index the columns that provide strong selectivity but also to
understand which type of conditions might be applied efficiently through an index. The database engine supports
different types of indexes. Before describing the properties and access paths supported by B-tree and bitmap indexes
in detail, it's important to discuss the clustering factor or, in other words, why the distribution of data impacts the
performance of an index scan (see Figure
13-4
).
■
although the database engine supports domain indexes for complex data such as pDF documents or images,
this isn't covered in this topic. refer to
Oracle Database Documentation
for further information.
Note
Clustering Factor
As described in Chapter 8, the clustering factor indicates how many adjacent index keys don't refer to the same data
block in the table (bitmap indexes are an exception because their clustering factor is always set to the number of keys
in the index). Here's a mental picture that might help: if the whole table is accessed through an index and in the buffer
cache there's a single buffer to store the data blocks, the clustering factor is the number of physical reads performed
against the table. For example, the clustering factor of the index shown in Figure
13-12
is 10 (notice that there are
12 rows and only two adjacent index keys, which are highlighted, refer to the same data block).
