function-based index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: function-based index

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    function-based index

    I have lots of these kinds of select and i want to change them so they use index.

    select *
    from t
    where (x = l_some_variable OR (x is null and l_some_variable is null));

    So i want to create a function-based index.

    create index t_idx on t(nvl(x,-1));

    My query become:

    select *
    from t
    where nvl(x,-1) = nvl(l_some_variable,-1);


    Writing this way, my select never use the function-index index.

    SQL> set autotrace traceonly;
    SQL> select *
    2 from t
    3 where nvl(x,-1) = nvl(null,-1);

    103 rows selected.


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 TABLE ACCESS (FULL) OF 'T'



    scenario 1: all record have x record null;
    select *
    from t
    where nvl(x,-1) = nvl(null,-1);

    and

    select *
    from t
    where nvl(x,-1) = nvl(100,-1);

    no index use

    scenario 2: all record are null exept 1 record that the value is 100

    select *
    from t
    where nvl(x,-1) = nvl(null,-1);

    and

    select *
    from t
    where nvl(x,-1) = nvl(100,-1);

    no index use


    Even if i put a hint, it does not use the index.

    What do i do wrong?

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    You have to read why my index not used doc from metalink/tom kyte site. Index will not be use on NULL columns. Oracle only use index if there is enough volume of data and stats for optimizer etc.,
    Reddy,Sam

  3. #3
    Join Date
    Nov 2000
    Posts
    440
    I tried tons of example, could someone show me an example that works.

    i did this:

    create table t
    as
    select * from all_users;


    alter table t add x number;

    update t set x = 1;
    update t set x = 2 where username = 'SYS';
    update t set x = NULL where username = 'SYSTEM';

    SQL> select * from t where nvl(x,-1) = nvl(1,-1);
    103 rows selected.

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 TABLE ACCESS (FULL) OF 'T'


    SQL> select * from t where nvl(x,-1) = nvl(2,-1);

    USERNAME USER_ID CREATED X
    ------------------------------ ---------- --------- ----------
    SYS 0 28-FEB-01 2

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 TABLE ACCESS (FULL) OF 'T'



    SQL> select * from t where nvl(x,-1) = nvl(NULL,-1);

    USERNAME USER_ID CREATED X
    ------------------------------ ---------- --------- ----------
    SYSTEM 5 28-FEB-01


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 TABLE ACCESS (FULL) OF 'T'

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    is the index analyzed?
    does your user have "query rewrite" system privilege granted (not through a role)?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by sreddy
    Index will not be use on NULL columns.
    Well, that's (generaly speaking) not true, not even for normal B*Tree indexes and even less for function based indexes.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Apr 2003
    Posts
    8
    hi,

    As far as I know,
    Function based index only works with Cost based Optimizer and you have to set one init.ora parameter QUERY_REWRITE_ENABLE to TRUE. But even though You do it, There is no guarantee that Oracle Optimizer will definately use FBI (Function based Index) , according to their documentation.

    Thanks,

    Bhavin.

    ---------------

  7. #7
    Join Date
    Nov 2000
    Posts
    440
    i have cbo but not query_rewrite_enabled set to true, i did change it to true and IT WORKS!!!

    THANKS MY QUERY USE THE FUNCTION INDEX!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width