Re: Index based function is not being used in plan execution

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_at_bluewin.ch <l.flatz_at_bluewin.ch>:

> 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_at_gmail.com
> Datum : 03/12/2014 - 02:20 (GMT)
> An : oracle-l_at_freelists.org
> 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
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l