Oracle 9i R2. Which version are you using that it didn't work for you? A
version from the '80s?
The last one I have used extensively was 8i. I know that a lot has
changed since then (for the better!). I believe that 8i was released
in the early 90's.
No, the record update, insert or delete can be made on the table and the
check constraint will still be executed unless the constraint is deferred
beforehand.
This seems like a recipe for unpleasant surprises when one developer
creates a view with a check constraint and another developer writing
procedures knows nothing about the view and its new constraints. IMHO,
views shouldn't have this kind of impact on the database design.
Hon, whoever's been telling you this has a head filled with sawdust. Oracle
is *made* for performance and can easily handle terabytes of data *very*
quickly, even in views.
Thinking back to the case in mind, it was a view that used "instead
of" triggers to perform DML on the underlying tables which were not
directly updatable due to security concerns. Some of the client
applications needed to do it this way; the stored procedures I was
writing should have been using an API implemented in PL/SQL by the
DBA's, but some of my code written early in the testing phase used the
views and somehow never got converted until everything was already in
production. Fortunately, these were batch jobs that only ran at night,
so I had time to correct it during the day. Wasn't really a major
issue ... it was actually running fast enough until additional indexes
had to be added for some of the client applications. Then there was a
noticeable difference between using the view and using the API.
If your views are slow, the DBA didn't design the schemas and tablespaces
correctly. Even if there are millions of rows in each table, in a cluster
index data from the multiple tables in the view are stored in the same data
block, meaning one disk I/O to retrieve records in an Oracle view with 4
joined tables would take 4 or more disk I/Os to retrieve the same records in
a database server that doesn't support this feature, like SQL Server, because
data in different tables is stored in different data blocks in SQL Server.
Oracle is built for performance and reliability and does both *extremely*
well, but even the high end database engines with the fastest hardware can't
completely overcome poorly designed databases and views. If you're
complaining Oracle is slow, something is really, really wrong.
I'm not complaining that Oracle is slow ... far from it! And what you
say about proper design is so true. However, it isn't always that
simple to design correctly, and requirements will change during the
lifetime of an application. For example, tables need to be filled with
data from external files. Do you want indexes? No ... only the bare
minimum of constraints to ensure RI. Later on in the game, the same
tables have millions of rows, and clients with views start complaining
that things are getting slow. So they add an index or two. But the
tables are still being filled with data! Now the developers of the
import routines complain that THEIR apps are slow as molasses, so the
DBA must tune the queries and adjust the indexes. Also, the query
engine migh suddenly decide to use a different index depending on the
cardinality of the data which can also change things drastically.
Hints need to be added to the SQL so that the correct indexes are used
.... tablespaces can get fragmented ... hard disks can fail ... it goes
on and on.
One thing I did learn during my Oracle experience is that there is a
reason why Oracle DBA's and senior developers get paid as much as they
do (if they are any good at it, that is).
