Hi all, Thanks for answers. 1.) The index is created like that: CREATE INDEX idx_tab ON tab (column1 ASC, 1); This is a way to create index keeping column with NULL in the table and the Oracle consider '1' only for index. and not CREATE INDEX idx_tab ON tab (NVL(column1,1)); 2.) If I do this : CREATE INDEX idx_tab ON tab (column1); the index idx_tab is used in the plan: |* 33 | TABLE ACCESS BY INDEX ROWID| TAB | 52 | 2756 | 2 (0)| 00:00:01 | |* 34 | INDEX RANGE SCAN | IDX_TAB | 53 | | 1 (0)| 00:00:01 | 3.) The database version is: (Not enterprise) Oracle Database 11g Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production These are informations that I have. Regards Eriovaldo 2014-12-03 6:35 GMT-02:00 l.flatz@xxxxxxxxxx <l.flatz@xxxxxxxxxx>: > Hi Eriovaldo, > > create a copy of your statement , run it and note the plan in some text > document. > Then use and Index hint to enforce use of the function based index. > > If the index is not used with the hint something is basically wrong. (e.g. > implict conversion.). > If the index can be used compare the cost with the cost without the index > use (and without the hint). > > If the cost is higher without the index double check if the row estimates > are correct. Maybe you need better stats on the base table. > Also double check the reponse time and the buffer gets. Is the index > really producing an advantage? > > > regards > > Lothar > > ----Ursprüngliche Nachricht---- > Von : ecandrietta@xxxxxxxxx > Datum : 03/12/2014 - 02:20 (GMT) > An : oracle-l@xxxxxxxxxxxxx > Betreff : Index based function is not being used in plan execution > > > Hi, > > I have a index create like this: > CREATE INDEX idx_tab ON tab (column1 ASC, 1); > > I used the the collect statistics command like this: > > BEGIN > dbms_stats.gather_table_stats( > ownname => user, > tabname => 'TAB', > estimate_percent => 100, > cascade => TRUE, > method_opt => 'FOR ALL HIDDEN COLUMNS SIZE 1'); > END; > / > > BEGIN > dbms_stats.gather_table_stats ( > ownname => USER, > tabname => 'TAB', > estimate_percent => 100, > cascade => TRUE, > method_opt => 'for all columns size skewonly for columns > (nvl(COLUMN1,1))' > ); > END; > / > > > But the index is not used in the query. > > Is this resource (index based on function) released for Oracle Standard ? > > Regards > Eriovaldo > > > >