SQL problem

Came across this SQL problem in Eddie Awad’s blog, where he gives a solution to a problem posted to the Oracle-l mailing list.

I thought, this would be so much easier to solve if we were allowed to model the complete problem domain in the database, e.g.:

— create the data set

SQL> create table t (id number);
Table created.

SQL> insert into t values (1);
1 row created.

SQL> insert into t values (2);
1 row created.

SQL> select * from t;
ID
———-
1
2

— create the query set

SQL> create table u (id number);
Table created.

SQL> insert into u values (1);
1 row created.

SQL> insert into u values (2);
1 row created.

SQL> insert into u values (3);
1 row created.

SQL> select * from u;
ID
———-
1
2
3

SQL> select * from t
2 where id in (select id from u)
3 and not exists (select id from u minus select id from t);
no rows selected

— correct: the complete query set was not found in the data set

SQL> insert into t values (3);
1 row created.

SQL> select * from t
2 where id in (select id from u)
3 and not exists (select id from u minus select id from t);
ID
———-
1
2
3

— correct: the complete query set was found in the data set

The query could be further improved by changing the “where id in ()” to a “where exists ()”.

To make the query table usable by multiple sessions simultaneously, it can be created as a global temporary table (and populated whenever a query is needed), or a “group” distinguisher could be added to it.


Instead-of-Delete trigger problem

In software development, quite often the problem you encounter has virtually nothing to do with the thing you’re trying to do.

I’m creating some views which will provide an interface to a database. The views will have Instead-of triggers so that all DML will be passed to my own (packaged) procedures.

I’ve got a particular view which is a join between a base table, filtered by some criteria, and a code/description mapping view. I’ve implemented instead-of-insert, update and delete triggers that do the appropriate DML on the base table.

Inserts and updates work fine, but when deleting from the view I’m getting “TM contention” wait events. When deleting across a database link the session gets “ORA-02049: timeout: distributed transaction waiting for lock” after a long delay. If the delete is issued within the same database as the view, the session waits indefinitely for the lock.

I had a look at the view and noticed that it is a key-preserved view, so I theorised that maybe the session was locking the row before running my instead-of-delete trigger that does the delete; the trigger tries to delete the row that has already been locked. Doesn’t make sense. When I remove the instead-of-delete trigger however, I get “ORA-01752: cannot delete from view without exactly one key-preserved table”. So I need the trigger to handle the delete.

Looked up the Oracle doc “Instance Tuning Using Performance Views” about “TM enqueue”: “The most common reason for waits on TM locks tend to involve foreign key constraints where the constrained columns are not indexed. Index the foreign key columns to avoid this problem.” I saw this ref come up in a search early on but I initially dismissed it because my problem wasn’t related to performance. I’m glad I took a second look.

Righty-o. Looked at the table I was deleting from, and lo and behold there is another table with a fk to my table with no index on the fk column (mind you, there was an index that contained that column along with another column, obviously the database wasn’t going to use that to avoid the table lock).

When I created an index on the fk column, the problem was resolved. Brilliant!

The application never needed to delete from that table before, so this had never been encountered. In this case adding the index solved the problem. It’s on a very small, rarely-used table so the index won’t have a negative impact. Took about half an hour to work out, but I learned a bit more about Oracle, so that’s good.


Upgraded to HTMLDB 2.0

Upgraded from 1.6. I’ve already done some playing on 2.0 on htmldb.oracle.com, already liked the little interface and management improvements.

I’m not too interested in all the bells and whistles – I just want to see first if I can produce usable maintenance forms for my preferred flavour of tables. I’ve been using the wizards to create all my pages, but have come across problems whenever the tables use natural PKs (specifically, when I try to make master-detail pages) – so far most problems have been resolved by adding a PK populated by a sequence from a trigger. Not what I really want to do; I’m hoping that I can find a way to get HTMLDB to work with natural-pk tables.

The main disadvantage to using surrogate keys I’ve found is practical – apart from the obvious one that I have to ensure that a unique constraint is on the natural key (just have to be careful to do it) – but then these surrogate keys get propogated throughout my design (especially when I have modelled multiple inheritance with table-per-subclass, which is my personal preference) and writing queries and certain types of constraints can become tricky.

So far my impression of HTMLDB is that it is a nice simple product – but hopefully not so simplistic as that it requires surrogate keys everywhere?

Another problem that I’ll investigate is HTMLDB’s behaviour in regards to checking for record changes prior to updates; I’ve gotten the “can’t update because the hash values differ” error quite often when I’ve based a page on an updateable view. I use updateable views a lot as a way of hiding the database complexity from the application, and I don’t want to have to (a) denormalise my design just to suit HTMLDB, or (b) try to design HTMLDB to work with each entity separately.


Experimenting with Oracle

I’ll post here results from some experiments I’m doing with Oracle. I want to explore the implications and possible benefits of mapping a truly relational logical database model directly to the physical model. In other words:

  1. Every entity mapped to a “base table”
  2. Every base table has one or more Unique keys (one arbitrarily chosen as “Primary”)
  3. No surrogate keys, except where the model doesn’t include any natural key
  4. All columns mandatory
  5. Application interfaces via fully updateable views (materialized or not)

In particular, I would like to determine how (if possible) to implement various types of table and database constraints apart from the standard PK and FK constraints.

Initial experiments indicate most of this is very possible, and with careful coding can be made to work. In fact, I’m sure some advantages over common practice will be demonstrated.

I’m doing all this on Oracle 10g Release 2 which I downloaded recently. I’ve installed it on my P4 with XP Home. For the frontend I’m using HtmlDB 1.6, although I’ll probably upgrade to HtmlDB 2.0 soon.

I should note that I don’t seriously expect this approach to have enough merit to be used in real projects – but this is not because of a lack in the relational model of data but because of the poor support of it by the SQL standard (and therefore, by DBMS’s that implement it). Therefore, I expect that to get all this to work I’ll have to write a lot of code very carefully, but that the resulting database will be very easy to write applications and queries for. For example, implementing all constraints on the database for a particular entity means that I don’t need complicated constraint checking code in every module that works with that entity.

I expect I may have to wrestle with row locking and handling constraint violations correctly in the presence of multiple users/sessions; I don’t want to have to enforce serialized access to get this to work because of the scalability problem.

I’ve found it interesting so far, anyway.