Oracle Performance – Using SSDs with Oracle

Mostly just for my own amusement, you can do this with a materialized view which has a unique index:

CREATE MATERIALIZED VIEW LOG ON COMPANY
WITH PRIMARY KEY, ROWID
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON DEPARTMENT
WITH PRIMARY KEY, ROWID (company_id)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON EMPLOYEE
WITH PRIMARY KEY, ROWID (department_id, username)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW xcheck_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT c.id as company_id, lower(e.username) as username,
    c.rowid as c_rowid, d.rowid as d_rowid, e.rowid as e_rowid
from company c, department d, employee e
where d.company_id = c.id
and e.department_id = d.id;

CREATE UNIQUE INDEX xcheck_ind ON xcheck_mv(company_id, username);

Then attempting to insert an employee record for a different department with the same username gives a unique constraint violation, although not until you commit:

insert into company (id) values(1);

1 row created.

SQL> insert into department (id, company_id) values(1, 1);

1 row created.

SQL> insert into department (id, company_id) values(2, 1);

1 row created.

SQL> insert into employee (id, department_id, username) values(1,1,'Joe Bloggs');

1 row created.

SQL> insert into employee (id, department_id, username) values(2,2,'Joe Bloggs');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (STACKOVERFLOW.XCHECK_IND) violated

Just for fun I put lower() in the MV definition to catch the most basic workaround - so trying to insert 'joe bloggs' also fails - but this model is never going to be very robust.

I'm not saying this is a good idea, just that it's possible...

First, are you really sure that you want to be starting out with a desupported version of the database that is at least 4 major releases out of date? It would seem much more useful to download a more recent version of Oracle from the Oracle Technology Network (OTN) and to start with that. You can get the express edition of Oracle 11.2 completely free and the enterprise edition is free if you just want to learn the product.

Second, when you installed Oracle, it should have prompted you to create a database as part of the installation process. If you chose not to create a database during the installation process, you should run the Database Creation Assistant (DBCA) to create a database now. Note that what lots of products refer to as a "database" is more like a schema in Oracle. In general, you would only have one Oracle database on your machine though that database may have multiple schemas.

What operating system are you using?