... "Conditional Function based Index has performance Issues :-
...
tell him "so what?"
I mean, come on - a unique index on column X has performance issues.
INSERT has performance issues.
SELECTS - have performance issues.
Everything has a performance impact.
Ask this DBA how badly the application will perform due to the LOCK TABLE command it would have to do to implement this rule safely??? I mean, if that DBA is really truly saying "implement a unique constraint in your application code" - then they have missed the proverbial boat.
It will affect the modifications (DML operations by the way include SELECT) performance - sure, that is true.
However, it will affect their performance by some small percentage when compared to DOING IT YOURSELF.
This makes me so sad - this DBA cannot see the forest for the trees.
You have a data rule you need to enforce.
You can enforce it right in the database.
You could attempt to write code to do it.
Guess which one will be faster? Guess...
Who cares if the selects will not use it? It won't hurt them at all and it will make the modifications MUCH FASTER than if you tried to do this in code (ask the DBA - how will we do this in code - we'll need TWO MORE INDEXES to make it efficient - one on
rte_nb_tx, acct_nb_)tx, lockbox_dda_id
and one on
rmit_nm, rmit_cust_id_tx, localbox_dda_id
and those indexes will be HUGE - with as many entries as the table probably. Whereas your function based index will only index rows where one of those two columns is NULL.
In order to efficiently see if someone else has inserted that data yet - and that is AFTER we have to apply a lock table command - since reads don't block writes and writes don't block reads.
There you go - if you do it yourself - you need:
a) to execute the functions anyway - your procedural code would have to do the is null checking (so you haven't avoided the functions)
b) to lock the table
c) have TWO indexes
d) make sure everyone always uses your package to insert or update - forever, no table access ever - since the rules are not in the database
If you use the function based index (which would be smart) - you need:
a) to execute a function during insert or update of the affected column(s) and maintain a single index
Ask him "so how will your do it yourself approach be FASTER again???"
I don't know your data, but you would want to code like one of the two following:
CREATE UNIQUE INDEX TESTCONDLUNIQUECONSTRAINT ON REMITTER
(
CASE WHEN ("RTE_NB_TX" IS NOT NULL AND "ACCT_NB_TX" IS NOT NULL)
THEN "RTE_NB_TX"
WHEN ("RTE_NB_TX" IS NULL OR "ACCT_NB_TX" IS NULL)
THEN "RMIT_NM"
END ,
CASE WHEN ("RTE_NB_TX" IS NOT NULL AND "ACCT_NB_TX" IS NOT NULL)
THEN "ACCT_NB_TX"
WHEN ("RTE_NB_TX" IS NULL OR "ACCT_NB_TX" IS NULL)
THEN "RMIT_CUST_ID_TX"
END ,
CASE WHEN ("RTE_NB_TX" IS NOT NULL AND "ACCT_NB_TX" IS NOT NULL)
THEN "LOCKBOX_DDA_ID"
WHEN ("RTE_NB_TX" IS NULL OR "ACCT_NB_TX" IS NULL)
THEN "LOCKBOX_DDA_ID"
END
)
CREATE UNIQUE INDEX TESTCONDLUNIQUECONSTRAINT ON REMITTER
(CASE WHEN ("RTE_NB_TX" IS NOT NULL AND "ACCT_NB_TX" IS NOT NULL)
THEN "RTE_NB_TX"||'/'||"ACCT_NB_TX"||'/'||TO_CHAR("LOCKBOX_DDA_ID")
WHEN ("RTE_NB_TX" IS NULL OR "ACCT_NB_TX" IS NULL)
THEN "RMIT_NM"||'/'||"RMIT_CUST_ID_TX"||'/'||TO_CHAR("LOCKBOX_DDA_ID")
END you have to make sure that the columns don't 'run into themselves'
eg: if rmit_nm = 'AA' and RMIT_CUST_ID_TX = 'B'
and some other:
rmit_nm = 'A' and RMIT_CUST_ID_TX = 'AB'
then they would appear "equal" unless you delimit them.