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-
-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@(protected)
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
-- -- Original Message -- -- From: Magnus Andersen To: ineyman@(protected) 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:
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. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1"> <META content="MSHTML 6.00.2900.2769" name=GENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=#ffffff> <DIV><FONT face="Courier New" size=2>And there was a reply that I report.</FONT></DIV> <DIV><FONT face="Courier New" size=2></FONT> </DIV> <DIV><FONT face="Courier New" size=2>======================================================================== ============================================</FONT></DIV> <DIV><FONT face="Courier New" size=2></FONT> </DIV> <DIV><FONT face="Courier New" size=2>Two Suggestions for Dual </FONT></DIV> <DIV><FONT face="Courier New"></FONT> </DIV> <DIV><FONT face="Courier New" size=2>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� </FONT></DIV> <DIV><FONT face="Courier New"></FONT> </DIV><FONT size=2> <DIV><FONT face=Arial></FONT><BR><FONT face="Courier New">create table mydual(dummy varchar2(1) primary key constraint one_row check(dummy='X')) organization index; </FONT></DIV> <DIV><FONT face="Courier New"></FONT> </DIV> <DIV><BR><FONT face="Courier New">�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'. </FONT></DIV> <DIV><FONT face="Courier New"></FONT> </DIV> <DIV><FONT face="Courier New">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.<BR>Mark J. Bobak<BR></FONT><A href="mailto:mark@(protected)"><FONT face="Courier New">mark@(protected)</FONT></A><FONT face="Courier New"> </FONT></DIV> <DIV><FONT face="Courier New"></FONT> </DIV> <DIV><FONT face="Courier New">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.<BR>Steven Feuerstein</FONT></FONT></DIV> <DIV><FONT face="Courier New"></FONT> </DIV> <DIV><FONT face="Courier New" size=2>======================================================================== ============================================</FONT></DIV> <DIV><FONT face="Courier New" size=2></FONT> </DIV> <DIV><FONT face="Courier New" size=2></FONT> </DIV> <DIV><BR><FONT face="Courier New" size=2>Dimitre Radoulov</FONT></DIV> <DIV><FONT face="Courier New"></FONT> </DIV> <DIV><FONT face="Courier New"></FONT> </DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV> </DIV> <BLOCKQUOTE style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> <DIV style="FONT: 10pt arial">-- -- Original Message -- -- </DIV> <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B> <A title=mag.andersen@(protected) href="mailto:mag.andersen@(protected)">Magnus Andersen</A> </DIV> <DIV style="FONT: 10pt arial"><B>To:</B> <A title=ineyman@(protected) href="mailto:ineyman@(protected)">ineyman@(protected)</A> </DIV> <DIV style="FONT: 10pt arial"><B>Cc:</B> <A title=oracle-l@(protected) href="mailto:oracle-l@(protected)">ORACLE-L</A> </DIV> <DIV style="FONT: 10pt arial"><B>Sent:</B> Tuesday, November 08, 2005 10:22 PM</DIV> <DIV style="FONT: 10pt arial"><B>Subject:</B> Re: Select from dual return 3 rows !</DIV> <DIV><BR></DIV>In the may issue of Oracle Magazine, Steven <SPAN class=italicbodycopy>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:<BR><BR><A href="http://www.oracle.com/technology/oramag/oracle/04-may/o34tech_plsql .html">http://www.oracle.com/technology/oramag/oracle/04-may/o34tech_plsql.html< /A><BR><BR>and here is how he suggests that you can do it.<BR><BR></SPAN><SPAN class=bodycopy>Code Listing 5: Creating a DUAL-like table</SPAN> <P></P><PRE> 1 CREATE TABLE onerow (dummy VARCHAR2(1))<BR> 2 /<BR> 3 GRANT SELECT ON onerow TO PUBLIC<BR> 4 /<BR> 5 CREATE PUBLIC SYNONYM onerow FOR onerow<BR> 6 /<BR> 7 CREATE OR REPLACE TRIGGER enforce_onerow<BR> 8 BEFORE INSERT <BR> 9 ON onerow<BR>10 DECLARE<BR>11 PRAGMA AUTONOMOUS_TRANSACTION; <BR>12 l_count PLS_INTEGER;<BR>13 BEGIN<BR>14 SELECT COUNT (*)<BR>15 INTO l_count<BR>16 FROM onerow;<BR>17 <BR>18 IF l_count = 1 <BR>19 THEN<BR>20 raise_application_error<BR>21 (-20000<BR>22 , 'The onerow table can have only one row .'<BR>23 );<BR>24 END IF;<BR>25 END; <BR>26 /<BR></PRE><BR>HTH,<BR>-- <BR>Magnus Andersen<BR>Systems Administrator / Oracle DBA<BR>Walker & Associates, Inc. </BLOCKQUOTE></BODY></HTML>
