Function Based Indexes, Please Help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Function Based Indexes, Please Help

  1. #1
    Join Date
    Sep 2001
    Posts
    34
    Hi Guys,

    We have created function based indexes and in the queries the FBI is not getting used, unless specified with a hint. The init.ora parameters QUERY_REWRITE_ENABLED = TRUE & QUERY_REWRITE_INTEGRITY = TRUSTED is set. Can someone tell me what else needs to be done.

    Thanks in Advance.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    2 How to create a function based index

    Step 1: Set QUERY_REWRITE_ENABLED = TRUE
    QUERY_REWRITE_INTEGRITY = TRUSTED
    TIMED_STATISTICS = TRUE (Optional)

    Step 2: Create TABLE TAMIL_TEST ( id number , name varchar2(20),
    state varchar2(2)) ;

    Step 3 Populate 1 Million rows data in the table. The simple way is to use PL/SQL.
    FOR K IN 1..1000000 LOOP
    INSERT INTO TAMIL_TEST
    VALUES ( K, ‘KANNAN’||TO_CHAR(K), ‘GA’);
    END LOOP;
    COMMIT;
    END;
    /
    Step 4 Update the state column with null value for some of the rows.
    UPDATE TAMIL_TEST SET STATE = NULL
    WHERE ID BETWEEN 800000 AND 800200 ;
    COMMIT;

    Step 5 CREATE INDEX TEST_IDX ON TAMIL_TEST NVL(STATE,’X’) TABLESPACE USERS ;

    Step 6 Analyze table:
    ANALYZE TABLE TAMIL_TEST COMPUTE STATISTICS
    FOR TABLE
    FOR ALL INDEXED COLUMNS
    FOR ALL INDEXES;
    Note: Analyzing table must be done after the index is created, otherwise function-based index will not work.

    Step 7 Set autotrace on with explain
    Run a query:
    Select * from tamil_test where nvl(state,’x’) = ‘x’ ;
    The explain plan statement will show that the index is used.

  3. #3
    Join Date
    Sep 2001
    Posts
    34
    Hi Tamilselvan,

    Thanks for your reply. I already created the function based indexes and anlyzed the tables. but in the select statement it is not using the function based indexes, instead if I use a index hint it uses the function based indexes. Am I doing something wrong.

    Thanks.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The problem with Function based index is :
    You must FIRST create an index and then analyze the table as shown above, otherwise the index will not be used.

  5. #5
    Join Date
    Sep 2001
    Posts
    34
    Hi Tamilselvan,

    I created the Index then analyzed the table. I have multiple databases, one on w2k which is using function based indexes. second one on Solaris which is not using function based indexes. What I found is when FIRST_ROWS pr COST hint is given in the queery it is using the index.

    Thanks,

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