Re: Select from dual return 3 rows !

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Wed, 9 Nov 2005 10:34:34 +0100
Message-ID: <013401c5e510$ccf0ae90$1a03310a@IBME1D11967173>

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_at_bobak.net

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_at_perceptron.com 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.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 09 2005 - 03:36:47 CST