DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: FUNCATION BASED INDEX

  1. #1
    Join Date
    Jun 2001
    Posts
    150
    DOES ANYONE KNOW BENEFITS OF FUNTION BASED INDEX
    CAN I USE INDEX WHERE
    NVL(COLUMN name)= value

    trunc(sysdate)=value

    what will be the effect if i pass bind variable in value and hard core value in value.

    Thanks
    UKDBA

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    U can't use nvl, sysdate ... functions directly, because its not deterministic.

    But u can create ur own deterministic function for it.
    For example:

    SQL> create index i_nvl_date on jobs(nvl(CLOSE_DATE, trunc(sysdate)));
    create index i_nvl_date on jobs(nvl(CLOSE_DATE, trunc(sysdate)))
    *
    ERROR at line 1:
    ORA-01743: only pure functions can be indexed
    ---------------------------------------------------
    create or replace function idx_sysdate(p_date date,
    def_date date default sysdate) return date
    DETERMINISTIC
    is
    begin
    if p_date is null then return trunc(def_date);
    else return p_date;
    end if;
    end;
    /
    Function created.

    SQL> create index i_nvl_date on jobs(idx_sysdate(CLOSE_DATE,'01-JAN-3001'));

    Index created.

    SQL> drop index i_nvl_date;

    Index dropped.

    SQL> create index i_nvl_date on jobs(idx_sysdate(CLOSE_DATE));

    Index created.


  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    And remember to collect histograms with FBIs in order to help the optimezer.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  4. #4
    Join Date
    Jun 2001
    Posts
    150
    i mean this
    Standard B-Tree index on SURNAME with cost based optimizer

    create index non_fbi on sale_contacts (surname);
    analyze index non_fbi compute statistics;
    analyze table sale_contacts compute statistics;
    SELECT count(*) FROM sale_contacts
    WHERE UPPER(surname) = 'ELLISON';

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=17)
    1 0 SORT (AGGREGATE)
    2 1 TABLE ACCESS (FULL) OF 'SALES_CONTACTS' (Cost=3 Card=16 Bytes=272)

    Now we use a function based index

    create index fbi on sale_contacts (UPPER(surname));
    analyze index fbi compute statistics;
    analyze table sale_contacts compute statistics;
    SELECT count(*) FROM sale_contacts WHERE UPPER(surname) = 'ELLISON';


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
    1 0 SORT (AGGREGATE)
    2 1 INDEX (RANGE SCAN) OF 'FBI' (NON-UNIQUE) (Cost=2 Card=381 Bytes=6477)

    you can use function in index creation in 8i.


  5. #5
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174

    can you use to_date function in index creation

    I get the same error:
    "ORA-01743: only pure functions can be indexed"

    when I try to do this:

    create index date_index
    on mytable(to_date(date_column, 'MM/DD/RR'))
    tablespace process_index;

    See, this "date_column" is not of "date" datatype, rather of varchar, and, it's not consistent...meaning sometimes it's '8/6/02' and sometimes it's '8/06/02'...so implicit conversion doesn't work, therefore I have to use the to_date function in my queries.

    I need to index this column becasue there's over 20 million rows in this table, but when I try to create a function based index with the above statement, I get the error.

    Help...any sugestions?

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The "RR" is non-deterministic because it's evaluation depends on sysdate.

  7. #7
    Join Date
    Jun 2012
    Posts
    1

    Good Use for Function Based Index

    You want fast access with a predicate that involves a function that checks for the first letter of the last name is 'A'. Here is the first query with the execution plan.

    set autotrace traceonly explain;

    select count(*) from emp_test where regexp_like( last_name, '^A' );



    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 982230031

    -------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 7 | 3040 (1)| 00:00:37 |
    | 1 | SORT AGGREGATE | | 1 | 7 | | |
    |* 2 | TABLE ACCESS FULL| EMP_TEST | 53500 | 365K| 3040 (1)| 00:00:37 |
    -------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - filter( REGEXP_LIKE ("LAST_NAME",'^A'))



    Without and index, a full table scan is performed. Here is a function and a function based index to improve the performance. I chose a bitmap index since the cardinality is 2, very low, either 0 or 1.

    create or replace function check_name( name in varchar2 ) return number
    DETERMINISTIC
    is
    begin
    if regexp_like( name, '[A]' ) then
    return 1;
    end if;
    return 0;
    end;
    /

    set autotrace off

    create bitmap index emp_test_ck_name_bmidx on emp_test( check_name( last_name ) );

    set autotrace on traceonly explain;

    select count(*) from emp_test where check_name( last_name ) = 1;

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1380382320

    ------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 13 | 17 (0)| 00:00:01 |
    | 1 | SORT AGGREGATE | | 1 | 13 | | |
    | 2 | BITMAP CONVERSION COUNT | | 10700 | 135K| 17 (0)| 00:00:01 |
    |* 3 | BITMAP INDEX SINGLE VALUE| EMP_TEST_CK_NAME_BMIDX | | | | |
    ------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    3 - access("TEST"."CHECK_NAME"("LAST_NAME")=1)


    Notice that the, very small, bitmap index is scanned and not the table.

    There you have it.

    David Bradford

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Thank you for the contribution David, just a couple of notes.

    1- Thread was going to be 10 years old on September 19th - now we will have to wait other 10 years for the big party!

    2- Function Based Indexes are primarily used to avoid predicate invalidation when there is no way of avoiding the use of a built-in-function e.g. truncate(), etc.

    3- Bitmap indexes are not designed to be used alone, bitmap indexes perform better when a number of them exist and are used on the predicate e.g. you want to select a RED car of the COMPACT type that has to have AUTOMATIC transmition and, has to have A/C - In this particular case four bitmap indexes would work together to return the few that comply with the search criteria.

    Hope this helps.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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