Referential and Function based index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Referential and Function based index

  1. #1
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    182

    Question Referential and Function based index

    Hi friends ,,

    I have two Questions/cooments .. both question looks fussy to me request you to convince/guide me on what situations below two scenarios are both for database performance.

    1) On Oracle 8 database whenever we create a referential integrity key on a particular column of the table with reference on the column in another table, we should create an index on this column in order to optimise the data insertion/deletion. This is necessary if we plan a significant number of insert / delete / update operations on the table and column affected. This is done automatically by Oracle 9, but not by Oracle 8



    2) In our SPACe code in a number of places we use UPPER or TRIM functions in the WHERE clauses. This causes significant degradation of the performance unless functional indexes exist on these expressions. According to Stanislas we sometimes use UPPER function for the column, where all the data by design is entered in upper case and utilisation of UPPER function can be omitted. In the other cases if UPPER is really needed we need to make sure that the relevant functional index exists. For TRIM it is necessary to check if the data in the table really needs to be trimmed or it is just a double-check measure, which can be avoided if specific assumptions are made and agreed with Mondial on the content of the data.



    Please review these requirements
    J Gangadhar

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    1) The impact (table locking) is on deletes in the parent table or updates of the column in the parent table that is referenced. (I don't know of any special impact on inserts in the parent table.) The locking is automatic in both 8i & 9i, but is of much shorter duration in 9i. Even so, I've seen T.Kyte recommend an index on the column(s) of the child table in 9i if these deletes & updates of the parent are at all frequent.


    2) The TRIM may be necessary due a mix of CHAR() and VARCHAR2() columns - best not to use CHAR() if possible.

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