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
User-named locks with DBMS_LOCK

Leave a Reply

Your email address will not be published / Required fields are marked *