Referential and Function based index

[ganga]

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

[DaPi]

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.