Re: Dual - multiple rows

From: Norris <johnnie_at_cooper.com.hk>
Date: 7 Sep 1999 01:40:13 GMT
Message-ID: <7r1qdt$on9$1@imsp009a.netvigator.com>

Thomas Kyte <tkyte_at_us.oracle.com> wrote:

: A copy of this was sent to Norris <johnnie_at_cooper.com.hk>
: (if that email address didn't require changing)
: On 6 Sep 1999 13:37:30 GMT, you wrote:

:>May I know if there is any performance difference if I do not the SYS.DUAL
:>and use my own dummy table?
:>

: if you create your own application specific single row table -- it'll be 100% : like dual.

So, in the following statements, statement 1 would be faster than statement 2. Am I right?

declare
x date;
begin

x:=sysdate;				-- statement 1
select sysdate into x from dual;	-- statement 2

end;

:>By the way, can I revoke the insert and delete rights on SYS.DUAL
:>from all users to set the table readonly?
:>

: your dba's have lots of "ANY TABLE" (eg: select any table, insert any table) : type of privs. it will not affect people with powerful privs.

: normal users only have select on dual so normally they will not be able to
: insert/delete from dual, only select it.  its the people with the power that are
: the problem.

:>Thomas Kyte <tkyte_at_us.oracle.com> wrote:
:>: A copy of this was sent to Norris <johnnie_at_cooper.com.hk>
:>: (if that email address didn't require changing)
:>: On 6 Sep 1999 03:40:31 GMT, you wrote:
:>
:>:>Thomas Kyte <tkyte_at_us.oracle.com> wrote:
:>:>: A copy of this was sent to Norris <johnnie_at_cooper.com.hk>
:>:>: (if that email address didn't require changing)
:>:>: On 4 Sep 1999 03:53:13 GMT, you wrote:
:>:>
:>
:>: [snip]
:>
:>:>
:>:>: It will also put an index on DUAL which *may* mess up query plans in some
:>:>: unforseen way (so it is not recommended).
:>:>
:>:>If a table has only one record with unique index, I think most RDBMS query
:>:>optimizer wouldn't use that index in the query plan unless we have
:>:>special instruction.
:>:>
:>
:>: as I said "some unforseen way". I haven't done it. I haven't tested it. I
:>: have no idea what sort of side effects it might have here and there with
:>: different releases. Rather then putting the constraints on dual (unsupported
:>: would probably be the quote support would use) -- I'd rather watch my DBA's.
:>
:>:>
:>:>: The best way to ensure dual has 1 row is to keep control of who has DBA and make
:>:>: sure they are competent for the job.
:>:>
:>:>Can we add audit trail on the dual so that we can know which DBA have
:>:>modified the dual?
:>:>
:>
:>: well, as long as your dba's dont use INTERNAL or SYS (which are never audited)
:>: and each have their own userids, this would probably work.
:>
:>:>
:>:>:>
:>
:>
:>: --
:>: See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
:>: Current article is "Part I of V, Autonomous Transactions" updated June 21'st
:>:
:>: Thomas Kyte tkyte_at_us.oracle.com
:>: Oracle Service Industries Reston, VA USA
:>
:>: Opinions are mine and do not necessarily reflect those of Oracle Corporation
:>
:>--

: -- 
: See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
: Current article is "Part I of V, Autonomous Transactions" updated June 21'st
:  
: Thomas Kyte                   tkyte_at_us.oracle.com
: Oracle Service Industries     Reston, VA   USA

: Opinions are mine and do not necessarily reflect those of Oracle Corporation

--
--
http://www.washington.edu/pine/faq/ Received on Mon Sep 06 1999 - 20:40:13 CDT