Re: Select from dual return 3 rows !
- From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
- To: <mag.andersen@xxxxxxxxx>, <ineyman@xxxxxxxxxxxxxx>
- Date: Wed, 9 Nov 2005 10:34:34 +0100
And there was a reply that I report.
====================================================================================================================
Two Suggestions for Dual
Regarding the article "Self-Managing PL/SQL" by Steven Feuerstein in the
May/June 2004 issue of Oracle Magazine, I wanted to raise two points, both
regarding the DUAL table. Might I suggest that a user create his or her own
DUAL table as an index-organized table (IOT), rather than a heap table? This
will reduce logical I/Os significantly. If you go with an IOT and define it
with the following DDL-
create table mydual(dummy varchar2(1) primary key constraint one_row
check(dummy='X')) organization index;
-you can avoid the overhead of a trigger and still protect the table from
having more than one row. Note that the primary key protects against more than
one row containing 'X' and the check constraint protects against any rows
containing something other than 'X', which results in exactly one row
containing 'X'.
Second, the idea to create the NEXT_PKY function utilizes EXECUTE IMMEDIATE.
While I agree that there's no better way to do it unless you want to write an
absurd amount of code, it should be noted that this would cause a PARSE call on
each execution that cannot be avoided. Again, because sequence generators can
be heavily used pieces of code, the impact of the extra parsing should be
considered on highly concurrent systems.
Mark J. Bobak
mark@xxxxxxxxx
Thanks for raising these very interesting and valid points about my suggestion
for replacing the DUAL table. I hope Oracle Magazine readers will put them to
use wherever appropriate.
Steven Feuerstein
====================================================================================================================
Dimitre Radoulov
----- Original Message -----
From: Magnus Andersen
To: ineyman@xxxxxxxxxxxxxx
Cc: ORACLE-L
Sent: Tuesday, November 08, 2005 10:22 PM
Subject: Re: Select from dual return 3 rows !
In the may issue of Oracle Magazine, Steven Feuerstein has an article of how
to write self managing PL/SQL. In the article he recommends to not use the
dual table but to actually create your own. You can find the article here:
http://www.oracle.com/technology/oramag/oracle/04-may/o34tech_plsql.html
and here is how he suggests that you can do it.
Code Listing 5: Creating a DUAL-like table
1 CREATE TABLE onerow (dummy VARCHAR2(1)) 2 / 3 GRANT SELECT ON onerow TO
PUBLIC 4 / 5 CREATE PUBLIC SYNONYM onerow FOR onerow 6 / 7 CREATE OR
REPLACE TRIGGER enforce_onerow 8 BEFORE INSERT
9 ON onerow10 DECLARE11 PRAGMA AUTONOMOUS_TRANSACTION;12 l_count
PLS_INTEGER;13 BEGIN14 SELECT COUNT (*)15 INTO l_count16 FROM
onerow;17 18 IF l_count = 1
19 THEN20 raise_application_error21 (-2000022
, 'The onerow table can have only one row.'23
);24 END IF;25 END;
26 /
HTH,
--
Magnus Andersen
Systems Administrator / Oracle DBA
Walker & Associates, Inc.
Other related posts:
