RE: Re[2]: Sequences in OPS/RAC

The problem is that the ORDER clause comes at the expense of CACHE. You can use SQL tracing to verify that each use of the sequence causes an update of SYS.SEQ$ when ORDER is set, effectively rendering the CACHE setting a no-op. So, especially in an OPS/RAC environment, the use of ORDERED sequences, especially heavily used ORDERED sequences, comes at a steep price.

Think about it: is ORDERED *really* necessary? In some situations (i.e. check numbers), the ORDERED clause would be necessary, but unless you are pumping out thousands of checks an hour, perhaps a cached sequence shouldn't be used.  But for system-generated keys, surrogate keys, etc, I don't think the semantics of ORDERED are necessary at all.

> Hi,
>
> I have RAC and I always use ORDER when I create SEQUENCE.
> The following information is from Oracle Manual:
> ORDER is necessary only to guarantee ordered generation if
> you are using Oracle with Real Application Clusters. If
> you are using exclusive mode, sequence numbers are always
> generated in order.
> Muqthar Ahmed
>
> -----Original Message-----
> Sent: Monday, November 03, 2003 12:04 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hello Hemant,
>
> Monday, November 3, 2003, 11:29:26 AM, you wrote:
> HKC> However, the Builder.Com article quite explicity
> asserts HKC> "Sequence generator numbers are guaranteed to
> be unique only for a single HKC> instance, which is
> unsuitable for use as a primary key in parallel or HKC>
> remote environments, where a sequence in each environment
> might generate HKC> the same number and result in
> conflicts
> Can you point us to the article? My guess is that the
> author is not familiar with Oracle, and is basing the
> above statement on his experience with some other database
> (DB2 perhaps?). There is no problem with using sequence
> numbers in a RAC. No conflicts will occur. I've never
> heard of a problem in that regard.
>
> Best regards,
>
> Jonathan Gennick --- Brighten the corner where you are
> http://Gennick.com * 906.387.1698 *
> mailto:jonathan_at_gennick.com
> Join the Oracle-article list and receive one
> article on Oracle technologies per month by
> email. To join, visit
> http://four.pairlist.net/mailman/listinfo/oracle-article,
> or send email to Oracle-article-request_at_gennick.com and
> include the word "subscribe" in either the subject or
> body.
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net --
> Author: Jonathan Gennick
> INET: jonathan_at_gennick.com
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com San Diego, California --
> Mailing list and web hosting services
> ----------------------------------------------------------
> ----------- To REMOVE yourself from this mailing list,
> send an E-Mail message to: ListGuru_at_fatcity.com (note
> EXACT spelling of 'ListGuru') and in the message BODY,
> include a line containing: UNSUB ORACLE-L (or the name of
> mailing list you want to be removed from). You may also
> send the HELP command for other information (like
> subscribing). --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net --
> Author: Muqthar Ahmed
> INET: Muqthar.Ahmed_at_decoratetoday.com
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com San Diego, California --
> Mailing list and web hosting services
> ----------------------------------------------------------
> ----------- To REMOVE yourself from this mailing list,
> send an E-Mail message to: ListGuru_at_fatcity.com (note
> EXACT spelling of 'ListGuru') and in the message BODY,
> include a line containing: UNSUB ORACLE-L (or the name of
> mailing list you want to be removed from). You may also
> send the HELP command for other information (like
> subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: tim_at_sagelogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).