Database Reference
In-Depth Information
---------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 |
| 2 |
INDEX FULL SCAN
| T_PK | 1 | 10000 |
---------------------------------------------------
For bitmap indexes, no specific operation is available to execute the
min
and
max
functions. The same operations
used for equality conditions and range conditions are used.
Function-based Indexes
Every time an indexed column is passed as an argument to a function, or is involved in an expression, the SQL engine
can't use the index built on that column for an index range scan. As a result, one of the fundamental rules to follow
is to never modify the values returned by an indexed column in the
WHERE
clause. For example, if an index exists on
the
c1
column, a restriction like
upper(c1) = 'SELDON'
can't be applied efficiently through the index built on the
c1
column. This should be pretty obvious, because you can search only for a value that is stored in an index, rather than
something else. The following example, as the others in this section, is based on the
fbi.sql
script:
SQL> CREATE INDEX i_c1 ON t (c1);
SQL> SELECT * FROM t WHERE upper(c1) = 'SELDON';
----------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | | 4 |
|* 1 | TABLE ACCESS FULL| T | 100 | 4 |
----------------------------------------------------
1 - filter(UPPER("C1")='SELDON')
An exception to the fundamental rule is when constraints ensure that an index contains the necessary information.
In the case just illustrated, two constraints on the
c1
column provide that information to the query optimizer:
SQL> ALTER TABLE t MODIFY (c1 NOT NULL);
SQL> ALTER TABLE t ADD CONSTRAINT t_c1_upper CHECK (c1 = upper(c1));
SQL> SELECT * FROM t WHERE upper(c1) = 'SELDON';
--------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100 | 4 |
|* 2 | INDEX RANGE SCAN | I_C1 | 4 | 4 |
--------------------------------------------------------------
