Great article on FBI. Very comprehensive and educational, as usual. 

Couple of questions:

1. You said that I need to substr the FBI expression when the function returns varchar2. I found this to be not (always) true.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> create table t (i int);

Table created.

SQL> create or replace function f(p in int) return varchar2 deterministic
  2  as
  3  begin
  4  return 'blah';       
  5  end;
  6  /

Function created.

SQL> create index fbi on t(f(i));

Index created.

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

How is this explained? 4000 is more than 1/3 of my block size, so how is Oracle able to create the index?

2. In my case, given my data distribution and the logic in my function, it returns only 2-3 distinct values against my 500K+ table. i.e. it has very low selectivity. So, even after creating the FBI, the CBO doesnt really use it. But I see that it *is* able to get a perfect estimate of the cardinality of any predicate involving this indexed column and that really helps the overall query (especially when this table is joined to other tables, views, etc)

In other words, the default selectivity that the CBO estimates when using a indexed column is 1%, as you demonstrated in

In this case, my selectivity is closer to 50%.

Is this a appropriate use of FBI i.e. use it just to help cardinality estimates but not really use the index itself?

3. Even if I dont use FBI, but instead do

associate statistics with functions myfn default selectivity 50;

I get the same result i.e. the CBO assigns the proper cardinality and the query performs great.

Which is better, the associate statistics or FBI?

4. Regarding disabling FBIs during direct path sqlldr, I see the 'skip_index_maintenance' option to sqlldr. Is this what you are referring to? Disable all index maintenance and rebuild all indexes later? Or is there a way to disable just the FBI and rebuild that later?

Thanks a lot

Thanks 

Verbunden mit: 
Oracle9i Release 9.2.0.8.0 - Production
JServer Release 9.2.0.8.0 - Production

SQL> set timing on
SQL> CREATE TABLE TB_TEST
  2  (
  3    ZS_ID,
  4    ZS_STATUS              
  5  )
  6  AS SELECT ROWNUM,
  7             'NICHT_AKTIV'
  8      FROM   ALL_OBJECTS,
  9             ALL_OBJECTS
 10      WHERE  ROWNUM <= 1200000;

Tabelle wurde angelegt.

Abgelaufen: 00:02:24.07
SQL> UPDATE TB_TEST SET zs_status = 'Klärfall'
  2  WHERE zs_id BETWEEN 500 AND 2000;

1501 Zeilen wurden aktualisiert.

Abgelaufen: 00:00:01.09
SQL> commit;

Transaktion mit COMMIT abgeschlossen.

Abgelaufen: 00:00:00.00
SQL> CREATE INDEX fidx_neu ON TB_TEST
  2      (
  3       CASE WHEN zs_status = 'Klärfall' THEN zs_status ELSE NULL END
  4      );

Index wurde angelegt.

Abgelaufen: 00:00:05.04
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, 'TB_TEST', CASCADE=>TRUE );

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Abgelaufen: 00:00:10.03
SQL> set autotrace on
SQL> SELECT COUNT(*)
  2  FROM TB_TEST zs 
  3  WHERE (CASE WHEN zs.zs_status = 'Klärfall' THEN zs.zs_status ELSE NULL END) = 'Klärfall';

  COUNT(*)
----------
      1501

Abgelaufen: 00:00:00.00

Ausführungsplan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=12)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'FIDX_NEU' (NON-UNIQUE) (Cost=1 Card=1200000 Bytes=14400000)

Statistiken
----------------------------------------------------------
          7  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> set autotrace off
SQL> set linesize 200
SQL> 
SQL> SELECT index_name, table_name, sample_size, num_rows, clustering_factor,last_analyzed
  2      FROM   ALL_INDEXES
  3     WHERE  INDEX_NAME = 'FIDX_NEU';

SAMPLE_SIZE NUM_ROWS CLUSTERING_FACTOR LAST_ANA
----------------- ----------------------------
1501       1501                 5 08.02.07

          
6  consistent gets

3694  consistent gets
2538  physical reads

create index [...] compute statistics