Constraints and performance: you want as many constraints as possible

Several times I have had to deal with people who do not want to define constraints. I have never understood why they don't, because my experience is that the more constraints you can define, the better Oracle will perform. If anyone knows where the idea that not defining constraints is a Good Thing comes from, I would be interested to know.

Following are two very simple examples of constraints allowing the optimizer to develop better plans.

First, foreign key constraints. These give the optimizer a lot of information about the data that may mean it can cut out whole tables from a query. Try this:

Obvious, isn't it? The constraint meant that Oracle did not have to touch the table at all, because every row must have an entry in the index.

So, to conclude, these very simple examples demonstrate the general case: always define as many constraints as you can, to give the optimizer the information it needs to develop efficient execution plans.

I think putting in foreign key constraints will slow down the DML's. In case of staging tables where data is inserted and deleted intensively the strategy of putting in constraints may not be useful. We had a situation like this recently where the performance of a screen used for uploading records into the staging table through webutil performed badly since the foreign keys were put with the actual base tables. In this case it's better to leave the task of validations to the batch processes rather than freezing up the screen for long. Please let me know your views.