Home
Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups
General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement |
|
As Solution Using a
Check Constraint with NVL2 |
Far too often I find
developers and DBAs, especially those not trained in design and
architecture thinking horizontally rather than vertically.
I just recently completed a 6 month project at a company where the
basic application design was created in the 1970s by people with the
punchcard mentallity. Every new datum seemed to cry out to them for
another column. So a table like this was common. |
CREATE TABLE report_views (
report_id VARCHAR2(2),
view_name VARCHAR2(30),
daily_rpt VARCHAR2(1),
weekly_rpt VARCHAR2(1),
monthly_rpt VARCHAR2(1),
quarterly_rpt VARCHAR2(1),
annual_rpt VARCHAR2(1));
desc report_views |
Which made me want to
rm -rf the
whole thing. But professionalism sometimes means expressing one's
feelings with profound silence.
What made it far worse, of course, ws that each view could be used
with one and only one report so the problem became how to allow
this: |
INSERT INTO report_views
(report_id, view_name, daily_rpt)
VALUES
(1, 'IO_DAILY','X');
SELECT * FROM report_views; |
|
And prevent this: |
UPDATE
report_views
SET daily_rpt = 'X', weekly_rpt = 'X', annual_rpt = 'X';
SELECT * FROM report_views; |
A better design would, of course, have
used only three columns to accomplish the goal but this was a legacy
application and fixing the problem was not a politically acceptable
option.
So the issue become one of enforcing the business rule that a view
could be used in one, and only one, report.
The option that jumped out at everyone was to use a table trigger.
Something like this: |
CREATE OR
REPLACE TRIGGER awful_trigger
BEFORE INSERT OR UPDATE
ON report_views
FOR EACH ROW
DECLARE
x VARCHAR2(5);
BEGIN
IF LENGTH(:NEW.daily_rpt ||
:NEW.weekly_rpt
||
:NEW.monthly_rpt
||
:NEW.quarterly_rpt
||
:NEW.annual_rpt)
<> 1 THEN
RAISE_APPLICATION_ERROR(-20101, 'Too many flags');
END IF;
END awful_trigger;
/
TRUNCATE TABLE report_views;
INSERT INTO report_views
(report_id, view_name, daily_rpt)
VALUES
(1, 'IO_DAILY','X');
SELECT * FROM report_views;
UPDATE
report_views
SET quarterly_rpt = 'X';
SELECT * FROM report_views;
UPDATE report_views
SET daily_rpt = NULL, quarterly_rpt = 'X';
SELECT * FROM report_views; |
| Well it does work. But it is both
ugly and inefficient. Lets try it using a check constraint instead. |
DROP TRIGGER
awful_trigger;
ALTER TABLE report_views
ADD CONSTRAINT
cc_report_views_flags_unique
CHECK (NVL2(daily_rpt,1,0) +
NVL2(weekly_rpt,1,0) +
NVL2(monthly_rpt,1,0) +
NVL2(quarterly_rpt,1,0) +
NVL2(annual_rpt,1,0) = 1);
SELECT * FROM report_views;
UPDATE report_views
SET daily_rpt = 'X';
SELECT * FROM report_views;
UPDATE report_views
SET daily_rpt = 'X', quarterly_rpt = NULL;
SELECT * FROM report_views; |
| That is much better and
substantially faster. Lets look at the number doing transactions
that don't raise an exception. |
--
drop the check constraint
ALTER TABLE report_views DROP CONSTRAINT
cc_report_views_flags_unique;
-- rebuild the trigger
CREATE OR
REPLACE TRIGGER awful_trigger
BEFORE INSERT OR UPDATE
ON report_views
FOR EACH ROW
DECLARE
x VARCHAR2(5);
BEGIN
IF LENGTH(:NEW.daily_rpt ||
:NEW.weekly_rpt
||
:NEW.monthly_rpt
||
:NEW.quarterly_rpt
||
:NEW.annual_rpt)
<> 1 THEN
RAISE_APPLICATION_ERROR(-20101, 'Too many flags');
END IF;
END awful_trigger;
/
set timing on
BEGIN
FOR i IN 1 .. 100000 LOOP
UPDATE report_views
SET annual_rpt = NULL;
END LOOP;
END;
/
-- drop the trigger and recreate the check
constraint
DROP TRIGGER awful_trigger;
ALTER TABLE report_views
ADD CONSTRAINT cc_report_views_flags_unique
CHECK (NVL2(daily_rpt,1,0) + NVL2(weekly_rpt,1,0) +
NVL2(monthly_rpt,1,0) +
NVL2(quarterly_rpt,1,0) + NVL2(annual_rpt,1,0) = 1);
-- and run the test again
BEGIN
FOR i IN 1 .. 100000 LOOP
UPDATE report_views
SET annual_rpt = NULL;
END LOOP;
END;
/ |
The following are the result from five test runs
| |
Run 1 |
Run 2 |
Run 3 |
Run 4 |
Run 5 |
|
Trigger |
00:00:09.79 |
00:00:09.64 |
00:00:09.39 |
00:00:09.61 |
00:00:09:28 |
|
Check Constraint |
00:00:06.81 |
00:00:07.78 |
00:00:07.37 |
00:00:07.67 |
00:00:07.77 |
|
|
