Nested Function-Based Indexes

A little bug that bites hard if you need to nest access to the function based indexes. Consider the following example:

--
--First a couple of tables that we'll use later
--

SQL> create table DEMO1 as select rownum x from sys.source$ where rownum < 50000;

Table created.

SQL> create table DEMO2 as select rownum x from sys.source$ where rownum < 50000;

Table created.


--
--Now a table that we'll place a function-based index on
--

SQL> create table DEMO3  as
  2  select rownum||'xxxxxxxxxxxx' z
  3  from sys.source$
  4  where rownum < 20000;

Table created.

SQL> create index demo3_fbi on demo3 ( substr(z,3,4) ) ;

Index created.

SQL> analyze table demo3 compute statistics;

Table analyzed.


--
--Now lets see that index in action
--

SQL> set autotrace on
SQL> select substr(z,3,4)
  2  from demo3
  3  where substr(z,3,4) = '123hasd';

Execution Plan
----------------------------------------------------------
SELECT STATEMENT
  INDEX (RANGE SCAN) OF 'DEMO3_FBI' (NON-UNIQUE)


--
-- We slap our table into a sub-select and things are still fine
--

SQL> select * from demo2
  2  where x in (
  3    select 230
  4    from demo3
  5    where substr(z,3,4) = '123hasd' )
  6  
SQL> /

Execution Plan
----------------------------------------------------------
SELECT STATEMENT
  HASH JOIN
    VIEW OF 'VW_NSO_1'
      SORT (UNIQUE)
        INDEX (RANGE SCAN) OF 'DEMO3_FBI' (NON-UNIQUE)
    TABLE ACCESS (FULL) OF 'DEMO2'


--
-- But push things one level deeper and ...
--

SQL> select * from demo1 
  2  where x in (
  3    select x from demo2
  4    where x in (
  5      select 230
  6      from demo3
  7      where substr(z,3,4) = '123hasd' )
  8  )
  9  
SQL> 
SQL> /
select * from demo1
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

You'll get this for similar INSERT, UPDATE and DELETE type of statements as well