At the customer site where I work, we are using an Oracle 9i. More specific a 9.2.0.5 database.
While demonstrating to one of my colleagues when an index can and cannot be used, I noticed something strange when I ran
the same script on my laptop. My laptop has an Oracle 10r2 on it.
I told my colleague that you would need a FBI, Function Based Index, when you apply a function like UPPER to an indexed column. That is
if you would want to use the index in the first place.
FBI in action
Starting up SQL*Plus to demonstrate, I ran this script:
SQL> create table t
2 as
3 select *
4 from all_objects
5 /
Table created.
SQL> create index tidx on t (owner, object_name)
2 /
Index created.
SQL> set autot trace expl
SQL> select owner, object_name
2 from t
3 where owner = 'ALEX'
4 and object_name = 'T'
5 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'TIDX' (NON-UNIQUE)
As you can see in the execution plan, it uses the index on OWNER and OBJECT_NAME as expected. When you apply the UPPER function to
OWNER and OBJECT_NAME it will go from an INDEX RANGE SCAN to a FULL TABLE SCAN:
SQL> select owner, object_name
2 from t
3 where upper (owner) = 'ALEX'
4 and upper (object_name) = 'T'
5 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'
When an index is created using the UPPER function in the definition, the index can be used. Or can it?
SQL> create index fbi_idx on t (upper (owner), upper (object_name))
2 /
Index created.
SQL> select owner, object_name
2 from t
3 where upper (owner) = 'ALEX'
4 and upper (object_name) = 'T'
5 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'
Even though I used the same functions in the Index creation and the Query, the execution plan does not show the use
of the index.
After gathering statistics on the table, the index is being used:
SQL> exec dbms_stats.gather_table_stats (user, 'T')
PL/SQL procedure successfully completed.
SQL> select owner, object_name
2 from t
3 where upper (owner) = 'ALEX'
4 and upper (object_name) = 'T'
5 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
32)
2 1 INDEX (RANGE SCAN) OF 'FBI_IDX' (NON-UNIQUE) (Cost=1 Car
d=1)
So, there you have it. When you apply a function on an indexed column, you can use a Function Based Index in order to use the index aswell.
And just for completeness, the version of the database:
SQL> set autot off
SQL> select *
2 from v$version
3 /
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Compaq Tru64 UNIX: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production
SQL>
Things change: Oracle 10r2 on my Laptop
Like I said before I ran the same script on my laptop, why I don’t know, but I did.
SQL> create table t
2 as
3 select *
4 from all_objects
5 /
Table created.
SQL> create index tidx on t (owner, object_name)
2 /
Index created.
SQL> select owner, object_name
2 from t
3 where owner = 'ALEX'
4 and object_name = 'T'
5 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3897160578
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TIDX | 1 | 34 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OWNER"='ALEX' AND "OBJECT_NAME"='T')
Note
-----
- dynamic sampling used for this statement
Nothing unusual here, like expected the index is being used in the execution plan.
Running the next part of the script did show something that I didn’t expect:
SQL> select owner, object_name
2 from t
3 where upper (owner) = 'ALEX'
4 and upper (object_name) = 'T'
5 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2008626562
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 170 | 71 (5)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| TIDX | 5 | 170 | 71 (5)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("OWNER")='ALEX' AND UPPER("OBJECT_NAME")='T')
Note
-----
- dynamic sampling used for this statement
The index is being used as well. Even though it changed from an INDEX RANGE SCAN to an INDEX FAST FULL SCAN. The index
is still being used. Wow, this CBO is really smart. Don’t you just love it?
Again for completeness, the version information
SQL> set autot off
SQL> select *
2 from v$version
3 /
BANNER
----------------------------------------------------------------
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>
Jumping to conclusions
While typing all this, it dawned to me… Dynamic Sampling is used default on an Oracle 10. It even says so in the note of the execution plan. What if I would gather statistics on the
Oracle 9i? Would the CBO know that it can use the index instead of going after the full table? Let’s give it a whirl:
SQL> exec dbms_stats.gather_table_stats (user, 'T')
PL/SQL procedure successfully completed.
SQL> select owner, object_name
2 from t
3 where upper (owner) = 'ALEX'
4 and upper (object_name) = 'T';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=2 Bytes=64)
1 0 INDEX (FAST FULL SCAN) OF 'TIDX' (NON-UNIQUE) (Cost=4 Card
=2 Bytes=64)
That’s what you get when you jump to conclusions. I was too hasty…
But still…. It is still amazing that the CBO will use the index, even though it is not a Function Based Index.
…one more thing
Using SQL Developer to look at the table definition, it showed on the INDEXES-tab the column names as
SYS_NC00014$ and SYS_NC00015$. When I looked in PL/SQL Developer, the index definition showed the same
as the one I entered.
Strange, but what really struck me is that you can use these system generated columns in your query as well. No need to
do so, but it is my database and I thought I give it a try:
SQL> select owner, object_name
2 from t
3 where SYS_NC00014$ = 'ALEX'
4 and SYS_NC00015$ = 'T'
5 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1971231365
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 170 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 5 | 170 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | FBI_IDX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("OWNER")='ALEX' AND UPPER("OBJECT_NAME")='T')
Note
-----
- dynamic sampling used for this statement
A little info on SYS_NC00014$.
You were wondering, why FBI was not used before you created statistics. It was because RBO does not consider FBIs. That database apparently had the default optimizer set to RBO. CBO is default, when there are statistics for some of tables in the query. If you used the hint /*+first_rows*/, the FBI would be used even without statistics…
And if you have FBI on upper(column), it cannot be used for searching without upper function. Workaround could be to put both conditions: column=:x and upper(column)=:x.
INDEX FAST FULL SCAN means that whole index is read. It could be used because only columns used in index were read in the select list, so there was no need to read whole table with (probably) lot more columns. If you used “select *”, you will get TABLE FULL SCAN… It’s misleading to say that “index was used” with IFFS – it’s still full scan, but little less data (but more than a table, because of PCTFREE).
You can think of an index as of a table physically sorted, and of an FBI as of a MV physically sorted…
Cool stuff indeed!
But what I don’t get… does the optimizer look for all the occurrences in the index which would be found when the function would not be applied on the column and apply the function on the result of that index scan? Or is there another mechanism in play?
I thought dynamic sampling was used to take a guess at un-indexed tables, but apparently it also uses tricks to optimize the use of existing indexes. Awesome!
That is pretty cool! That means that the contents of a Function (or probably better defined as Expression) Based Index is available to us as a sort of cache of precalculated values!
In the article Smart denormalization and Enhanced PL/SQL Function Call performance – Comparing Function Based Index and Materialized Views on our weblog, I compare Materialized Views and Function Based Index, and suggested (erroneously as you demonstrate) that only the MV provides a cache of precalculated values that is automatically maintained and that is accessible to us. Apparently, the same applies to FBI!
Thanks for the analysis and the clear conclusions.
Lucas